What you will be able to do
- Tell Standard Gen1, Standard Gen2 and Snowpark-optimized warehouses apart and pick the right one for a workload
- Work out credit consumption from warehouse size and per-second billing
- Explain what resizing a warehouse up or down does, and does not do, to queries
- Configure auto-suspend and auto-resume and explain the trade-off with the warehouse cache
Key concept
Size is compute per cluster — Warehouse size sets how much compute each cluster has, and credit cost doubles with each step up in size. Scaling up or down changes that per-cluster capacity. Scaling out or in changes how many clusters run. Every configuration decision in this lesson means choosing between these two controls.
1.What a warehouse is and how it bills
A virtual warehouse is the compute that does the work in Snowflake. Every query needs one, and so does every DML statement, including loads into tables. Three things define a warehouse: its type (Standard or Snowpark-optimized), its size, and a set of properties that control and automate its activity, such as auto-suspend. You can start, stop and resize a warehouse at any time, even while it is running.
Size sets cost. Each step up in size doubles the credits per hour, from 1 credit for X-Small to 512 for 6X-Large. The figures below are for first-generation (Gen1) standard warehouses. Billing is per second, with a 60-second minimum every time the warehouse starts. Stopping a warehouse within its first minute therefore saves nothing, and a warehouse that restarts repeatedly pays that minimum each time.
| Warehouse size | Credits / hour (Gen1) |
|---|---|
| X-Small | 1 |
| Small | 2 |
| Medium | 4 |
| Large | 8 |
| X-Large | 16 |
| 4X-Large | 128 |
| 6X-Large | 512 |
Standard warehouses come in two generations, Gen 1 and Gen 2. Gen 1 is the original standard warehouse, and the credit table above applies to Gen 1 only. Gen 2 is the newer generation of standard warehouse. Its credit consumption is listed in the Snowflake Service Consumption Table, not in the table above, and Gen 2 is not yet available for all cloud service providers or regions. The next section covers how Gen 1 and Gen 2 differ and how to choose between them.
Checkpoint 1 of 6· Check yourself
A warehouse runs for 61 seconds and suspends. It then resumes and runs for 20 seconds. How many seconds are billed?
The first run is billed per second after its 60-second minimum, so 61 seconds. The restart triggers another 60-second minimum, which gives 61 + 60 = 121 seconds.
“restarts and runs for less than 60 seconds, it is billed for 121 seconds”Source: docs.snowflake.com
Sources1
2.Standard warehouses: Gen1 and Gen2
Standard warehouses come in two generations. Gen1 is the original. Gen2 is the next generation, aimed at analytics and data engineering. It runs on faster hardware and adds software optimizations for DELETE, UPDATE, MERGE and table scans. Snowflake expects most queries to finish faster on Gen2, but it advises you to test the effect on your own costs and performance.
You choose the generation in CREATE WAREHOUSE or ALTER WAREHOUSE. The recommended syntax is the GENERATION clause ('1' or '2'). The alternative is RESOURCE_CONSTRAINT (STANDARD_GEN_1 or STANDARD_GEN_2). Snowsight does not offer these values yet, so you set them in SQL. There are some limits. Gen2 applies only to standard warehouses, so a Snowpark-optimized warehouse cannot be Gen2. Gen2 is also unavailable at the 5X-Large and 6X-Large sizes. A new Gen2 warehouse has the Query Acceleration Service enabled by default, but converting an existing Gen1 warehouse to Gen2 leaves QAS in whatever state it was in.
CREATE OR REPLACE WAREHOUSE old_to_new_xlarge_gen
WAREHOUSE_SIZE = XLARGE;
ALTER WAREHOUSE old_to_new_xlarge_gen
SET GENERATION = '2';You can convert a warehouse while it is running or while it is suspended, and the choice affects cost. If you convert a running warehouse, queries already in flight finish on Gen1 while new queries start on Gen2. You pay for both sets of resources until the old queries finish. Converting while running keeps the warehouse available, and converting while suspended costs less.
Checkpoint 2 of 6· Fill the gap
Complete this statement so that it creates a Gen2 standard warehouse using the RESOURCE_CONSTRAINT syntax.
CREATE OR REPLACE WAREHOUSE next_generation_default_size
RESOURCE_CONSTRAINT = ? ;STANDARD_GEN_2 is the RESOURCE_CONSTRAINT value for a Gen2 standard warehouse. The MEMORY_* values are for Snowpark-optimized warehouses.
Source: docs.snowflake.comSources2
3.Snowpark-optimized warehouses
A Snowpark-optimized warehouse lets you configure the memory and CPU architecture of a single-node instance. Snowpark code can run on a standard warehouse too. Snowpark-optimized is recommended when the code has large memory requirements or depends on a specific CPU architecture. The typical example is ML training in a stored procedure on a single warehouse node. UDF and UDTF workloads may also benefit. By default this type gives 16x the memory per node of a standard warehouse. One trade-off is that creating or resuming one can take longer than for a standard warehouse.
| Memory (up to) | RESOURCE_CONSTRAINT values | Minimum warehouse size |
|---|---|---|
| 16GB | MEMORY_1X, MEMORY_1X_x86 | XSMALL |
| 256GB (default) | MEMORY_16X, MEMORY_16X_x86 | M |
| 1TB | MEMORY_64X, MEMORY_64X_x86 | L |
CREATE WAREHOUSE so_warehouse WITH
WAREHOUSE_SIZE = 'LARGE'
WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED'
RESOURCE_CONSTRAINT = 'MEMORY_16X_X86';You can switch between types in either direction, whether the warehouse is started or suspended. A Gen2 standard warehouse has the same memory capacity as a Snowpark-optimized warehouse at MEMORY_1X. Converting a MEMORY_16X warehouse back to standard therefore reduces its memory per node. The 1 TB option is not available on Azure or GCP, and it is still in preview on AWS.
Checkpoint 3 of 6· Match them up
Match each RESOURCE_CONSTRAINT value to what it provides
Tap a term, then the definition that fits it.
The three MEMORY_* values are Snowpark-optimized memory tiers, and each larger tier needs a larger minimum warehouse size. STANDARD_GEN_1 is a standard-warehouse value whose memory matches MEMORY_1X.
“1TB | Default or x86 | MEMORY_64X, MEMORY_64X_x86 | L”Source: docs.snowflake.com
4.Sizing up and down
Changing size is called scaling up or down, and its main purpose is query performance. Most queries scale roughly linearly with warehouse size, especially large, complex ones, because a bigger warehouse has more compute. A resize also has a timing rule that the exam likes to test. The new resources do nothing for queries that are already running. Once they are provisioned, they serve queries that are queued or submitted afterwards.
Per-second billing makes it reasonable to start large and adjust. Snowflake's own guidance is not to fixate on size: run a larger warehouse and suspend it when it is idle. You can decrease the size at any time, so a job that has shrunk should be moved to a smaller warehouse rather than left oversized. Bigger also has limits, because small, basic queries do not necessarily run faster on a larger warehouse. To find the right size, run the same queries on several sizes. Use queries that typically complete within 5 to 10 minutes.
Checkpoint 4 of 6· Exam question
A team of business analysts runs unpredictable, exploratory SQL queries throughout the day, with idle gaps of 5-15 minutes between queries. Which warehouse configuration best fits this ad-hoc workload while minimizing wasted credit spend?
Correct answer: A — Provision an X-Small or Small standard warehouse with a short auto-suspend interval of around 60 seconds so compute stops quickly between exploratory queries.
- A. A small warehouse with a short auto-suspend interval is the standard fit for sporadic, unpredictable queries: it keeps compute cheap during idle gaps while still resuming quickly since small warehouses start fast.
- B. Disabling auto-suspend keeps a Large warehouse billing continuously through every idle gap in the day, which wastes far more credit than the sporadic query volume justifies.
- C. Running three clusters continuously is a maximized multi-cluster setup meant for sustained concurrent load from many users, not for a single team's occasional single-query workload, and it burns credits around the clock.
- D. Snowpark-optimized warehouses add extra per-node memory for ML and UDF workloads at a higher cost, which is unnecessary overhead for ordinary exploratory SQL that does not require that memory profile.
Checkpoint 5 of 6· Check yourself
A Medium warehouse is resized to X-Large while a long query is already executing on it. What happens?
A resize can happen while the warehouse is running. The additional compute serves queued and newly submitted queries, not queries that were already executing.
“The additional resources do not impact any queries that are already running”Source: docs.snowflake.com
5.Auto-suspend, auto-resume and the cache trade-off
Auto-suspend and auto-resume are both enabled by default. Auto-suspend stops a warehouse after it has been idle for a set period, so it does not burn credits with no queries arriving. Auto-resume starts it again when a statement needs it and it is the session's current warehouse. Between them, idle compute stops costing money without anyone having to intervene.
Suspending has a cost of its own. While a warehouse runs, it caches the table data its queries read, and larger warehouses have larger caches. That cache is dropped on suspend. The first queries after a resume may therefore run slower until the cache rebuilds. When you set the suspend timeout, you are trading credits saved against warm-cache performance. With the 60-second billing minimum, a very aggressive timeout on a warehouse that wakes up often can also cost more than it saves.
Checkpoint 6 of 6· Check yourself
A dashboard warehouse auto-suspends quickly between bursts, and users complain that the first queries after each pause are slow. What is the most likely cause?
Suspending clears the warehouse's data cache, so the first queries after a resume read from storage again until the cache is rebuilt.
“This cache is dropped when the warehouse is suspended”Source: docs.snowflake.com
Exam traps
Each one states something that sounds right. Open it to see what is actually true.
1.Resizing a warehouse mid-query makes the running query finish faster.Why is that wrong?
Added resources only serve queries that are queued or submitted after they are provisioned. Running queries are unaffected.
Covered in Sizing up and down
2.Suspending a warehouse after 30 seconds saves half a minute of credits.Why is that wrong?
Every start is billed for at least 60 seconds, so stopping earlier saves nothing.
Covered in What a warehouse is and how it bills
3.Any warehouse, including a Snowpark-optimized one, can be made Gen2 with GENERATION = '2'.Why is that wrong?
Gen2 applies only to standard warehouses. The GENERATION clause cannot be used with Snowpark-optimized warehouses.
Covered in Standard warehouses: Gen1 and Gen2
Sources
Every claim above is drawn from one of these pages, quoted as it was written on the date shown.
- 1.
“Warehouses are required for queries, as well as all DML operations, including loading data into tables.”
↩︎ What a warehouse is and how it bills“Snowflake utilizes per-second billing (with a 60-second minimum each time the warehouse starts)”
↩︎ What a warehouse is and how it bills“The numbers in the preceding table refer to the first generation (Gen1) of Snowflake standard warehouses.”
↩︎ What a warehouse is and how it bills“a multi-cluster X-Small warehouse, SYSTEM$STREAMLIT_NOTEBOOK_WH, is automatically provisioned within each account”
↩︎ What a warehouse is and how it bills“In general, query performance scales with warehouse size because larger warehouses have more compute resources available to process queries.”
↩︎ Sizing up and down“By default, auto-suspend is enabled.”
↩︎ Auto-suspend, auto-resume and the cache trade-off“Size specifies the amount of compute resources available per cluster in a warehouse.”
↩︎ Key concept“The additional resources do not impact any queries that are already running”
↩︎ Exam trap 1“The additional resources do not impact any queries that are already running”
↩︎ Checkpoint - 2.
“Gen2 is built on top of faster underlying hardware and intelligent software optimizations”
↩︎ Standard warehouses: Gen1 and Gen2“While the existing queries are running, you are charged for both sets of compute resources.”
↩︎ Standard warehouses: Gen1 and Gen2“Altering an existing Gen1 warehouse to Gen2 does not enable QAS.”
↩︎ Standard warehouses: Gen1 and Gen2“STANDARD_GEN_1 provides the same memory capacity for standard warehouses as MEMORY_1X does for Snowpark-optimized warehouses.”
↩︎ Snowpark-optimized warehouses“the GENERATION clause applies only to standard warehouses and cannot be used with Snowpark-optimized warehouses”
↩︎ Exam trap 3 - 3.
“The default configuration for a Snowpark-optimized warehouse provides 16x memory per node compared to a standard warehouse.”
↩︎ Snowpark-optimized warehouses“Example workloads include Machine Learning (ML) training use cases using a stored procedure on a single virtual warehouse node.”
↩︎ Snowpark-optimized warehouses“Workloads that don’t use Snowpark might not benefit from running on Snowpark-optimized warehouses.”
↩︎ Prediction“1TB | Default or x86 | MEMORY_64X, MEMORY_64X_x86 | L”
↩︎ Checkpoint - 4.
“You can decrease the size of a warehouse at any time.”
↩︎ Sizing up and down“typically complete within 5 to 10 minutes (or less)”
↩︎ Sizing up and down“consider the trade-off between saving credits by suspending a warehouse versus maintaining the cache of data from previous queries”
↩︎ Auto-suspend, auto-resume and the cache trade-off“There is no benefit to stopping a warehouse before the first 60-second period is over”
↩︎ Exam trap 2“restarts and runs for less than 60 seconds, it is billed for 121 seconds”
↩︎ Checkpoint“This cache is dropped when the warehouse is suspended”
↩︎ Checkpoint