# Snowflake Warehouse Spilling to Remote Storage

> Flags Snowflake warehouses whose p95 remote spill exceeds 1 GiB over 14 days, a sign the warehouse is too small for its queries.

Source: https://zop.dev/integrations/snowflake/recommendations/snowflake-warehouse-spilling-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](https://docs.snowflake.com/en/user-guide/performance-query-warehouse-memory) 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

```sql
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](https://docs.snowflake.com/en/sql-reference/account-usage/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](https://zop.dev/integrations/snowflake/recommendations/snowflake-warehouse-oversized), will say so.