Snowflake warehouses whose queries spill more than 1 GiB to remote storage

Spilling stretches runtime, and runtime is the bill

When a query needs more memory than the warehouse has, Snowflake spills intermediate data first to local disk and then, if that is not enough, to remote cloud storage. Snowflake describes the performance drop as drastic, and worse for remote spill. Because warehouses bill per second while they run, the extra runtime a spilling query adds is paid for at the warehouse’s full rate.

The documented remedies are a larger warehouse, which adds memory and local storage, or processing data in smaller batches.

Finding the spilling queries

SELECT warehouse_name, warehouse_size, query_id,
       SUBSTR(query_text, 1, 60) AS query_start,
       ROUND(bytes_spilled_to_local_storage  / POWER(1024, 3), 2) AS local_gib,
       ROUND(bytes_spilled_to_remote_storage / POWER(1024, 3), 2) AS remote_gib
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('day', -14, CURRENT_TIMESTAMP())
  AND bytes_spilled_to_remote_storage > 0
ORDER BY bytes_spilled_to_remote_storage DESC
LIMIT 20;

QUERY_HISTORY needs the GOVERNANCE_VIEWER role. The Query Profile for any listed query shows which operator is spilling, which tells you whether a bigger warehouse or a rewrite is the better fix.

Remote spill above 1 GiB at p95

ZopNight reads the warehouse’s remote spillage series over the last 14 days and takes the 95th percentile. When that value is above 1 GiB (1,073,741,824 bytes), the warehouse is flagged. Using p95 rather than the maximum means a single pathological query does not trigger the finding; the spilling has to be a regular part of the workload.

Spill it tolerates

Local spill is not considered at all, only remote. Warehouses without a remote spill series, or with no datapoints in the window, are not flagged. If query acceleration is enabled, Snowflake notes that a small nonzero remote spill value is expected even when acceleration is not used.

No saving figure, by design

This finding is governance with no dollar amount. Moving one size up doubles the hourly credit rate, so the result depends on how much faster the spilling queries finish, which cannot be known in advance. The cost of leaving it is long runtimes billed at the current rate, plus the wait time for anyone queued behind them.

Removing the spill

1. Open the Query Profile for the worst queries and check whether a join or aggregate can be filtered earlier or split into smaller batches.
2. If the queries are already reasonable, move up one size: ALTER WAREHOUSE transform_wh SET WAREHOUSE_SIZE = 'LARGE'; New resources serve queued and new queries; queries already running are not affected.
3. Re-run the spill query after a few days and compare runtime and credits. If the larger size is now lightly loaded, the reverse check, Snowflake Warehouse Oversized, will say so.