Course contents

The Analytics Engineer’s Field GuideSnowflake: the engineStep 5: Warehouses

Warehouses

Part 4 · Snowflake · step 5

Warehouses: sizing and scaling compute

Compute is a dial. Turn it the right way for the job, and turn it off when idle.

· · ·

When better SQL isn't enough, Ismail reaches for the warehouse. A warehouse is the compute that runs your SQL, with two independent dials, size and clusters, and they solve different problems. Getting them right is most of your Snowflake bill.

Two knobs, and they fix different problems

Because compute is separate from data, you can change it without touching anything else. There are exactly two ways, and picking the wrong one is the most common mistake in this lesson:

Scale UP · a bigger warehouse
One query is too slowXS → L gives it more memory and more parallelism.
Fixes: heavy single jobsA giant nightly rebuild, a monstrous join.
Scale OUT · more clusters
Too many queries at onceA multi-cluster warehouse adds clusters when queries queue, drops them when they stop.
Fixes: crowdsForty analysts hitting the same warehouse at 9am.

Both happen on the compute layer alone. Neither moves your data, and neither disturbs anybody else's queries. The two sections below take them one at a time.

Size: make one query faster

Warehouses come in t-shirt sizes. Each step up doubles the compute and the cost per hour, and gives a single heavy query more memory and more parallelism.

Size
Cost / hour
Same query
Reach for it when
XS
1×
60 min
Small models, tests, light ad-hoc.
S
2×
~30 min
The default for a daily dbt run on a modest project.
M
4×
~15 min
Bigger builds, or when queries start spilling to disk.
L
8×
~8 min
One heavy nightly job with a real deadline.
XL
16×
~4 min
Rare. Prove the query can't be fixed first.
Why doubling is often free
Twice the machines at twice the price, running half as long, costs about the same, because Snowflake bills per second. So sizing up is usually a wash on the bill and a win on the clock, and it buys the query more memory so it stops spilling to disk. The times above assume the query actually parallelises, which is the part worth checking in the query profile. And it breaks down for short queries: every resume bills at least 60 seconds, so a 10-second query costs a full minute on any size, and on an XL that minimum is 16 times the XS one.

The credit rates in this table are for standard warehouses. If Snowsight offers you newer warehouse generations or Snowpark-optimized warehouses, they bill at different rates per size: check Snowflake's rate table before comparing.

Clusters: handle many queries at once

Size does nothing for concurrency. When lots of users hit a warehouse at once and queries start queuing, a multi-cluster warehouse (Enterprise edition) adds whole clusters to run them in parallel, then removes them when demand drops. Each query still runs at the warehouse's size, you just get more of them running at once, and each extra cluster bills like a second warehouse of that size while it's up.

The SCALING_POLICY decides how eager it is. STANDARD, the default, starts a cluster as soon as a query queues: best for dashboards where people are waiting. ECONOMY only starts one when there's enough queued work to keep it busy for about six minutes: queries wait a little longer, you pay for fewer half-idle clusters. On Standard edition, the honest substitute is one warehouse per workload.

Both settings live on the same object, so one CREATE WAREHOUSE covers them:

CREATE WAREHOUSE bi_wh
  WAREHOUSE_SIZE    = 'MEDIUM'
  AUTO_SUSPEND      = 300         -- BI: queries every few minutes (see below)
  AUTO_RESUME       = TRUE
  MIN_CLUSTER_COUNT = 1           -- scale out for concurrency
  MAX_CLUSTER_COUNT = 3
  SCALING_POLICY    = 'STANDARD';  -- start clusters as soon as queries queue

-- resize any time: running queries finish on the old size, new ones get the new size
ALTER WAREHOUSE etl_wh SET WAREHOUSE_SIZE = 'LARGE';

Suspend when idle, and split by workload

AUTO_SUSPEND stops the clock after a set number of idle seconds and AUTO_RESUME wakes it on the next query, so an idle warehouse costs nothing. Two catches decide the right value:

  • Every resume bills at least 60 seconds. Billing is per second, but only after a one-minute minimum each time the warehouse starts. A warehouse that suspends after 60 seconds and gets a query every 90 seconds pays a fresh minimum on every wake-up.
  • Suspending drops the local cache (step 6), so the first query after a resume reads cold from remote storage.

So set it per workload, by looking at the gaps between queries rather than picking one number for everything:

Warehouse
AUTO_SUSPEND
Why
ELT / dbt
60 s
Runs in bursts, then nothing for hours. Sleep the moment the run ends.
BI / dashboards
300 to 600 s
Queries arrive every few minutes all day. A short suspend means constant resumes, each with its 60-second minimum and a cold cache.
Ad-hoc / dev
60 s
Humans think between queries. An open worksheet must not keep a warehouse awake.

And give each workload its own warehouse, so a heavy load job can't starve the dashboards, and you can size and bill each separately. In dbt that's one config: route the heavy model to a bigger warehouse and leave the rest on the default.

-- models/marts/fct_events.sql: the one model that needs muscle
{{ config(materialized='incremental', snowflake_warehouse='etl_wh_l') }}

Spilling: when the warehouse runs out of memory

Every warehouse has a fixed amount of RAM, more on a bigger size. When an operation needs more working memory than it has, Snowflake spills the overflow to disk, and the query slows down sharply. There are two levels, and they're not equal:

In the query profile
What it means
Bytes spilled to local storage
Ran out of RAM, paged to the warehouse's local SSD. Slower than memory, but bearable.
Bytes spilled to remote storage
Overflowed the local SSD too, now paging to remote object storage. Dramatically slower, and the clearest signal the query is under-resourced.

