Snowflake warehouses running under 30% load with no remote spill for 14 days

One size down halves the hourly rate

Snowflake warehouse sizes double in credits per hour at each step: 1 for X-Small on Gen1, then 2, 4, 8, 16 and upward. A warehouse that never works hard at its current size is paying for compute its queries do not use. Snowflake notes that larger is not necessarily faster for small, basic queries, and its sizing guidance is to match size to the complexity of the workload and to try the same queries on several sizes.

The catch is memory: a smaller warehouse has less of it, and queries that start spilling to remote storage slow down sharply. That is why this check looks at spill as well as load.

Looking at load and spill together

WAREHOUSE_LOAD_HISTORY gives query load in 5-minute intervals, and QUERY_HISTORY gives spill:

WITH load AS (
  SELECT warehouse_name,
         APPROX_PERCENTILE(avg_running, 0.95) AS p95_running
  FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_LOAD_HISTORY
  WHERE start_time >= DATEADD('day', -14, CURRENT_TIMESTAMP())
  GROUP BY warehouse_name
), spill AS (
  SELECT warehouse_name,
         MAX(bytes_spilled_to_remote_storage) AS max_remote_spill
  FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
  WHERE start_time >= DATEADD('day', -14, CURRENT_TIMESTAMP())
  GROUP BY warehouse_name
)
SELECT l.warehouse_name, l.p95_running, COALESCE(s.max_remote_spill, 0) AS max_remote_spill
FROM load l LEFT JOIN spill s ON s.warehouse_name = l.warehouse_name
ORDER BY l.p95_running;

AVG_RUNNING is Snowflake’s query load value for executing queries. It will not match ZopNight’s load percentage exactly, so treat it as a relative signal. The load view needs USAGE_VIEWER; QUERY_HISTORY needs GOVERNANCE_VIEWER.

Low load and almost no spill over 14 days

ZopNight reads the warehouse’s load percentage and its remote spill over the last 14 days and takes the 95th percentile of each. The finding fires when p95 load is below 30% and p95 remote spill is below 1 MiB (1,048,576 bytes). Both must hold: a lightly loaded warehouse that spills is not a downsizing candidate.

Warehouses kept at their size

If either series is missing or empty, no finding is raised. The finding is also dropped when ZopNight has no monthly cost for the warehouse, so there is never a zero-dollar resize suggestion. Warehouses that burn credits with no queries at all are handled by Snowflake Warehouse Idle instead, and heavy spillers by Snowflake Warehouse Spilling to Remote Storage.

Half the monthly cost

Because each size step doubles the credit rate, one step down halves it for the same running time. ZopNight takes the warehouse’s monthly cost, priced from its credits at the edition’s credit rate, and counts half of it:

saving = monthly warehouse cost x 0.5

If the smaller warehouse runs queries for longer, part of that saving is given back, which is why the fix below includes a check.

Stepping down one size

1. Resize: ALTER WAREHOUSE bi_wh SET WAREHOUSE_SIZE = 'SMALL'; Queries already running keep their current resources; queued and new queries use the new size.
2. Watch spill and queuing (AVG_QUEUED_LOAD) for a few days.
3. If remote spill appears or queuing grows, go back up one size.