Snowflake
Query Profile & Performance Optimization
Diagnose and resolve query performance bottlenecks using Snowflake's execution visualizations.
The Query Profile shows what a query actually did: a graph of operators, each annotated with time, rows and bytes.
Query Profile is like a GPS trip report showing exactly where time was lost on a drive — stuck in traffic (spilling), took a long detour (poor pruning), or waited at a big intersection (an expensive join) — so you know precisely what to fix instead of just assuming you need a faster car.
Key Concepts
1
It answers why a query was slow, which is a different question from that it was slow. Three numbers on
it do most of the diagnosing.
2
1. Partitions scanned against partitions total.
Partitions scanned: 3,940
Partitions total: 4,0003
Your filter eliminated almost nothing. Either the table is badly clustered for this filter, or the query
prevents pruning — a function around the filtered column does it, as does comparing a date column to a
string that needs conversion.
4
A selective filter should scan a small fraction. Seeing most of the table here is the most common finding
on a slow Snowflake query.
5
2. Bytes spilled.
Bytes spilled to local storage: 40 GB
Bytes spilled to remote storage: 8 GB6
The warehouse ran out of memory. Local spilling is slower; spilling to remote storage is dramatically
slower and means the warehouse is clearly undersized for this query.
7
This is the clearest signal to size up, and often the cheapest fix — remember that doubling the size
frequently costs the same in credits because the query finishes in half the time.
8
3. The most expensive node. The profile ranks operators by time. Read the top one before changing
anything, because everything else is noise by comparison.
9
Then look at what that operator is telling you. An exploding join is the classic: a join whose output
row count is far larger than either input means duplicate keys on both sides, and no amount of warehouse
size will fix a result that is a hundred times too big.
10
The levers, in the order worth trying.
1. Filter earlier and read less — usually the biggest win and free
2. Size up if spilling to remote storage
3. Fix the join producing more rows than expected
4. Materialize or pre-aggregate a repeated expensive computation
5. A clustering key, only after proving pruning is the bottleneck11
Clustering keys are last because they are the only item with an ongoing bill.
12
Beyond one query. ACCOUNT_USAGE.QUERY_HISTORY holds every query with its bytes scanned and spilled,
so you can find the expensive patterns rather than the one query somebody complained about — and as with
any warehouse, sort by total time, not by worst case.
ACCOUNT_USAGE.QUERY_HISTORY
13
The Query Acceleration Service can offload parts of scan-heavy queries to serverless compute, which helps a
minority of workloads. It is a real option and not a substitute for reading the profile.
14
What the interviewer is probing.1. "What do you look at first in a profile?" Probing: the method. Stalls: "The slowest
node." Moves up: partitions scanned versus total, and spilling to local or remote storage — those
two explain most slow queries.
15
2. "What does spilling to remote storage mean?" Probing: the diagnosis. Stalls: "Disk is
slow." Moves up: the warehouse ran out of memory, so a larger warehouse or a less memory-hungry
query is needed.
16
3. "What does a high percentage of partitions scanned tell you?" Probing: the conclusion.
Stalls: "The table is big." Moves up: pruning is not working — the filter does not align with
how the data is clustered.
17
4. "What is an exploding join in the profile?" Probing: the pattern. Stalls: "A big join."
Moves up: output rows far exceeding input, which means a missing or wrong join key.