Data spilling occurs when a virtual warehouse runs out of memory during query execution and writes intermediate data to local disk storage. Reading that data back from disk slows the query; if local storage also runs out, Snowflake writes to remote cloud storage, reducing performance further. section 9.2.3
Spilling commonly appears in large, complex queries that group or sort substantial table data. Check the query profile’s Statistics pane for Bytes spilled to local storage alongside total execution time. section 9.2.3 section 8.2.3
Reduce spilling by choosing the least costly effective option:
- Rewrite grouping or sorting queries so they process less data, such as by filtering to fewer records before aggregation. section 9.2.3
- Increase warehouse size when reducing processed data is not possible; larger warehouses provide more resources but consume more credits. section 9.6
Filter stores before aggregation
A query that totals product quantities by store can filter stores to those less than 1,000 kilometers away before grouping and sorting. On an extra-small warehouse, processing less data reduced execution time from 22 to 16 seconds and reduced local spilling. section 9.2.3
Listing 9.2 Amended query from listing 9.1 with filtered data
use warehouse BAKERY_WH_XSMALL;
select
store_id,
distance_km,
product_id,
sum(sales_quantity) as total_quantity
from RETAILER_SALES
where store_id in (
select store_id
from (
select store_id,
count(distinct product_id) as product_cnt
from RETAILER_SALES
where distance_km < 1000 #1
group by store_id
having product_cnt > 100
)
)
group by store_id, distance_km, product_id
order by distance_km;
Use the filtered query as a pattern for applying a selective condition before the aggregation and ordering work. section 9.2.3
