Course contents

SnowPro Core COF-C03 Study GuidePart 2: Warehouses and performanceReading the Query Profile

Reading the Query Profile

Performance Services

Four managed services sit on top of warehouses, each fixing one specific slowness pattern. The skill is matching the symptom to the service, so this chapter is organized symptom-first.

The master decision, before any money is spent

The whole diagnostic is one path. Walk it top to bottom, and stop at the first box that matches:

1 · Query submitted
First fork: did it actually start running?
start
↓ status says Queued, nothing executes
2 · Queued → stop, it's the warehouse
The cluster is already running MAX_CONCURRENCY_LEVEL statements and this one is waiting for a slot. There's no profile to read because nothing executed. Fix: scale out (multi-cluster, the Scaling chapter). Not clustering, not SOS, not a rewrite.
warehouse fix
↓ it ran, but slowly → open the Query Profile
3 · Read the profile
Statistics panel first (partitions scanned vs total, bytes spilled), then the operator tree for the hottest node. Match what you find against the symptom cards below.
diagnose
?It ran: what does the Query Profile actually show?
Rows exploding at a join
Fix the SQL

Missing or wrong join condition. Free, and no warehouse or service will save a Cartesian product.

Bytes spilled, local or remote
Bigger warehouse

Memory doubles with each size. Remote spill means go up now, not eventually.

Partitions scanned ≈ total, range filters
Clustering key

Dates, regions, slices of the table. Physical reordering serves range predicates.

Partitions scanned ≈ total, equality lookups
Search Optimization

Needle in haystack: user IDs, order numbers, substrings. The access path, not reordering.

Same aggregation computed all day
Materialized view

Single table, deterministic, read constantly, written rarely. Pay refresh once, read cheap forever.

Occasional huge scans among small queries
Query Acceleration

The outlier queries borrow serverless compute; the warehouse stays sized for the median.

✓

Order matters: the SQL fix is free, the resize is one number, the four services each add a permanent line to the bill. The right answer is the cheapest fix that actually addresses the symptom, and Youness agrees when the invoice arrives.

That ordering is worth making explicit as a repair ladder. First read the profile, because guessing buys the wrong fix. Second, repair the query itself: join conditions, filters that were meant to exist, a SELECT * feeding a 40-column scan nobody needed. Third, check whether the warehouse is merely mis-sized, one ALTER away. Only fourth come the services, because every one of them bills continuously: clustering pays recluster credits on every load, SOS pays storage plus maintenance forever, MVs pay refresh on every base change, QAS pays per accelerated second. At IsmaFrost, Thomas's rule is that a service proposal comes with the profile screenshot that justifies it, which kills most proposals at step two.

Reading the Query Profile

Before buying any optimization, diagnose. The Query Profile (Snowsight → Query History → click a query → Query Profile) shows the execution plan as a tree of operators, each annotated with its share of total time and its statistics. The same data is queryable with GET_QUERY_OPERATOR_STATS('<query_id>'), useful when you want to analyze plans in SQL rather than click through them.

Example 1: the query that never seems to start

Thomas submits a query and the UI just says Queued, minute after minute, no operator tree, no statistics, nothing to read yet. He opens the Query Profile anyway, out of habit, and there's nothing there to diagnose: a queued query hasn't been given compute, so it hasn't executed a single operator.

This is the first branch on the master decision from the top of this page, and it's worth stating plainly: the problem is the warehouse, not the query. The SQL might be perfectly written; it simply hasn't been let in yet, because the warehouse's cluster is already running MAX_CONCURRENCY_LEVEL statements (8 by default) and everything past that number waits in line. Nothing about rewriting the query, clustering a table, or enabling SOS touches this, because none of those tools run until the warehouse admits the statement. The fix lives back in Scaling Warehouses: more clusters (multi-cluster, Auto-scale) if this happens at predictable busy times, or simply waiting if it's a one-off spike. Resizing the warehouse doesn't help either, since a bigger cluster still runs one admission queue; scaling out is what adds lines.

Example 2: the query that ran, slowly

A different query from Thomas does start, finishes, and this time the Query Profile is full: an operator tree, a statistics panel, numbers everywhere. Say it shows partitions scanned equal to partitions total, and 40 GB spilled to local storage. That's not a warehouse problem anymore, the warehouse admitted and ran it, it's a performance problem inside the query's own execution, and each symptom below points at one of the Performance Services from the previous chapters:

What the profile showsWhere the fix lives
Partitions scanned ≈ partitions totalClustering Keys & Automatic Clustering
Selective point lookups still scanning everythingSearch Optimization Service
A single huge scan feeding an aggregation, no time to reorganize the tableQuery Acceleration Service
Bytes spilled to local or remote storageResize the warehouse (Virtual Warehouses chapter), the one fix that lives in warehouse sizing rather than Performance Services

The two examples together give the whole master decision in miniature: if the query hasn't started, look at the warehouse; if it ran and was still slow, look at the profile's own statistics and match the symptom to a service.

The statistics panel

Four numbers in the profile's side panel carry almost all the diagnostic weight.

StatisticWhat it tells youThe fix it points to
Partitions scanned vs partitions totalScanned ≈ total means pruning failed: the filter column's values are scattered across every micro-partitionClustering key or, for point lookups, the Search Optimization Service
Bytes spilled to local storageWorking set exceeded memory; the node fell back to its SSD. A slowdownLarger warehouse (memory doubles per size)
Bytes spilled to remote storageEven the SSD overflowed; now paging to object storage. A cliff, often 10x slowerLarger warehouse, urgently
Percentage scanned from cacheHow much of the scan came from the warehouse's local disk cache rather than remote storageHigh is good; drops to zero after suspend and resume, and partly after a downsize
Exam trap

