Warehouses running below 30% load with negligible spill are one size too big

Half the size, half the rate, same work

Warehouse sizes are a strict doubling ladder: each step down halves the credits per hour: 4X-Large 128, down through Large 8, Medium 4, Small 2, X-Small 1. A warehouse whose queue rarely has a full query’s worth of work in flight is buying parallelism it does not use, and one step down would serve the same queries at half the rate. The catch is proving “rarely” with data rather than vibes, which is what this rule’s two gates do.

Two gates and a veto

Gate one: snowflake.warehouse.load_pct at the 95th percentile over 14 days must be under 30. The series comes from WAREHOUSE_LOAD_HISTORY, the hourly average of AVG_RUNNING, the number of queries executing concurrently, scaled by 100. Gate two is the veto: remote spill at p95 must be under 1,048,576 bytes (1 MiB), because a warehouse already spilling to object storage will spill catastrophically at half the memory, and no rate saving survives that. Both must hold; spill evidence silently cancels the load evidence.

Read the load series yourself

SELECT warehouse_name,
       DATE_TRUNC('hour', start_time) AS bucket,
       AVG(avg_running) * 100 AS load_pct
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY
WHERE start_time >= DATEADD('day', -14, CURRENT_TIMESTAMP())
GROUP BY 1, 2
ORDER BY 1, 2;

Sustained values near 100 mean roughly one query always executing; a p95 under 30 means even busy hours leave most capacity idle.

The multi-cluster quirk in the number

On multi-cluster warehouses AVG_RUNNING sums running and queued work across clusters, so load_pct can legitimately exceed 100. That never causes a false flag here, since the rule compares against a low threshold and a value above 100 simply reads as “not idle”. It is why this number should not be treated as a normalized 0-to-100 utilization gauge elsewhere.

Confidence that scales with headroom

The finding’s confidence is not flat: it rises linearly as measured load falls from the 30 threshold toward 0, so a warehouse at p95 load of 5 outranks one at 28 in the queue. Downsizing is one statement, ALTER WAREHOUSE reporting_wh SET WAREHOUSE_SIZE = 'MEDIUM', which takes effect for new queries immediately and is just as easily reversed if p95 latency shifts.