What you will be able to do
- Decide when a multi-cluster warehouse is the right fix and when resizing is
- Configure Maximized or Auto-scale mode with MIN_CLUSTER_COUNT and MAX_CLUSTER_COUNT
- Set and change a scaling policy
- Estimate multi-cluster credit usage and know the QAS default
- Monitor cluster activity and queuing in Snowsight
1.When to scale out instead of up
A single-cluster warehouse queues queries when it runs out of resources. Without multi-cluster, you have two ways to handle a growing user load. You can enlarge the warehouse, or you can create more warehouses and point some users at them. Either way, you have to shrink or suspend them again by hand afterwards. Snowflake also notes that resizing is not meant for concurrency problems.
A multi-cluster warehouse, an Enterprise Edition feature, adds clusters of the same size so more users can share one warehouse. The benefit is concurrency. Multi-cluster warehouses do much less for slow-running queries or data loading. For those, resize the warehouse.
Checkpoint 1 of 7· Check yourself
Every morning at 9:00, 200 analysts open dashboards and their queries queue. Each query on its own is fast. What is the best fit?
Queuing caused by many concurrent users is a concurrency problem, and multi-cluster warehouses exist to solve it. Resizing is aimed at making individual queries faster.
“Multi-cluster warehouses are best utilized for scaling resources to improve concurrency for users/queries.”Source: docs.snowflake.com
2.Maximized and Auto-scale modes
Two properties define a multi-cluster warehouse: MAX_CLUSTER_COUNT, which must be greater than 1, and MIN_CLUSTER_COUNT, which must be at most the maximum. How the two compare sets the mode:
- Maximized: minimum equals maximum. Every cluster starts with the warehouse. This suits large numbers of concurrent sessions or queries that stay fairly steady. You control capacity by changing the cluster count yourself. - Auto-scale: minimum is less than maximum. When queries start to queue, Snowflake adds clusters up to the maximum. When load falls, it shuts clusters down. No manual resizing is needed.
Snowflake suggests starting in Auto-scale with small numbers, such as maximum 2 or 3 and minimum 1. Raise them as you learn how your concurrency peaks. You can change both counts at any time, even while the warehouse is running.
| Warehouse size | Allowed maximum cluster count |
|---|---|
| XSMALL / SMALL / MEDIUM | 300 |
| LARGE | 160 |
| XLARGE | 80 |
| 2XLARGE | 40 |
| 3XLARGE | 20 |
| 4XLARGE / 5XLARGE / 6XLARGE | 10 |
These limits apply to CREATE WAREHOUSE and ALTER WAREHOUSE in SQL. Snowsight currently lets you choose at most 10. Suspend and resume also behave differently. Auto-suspend and auto-resume apply to the whole warehouse, never to a single cluster. Auto-suspend happens only when the warehouse is down to its minimum number of clusters and has been idle for the set period. Auto-resume applies only when no clusters are running.
Checkpoint 2 of 7· Match them up
Match each configuration to its result
Tap a term, then the definition that fits it.
Equal minimum and maximum means Maximized mode. A minimum below the maximum means Auto-scale mode. A maximum of 1 is a single-cluster warehouse.
“If MIN_CLUSTER_COUNT is less than MAX_CLUSTER_COUNT, the warehouse runs in Auto-scale mode.”Source: docs.snowflake.com
Checkpoint 3 of 7· Exam question
An administrator runs the following statement: CREATE WAREHOUSE rpt_wh WAREHOUSE_SIZE = 'MEDIUM' MIN_CLUSTER_COUNT = 3 MAX_CLUSTER_COUNT = 3; What happens when this warehouse is resumed by the first incoming query?
Correct answer: B — Snowflake starts all three clusters immediately and keeps them running until the warehouse is suspended, in Maximized mode.
- A. Incorrect. Gradual scale-out only occurs in Auto-scale mode, where the minimum is lower than the maximum. Here both values are equal.
- B. Correct. When MIN_CLUSTER_COUNT equals MAX_CLUSTER_COUNT and is greater than 1, the warehouse runs in Maximized mode and starts every cluster on resume.
- C. Incorrect. Equal values are valid and are exactly how Maximized mode is configured, so the statement succeeds.
- D. Incorrect. In Maximized mode clusters are not started and stopped by load checks. The default policy is Standard, and no Economy setting was specified.
Sources1
3.Implementing a scaling policy
In Auto-scale mode, the SCALING_POLICY property controls how readily Snowflake starts and stops clusters. It lets you favour responsiveness and throughput, or favour lower cost. The policy applies only in Auto-scale mode. In Maximized mode every cluster is always running, so there is nothing to scale. Snowsight shows the Scaling Policy drop-down only when the warehouse's maximum cluster count is higher than its minimum.
The default policy is STANDARD. It prevents or minimizes queuing by starting clusters rather than saving credits. When a query queues, or Snowflake estimates the running clusters cannot take more, it adds clusters. With a MAX_CLUSTER_COUNT of 10 or less it adds one at a time. Above 10 it adds several at once. The other supported value is ECONOMY. The sources here do not give its exact start and shutdown rules, so do not assume specific thresholds. The old Legacy policy has been removed, and warehouses that used it now use Standard.
You can set the policy when you create the warehouse or change it later, in Snowsight or in SQL.
Checkpoint 4 of 7· Fill the gap
This documented example creates a warehouse with the default policy and then switches it to the other supported policy. Which value completes it?
CREATE WAREHOUSE mywh WITH MAX_CLUSTER_COUNT = 2, SCALING_POLICY = 'STANDARD'; ALTER WAREHOUSE mywh SET SCALING_POLICY = ' ? ';STANDARD and ECONOMY are the scaling policies. Maximized and Auto-scale are modes set by the cluster counts, and Legacy has been removed.
Source: docs.snowflake.comCheckpoint 5 of 7· Exam question
A multi-cluster warehouse sometimes leaves analysts waiting even though MAX_CLUSTER_COUNT has headroom. Which TWO ACCOUNT_USAGE views or columns would BEST help the administrator verify queuing and cluster scale-out events? Select TWO.(Select 2)
Correct answers: A, B — WAREHOUSE_LOAD_HISTORY, whose AVG_QUEUED_LOAD column shows how many queries waited because the warehouse was overloaded.; WAREHOUSE_EVENTS_HISTORY, which records cluster events such as SPINUP and SUSPEND with timestamps and cluster numbers.
- A. Correct. AVG_QUEUED_LOAD and QUEUED_OVERLOAD figures in the load history directly show queuing caused by a saturated warehouse.
- B. Correct. The events history shows when clusters were started and suspended, so the administrator can compare scale-out with the queuing periods.
- C. Incorrect. Storage usage reports data volume, not compute load or cluster activity.
- D. Incorrect. Login history records authentication events and says nothing about queue depth or clusters.
- E. Incorrect. Access history tracks which objects queries touched for governance, not warehouse scaling behaviour.
Sources1
4.Credit usage and the QAS default
The hourly ceiling is the size rate times the maximum cluster count, so a 3-cluster Medium warehouse can use up to 12 credits an hour. What it actually uses depends on how many clusters run. If you resize a multi-cluster warehouse, the new size applies to every cluster, both running ones and ones started later. That is why the resize example below jumps from 4 to 8 credits per cluster.
| Scenario | Total credits |
|---|---|
| Maximized mode, 2 hours | 24 |
| Auto-scale, 2 hours (cluster 2 in hour 2 only; cluster 3 for 30 min) | 14 |
| Auto-scale, 3 hours | 20 |
| Auto-scale, 3 hours, resized Medium to Large at 1:30 | 34 |
A newly created multi-cluster warehouse has the Query Acceleration Service (QAS) turned on by default, with a QUERY_ACCELERATION_MAX_SCALE_FACTOR of 2. Converting an existing single-cluster warehouse to multi-cluster does not turn QAS on. Changing the cluster counts or the scaling policy does not change QAS either. To change QAS, set ENABLE_QUERY_ACCELERATION and QUERY_ACCELERATION_MAX_SCALE_FACTOR. For multi-cluster warehouses, consider a higher scale factor than you would use for a single cluster.
Checkpoint 6 of 7· Check yourself
An admin runs ALTER WAREHOUSE to raise the MAX_CLUSTER_COUNT of an existing single-cluster warehouse, which has QAS off, to 4. What is the QAS state afterwards?
QAS is turned on automatically only when a multi-cluster warehouse is created. Converting an existing warehouse leaves QAS as it was.
“Altering an existing single-cluster warehouse to multi-cluster does not enable QAS.”Source: docs.snowflake.com
Sources1
5.Monitoring a multi-cluster warehouse
In Snowsight, go to Compute » Warehouses. The Clusters column shows each warehouse's minimum and maximum cluster counts and how many clusters are running. Hover over the bar to see the active count. The Running and Queued columns count the statements executing and waiting. You can also add a Scaling Policy column, plus Auto Resume and Auto Suspend columns.
Select a warehouse to see more. The Details panel lists its minimum and maximum clusters and its scaling policy. The Warehouse Activity graph shows warehouse load over time. Load is the average number of queries running or queued in each interval. Regular queuing at the maximum cluster count suggests raising the maximum. Clusters that rarely start suggest the maximum or minimum could be lower.
Checkpoint 7 of 7· Check yourself
Where in Snowsight can you see how many clusters of a multi-cluster warehouse are running right now?
The Clusters column shows the minimum and maximum cluster counts and how many clusters are currently running.
“The Clusters column displays the minimum and maximum clusters for each warehouse, as well as the number of clusters that are currently running”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Adding clusters speeds up a single slow query or a large data load.Why is that wrong?
Multi-cluster warehouses address concurrency. For slow queries and loading, resizing helps more.
Covered in When to scale out instead of up
2.The scaling policy controls cluster start-up in Maximized mode too.Why is that wrong?
The scaling policy applies only in Auto-scale mode. In Maximized mode all clusters are always running.
Covered in Implementing a scaling policy
3.Idle extra clusters in a multi-cluster warehouse are auto-suspended one by one.Why is that wrong?
Auto-suspend applies to the whole warehouse. It happens only once the warehouse is down to its minimum cluster count and has been idle for the set period.
Covered in Maximized and Auto-scale modes
Practise it for real
Create an Auto-scale multi-cluster warehouse, change its scaling policy, and confirm both settings in Snowsight (requires Enterprise Edition or higher).
1.Run: CREATE WAREHOUSE mywh WITH MAX_CLUSTER_COUNT = 2, SCALING_POLICY = 'STANDARD';
Why: A maximum above 1 makes it multi-cluster. Because MIN_CLUSTER_COUNT defaults to 1, the warehouse runs in Auto-scale mode, where a scaling policy applies.
You should see: The warehouse is created with up to 2 clusters and the Standard policy.
2.Run: ALTER WAREHOUSE mywh SET SCALING_POLICY = 'ECONOMY';
Why: You can change the policy at any time after creation.
You should see: The statement succeeds.
3.In Snowsight, open Compute » Warehouses and add the Scaling Policy column.
Why: This is where you confirm the policy and watch the Clusters, Running and Queued columns.
You should see: mywh shows the Economy policy and a Clusters bar covering 1 to 2 clusters.
4.Suspend the warehouse with ALTER WAREHOUSE and the SUSPEND keyword.
Why: A running warehouse uses credits.
You should see: The status changes to Suspended once all compute resources have shut down.
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“a multi-cluster warehouse enables larger numbers of users to connect to the same size warehouse”
↩︎ When to scale out instead of up“This mode is enabled by specifying different values for maximum and minimum number of clusters.”
↩︎ Maximized and Auto-scale modes“Currently, the highest value you can choose in Snowsight is 10.”
↩︎ Maximized and Auto-scale modes“start with Auto-scale mode and start small (for example, maximum = 2 or 3, minimum = 1).”
↩︎ Maximized and Auto-scale modes“Prevents/minimizes queuing by favoring starting additional clusters over conserving credits.”
↩︎ Implementing a scaling policy“For warehouses with a MAX_CLUSTER_COUNT of 10 or less, Snowflake starts one additional cluster.”
↩︎ Implementing a scaling policy“Legacy has been removed. All warehouses that were using the Legacy policy now use the default Standard policy.”
↩︎ Implementing a scaling policy“If a multi-cluster warehouse is resized, the new size applies to all the clusters for the warehouse”
↩︎ Credit usage and the QAS default“The default QUERY_ACCELERATION_MAX_SCALE_FACTOR is 2 when QAS is enabled automatically at creation time.”
↩︎ Credit usage and the QAS default“They are not as beneficial for improving the performance of slow-running queries or data loading.”
↩︎ Exam trap 1“The scaling policy for a multi-cluster warehouse only applies if it is running in Auto-scale mode.”
↩︎ Exam trap 2“Multi-cluster warehouses are best utilized for scaling resources to improve concurrency for users/queries.”
↩︎ Checkpoint“The actual number of credits consumed per hour depends on the number of clusters running during each hour that the warehouse is running.”
↩︎ Prediction“Altering an existing single-cluster warehouse to multi-cluster does not enable QAS.”
↩︎ Checkpoint“The Clusters column displays the minimum and maximum clusters for each warehouse, as well as the number of clusters that are currently running”
↩︎ Checkpoint - 2.
“Note that warehouse resizing is not intended for handling concurrency issues”
↩︎ When to scale out instead of up - 3.
“The Warehouse Activity section provides a graph of warehouse load over a period of time”
↩︎ Monitoring a multi-cluster warehouse - 4.https://docs.snowflake.com/en/user-guide/warehousesOfficial docs
“Warehouse query load measures the average number of queries that were running or queued within a specific interval.”
↩︎ Monitoring a multi-cluster warehouse
Also cited
“Auto-suspend only occurs when the minimum number of clusters is running and there is no activity for the specified period of time.”
↩︎ Exam trap 3“If MIN_CLUSTER_COUNT is less than MAX_CLUSTER_COUNT, the warehouse runs in Auto-scale mode.”
↩︎ Checkpoint