Snowflake – Virtual Warehouses and Sizing

A virtual warehouse is the compute you rent. It is also, in nearly every account, the whole of the bill. This lesson is about the four decisions that determine what you spend: how big, how many, when it sleeps, and which workloads share one.

Sizes and what they cost

A warehouse has a T-shirt size. Each step up doubles the nodes, doubles the credits per hour, and — for work that parallelises — roughly halves the time.

Read that table twice, because it contains the least intuitive fact about Snowflake pricing: doubling the size of a warehouse is free if the query scales perfectly. Twice the credits per hour for half as many hours is the same number of credits. You get the answer sooner at no extra cost.

Which means the usual advice — "start small to save money" — is only half right. Starting small saves money when queries are small. When a query is genuinely large and parallelisable, a bigger warehouse is faster for the same spend, and the reason to hesitate is not cost.

The reason to hesitate is that the scaling is not perfect. Credits are metered per second with a 60-second minimum each time a warehouse resumes, so a query that finishes in ten seconds on a Medium bills a full minute either way — doubling the size there buys nothing and costs double. And a query that does not parallelise, because it is one small file or one un-splittable step, runs at about the same speed on a 6X-Large as on an X-Small while burning 512 times the credits.

So the practical rule: size up for large scans and heavy joins; do not size up for queries that are already fast. Change it and measure, because it is one statement:

ALTER WAREHOUSE learn_wh SET WAREHOUSE_SIZE = MEDIUM;

Resizing takes effect for queries that start afterwards; anything already running finishes on the old size. There is no downtime and no data movement, which is the point of separating storage from compute in the first place.

Auto-suspend, and the setting people get wrong

A running warehouse bills whether or not anyone is querying it. Auto-suspend stops it after a period of idleness; auto-resume starts it again when a query arrives.

CREATE WAREHOUSE bi_wh
  WAREHOUSE_SIZE      = SMALL
  AUTO_SUSPEND        = 300     -- seconds. NULL or 0 means never suspend.
  AUTO_RESUME         = TRUE
  INITIALLY_SUSPENDED = TRUE;

Two mistakes are common, in opposite directions.

Never suspending. AUTO_SUSPEND = NULL means the warehouse runs until somebody stops it by hand, and nobody ever does. This is the single most common source of a surprising Snowflake bill: not an expensive query, an idle warehouse nobody noticed over a weekend. There is essentially never a good reason for it.

Suspending too aggressively. Setting it to 60 seconds everywhere looks thrifty and can cost more. Suspending drops the warehouse's local SSD cache, so the next query re-reads from object storage — slower, and the extra seconds are billed. Worse, if a warehouse suspends and resumes repeatedly, each resume triggers the 60-second minimum charge again.

Sensible starting points, adjusted after watching real usage:

Multi-cluster: the other kind of scaling

Sizing up makes one query faster. It does nothing for fifty users at once — when a warehouse is saturated, further queries queue, and a bigger single cluster still has one queue.

A multi-cluster warehouse (Enterprise edition and above) answers that by starting additional clusters of the same size when queries begin to queue, and shutting them down when the rush passes:

CREATE WAREHOUSE bi_wh
  WAREHOUSE_SIZE      = SMALL
  MIN_CLUSTER_COUNT   = 1
  MAX_CLUSTER_COUNT   = 4       -- up to 4 × SMALL, only while needed
  SCALING_POLICY       = STANDARD
  AUTO_SUSPEND        = 300
  AUTO_RESUME         = TRUE;

The distinction is worth stating plainly, because interviews ask it and dashboards depend on it:

• Scale up (bigger size) — for one slow query. More nodes on the same problem.
• Scale out (more clusters) — for many concurrent queries. More copies of the same-sized machine.

SCALING_POLICY has two values. STANDARD starts a new cluster as soon as a query queues, favouring responsiveness. ECONOMY waits until there is enough backlog to keep a new cluster busy for several minutes, favouring credits. Use STANDARD where people are waiting and ECONOMY for batch.

Setting MIN_CLUSTER_COUNT equal to MAX_CLUSTER_COUNT keeps that many clusters running permanently — maximised mode. It removes any warm-up delay and bills for every cluster the whole time. Reach for it only when you know you need it.

Separating workloads

The reason Snowflake accounts end up with several warehouses is not scale, it is isolation. Since warehouses share storage but not compute, giving each workload its own means one cannot slow another down — and, just as usefully, the credit report tells you who spent what.

-- Loading: small, suspends fast, nobody is waiting on it.
CREATE WAREHOUSE loading_wh WAREHOUSE_SIZE = SMALL
  AUTO_SUSPEND = 60  AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;

-- Dashboards: concurrency matters more than raw size.
CREATE WAREHOUSE bi_wh WAREHOUSE_SIZE = SMALL
  MIN_CLUSTER_COUNT = 1 MAX_CLUSTER_COUNT = 3
  AUTO_SUSPEND = 600 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;

-- Analysts running whatever they like. Capped by a resource monitor (lesson 15).
CREATE WAREHOUSE adhoc_wh WAREHOUSE_SIZE = MEDIUM
  AUTO_SUSPEND = 120 AUTO_RESUME = TRUE INITIALLY_SUSPENDED = TRUE;

Three warehouses that are idle most of the day cost nothing, because a suspended warehouse is free. The cost of splitting is zero; the cost of not splitting is a dashboard that goes slow every time the nightly load overruns.

Sizing in practice

Rather than guessing, change one thing and read the Query Profile (lesson 11). Three signals tell you which direction to move.

That last row is the one worth holding onto. A bigger warehouse makes a badly-pruned query finish sooner while reading exactly as much data, and you pay for the privilege. Compute is the answer to "this is genuinely a lot of work"; it is not the answer to "this is doing work it should not be doing".

Snowflake also offers a couple of specialised options once the basics are in place — Query Acceleration Service, which offloads bursty scan work off-cluster, and Snowpark-optimized warehouses with more memory per node for heavy Python or Java workloads. Both are documented under warehouses in the docs, and neither is where to start.

Guard rails worth setting

Two parameters stop a single bad query doing unbounded damage:

-- Kill any query running longer than 30 minutes on this warehouse.
ALTER WAREHOUSE adhoc_wh SET STATEMENT_TIMEOUT_IN_SECONDS = 1800;

-- Give up rather than sit in a queue for more than 5 minutes.
ALTER WAREHOUSE adhoc_wh SET STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 300;

The default statement timeout is two days, which is not a limit anyone intends. Set it on every warehouse.

Checking what is running

SHOW WAREHOUSES;

-- Credits per warehouse over the last week. Latency on this view is up to ~3 hours.
SELECT warehouse_name,
       ROUND(SUM(credits_used), 2) AS credits
FROM   snowflake.account_usage.warehouse_metering_history
WHERE  start_time >= DATEADD('day', -7, CURRENT_TIMESTAMP())
GROUP  BY warehouse_name
ORDER  BY credits DESC;

Run that weekly. If a warehouse you have forgotten about is near the top, you have found the auto-suspend you did not set.

Next: databases, schemas and tables — the objects the compute is pointed at.