"Percentage scanned from cache" refers to the warehouse's local SSD cache, not the result cache. A result-cache hit never reaches the profile as a scan at all: the profile shows a single QUERY RESULT REUSE node with no operator tree, because no warehouse did any work.

Reading the operator tree

Operators are the usual suspects: TableScan (reads micro-partitions, where pruning shows up), Filter, Join, Aggregate, Sort, and Result. Two patterns matter most.

One operator dominating total time. The profile attributes execution time per operator. A TableScan at 95% means the query is I/O bound and pruning is the lever. A Sort or Aggregate dominating alongside spill bytes means memory is the lever.

An operator emitting far more rows than its inputs. A join producing 400M rows from two 20K-row inputs is an exploding join, almost always a missing or wrong join condition producing a Cartesian product. The fix is the SQL, not the warehouse. A query with no join criteria at all always explodes.

A concrete example of reading the profile

A dashboard tile joins a 40M-row fact table to a small dimension, filtered on a scattered column, and takes forever. Start where the profile starts you: the two TableScans, side by side.

The two TableScan operators at the bottom of the query profile, feeding the Join above them (illustrative numbers). DIM_CUSTOMER scans 1 of 1 partitions and reads 1,200 rows in 2 percent of execution time: a tiny table, read in full, which is correct. SALES_FACT scans 8,140 of 8,200 partitions and reads 39.8 million rows in 71 percent of execution time: pruning failed on the filter column.OPERATORJoin · 9%continues up the treeTABLESCAN · 2% OF TIMEDIM_CUSTOMER1 of 1 partition · 1,200 rowspartitions scanned: 100%✓tiny table, full read is rightTABLESCAN · 71% OF TIMESALES_FACT8,140 of 8,200 partitions · 39.8M rowspartitions scanned: 99%✗pruning failed on the filter1,200 rows39.8M rowsPercentages are shares of execution time (illustrative).

OPERATORJoin · 9%continues up the tree

  • 1,200 rows
    TABLESCAN · 2% OF TIMEDIM_CUSTOMER1 of 1 partition · 1,200 rowspartitions scanned: 100%✓tiny table, full read is right
  • 39.8M rows
    TABLESCAN · 71% OF TIMESALES_FACT8,140 of 8,200 partitions · 39.8M rowspartitions scanned: 99%✗pruning failed on the filter

Percentages are shares of execution time (illustrative).

Figure 2.14 Read the two scans side by side. The dimension scan reads everything because the table is tiny, which is correct. The fact scan reads 99% of its partitions for a selective filter, the signature of failed pruning, and takes most of the execution time. Times and counts are illustrative.

Reading them together is the diagnostic. The dimension scan is what health looks like: partitions scanned matches what the query needs. The fact scan reads 99% of the table to satisfy a filter that will discard almost everything, which means the metadata couldn't exclude anything: the filter column's values are scattered across every partition. That symptom has one name (pruning failure) and one cheap fix (a clustering key on the filter column), and no warehouse size changes it.

The rest of the tree confirms a second, separate problem:

Join
39.8M rows in, 39.8M rows out: not exploding, just fed too much by the scan above
9%
↓
Aggregate
312 MB spilled to local storage: the warehouse ran out of memory
17%
↓
Result
48 rows returned
1%

So the profile hands you two findings with two different price tags. The scan problem is fixed by clustering (cheap, targeted, permanent). The spill problem is fixed by a larger warehouse (direct, but doubles the rate). A bigger warehouse would also mask the scan problem, since more nodes reading garbage in parallel still finish sooner, and that's exactly the trap: the cheaper fix that addresses the cause beats the expensive one that happens to also work. Diagnose per operator, fix per symptom, spend last.

Where query history lives

Three places, with different retention and latency.

-- INFORMATION_SCHEMA table function: last 7 days, zero latency
SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(result_limit => 100));

-- ACCOUNT_USAGE view: 365 days of history, up to 45 minutes behind
SELECT query_id, warehouse_name, bytes_spilled_to_remote_storage
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE bytes_spilled_to_remote_storage > 0;

-- Operator-level profile data in SQL
SELECT * FROM TABLE(GET_QUERY_OPERATOR_STATS(LAST_QUERY_ID()));
Memory aid

Four symptoms, four fixes. Scanning everything → clustering. Spilling → bigger warehouse. Exploding rows → fix the join. Queuing (from the warehouse pages) → more clusters. Every slow-query symptom maps to one of these pairings or to a service in this chapter.

Two profiles that confuse people the first time

Run a query twice and open the second run's profile: a single node labeled QUERY RESULT REUSE, no operators, no statistics. Nothing broke; the result cache answered, and there was no execution to profile. The other head-scratcher is a profile whose Initialization or compilation time dwarfs execution. That points at cloud services, not the warehouse: thousands of files in a stage scan, an enormous IN list, or view-on-view-on-view definitions the optimizer must expand. Resizing the warehouse does nothing for either pattern, which is why "scale up" is the wrong answer for them.

In Snowsight, the Monitoring tab exposes Query History and Copy History to users directly, no ACCOUNT_USAGE privileges needed, which is where Alice checks her own slow dashboards without asking Youness for anything.

Key takeaways
  • Partitions scanned ≈ partitions total means pruning failed. Spill bytes mean memory ran out. Row explosion means broken join logic.
  • Cache percentage in the profile is the local disk cache; result-cache hits skip the warehouse entirely and show no operators.
  • INFORMATION_SCHEMA.QUERY_HISTORY: 7 days, live. ACCOUNT_USAGE.QUERY_HISTORY: 365 days, up to 45 minutes of latency.