Course contents

SnowPro Core COF-C03 Study GuidePart 2: Warehouses and performanceVirtual Warehouses: Compute & Billing

Virtual Warehouses: Compute & Billing

Part 2

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

Definition

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.

The three places data sits while a warehouse runs a query. In the virtual warehouse, each node has RAM, the fastest and smallest tier, where joins, sorts and aggregations run, and a local SSD cache of recently read partitions. Below, in the shared storage layer, remote storage holds every table's micro-partitions. On a cache miss a partition is fetched from remote storage into the SSD cache, then rows move into RAM. When RAM is full, work spills to the local SSD (spill 1); when the SSD is full too, it spills to remote storage (spill 2), the slowest case. RAM and the SSD cache are wiped when the warehouse suspends.VIRTUAL WAREHOUSE · ONE NODESTORAGE LAYERTIER 2 · FASTLocal SSD cacherecently read partitionsTIER 1 · FASTESTRAMjoins, sorts, aggregationsTIER 3 · LARGESTRemote storageevery micro-partitioncache miss:fetch partitionspill 2: SSD full,slowest caseRAM and SSD cache are wipedwhen the warehouse suspendspersistent, shared by every warehouserows into RAMspill 1: RAM full

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

Figure 2.1 Faster tiers are smaller and more temporary. Data climbs from remote storage through the SSD cache into RAM; spills go back down, first to the local SSD, then to remote storage, and each step slows the query. The SSD cache belongs to the warehouse and is wiped when it suspends.

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:

1 · The optimizer picks the reading list
Before any node touches data, pruning (Part 1) turns "scan the table" into "read exactly these micro-partitions." A short list here is the cheapest performance win that exists.
compile time
↓ then, for each partition on the list
2 · Look in the local SSD cache first
Already fetched on a previous query? Read it from local disk, fast. Not there? Pull it from remote storage, slow, and keep a copy so the next query gets the fast path.
hit: fast · miss: slow
↓ rows move up into memory
3 · Do the actual work in RAM
Joins, sorts, and aggregations all execute in node memory. As long as the working set fits, this is the fastest place in the system.
fastest tier
↓ only if RAM runs out
4 · Overflow: spill down the tiers
Excess goes to local SSD first; if that fills too, to remote storage, and the query crawls. The profile reports this as "bytes spilled to local / remote storage," and it's the single clearest signal to size the warehouse up.
the slowdown

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).

Memory aid

"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.

SizeCredits / hourRough mental model
X-Small11 node · dev work, light loads
Small22 nodes
Medium44 nodes · typical BI
Large88 nodes · ELT
X-Large1616 nodes
2X-Large32and so on, doubling
3X-Large64
4X-Large128largest on every cloud (5XL/6XL exist on AWS and Azure)

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.

Definition

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.

Symptom: memory exhausted, CPU idle
  • Fix: Snowpark-optimized warehouse, similar size
  • Not: resize up on Standard (buys unwanted CPU too)
Symptom: queries queuing, each one fast alone
  • 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.

Three separate starts of the same warehouse, each followed by a suspend. Start 1 runs a 5-second query and is billed 60 seconds. Start 2 runs a 90-second query and is billed 90 seconds. Start 3 runs a 20-second query and is billed 60 seconds. A dashed line marks the 60-second minimum that applies to every start. Between starts the warehouse is suspended and bills nothing.60 s minimum per startStart 1resume, 5 s query60 s billed5 sStart 2resume, 90 s query90 s billedStart 3resume, 20 s query60 s billed20 s0 s30 s60 s90 s120 sEach row is its own start. Between starts the warehouse is suspended: 0 credits. Three separate starts of the same warehouse, each followed by a suspend. Start 1 runs a 5-second query and is billed 60 seconds. Start 2 runs a 90-second query and is billed 90 seconds. Start 3 runs a 20-second query and is billed 60 seconds. A dashed line marks the 60-second minimum that applies to every start. Between starts the warehouse is suspended and bills nothing.60 s minimum per startStart 160 s billedresume, 5 s query5 sStart 290 s billedresume, 90 s queryStart 360 s billedresume, 20 s query20 s0 s30 s60 s90 s120 sEach row is its own start. Between startsthe warehouse is suspended: 0 credits.
Figure 2.2 Every start bills at least 60 seconds, then per second. Each row is one start: the green bar is the query, the amber bar is what you pay. Thirty resume-and-suspend cycles for 5-second queries in an hour cost 30 minutes of credits for 2.5 minutes of work, which is why AUTO_SUSPEND shouldn't be set shorter than the gap between queries.

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 eveningWhat happens after 18:00Cost by 06:00
Nobody suspends itRuns idle all night: 12 hours × 4 credits/hour, zero queries executed48 credits
AUTO_SUSPEND = 600Ten idle minutes, then it parks itself; suspended hours bill nothing0.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.

