snowflake query spilling to remote disk fix

TL;DR

Spilling means the query needed more memory than the warehouse nodes have. Fix it by using a larger warehouse, reducing the working set (fewer columns, pre-aggregation), or restructuring the join. Spill is correctness-preserving but brutally slow.

snowflake query spilling to remote disk fix

Use this when

• Query profiles show "bytes spilled to remote disk"
• A query is slow despite a reasonable plan
• You are sizing a warehouse for heavy analytics

Not for this skill when

• BigQuery queries queue on slots
• You are hitting zero-copy clone limitations
• dbt models need tuning

Steps

1. Confirm the spill in the query profile:

SELECT query_id, bytes_spilled_to_remote_storage
FROM snowflake.account_usage.query_history
WHERE bytes_spilled_to_remote_storage > 0
ORDER BY start_time DESC LIMIT 10;

Expected output: the queries that spilled and how many bytes. Local-disk spill is a warning; remote-disk spill is the performance killer.

1. The fastest fix is usually a bigger warehouse. Spill is per-node memory:

ALTER WAREHOUSE analytics SET warehouse_size = 'LARGE';

Expected output: each node gets more RAM, so the same query spills less or not at least. Doubling the warehouse doubles per-node memory (and cost per second, but the query finishes faster).

1. Reduce the working set before the heavy operation:

-- aggregate before the join, not after
WITH daily AS (
  SELECT user_id, date, sum(amount) AS amount
  FROM events GROUP BY user_id, date
)
SELECT ... FROM daily JOIN users USING (user_id);

Expected output: the join builds its hash table on thousands of rows instead of billions. Pre-aggregation is the highest-leverage rewrite for spill.

1. Look for the classic spill generators in the plan:

- huge joins with no selective filter on either side
- ORDER BY over the full result set (add LIMIT or sort less)
- window functions over unpartitioned billions of rows
- SELECT * through a join (carry only needed columns)

Expected output: the operation to restructure. Each of these multiplies the memory the engine must hold at once.

1. For recurring heavy queries, consider a dedicated larger warehouse rather than upsizing the shared one:

CREATE WAREHOUSE heavy_etl WITH warehouse_size = 'XLARGE' auto_suspend = 300;

Expected output: the heavy query gets its memory without paying XLARGE prices for every lightweight query on the shared warehouse. Auto-suspend keeps the cost bounded.

Variant phrasings

snowflake bytes spilled to remote storage

The query exceeded node memory and spilled to S3-backed storage (step 1). Larger warehouse or smaller working set.

snowflake query slow spilling

Remote spill is orders of magnitude slower than memory. Treat any remote spill as a bug to fix, not a state to accept.

snowflake warehouse sizing for large joins

Size so the largest build side fits in node memory. When in doubt, test one size up and compare elapsed time vs cost.

Why it happens

Each warehouse node has fixed RAM. Operations like joins, sorts, and aggregations need their working set in memory; when it does not fit, Snowflake spills to local SSD first, then to remote storage. Remote spill keeps the query correct but the I/O dominates runtime.

Edge cases

• Multi-cluster warehouses add nodes, not per-node memory; they fix concurrency, not spill. Only a larger size fixes spill.
• Spill accounting is per query; concurrent queries on the same warehouse do not share the spill, they each get node memory.
• bytes_spilled_to_local_storage is normal under pressure and much cheaper than remote; optimize for zero remote spill first.
• Result caching does not help spilled queries go faster; it only helps identical repeats skip execution.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_XSRzWAlQ8Oq2t1FTFRhCBw