What you will be able to do
- Choose between Standard and Snowpark-optimized warehouses and read the size-to-credit table
- Predict how a size change affects data loading versus query processing
- Configure auto-suspend and auto-resume and weigh their cost and cache trade-offs
- Work out which warehouse a session uses and change it
- Decide when a multi-cluster warehouse is the right fix and pick Maximized or Auto-scale mode and a scaling policy
- Adjust cluster counts on a running multi-cluster warehouse and monitor it
Key concept
Scale up vs scale out — Making a warehouse larger (scaling up) gives each cluster more compute, so big, complex queries and heavy loads run faster. Adding clusters (scaling out) helps when many queries run at the same time. Every sizing decision starts with working out which of these two problems you have.
1.Warehouse types, sizes and what they cost
A virtual warehouse is the compute that runs SELECT statements and DML, including COPY INTO for loading and unloading. It comes in two types. A Standard warehouse is the general-purpose type. A Snowpark-optimized warehouse is meant for memory-heavy work. Snowpark code runs on either type, but Snowflake recommends the optimized type when memory is the bottleneck, as in ML training.
Apart from its type, a warehouse is defined by its size. Size sets the amount of compute in each cluster. That wording matters later, when a warehouse has more than one cluster. Each step up in size doubles the hourly credit rate.
| Warehouse size | Credits / hour (Gen1) |
|---|---|
| X-Small | 1 |
| Small | 2 |
| Medium | 4 |
| Large | 8 |
| X-Large | 16 |
| 2X-Large | 32 |
| 3X-Large | 64 |
| 4X-Large | 128 |
| 5X-Large | 256 |
| 6X-Large | 512 |
Billing is per second, with a 60-second minimum each time the warehouse starts. A warehouse that runs for 61 seconds is billed for 61 seconds. If it then shuts down and restarts for a few seconds, the restart carries a new 60-second minimum. Suspending early to save credits gains nothing within the first minute. These rates are for first-generation (Gen1) standard warehouses. Gen2 warehouses are priced separately and are not the default.
Checkpoint 1 of 7· Check yourself
A team runs ML training in Snowpark and keeps running out of memory on a Standard warehouse. Which change matches Snowflake's recommendation?
Snowpark-optimized warehouses are recommended for workloads with large memory requirements, such as ML training. Adding clusters helps with concurrency, not with the memory available to one job.
“Snowpark-optimized warehouses are recommended for workloads that have large memory requirements such as ML training use cases.”Source: docs.snowflake.com
2.How size affects loading and query processing
Loading and querying respond to size differently. For loading, match the warehouse to the number of files and the amount of data in each. A Small, Medium or Large warehouse is generally enough unless you are bulk loading hundreds or thousands of files at once. An X-Large or bigger warehouse uses more credits and may not load any faster.
Queries do scale with size, especially large, complex ones, because a bigger warehouse has more compute to work with. The total size of the tables queried matters more than the row count, and predicates and joins also affect cost. That scaling stops at the bottom end: small, simple queries do not get faster on an X-Large warehouse. The recommended way to choose a size is to experiment. Run the same representative queries, ones that finish within 5 to 10 minutes, on several sizes and compare.
You can resize a warehouse at any time, even while it is running. New compute does not help queries that are already running. Once it is fully provisioned, it serves queued and newly submitted queries. Resizing a suspended warehouse provisions nothing until the warehouse next resumes. For each workload, run similar queries on the same warehouse. Mixing very different queries on one warehouse makes its load hard to read and its size hard to choose.
Checkpoint 2 of 7· Check yourself
A long-running report is halfway through on a Large warehouse. An admin resizes the warehouse to X-Large. What happens to that report?
Resizing does not affect statements already running. Once the new resources are fully provisioned, they serve queued and future queries.
“The additional resources do not impact any queries that are already running”Source: docs.snowflake.com
Checkpoint 3 of 7· Exam question
A BI warehouse is a multi-cluster warehouse with MAX_CLUSTER_COUNT = 4. Finance is cost-sensitive and accepts that dashboards may queue briefly during the 9:00 spike, but wants clusters to run only when they will be fully used. Which configuration is MOST appropriate?
Correct answer: A — Set SCALING_POLICY = 'ECONOMY' so extra clusters start only when enough load exists to keep them busy for about six minutes.
- A. Correct. The Economy policy conserves credits by starting an additional cluster only when the queued workload would keep it busy for roughly six minutes, accepting some queuing.
- B. Incorrect. Setting the minimum equal to the maximum turns the warehouse into Maximized mode, so all four clusters run continuously, which is the opposite of cost conservation.
- C. Incorrect. Disabling auto-suspend keeps the warehouse running and billing indefinitely, and it does not change when extra clusters are started or shut down.
- D. Incorrect. The Standard policy starts a new cluster as soon as a query is queued, minimising wait time at higher cost. The six-minute rule belongs to Economy.
3.Auto-suspend, auto-resume and the warehouse cache
Auto-suspend and auto-resume are both on by default. Auto-suspend stops a warehouse after a set period of inactivity, so an idle warehouse stops costing credits. Auto-resume starts the warehouse again when a statement needs it and it is the session's current warehouse.
Suspending has a cost the bill does not show. A running warehouse keeps a cache of the table data its queries have read, and larger warehouses have larger caches. That cache is dropped on suspend, so the first queries after a resume can be slower until it fills again. Choosing an auto-suspend period means trading saved credits against a warm cache.
Turning auto-suspend off makes sense for a heavy, steady workload, or when the warehouse must be ready with no provisioning delay. To turn it off, select Never in the web interface, or set it to 0 or NULL in SQL. The cost of leaving a warehouse running can be large, especially for X-Large and bigger sizes. Auto-resume is a control choice. Leave it on if cost and access are not a concern. Turn it off and resume the warehouse by hand if you want to control spending or who can start it.
Checkpoint 4 of 7· Check yourself
In SQL, which value turns off auto-suspend for a warehouse?
Snowflake documents only 0 or NULL in SQL (Never in the web interface) for turning off auto-suspend.
“To disable auto-suspend, you must explicitly select Never in the web interface, or specify 0 or NULL in SQL.”Source: docs.snowflake.com
4.Which warehouse a session uses
A query runs only if the session has a current warehouse and that warehouse is running. A new session has no warehouse by default, and a session can have only one current warehouse at a time. To make sessions usable right away, you can set a default warehouse at several levels. Each level overrides the one before it:
1. The user's default warehouse, set with CREATE USER or ALTER USER. 2. The default warehouse in a client's configuration file (Snowflake CLI, SnowSQL and similar clients). 3. A warehouse given on the client command line or as a driver or connector parameter.
During the session, USE WAREHOUSE switches to another warehouse at any time. This is how you size for the session: send a heavy batch session to a larger warehouse and leave lightweight work on a small one. For Snowsight pages that query metadata, such as Task Run History or Data Preview, an X-Small warehouse is recommended and is generally enough.
Checkpoint 5 of 7· Put it in order
Order these sources of a session's warehouse from lowest to highest precedence. Each one overrides those before it.
- 1.USE WAREHOUSE executed inside the session
- 2.Default warehouse in the client configuration file
- 3.Warehouse passed on the command line or as a driver/connector parameter
- 4.Default warehouse set on the user
The user default is overridden by the client configuration file, which is overridden by command-line or connector parameters. USE WAREHOUSE can change the warehouse at any point during the session.
“the default warehouse for a session can be changed at any time by executing the USE WAREHOUSE command within the session.”Source: docs.snowflake.com
5.Multi-cluster warehouses: use cases, benefits and management
A multi-cluster warehouse is the scale-out answer from the key concept. By default a warehouse has one cluster, and when it runs out of resources Snowflake queues the extra queries. A multi-cluster warehouse adds clusters, statically or dynamically, so a larger pool of compute is available. It is an Enterprise Edition feature.
Use case and benefits: it fits workloads where user and query concurrency changes, such as peak and off hours. With a single-cluster warehouse you would have to resize it, or start extra warehouses and redirect users by hand, then undo it later. In Auto-scale mode Snowflake starts and stops clusters for you. In Maximized mode you control capacity by changing the number of clusters. It is not the tool for a single slow query or a slow load. For those, resize the warehouse instead.
Modes: a multi-cluster warehouse has a MAX_CLUSTER_COUNT greater than 1 and a MIN_CLUSTER_COUNT no larger than the maximum. Equal values give Maximized mode, where all clusters start with the warehouse. This suits large, steady concurrency. Different values give Auto-scale mode, where clusters start as queries queue and stop as load falls. Unless you need Maximized, use Auto-scale, and start small (for example maximum 2 or 3, minimum 1), then raise the numbers as you watch the load. The size applies to every cluster, so the most credits per hour is the size's rate times the maximum number of clusters. The allowed maximum shrinks as the size grows.
Scaling policy: SCALING_POLICY applies only in Auto-scale mode. STANDARD (the default) favors starting clusters to avoid queuing. ECONOMY keeps running clusters fully loaded and starts a new one only when there is enough work to keep it busy for at least 6 minutes, so queries may queue and take longer. Choose STANDARD for responsiveness and ECONOMY for lower cost. You can change the policy at any time with ALTER WAREHOUSE.
Managing a running warehouse: you can raise or lower the maximum and minimum while it runs. In Auto-scale mode, raising the maximum changes nothing until more clusters are needed. Raising the minimum above the running count starts clusters immediately. Lowering either lets excess clusters shut down once their statements finish and the scaling policy conditions are met. Auto-suspend and auto-resume apply to the whole warehouse, not to individual clusters.
Scenario: a dashboard warehouse queues queries at the 9:00 peak but is quiet otherwise. Run it in Auto-scale mode with a minimum of 1 and a higher maximum, and use STANDARD to avoid queuing. If queues persist, raise the maximum.
Monitoring: in Snowsight, the Warehouses page shows min, max and running clusters and the Queued count, and the Warehouse Activity chart shows queued load. SHOW WAREHOUSES returns min_cluster_count, max_cluster_count and started_clusters. Query History can show the cluster number that ran each statement. Cluster starts and stops are logged in WAREHOUSE_EVENTS_HISTORY as MULTICLUSTER_SPINUP and MULTICLUSTER_SPINDOWN.
Checkpoint 6 of 7· Check yourself
A reporting warehouse is queuing queries every morning when many analysts log in at once. Individual queries run quickly. What is the best-fit change?
Queuing from many concurrent users is the multi-cluster use case. Auto-scale adds clusters as queries queue and removes them as load falls. Resizing mainly helps slow individual queries, and Maximized needs a value larger than 1.
“Multi-cluster warehouses are best utilized for scaling resources to improve concurrency for users/queries.”Source: docs.snowflake.com
Checkpoint 7 of 7· Match them up
Match each multi-cluster setting to what it does.
Tap a term, then the definition that fits it.
Mode is set by comparing the maximum and minimum cluster counts. The scaling policy then tunes Auto-scale between responsiveness and cost.
“Prevents/minimizes queuing by favoring starting additional clusters over conserving credits.”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.A larger warehouse always makes bulk data loading faster.Why is that wrong?
Load speed depends mainly on the number and size of files. For most loads, Small to Large is enough, and bigger sizes just use more credits.
2.Suspending a warehouse is free apart from the restart delay.Why is that wrong?
Suspending drops the warehouse's data cache, so queries after a resume can be slower until the cache fills again.
Covered in Auto-suspend, auto-resume and the warehouse cache
3.Adding clusters to a multi-cluster warehouse speeds up slow individual queries and data loads.Why is that wrong?
Extra clusters improve concurrency. For slow queries or loading, resizing the warehouse helps more.
Covered in Multi-cluster warehouses: use cases, benefits and management
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.https://docs.snowflake.com/en/user-guide/warehousesOfficial docs
“Snowpark-optimized warehouses are recommended for workloads that have large memory requirements such as ML training use cases.”
↩︎ Warehouse types, sizes and what they cost - 2.
“Size specifies the amount of compute resources available per cluster in a warehouse.”
↩︎ Warehouse types, sizes and what they cost“In general, query performance scales with warehouse size because larger warehouses have more compute resources available to process queries.”
↩︎ How size affects loading and query processing“Snowflake automatically resumes the warehouse when any statement that requires a warehouse is submitted and the warehouse is the current warehouse for the session.”
↩︎ Auto-suspend, auto-resume and the warehouse cache“Default warehouse specified on the client command line or through the driver/connector parameters passed to Snowflake.”
↩︎ Which warehouse a session uses“Auto-suspend only occurs when the minimum number of clusters is running and there is no activity for the specified period of time.”
↩︎ Multi-cluster warehouses: use cases, benefits and management“a smaller warehouse (Small, Medium, Large) is generally sufficient”
↩︎ Exam trap 1“influenced more by the number of files being loaded (and the size of each file) than the size of the warehouse”
↩︎ Prediction“The additional resources do not impact any queries that are already running”
↩︎ Checkpoint“the default warehouse for a session can be changed at any time by executing the USE WAREHOUSE command within the session.”
↩︎ Checkpoint - 3.
“There is no benefit to stopping a warehouse before the first 60-second period is over”
↩︎ Warehouse types, sizes and what they cost“For data loading, the warehouse size should match the number of files being loaded and the amount of data in each file.”
↩︎ How size affects loading and query processing“If you wish to control costs and/or user access, leave auto-resume disabled and instead manually resume the warehouse only when needed.”
↩︎ Auto-suspend, auto-resume and the warehouse cache“Scale out by adding clusters to a multi-cluster warehouse (requires Snowflake Enterprise Edition or higher).”
↩︎ Key concept“This cache is dropped when the warehouse is suspended, which might result in slower initial performance for some queries after the warehouse is resumed.”
↩︎ Exam trap 2“To disable auto-suspend, you must explicitly select Never in the web interface, or specify 0 or NULL in SQL.”
↩︎ Checkpoint - 4.
“Resizing a suspended warehouse does not provision any new compute resources for the warehouse.”
↩︎ How size affects loading and query processing“A Snowflake session can only have one current warehouse at a time.”
↩︎ Which warehouse a session uses - 5.
“Multi-cluster warehouses are best utilized for scaling resources to improve concurrency for users/queries.”
↩︎ Multi-cluster warehouses: use cases, benefits and management“In Auto-scale mode, a multi-cluster warehouse eliminates the need for resizing the warehouse or starting and stopping additional warehouses to handle fluctuating workloads.”
↩︎ Multi-cluster warehouses: use cases, benefits and management“This mode is enabled by specifying the same value for both maximum and minimum number of clusters”
↩︎ Multi-cluster warehouses: use cases, benefits and management“Conserves credits by favoring keeping running clusters fully-loaded rather than starting additional clusters”
↩︎ Multi-cluster warehouses: use cases, benefits and management“If new_min_clusters > running_clusters, additional clusters immediately started to meet the minimum.”
↩︎ Multi-cluster warehouses: use cases, benefits and management“You can also see the cluster used to execute each statement that the warehouse processed.”
↩︎ Multi-cluster warehouses: use cases, benefits and management“They are not as beneficial for improving the performance of slow-running queries or data loading.”
↩︎ Exam trap 3“Prevents/minimizes queuing by favoring starting additional clusters over conserving credits.”
↩︎ Checkpoint