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
Scale OUT · more clusters
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.
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_ MAX_COUNT = 1 -- scale out for concurrency CLUSTER_ SCALING_COUNT = 3 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:
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/ {{ config(fct_ events. sql: the one model that needs muscle 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:
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:
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.How you catch spilling without opening a single profile: the SNOWFLAKE.ACCOUNT_ view (step 8) has bytes_spilled_to_local_storage and bytes_ columns. The right-sizing query at the end of this lesson uses them.
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:
SYSTEM$CLUSTERING_INFORMATION('orders','(order_date)') Once you've earned the right to scale, match the symptom to the lever:
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_ sum(elapsed_ time) / 1000 AS median_ seconds, bytes_ avg(spilled_ to_ remote_ storage) / power(1024, 3) AS gb_ spilled_ remote, queued_ FROM snowflake.overload_ time) / 1000 AS avg_ seconds_ queued account_ WHERE start_time >= dateadd('day', -7, current_usage. query_ history timestamp ()) AND warehouse_name IS NOT NULL GROUP BY warehouse_name ORDER BY gb_spilled_ remote DESC;

