Query Profile Masterclass
How to read the tea leaves: Identifying exploding joins, spilling to remote, and partition scanning issues.
The Query Profile is the single most important tool for a Snowflake Data Engineer. It visualizes exactly how the database engine executed your command. Here is how to interpret the most common “red flags.”
1. Bytes Spilled to Remote Storage#
The Symptom: A query that usually takes 1 minute suddenly takes 20 minutes, and the bar is purple. The Cause: The operation (usually a Sort or Join) couldn’t fit in the Warehouse memory (RAM) and couldn’t even fit on the local SSD. It had to write temp data to S3/Blob storage. The Fixes:
- Scale Up: Move from L to XL. Double the RAM.
- Filter Earlier: Reduce the dataset size before the sort/join.
2. Exploding Joins (Cartesian Products)#
The Symptom: The “Rows Processed” count explodes. You output 10M rows from a join of two 1M row tables. The
Cause: A Many-to-Many join relationship that wasn’t expected. Often caused by duplicates in a join key. The Fix:
Check the uniqueness of your join keys (COUNT(DISTINCT key) vs COUNT(*)).
3. Partitions Scanned (Pruning)#
The Symptom: A query filters on WHERE transaction_date = '2025-11-21' but scanning the progress bar shows
“Partitions Scanned: 10,000 / 10,000”. The Cause: Snowflake couldn’t use metadata to skip files. The Fix:
- Is the column wrapped in a function?
WHERE TRUNC(date) = ...kills pruning. - Is the table clustered on that column?
4. Union without All#
The Symptom: An expensive Aggregate node appears unexpectedly. The Cause: You used UNION instead of
UNION ALL. UNION performs a de-duplication (DISTINCT) across the entire result set, which is expensive. 99% of the
time, you want UNION ALL.
Conclusion#
A slow query isn’t a mystery; it’s a math problem. The Query Profile gives you the variables—you just need to solve it.