skip to content

In Snowflake, what is a virtual warehouse and what changes when you resize it from X-Small to Large?

level: juniorimportance: must knowfreq 85%

answer

  1. compute, not storage
  2. T-shirt sizes with a doubling ladder
  3. one query faster, not more queries
  4. each step doubles nodes and credits per hour

basics

~20 s

A Snowflake virtual warehouse is a named cluster of compute nodes that runs queries, DML and loads; it stores no data. Each size step up doubles the compute in the cluster and doubles the credits billed per running hour.

solid answer

~50 s

A virtual warehouse is Snowflake's unit of compute: a cluster of cloud VMs with CPU, memory and local SSD that executes queries, DML and loads. It owns no data — storage is separate, so many warehouses read the same tables without copying anything. Sizes run X-Small, Small, Medium, Large, X-Large, then 2X-Large up to 6X-Large, and **each step doubles both the compute in the cluster and the credit rate**: X-Small burns 1 credit per running hour, Small 2, Medium 4, Large 8, X-Large 16. Resizing is just a property change (`ALTER WAREHOUSE ... SET WAREHOUSE_SIZE = 'LARGE'`) — no data moves, and in-flight queries finish on the existing compute while new queries get the new size. A bigger size helps scans, large sorts and joins that spill; it does nothing for how *many* queries can run at once.

code

sql · 6 lines
sql
CREATE WAREHOUSE analytics_wh
  WAREHOUSE_SIZE = 'XSMALL'
  INITIALLY_SUSPENDED = TRUE;

-- one step up: twice the compute, twice the credits per running hour
ALTER WAREHOUSE analytics_wh SET WAREHOUSE_SIZE = 'SMALL';

go deeper

for a junior

Be ready to name the size ladder, say that compute is separate from storage, and state that each size step doubles both the cluster and the credits per hour.

for a middle

Explain per-second billing with the 60-second minimum on each start, why a perfectly parallel query costs the same at any size, and which query shapes ignore extra compute.

for a senior

Show how you decide a size from evidence — spilling to local versus remote storage, scan time versus compile time — and how you schedule different sizes for ETL versus dashboard windows.

for a principal

Own the policy: who may create or resize warehouses, what default size a new team gets, and how sizing decisions are reviewed so the account does not drift into a fleet of oversized idle clusters.

## What a virtual warehouse actually is Snowflake separates a database into three layers: cloud services (optimizer, metadata, authentication), storage (immutable columnar files in the cloud provider's object store), and compute. A **virtual warehouse** is the compute layer in unit form — a named cluster of cloud VMs, each with CPU, memory and local SSD, that you create, suspend, resume and resize with SQL. It holds no persistent data of its own. Every warehouse in the account reads the same tables from the same storage layer, which is why one team's warehouse cannot block another team's reads: they are independent compute clusters over shared data. A warehouse is required for anything that touches data rows — `SELECT`, `INSERT`/`UPDATE`/`DELETE`/`MERGE`, `COPY INTO`. Pure metadata operations (`SHOW`, `DESCRIBE`, many `INFORMATION_SCHEMA` lookups, `CREATE TABLE`) are served by cloud services and do not need a running warehouse. ## The size ladder Sizes are T-shirt sized: X-Small, Small, Medium, Large, X-Large, 2X-Large, 3X-Large, 4X-Large, 5X-Large, 6X-Large. Two things double at every step: the amount of compute in the cluster, and the **credit rate**. X-Small consumes 1 credit per hour of running time, Small 2, Medium 4, Large 8, X-Large 16, and the doubling continues up the ladder. A credit is Snowflake's abstract compute unit; what a credit costs in money depends on your edition, cloud and region — never assume a dollar figure. Billing is per second of running time, with a **60-second minimum each time the warehouse starts or resumes**. That minimum is the single most important billing detail at this level: a warehouse woken up for a three-second query still bills a full minute. ## What a bigger size actually buys More nodes means more parallel workers scanning more files at once, and more aggregate memory and local disk for intermediate results. Concretely, size fixes: - **Long scans** — a query reading many micro-partitions finishes sooner because the work is split more ways. - **Spilling** — a large sort, join build side or high-cardinality aggregation that exceeds memory spills first to local SSD and then to remote storage; remote spilling is brutally slow, and more memory per cluster is the direct fix. Size does *not* fix: - **Concurrency.** How many queries can run simultaneously is a different lever (a multi-cluster warehouse), not a bigger single cluster. - **Selective point lookups.** A query touching a handful of micro-partitions is already fast; extra nodes sit idle. - **Compilation-bound or single-threaded work.** A query dominated by planning, or by one non-parallelizable step, gets nothing. - **Bad pruning.** If a query scans everything because the data isn't organised for it, a bigger warehouse just scans everything faster and at double the rate. ## The "double the size, double the bill" illusion Because billing is per second, doubling the size doubles the *rate*, not necessarily the *bill*. If a query scales perfectly — 8 minutes on Medium, 4 minutes on Large — the credits consumed are identical and you got the answer twice as fast for free. If the query does not parallelize, you paid double for nothing. This is why testing one size up is cheap and is the standard first experiment: run the same query at two sizes (disable result reuse for the test with `ALTER SESSION SET USE_CACHED_RESULT = FALSE`) and compare both elapsed time and credits. ## Resizing mechanics ```sql ALTER WAREHOUSE analytics_wh SET WAREHOUSE_SIZE = 'LARGE'; ``` The change is a property update: no data is moved or reloaded, because the warehouse never owned data. Queries already running continue on the compute they started with; queries submitted afterwards use the new size. If the warehouse is suspended, the new size applies when it next resumes. Because sizing is this cheap to change, warehouses are routinely resized on a schedule — a Large for the nightly ETL window, an X-Small the rest of the day. ## How to answer this in an interview Say what the warehouse is (compute, separate from storage, shared data), what the size ladder does (doubling nodes and doubling credits per hour), and then immediately draw the line the interviewer is fishing for: **size is for making one query faster, cluster count is for running more queries at once.** Candidates who conflate the two are the ones who "fix" a morning dashboard pile-up by moving from Medium to 2X-Large and are surprised when the queue is still there and the bill quadrupled.

  • If a query drops from 8 minutes on Medium to 4 minutes on Large, what happened to its credit cost?
    It stayed roughly the same. Large bills at twice the credit rate of Medium but ran for half the time, so credits consumed are about equal — you bought latency for free. That linear scaling only holds while the query is genuinely parallelizable; a query that barely speeds up on the bigger size really does cost double.
  • Which query problems will a larger Snowflake warehouse not fix?
    Anything that isn't compute-bound: queries queueing because too many run at once (that needs more clusters, not a bigger one), highly selective lookups that already touch few micro-partitions, compilation-heavy or single-threaded steps, and poor pruning. Sizing up a badly pruned query just scans the same data faster at double the credit rate.
  • Do all Snowflake operations require a running warehouse?
    No. Statements answered from metadata in the cloud services layer — `SHOW`, `DESCRIBE`, DDL such as `CREATE TABLE`, and many `INFORMATION_SCHEMA` queries — run without a warehouse. Anything that reads or writes table rows, including `SELECT`, DML and `COPY INTO`, needs one running.

saying these in an interview costs you the question

  • Saying a virtual warehouse stores the data it queries
  • Claiming a bigger warehouse lets more queries run concurrently
  • Assuming doubling the size always doubles the credit cost
  • Thinking resizing moves or reloads data and needs downtime
  • Recommending a bigger size for a query that scans too much

context

resolving links…