Query Profile: why is this query slow?
Ismail's most-used tool. A slow query has a reason, and Snowflake shows it to you.
When someone tells Ismail "this query is slow", he doesn't guess. He opens the Query Profile: the execution graph plus per-operator statistics, and five numbers tell him almost everything.
Opening it
Three ways in, all in Snowsight:
- Right after running it. In the worksheet, open the query details under the results and choose the profile view.
- Any query from the last 14 days. Monitoring → Query History, filter by user, warehouse or text, click the query, then the Query Profile tab. That's the route for "the dashboard was slow at 9am".
- A model from last night's dbt run. Filter Query History on the query tag your dbt project sets (part 2), or copy the
query_iddbt wrote for that model intarget/run_results.jsonand paste it in the search.
The screen has three parts. The graph in the middle: the operators and the rows flowing between them (the diagrams in this guide follow the data along the arrows, and Snowsight draws it vertically). The Most Expensive Nodes list, which ranks operators by their share of the time and is the fastest way to the culprit. And the Statistics panel, where partitions, bytes, spill and cache numbers live. Click an operator and the panel switches to that operator's numbers.
MONITOR on the warehouse that ran them. If Query History looks suspiciously empty, that's usually why.The five things to read
120 / 4,000 means pruning worked, you touched 3% of the table. 4,000 / 4,000 is a full scan: your filter didn't prune. This is the first number to look at.SELECT *.Partitions scanned: pruned vs full scan
The single most useful line in the profile. Each micro-partition knows the min and max of its columns (step 1), so a filter can skip every block that can't contain a match. That's pruning, and step 3 is entirely about getting it. Pruned means your filter let Snowflake skip most of the table. A full scan means it read all of it:
pruned120 / 4,000 · 3%
full scan4,000 / 4,000 · 100%
Two companions worth knowing. EXPLAIN before a query shows the plan without running it, useful when the run itself is the expensive part: compare partitionsAssigned with partitionsTotal in its output and you know whether the filter prunes before you pay a credit. And the profile's numbers are available in SQL, for any query from the last 14 days:
-- the profile of the query you just ran, one row per operator SELECT operator_id, operator_type, operator_statistics: operator_pruning: partitions_ scanned AS scanned, statistics: execution_pruning: partitions_ total AS total, time_ FROM TABLE(GET_breakdown: overall_ percentage AS pct_of_time QUERY_ (LAST_OPERATOR_ STATS QUERY_ ())) ORDER BY pct_of_time DESC;ID
It's a table function, so it goes in FROM TABLE(...), and it works on one query at a time. For "every query that spilled this week", use the spill columns of query history instead (step 8).
Spilling, in one paragraph
Every warehouse has a fixed amount of memory. When a sort, a join or an aggregation needs more than that, Snowflake writes the overflow to the warehouse's local disk (spill to local), and if that fills too, to remote storage (spill to remote). The query still finishes, just much slower. Local spill on a big job is tolerable, remote spill is a red flag. Step 5 covers what causes it and how to fix it, for now just know where to see it.
Reading the graph
The graph shows where the time went. You're looking for the one operator doing most of the work:
- A heavy TableScan with most partitions scanned is a pruning problem (step 3).
- A heavy Join that produces far more rows than its inputs is an exploding join: a missing key or a missing filter (step 4).
- A heavy Sort, Aggregate or WindowFunction with spill is a memory problem: reduce the rows reaching it, then size up (step 5).
- Long time in Initialization or a big queued number before the query even started is the warehouse, not the SQL: too busy (scale out) or resuming from suspend.
- A profile with a single QUERY RESULT REUSE or METADATA-BASED RESULT node and no warehouse means a cache answered it (step 6): nothing to tune.
Here's that as the profile draws it: data flows along the arrows, each box carries its share of the run time, and the bottleneck stands out:
status='open'6%-- Ismail reads the profile for a slow query and sees: -- TableScan ORDERS 3,980 / 4,000 partitions 45 GB scanned -- Aggregate spilled 8 GB to local storage -- diagnosis: the filter isn't pruning (full scan), and the -- aggregate is too big for the warehouse. Two separate fixes.

