Course contents

The Analytics Engineer’s Field GuideSnowflake: the engineStep 2: Reading a Query Profile

Reading a Query Profile

Part 4 · Snowflake · step 2

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_id dbt wrote for that model in target/run_results.json and 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.

You need the right to look
You can always profile your own queries. Seeing other people's needs MONITOR on the warehouse that ran them. If Query History looks suspiciously empty, that's usually why.

The five things to read

Signal
What it tells you
Partitions scanned
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.
Bytes scanned
How much data the scan read. Next to it, percentage scanned from cache says how much came from the warehouse's local SSD instead of remote storage (step 6). Cut bytes by pruning and by selecting fewer columns, not SELECT *.
Spill to local
An operator (sort, join, aggregate) ran out of memory and spilled onto the warehouse's local SSD. It slows down. Caused by too much intermediate data, or a warehouse that's too small.
Spill to remote
Worse: it spilled past local SSD to remote storage. A red flag that the warehouse is far too small for the data. Cut the data reaching the operator first, then size up (step 5).
Execution graph
The operator tree, with the slowest nodes highlighted. Find the fattest node and you've found the bottleneck.

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:pruning:partitions_scanned AS scanned,
       operator_statistics:pruning:partitions_total   AS total,
       execution_time_breakdown:overall_percentage    AS pct_of_time
FROM TABLE(GET_QUERY_OPERATOR_STATS(LAST_QUERY_ID()))
ORDER BY pct_of_time DESC;

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:

TableScan3,980/4,000 · 45 GB68% of time
Filterstatus='open'6%
Aggregatespill 8 GB → local22%
Resultreturn rows4%
-- 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.
Read first, change second
The profile points at the one operator that's actually slow. Resize a warehouse when the real problem was a full scan and you'll spend more for the same slow query. The profile stops you fixing the wrong thing.

Lesson quiz

  1. On a filtered query, the profile shows 4,000 partitions scanned out of 4,000. What does it mean?

    1. Pruning worked: every match was found
    2. The filter pruned nothing: a full table scan
    3. The rows were all served from the warehouse cache
    4. The warehouse is too small for the table
  2. An aggregate shows bytes spilled to remote storage. In what order do you fix it?

    1. Add clusters, so the aggregate gets extra memory and CPU to work with
    2. Rely on the result cache so the aggregate doesn't run again
    3. Cut the data reaching the operator, then size up if it still spills
    4. Size the warehouse up, then look at the SQL if it's still slow
  3. A slow query's profile shows Partitions scanned 4,000 / 4,000 and an Aggregate spilling 8 GB to local storage. Following the lesson, what do you fix first?

    1. Nothing, since local spill on a big job is tolerable
    2. The warehouse size, since spill means it's short of memory
    3. The filter, so less data reaches the Aggregate
    4. The cache, since none of the scan came from the local SSD