Question

A data engineer notices that a large aggregation query on a Medium warehouse is running much slower than expected. Reviewing the Query Profile, they see significant values under both 'Bytes spilled to local storage' and 'Bytes spilled to remote storage'. What does the remote spillage specifically indicate, and what is the most appropriate first remediation?

Accepted Answer

The query exceeded both memory and local disk capacity on the warehouse nodes, so intermediate data was written to remote cloud storage; increasing the warehouse size adds memory and local disk to reduce or eliminate spilling.

Answer

The result cache was too small to hold the output, so results were written remotely; enabling the query acceleration service will cache the results in memory.

Answer

The table lacks a clustering key, forcing Snowflake to scan remote partitions; defining a clustering key on the aggregation columns will remove the spillage.

Answer

Micro-partition metadata could not be pruned, causing a remote metadata read; creating a materialized view will eliminate the remote spillage.