Snowflake: A Data Warehouse built for the Cloud

Adjust computing power to meet variable demands

Published in

Globant

5 min readDec 6, 2022

In this blog, we will see how to create a Snowflake Multi-Cluster Warehouse and its usage for processing Organization’s data optimally and efficiently.

Multi-Cluster Warehouse in Snowflake

Why do we need a Snowflake Multi-Cluster warehouse?

As in the banking domain industry, the data received is in million or sometimes trillions of records daily, and processing this data in Data Warehouse, there were performance issues. To overcome this issue, a Snowflake multi-cluster warehouse was implemented.

While a typical virtual warehouse only has one cluster of processing resources, a multi-cluster warehouse can contain up to ten clusters. However, the number of multi-cluster warehouses you can have in your Snowflake account gives you unlimited power to process data faster.

Benefits

The following are the main benefits of a Snowflake Multi-Cluster warehouse.

Automatic resizing: When using Autoscale mode, you do not need to resize the warehouse to accommodate fluctuating queries as you would in a regular warehouse. Furthermore, as needed, you can control the multi-cluster warehouse capacity in maximized mode. Snowflake will handle this for you.
Time Travel: Data that has been altered or removed can be accessed at any time throughout a specified period (i.e., 90 days of time frame).
Fail-Safe: Apart from and separate from time travel, In the event of a system breakdown or other occurrence, fail-safe protects the protection of previous data (e.g., a security breach); it supports up to 7 days.

Functionality

The functionality of multi-cluster warehouses, which is only available in the Enterprise version of Snowflake, enables you to make a bigger pool of processing capacity accessible for running queries.

Modes of Operation

There are two modes of operation available for multi-cluster warehouses in Snowflake:

Maximized mode — This mode is effective for statically controlling the available resources. We will go for this mode if we have large numbers of concurrent user sessions and queries and the numbers do not fluctuate significantly.
Auto-scale mode — If the number of concurrent user sessions or queries for the warehouse increases or decreases, then this mode automatically starts or shuts down additional clusters up to the maximum or minimum number defined for the warehouse.

The figure below shows how to select the mode in the Snowflake Web interface.

Snowflake Scaling Policy

The scaling policy for a multi-cluster warehouse only applies if it runs in Auto-scale mode. In maximized mode, all clusters run concurrently, so there is no need to start or shut down individual clusters.

There are two Types of Scaling Policies in the snowflake multi-cluster warehouse.

Standard: If the volume of data is huge and the user wants better performance, then they can opt for a standard scaling policy. Standard consumes more credits as compared to the economy.
Economy: It favors conserving credits and prioritizing cost over performance. If users can compromise for performance, they can opt for an Economy scaling policy. The system will get queued for some time, but it will save cost.

How to Create a Multi-Cluster Warehouse in Snowflake

You can create a multi-cluster warehouse using the Snowflake web interface or SnowSQL.

Using the Snowflake Web Interface

The following are the steps to apply with the web interface.

1: Go to Admin — Warehouses. The below image shows what you’ll see.

2: Click on + Warehouse to create a new warehouse. This image shows how to create a new warehouse.

3: After clicking new warehouse, you need to enter details such as the warehouse name, and depending on your requirements, you need to select the warehouse size. For more understanding, please read on sizing a warehouse.

4: You must select the mode and scaling policy discussed earlier in this blog; then click on create warehouse.

Using the SnowSQL command line

The SnowSQL command line client connects to Snowflake and allows users to run SQL queries and carry out all DDL and DML activities, including loading and unloading data from database tables.

Create warehouse Test with
       warehouse_size     = SMALL
       auto_suspend       = 600
       auto_resume        = true
       intially_suspended = true
       comment = 'Sample Reporting Warehouse'

Please see the below image for the SQL query to create a warehouse.

With the help of the below SQL command, users can check the warehouse in Snowflake.

show warehouses

The result for the above query will show a detailed view of the created warehouse.

Summary

In this blog, I have presented you with how to create a Snowflake multi-cluster warehouse and the benefits of a multi-cluster warehouse. I hope this blog was helpful and has motivated you enough to get interested in the topic.