The tradeIdle time isn't entirely worthless: a running warehouse keeps its local SSD cache warm, so the next query skips the cold reads. That's the whole tension AUTO_SUSPEND tunes: suspend fast and pay cold caches, suspend slow and pay idle credits. Set it just above the workload's natural gap between bursts, and remember the exam framing: suspended = 0 credits, started = full rate, always.

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_wh
  WAREHOUSE_SIZE = 'LARGE'
  AUTO_SUSPEND = 300
  AUTO_RESUME = TRUE
  INITIALLY_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_QUEUED_TIMEOUT_IN_SECONDS (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.

Exam traps
  • 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.
Key takeaways
  • 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.

Gen1 and Gen2 standard warehouses side by side. The same SQL runs on either. In the compute layer, STANDARD_GEN_1 offers X-Small to 6X-Large at the classic credit rate; STANDARD_GEN_2 offers X-Small to 4X-Large at its own credit rate and is documented by Snowflake as faster for DML and scans. In the storage layer below, both read and write the same micro-partitions. Both bill per second with a 60-second minimum per start.COMPUTE LAYERSTORAGE LAYERSTATEMENTYour SQLsame statements on eitherGEN1 WAREHOUSESTANDARD_GEN_1X-Small to 6X-Largeclassic credit rateGEN2 WAREHOUSESTANDARD_GEN_2X-Small to 4X-Large · own credit ratefaster DML and scans (per Snowflake)TABLE DATASame micro-partitionsone copy of the table databoth read and writeSame for both: per-second billing with a 60-second minimum per start.

STATEMENTYour SQLsame statements on either

COMPUTE LAYER

GEN1 WAREHOUSESTANDARD_GEN_1X-Small to 6X-Largeclassic credit rate

GEN2 WAREHOUSESTANDARD_GEN_2X-Small to 4X-Large · own credit ratefaster DML and scans (per Snowflake)

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.

Figure 2.3 Choosing a generation changes the compute, not the data or the billing model. Read top to bottom: the same SQL runs on either generation, and both read and write the same micro-partitions. Gen2 stops at 4X-Large and has its own credit rate. Snowflake documents Gen2 as faster for DML and scans but doesn't document its internal write mechanism, so treat any delta-file explanation as unofficial.
Gen1 standardGen2 standard
SizesX-Small to 6X-Large (5XL/6XL on AWS and Azure)X-Small to 4X-Large
What Snowflake statesThe classic warehouse the exam assumesFaster DML and table scans
Credit rate1, 2, 4, 8… credits per hourIts own per-hour rate per size
Billing modelIdentical: per second, 60-second minimum per start, nothing billed while suspended
Micro-partitionsSame storage, same immutable micro-partitions; switching generation moves no data
Exam noteCOF-C03 questions assume the documented model: immutable micro-partitions and copy-on-write on DML. Sizes, billing, resizing and suspension follow the same rules on both generations. Whether Gen2 is cheaper for your workload depends on whether its speed-up outweighs its rate, so measure your own queries before switching.