What causes it: memory-hungry operations on data too big to fit in RAM, large sorts (ORDER BY on a huge result), big joins that build a large hash table, wide aggregations and window functions with many partitions, and SELECT DISTINCT over lots of rows. A join that fans out and multiplies rows is a common hidden cause.

How to fix it, in order:

1
Feed the operator less dataFilter and prune earlier, select fewer columns, aggregate before you join. Less data in means less to spill.
2
Cut the expensive operationDrop an unneeded ORDER BY, fix any join that's fanning rows out (that's usually what a DISTINCT was hiding), and use APPROX_COUNT_DISTINCT where an exact count isn't needed.
3
Then, and only then, size upIf it's genuinely a big job and the data's already minimal, a bigger warehouse gives more RAM and the spill disappears. Spill is the one symptom a bigger warehouse genuinely cures, just not before the first two fixes.

How you catch spilling without opening a single profile: the SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view (step 8) has bytes_spilled_to_local_storage and bytes_spilled_to_remote_storage columns. The right-sizing query at the end of this lesson uses them.

Remote spill is a red flag
Local spill on a heavy query is sometimes acceptable. Remote spill almost never is, it usually means the warehouse is too small for the work, or the query is reading far more data than it needs. Check pruning first, then size.

When sizing up is the wrong fix

A bigger warehouse is the reflex when a query is slow, and it's often the wrong move. More compute only helps when compute is the bottleneck, and scaling a badly-pruned query just makes the wrong thing cost more. Walk this order every time, and treat the warehouse as the last lever, not the first:

#
Check
What it tells you
1
Partitions scanned vs total, in the query profile
Scanned near total on a filtered query means pruning, not compute, is the problem.
2
SYSTEM$CLUSTERING_INFORMATION('orders','(order_date)')
A high average depth means the table isn't organised for the column you filter on (step 3).
3
Exhaust pruning first
Fix the filter (bare column, matching types), cluster on the filter column, add Search Optimization for selective point lookups.
4
Only now touch the warehouse
Still slow after all that? Spilling means size up. Queueing under real concurrency means scale out. Never before row 3.

Once you've earned the right to scale, match the symptom to the lever:

Symptom
The real fix (not a bigger warehouse)
Full table scan
A pruning problem. A bigger warehouse reads all the data faster but still reads all of it. Fix the filter or cluster the table.
Queries queueing
A concurrency problem. Add clusters (scale out). Size does nothing for the number of queries running at once.
One small, quick query
Nothing to parallelise. A bigger warehouse costs more for the same millisecond.
Spilling to disk
This is the one where sizing up genuinely helps: more memory, no spill. Or cut the data reaching the operator.

Is my warehouse the right size? Ask the history

You don't have to guess. Three columns of QUERY_HISTORY say whether a warehouse is too small, too crowded, or fine. It's an ACCOUNT_USAGE view, readable only with the right grants: ask your admin for the GOVERNANCE_VIEWER and USAGE_VIEWER database roles on the SNOWFLAKE database (step 8 explains why both). queued_overload_time is the time a query waited because the warehouse was busy, that's where queueing becomes visible:

-- last 7 days, per warehouse: spilling, queueing, typical runtime
SELECT warehouse_name,
       count(*)                                                   AS queries,
       median(total_elapsed_time) / 1000                       AS median_seconds,
       sum(bytes_spilled_to_remote_storage) / power(1024, 3)   AS gb_spilled_remote,
       avg(queued_overload_time) / 1000                        AS avg_seconds_queued
FROM snowflake.account_usage.query_history
WHERE start_time >= dateadd('day', -7, current_timestamp())
  AND warehouse_name IS NOT NULL
GROUP BY warehouse_name
ORDER BY gb_spilled_remote DESC;
What you see
What it means
Remote spill on a few big queries
Too small for those jobs. Fix their SQL first, then size up, or run them on a bigger warehouse of their own.
Queued time climbs at peak hours
Too crowded, not too small. Scale out (more clusters), or split the workload.
No spill, no queue, median of a few seconds
Try one size smaller for a week. If nothing gets worse, keep the saving.
The rules of thumb
Size up for a single heavy query (often cost-neutral, and it stops spilling). Scale out for many concurrent users. Set auto-suspend from the gaps between queries. Run ELT, BI, and ad-hoc on separate warehouses, and check spill and queue time weekly.

Lesson quiz

  1. One heavy query spills to disk and nothing is queueing. The SQL is already lean. Which dial do you turn?

    1. Lower AUTO_SUSPEND to keep the cache fresh
    2. Switch SCALING_POLICY to ECONOMY
    3. Size the warehouse up for more memory
    4. Add clusters with a multi-cluster setup
  2. At 9am, dashboards queue on one warehouse, though each query alone is fast. What's the fix the lesson recommends?

    1. Scale out: a multi-cluster warehouse adds clusters while queries queue
    2. Raise AUTO_SUSPEND so the warehouse is already awake before 9am
    3. Run the dashboards one after another so they stop competing
    4. Size up: a larger warehouse gives each dashboard query more memory and CPU
  3. A nightly 10-second query runs on an X-Large warehouse that's suspended before and after it. Ismail suggests X-Small. What does that do to the cost of each run?

    1. Nothing, since per-second billing makes size a wash
    2. It raises it, since the query runs 16 times longer on X-Small
    3. It cuts it: an XL's 60-second minimum costs 16 times more
    4. Nothing, since a 10-second run is billed 10 seconds