Multi-cluster warehouses in Snowflake - Step-by-Step Execution

Process Flow - Multi-cluster warehouses

User Query Arrives

↓

Check Warehouse Load

↓

Is Load High?

No→Use Single Cluster

Yes↓

Start Additional Clusters

↓

Distribute Queries Across Clusters

↓

Monitor Load and Scale Down

↓

Return Results to User

The system checks warehouse load and adds clusters if needed to handle more queries, then scales down when load decreases.

Execution Sample

Snowflake

CREATE WAREHOUSE my_wh
  WAREHOUSE_SIZE = 'MEDIUM'
  MIN_CLUSTER_COUNT = 1
  MAX_CLUSTER_COUNT = 3;

This code creates a multi-cluster warehouse that can scale between 1 and 3 clusters based on query load.

Process Table

Step	Warehouse Load	Clusters Active	Action Taken	Result
1	Low	1	Use single cluster	Queries processed by 1 cluster
2	Medium	1	Monitor load	No change, still 1 cluster
3	High	1	Start 2nd cluster	Queries distributed between 2 clusters
4	Very High	2	Start 3rd cluster	Queries distributed among 3 clusters
5	Medium	3	Scale down to 2 clusters	One cluster stopped, load balanced
6	Low	2	Scale down to 1 cluster	Back to single cluster
7	Low	1	Maintain single cluster	Stable state
8	Low	1	No further action	Execution ends

💡 Load is low and only 1 cluster active, no scaling needed

Status Tracker

Variable	Start	After Step 3	After Step 4	After Step 6	Final
Warehouse Load	Low	High	Very High	Low	Low
Clusters Active	1	2	3	1	1

Key Moments - 3 Insights

Why does the warehouse start additional clusters only when load is high?

Because as shown in execution_table steps 3 and 4, additional clusters start only when load reaches 'High' or 'Very High' to handle more queries efficiently.

What happens when the load decreases after scaling up?

As seen in steps 5 and 6, the warehouse scales down clusters to save resources, stopping extra clusters when load is medium or low.

Can the warehouse have zero clusters active?

No, the minimum cluster count is 1, so there is always at least one cluster active, as shown in all steps of the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, how many clusters are active at step 4?

A 1

B 2

C 3

D 4

💡 Hint

Refer to the 'Clusters Active' column at step 4 in the execution_table.

At which step does the warehouse scale down from 3 to 2 clusters?

A Step 3

B Step 5

C Step 6

D Step 7

💡 Hint

Check the 'Action Taken' column for scaling down actions in the execution_table.

If the maximum cluster count was set to 2, what would happen at step 4?

A Stay at 2 clusters and distribute queries

B Scale down to 1 cluster

C Start 3rd cluster anyway

D Stop all clusters

💡 Hint

Look at the 'MAX_CLUSTER_COUNT' setting in the execution_sample and relate it to cluster limits.

Concept Snapshot

Multi-cluster warehouses automatically add or remove compute clusters
based on query load to improve performance and concurrency.
Set MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT to control scaling.
Queries are distributed across active clusters.
Clusters scale down when load decreases to save costs.

Full Transcript

Multi-cluster warehouses in Snowflake help handle varying query loads by automatically adding or removing compute clusters. When query load is low, a single cluster processes queries. As load increases, additional clusters start to share the work, improving speed and concurrency. When load drops, extra clusters stop to save resources. This behavior is controlled by settings like MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT. The execution table shows step-by-step how clusters start and stop based on load changes, ensuring efficient resource use and fast query processing.