Snowflake
Query Profile & Performance Optimization
Diagnose and resolve query performance bottlenecks using Snowflake's execution visualizations.
The Query Profile is Snowflake's visual and statistical breakdown of exactly how a specific query executed — a graph of operator nodes (scans, joins, aggregations, sorts) annotated with timing, row counts, and bytes processed at each step. It's the primary diagnostic tool for understanding *why* a query is slow, rather than just knowing that it is.
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
Key signals to look for include: partition pruning (are partitions scanned close to partitions total, or is the query filtering effectively?), spilling (is data spilling to local disk or, worse, remote storage because the warehouse doesn't have enough memory for the operation — visible as "Bytes spilled to local/remote storage"), and most expensive nodes (which operator consumed the most time — often a large join or aggregation).
partition pruningpartitions scannedpartitions totalspillingmost expensive nodes
2
Common optimization levers, in rough order of typical impact: rewriting queries to filter earlier and reduce data scanned (better predicate pushdown), increasing warehouse size to reduce spilling (more memory per node), adding a clustering key for very large, poorly-pruned tables, restructuring joins (broadcast vs. shuffle join strategies, join order), and leveraging materialized views or aggregation tables for frequently repeated expensive computations.
3
Beyond the profile of a single query, ACCOUNT_USAGE.QUERY_HISTORY and the QUERY_ACCELERATION_SERVICE (which offloads portions of eligible queries to serverless compute) provide account-wide visibility and automated acceleration options for consistently slow, scan-heavy queries.
ACCOUNT_USAGE.QUERY_HISTORYQUERY_ACCELERATION_SERVICE