In Snowflake's Query Profile, which statistics tell you why a query is slow, and what fix does each imply?

## What Query Profile shows you Query Profile renders the executed plan as a tree of operators — `TableScan`, `Filter`, `Join`, `Aggregate`, `Sort`, `WindowFunction`, `Result` — with per-operator time percentages, plus a statistics panel and a **Most Expensive Nodes** list. The discipline is: find the dominant node first, then read the statistic that explains it. "Use a bigger warehouse" is the wrong first answer precisely because the profile usually names a cheaper fix. ## Pruning: partitions scanned vs partitions total Snowflake stores tables as micro-partitions and keeps min/max metadata per column per partition. If `partitions scanned` is close to `partitions total`, the query read the whole table and the filter bought nothing. Common causes are predicate shapes that defeat the metadata comparison: wrapping the filtered column in a function (`WHERE TO_DATE(event_ts) = ...`), an implicit or explicit cast on the column side, a leading-wildcard `LIKE`, or filtering on a column whose values are scattered across every partition because rows arrived in unrelated order. The fixes live in the predicate (compare the raw column against a computed constant instead of computing on the column) or in how the data is laid out. ## Spilling: bytes spilled to local and remote storage Sorts, hash joins, and aggregations build state in memory. When that state exceeds the warehouse's memory, Snowflake spills to the cluster's local SSD; when it exceeds local disk, it spills further to remote storage. Remote spilling is dramatically slower than local spilling and is a reliable sign a query has fallen off a cliff rather than just being big. The instinct is to size up, and more memory does help. But the profile usually shows a cheaper fix first: project fewer columns so less width flows into the sort, filter earlier so fewer rows reach the join, aggregate before joining rather than after, replace a global `ORDER BY` over the full result with a top-N, and check for the duplicate-key explosion that quietly multiplied the join's build side. ## Cache warmth: percentage scanned from cache This IO statistic is the share of scanned data served from the warehouse's local SSD cache rather than remote storage. A cold number on a query you expected to be warm tells you the warehouse suspended between runs, was resized, or that the workload is spread over so many warehouses that none stays warm. It explains a latency difference between two runs of the same query without any plan change. ## Exploding joins In the operator tree, compare a join's input row counts with its output. Output far larger than either input means the join key is not as unique as assumed — duplicates on the supposedly-one side, or a join condition missing a component so the engine produced a near-cross product. This is one of the classic problems Query Profile is documented to help identify, alongside `UNION` without `ALL`, queries too large to fit in memory, and inefficient pruning. The fix is a corrected join key or a deduplicated build side, never a bigger warehouse. ## UNION versus UNION ALL `UNION` removes duplicates, which means an extra full aggregation over the combined result. When the inputs are known disjoint — the usual case when someone is stitching together date ranges or source systems — `UNION ALL` deletes an entire expensive operator from the plan. ## The time breakdown The execution-time categories tell you which resource dominated: high **processing** points at CPU-bound expression work or a genuinely large aggregation; high **remote disk I/O** points at cold scans or remote spilling; high **network communication** points at large redistributions between nodes; high **synchronization** points at skew or waiting; high **initialization** on a fast query points at compilation overhead, which matters for very short statements executed at high rates. ## Working the profile in order A reliable routine: (1) Most Expensive Nodes — where did time go; (2) pruning ratio on the dominant `TableScan`; (3) spilling numbers, local then remote; (4) row counts across joins for explosions; (5) cache percentage to explain run-to-run variance. Only after all five come back clean is "this query needs more compute" the honest conclusion — and at that point the right lever belongs to warehouse configuration rather than to the query.