Virtual Warehouses, Performance & Cost
Compute is 80 to 90% of a Snowflake bill, and Domain 4 (21% of the exam) tests it almost entirely through scenarios where two answers work and one is cheaper. Resizing always works, which is exactly why it's the planted wrong answer next to clustering, SOS, and multi-cluster. Build three reflexes: spill means size up, queuing means add clusters, full scans mean fix pruning. And do the credit math on paper, because the doubling ladder shows up as plain arithmetic.
Virtual Warehouses
A warehouse decision is always one of two moves: scale up (a bigger cluster, for heavier queries) or scale out (more clusters, for more concurrent queries). Every question in this chapter reduces to knowing which move solves which problem.
Virtual Warehouses: Compute & Billing
A virtual warehouse is a named cluster of compute resources (CPU, memory, local SSD) that executes queries and DML. It holds no permanent data, can be created, resized, suspended, resumed, or dropped at any moment, and is billed in credits per second while running, with a 60-second minimum each time it starts.
How a warehouse actually touches data
"Compute" isn't one undifferentiated thing. Every node in a warehouse has three places data can sit while a query runs, and they trade off speed against size in the usual way: the faster the tier, the smaller and more temporary it is.
VIRTUAL WAREHOUSE · ONE NODE
TIER 1 · FASTESTRAMjoins, sorts, aggregations
rows into RAM
spill 1: RAM full
TIER 2 · FASTLocal SSD cacherecently read partitions
RAM and SSD cache are wiped when the warehouse suspends
cache miss: fetch partition
spill 2: SSD full, slowest case
STORAGE LAYER
TIER 3 · LARGESTRemote storageevery micro-partition
persistent, shared by every warehouse
Now put the three tiers in motion. Every query walks the same four steps, top to bottom, and each step maps to something you can see in the Query Profile. Follow one query through the machine:
Read the flow backwards and the diagnostics fall out: spill at step 4 means not enough RAM (resize up), constant misses at step 2 mean a cold or churned cache (check AUTO_SUSPEND), a long reading list at step 1 means pruning failed (clustering, next chapter).
"Think in RAM, cache on SSD, live on the cloud." Processing happens in RAM. The SSD is a waypoint that remembers what was recently fetched. Remote storage is where data actually lives, permanently, for every warehouse to share. A bigger warehouse doesn't just add CPU, it adds RAM and SSD too, which is exactly why resizing up fixes both slow processing and spilling.
Sizes and credit rates
Each size step doubles the nodes and doubles the credit rate. Memorize the doubling, because the exam asks arithmetic on it.
| Size | Credits / hour | Rough mental model |
|---|---|---|
| X-Small | 1 | 1 node · dev work, light loads |
| Small | 2 | 2 nodes |
| Medium | 4 | 4 nodes · typical BI |
| Large | 8 | 8 nodes · ELT |
| X-Large | 16 | 16 nodes |
| 2X-Large | 32 | and so on, doubling |
| 3X-Large | 64 | |
| 4X-Large | 128 | largest on every cloud (5XL/ |
The doubling has a consequence the exam loves: a query that parallelizes well runs about twice as fast on the next size up, at the same total cost. Twice the credits per second for half the seconds is a wash. Bigger is only more expensive when queries can't use the extra parallelism, or when the warehouse sits idle at the higher rate.
Warehouse types: Standard vs Snowpark-Optimized
Every size above is really a size of the Standard warehouse type. There's a second type built for a different bottleneck: memory instead of compute.
A Snowpark-optimized warehouse (WAREHOUSE_TYPE = 'SNOWPARK-OPTIMIZED') gives each node 16x the memory of a Standard node by default (memory option MEMORY_16X, available from Medium upward; RESOURCE_CONSTRAINT picks other memory sizes). Everything else about it is unchanged: auto-suspend, multi-cluster, resource monitors, the resize rules from the billing section below. Only the memory-to-CPU ratio and the credit rate move.
The workload it exists for has a specific signature, and it's worth being able to spot: a Snowpark Python job (often ML training, or a large UDF/UDTF) fails with out-of-memory errors while CPU usage stays low. That combination, memory exhausted and CPU idle, is the tell. A standard warehouse can chase the same problem by resizing up, but resizing buys CPU and memory together, so the job pays for compute it never asked for; Snowpark-optimized buys memory alone, at a higher rate than Standard, but far below what four size-doublings would cost to reach the same headroom.
- Fix: Snowpark-optimized warehouse, similar size
- Not: resize up on Standard (buys unwanted CPU too)
- Fix: multi-cluster (previous chapter)
- Not: Snowpark-optimized (doesn't add concurrency)
One memory problem, one concurrency problem, two different tools; mixing them up is the exam's favorite way to test this page.
Billing mechanics
Three rules cover every billing question on the exam. One: the meter runs per second, from start to suspend, and only while the warehouse is running. Two: every start, including every auto-resume, bills a minimum of 60 seconds, so short bursts pay the floor. Three: resizing changes the rate from the moment the new nodes provision, but only queued and new queries use them; statements already executing finish on the resources they started with.
The third row is the trap in the wild: bursty workloads with aggressive auto-suspend pay the 60-second floor over and over. Absorbing several bursts in one running window is usually cheaper than parking after each one.
Idle cost: the meter doesn't care if anyone is querying
A running warehouse bills its full rate whether it's crunching a 40-way join or doing absolutely nothing. There is no "idle discount": started means billed, and the only free state is suspended. That makes forgotten warehouses the most common line item on a surprise bill, and AUTO_SUSPEND the guard rail against them. One evening at IsmaFrost makes the arithmetic concrete: a Medium warehouse (4 credits/hour) whose last query finishes at 18:00 and that nobody suspends.
| Same warehouse, same evening | What happens after 18:00 | Cost by 06:00 |
|---|---|---|
| Nobody suspends it | Runs idle all night: 12 hours × 4 credits/ | 48 credits |
| AUTO_ | Ten idle minutes, then it parks itself; suspended hours bill nothing | 0.7 credits |
Same machine, same night, roughly a 70x difference, and the only thing that changed is one setting. The 48 credits buy exactly what 12 fully loaded hours would buy, because the meter prices time running, not work done.
States and auto-management
A warehouse is either started, suspended, or resizing. Two settings do most of the operational work. AUTO_SUSPEND (default 600 seconds) suspends the warehouse after N seconds of inactivity. AUTO_RESUME (default TRUE) restarts it the instant a query arrives. Suspending discards the local disk cache, which Part 1 covered; the practical tuning rule is to set auto-suspend just above your workload's gap between bursts, not to the minimum the UI allows.
CREATE WAREHOUSE etl_whWAREHOUSE_SIZE = 'LARGE' AUTO_SUSPEND = 300AUTO_RESUME = TRUEINITIALLY_SUSPENDED = TRUE; -- don't start billing at creation ALTER WAREHOUSE etl_wh SET WAREHOUSE_SIZE = 'XLARGE'; -- applies to queued + new queries ALTER WAREHOUSE etl_wh SUSPEND;
Concurrency and timeout parameters
First, why queuing exists at all. A cluster is a fixed pool of CPU and RAM, and every statement admitted to it takes a slice. Admit too many at once and nobody has enough memory: everything slows down together and the heavy queries start spilling. So instead of degrading everyone, Snowflake caps admissions per cluster and makes the overflow wait in line. A queued query isn't broken or failed; it's protected, holding its place until a running statement finishes and frees a slot. That's also why the fix for chronic queuing is more clusters (more lines), never a timeout (shorter patience).
Three parameters, settable at warehouse level, tune this behavior. MAX_CONCURRENCY_LEVEL (default 8) caps how many statements run at once per cluster; beyond it, statements queue. Lowering it gives each running query a bigger slice of the warehouse, which can help heavy Snowpark or ELT jobs at the price of more queuing. STATEMENT_ (default 0, meaning never) aborts statements that wait in the queue too long. STATEMENT_TIMEOUT_IN_SECONDS (default 172,800, two days) aborts statements that run too long, a safety net against a runaway Cartesian product burning credits all weekend.
- Warehouses are created started unless you say
INITIALLY_SUSPENDED = TRUE. The minimum-60-seconds meter begins immediately. - Resizing does not interrupt running queries. They complete on the old size; the new size serves what's queued and what comes next.
- Concurrency questions: a single warehouse queues queries beyond its concurrency limit. The fix for queuing is more clusters (next page), never a bigger size. A bigger size fixes slow individual queries, not waiting queries.
- Credits double per size step; a well-parallelized query costs the same on any size, it just finishes sooner.
- 60-second minimum on every resume; per-second after that.
- Scale up for heavy queries, scale out for many queries. This one sentence answers a dozen exam questions.
Gen1 vs Gen2 standard warehouses
Snowflake offers a second generation of standard warehouses, selected with GENERATION = '2' (older syntax: RESOURCE_CONSTRAINT = STANDARD_GEN_2). What Snowflake documents is short: Gen2 runs on newer hardware with software improvements, and it's designed to make DML (UPDATE, DELETE, MERGE) and table scans faster. Gen2 is offered from X-Small to 4X-Large and consumes credits at its own per-hour rate for each size (see the service consumption table). New standard warehouses are created as Gen2 unless the region doesn't offer it, and, like every new standard warehouse, a new Gen2 warehouse starts with Query Acceleration on and a maximum scale factor of 8.
What Snowflake doesn't document is how Gen2 writes internally. Blog posts describe small "delta" files written beside unchanged partitions; treat that as an unofficial mental model, not a guaranteed behavior, and never use it to answer an exam question.
STATEMENTYour SQLsame statements on either
COMPUTE LAYER
GEN1 WAREHOUSESTANDARD_
GEN2 WAREHOUSESTANDARD_
both read and write
STORAGE LAYER
TABLE DATASame micro-partitionsone copy of the table data
Same for both: per-second billing with a 60-second minimum per start.
| Gen1 standard | Gen2 standard | |
|---|---|---|
| Sizes | X-Small to 6X-Large (5XL/ | X-Small to 4X-Large |
| What Snowflake states | The classic warehouse the exam assumes | Faster DML and table scans |
| Credit rate | 1, 2, 4, 8… credits per hour | Its own per-hour rate per size |
| Billing model | Identical: per second, 60-second minimum per start, nothing billed while suspended | |
| Micro-partitions | Same storage, same immutable micro-partitions; switching generation moves no data | |

