Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Snowflake query spilling to remote storage

Snowflake, BigQuery & Databricks · Snowflake

Snowflake query spilling to remote storage

Hardwarehouses-15
scenariosnowflakespillquery-profileperformance

Question

The query profile shows "bytes spilled to remote storage". What does that mean, and what do you do?

Solution

Spilling to remote storage means a query needed more memory than the warehouse had, filled the local disk too, and then wrote intermediate data to cloud object storage. Remote storage is the slowest place to spill, so a query that does it can be much slower than one that does not. It is a sign the query handles more data than the warehouse can hold.

What the profile tells you

In the query profile, a node (often a join, sort or aggregate) shows "Bytes spilled to local storage" and "Bytes spilled to remote storage". Local spill is a mild warning. Remote spill is the serious one.

Investigation order

Start with the cheapest question: is the query processing more data than it needs to?

  • Check partitions scanned against partitions total in the TableScan node. If most partitions are scanned, the filter is not pruning, so look at the filter and the clustering of the table.
  • Check the bytes scanned and the columns selected. SELECT * over a wide table reads data you do not use.
  • Look for an exploding join: if a join node outputs far more rows than it takes in, a key is not unique, or a join condition is missing. The row counts between nodes show this at once. Fixing a bad join often removes the spill without any resize.
  • Look at big sorts and DISTINCT or GROUP BY on high-cardinality columns. Can they be done on less data, or later in the query?
  • Check window functions over huge partitions.

Then change the query shape

Break it into steps with a temporary table, filter earlier, aggregate before joining, or process by date ranges.

Only then, resize

A larger warehouse has more memory per query, so it can stop the spill, and the query often gets much faster than the size jump would suggest. That is a legitimate fix, but it costs credits on every run, so do it after you have removed waste. Test the cost: a query that runs 4 times faster on a warehouse twice the size is cheaper overall.

Keep it from coming back

Add a check on the largest spilling queries from QUERY_HISTORY (the spill columns are there), and review them regularly.

PreviousNext