Skip to main content

Handling Query Spilling in Snowflake


🧐 What is Query Spilling?​

In Snowflake, queries are executed in virtual warehouses, which have a fixed amount of memory (RAM).

πŸ’‘ When a query needs more memory than available, Snowflake moves (or spills) part of the intermediate data to local SSD storage of the warehouse.

πŸ‘‰ This process is called Query Spilling.

If the SSD cache also overflows, Snowflake continues spilling to remote cloud storage (S3/Azure/GCS), which is slower.


βš™οΈ How Query Execution Uses Memory​

  1. In-Memory Processing (Fastest πŸš€)

    • Snowflake tries to perform all query operations (joins, sorts, aggregations) directly in memory.
  2. Spill to Local SSD (Moderate ⚑)

    • If memory runs out, intermediate data is spilled to the warehouse’s local SSD cache.
  3. Spill to Remote Cloud Storage (Slow ⏳)

    • If SSD is also full, Snowflake spills to cloud storage β†’ biggest performance hit.

πŸ“Š Example Scenario​

SELECT 
c.customer_id,
SUM(s.amount) AS total_spent
FROM sales s
JOIN customers c
ON s.customer_id = c.customer_id
GROUP BY c.customer_id
ORDER BY total_spent DESC;
  • Small dataset β†’ Runs fully in memory (fast πŸš€).
  • Medium dataset β†’ Some data spills to local SSD (slower ⚑).
  • Huge dataset β†’ Large intermediate results spill to cloud storage (much slower ⏳).

πŸ” How to Detect Query Spilling​

Use the Query Profile in Snowsight or Classic UI:

  • Look for orange/yellow warning icons on nodes (e.g., Join, Aggregation, Sort).
  • Check Spilled Bytes metric β†’ shows how much data was written to disk/cloud.

πŸ‘‰ High spilled bytes = memory pressure = performance bottleneck.


🌟 Benefits of Spilling​

  • βœ… Prevents query failure – Instead of crashing when memory runs out, Snowflake gracefully spills.
  • βœ… Scales automatically – Handles queries of any size, even beyond warehouse memory.
  • βœ… User-transparent – Query still completes without intervention.

⚠️ Disadvantages​

❌ Performance degradation – Disk/Cloud I/O is much slower than RAM. ❌ Higher cost – Longer execution β†’ more credits consumed. ❌ Unpredictable latency – Same query may run fast on small data, slow when input grows.


πŸ“Œ Best Practices to Handle Query Spilling​

πŸ’‘ 1. Right-size your warehouse

  • Scale up warehouse size (e.g., MEDIUM β†’ LARGE) to provide more memory.

πŸ’‘ 2. Optimize queries

  • Avoid SELECT *.
  • Filter data early (WHERE, LIMIT).
  • Use proper clustering to reduce scanned partitions.

πŸ’‘ 3. Break down large queries

  • Split complex queries into stages (materialize intermediate results into temp tables).

πŸ’‘ 4. Monitor query profile

  • Regularly check QUERY_HISTORY and Query Profile for spill warnings.

πŸ’‘ 5. Use efficient joins

  • Prefer JOIN on selective keys.
  • Consider BROADCAST JOIN for small lookup tables.

πŸ§‘β€πŸ’» Example: Warehouse Resize to Reduce Spilling​

-- Before: Spilling occurs on SMALL warehouse
ALTER WAREHOUSE analytics_wh SET WAREHOUSE_SIZE = 'LARGE';
  • βœ… Larger warehouse = more memory per cluster β†’ less spilling.

πŸ”‘ Roles & Privileges​

  • To analyze spilling:

    • Use QUERY_HISTORY and Query Profile β†’ Requires MONITOR privilege.
  • To resize warehouse:

    • Need SYSADMIN or MODIFY WAREHOUSE privilege.

Summary​

  • 🧠 Query Spilling happens when Snowflake runs out of memory β†’ spills to SSD, then cloud.
  • πŸš€ In-memory β†’ fastest | ⚑ SSD β†’ moderate | ⏳ Cloud β†’ slowest.
  • πŸ“‰ Too much spilling = performance degradation & extra cost.
  • πŸ› οΈ Avoid by right-sizing warehouses, optimizing queries, and monitoring Query Profiles.
  • βœ… In short: Spilling saves queries from failing, but slows them down. Minimize it for best performance.