Lesson 9.1 — Snowflake Query Profile, How to Read It

Every Snowflake query comes with a visual execution plan. Learning to read it is the single highest-leverage optimization skill for this JD. Your RDS tuning experience transfers — same diagnostic mindset, different diagram.

Where to find it

In Snowsight → the query's History detail → Query Profile tab. You'll see a boxes-and-arrows graph flowing bottom-up (scans at the bottom, result at the top).

The five things you read first

1 · Percentage of time per operator

Each box shows % of total execution time. Focus on the top 1–2 boxes — typically a TableScan or Join. That's where your time goes.

2 · Partitions scanned vs total

In a TableScan, look for "Partitions scanned: 12 / 4,500." 12 of 4,500 means excellent pruning — the optimizer found your filter. 4,500 / 4,500 means no pruning — your WHERE clause isn't helping, or you have no clustering.

This is the first metric to check on any slow query.

3 · Bytes spilled to local/remote storage

If a Sort or Join operator spilled bytes to disk, the warehouse was memory-undersized for the work. Two fixes:

• Upsize the warehouse (scale up)
• Reduce the data reaching this operator (better filters earlier, smaller joins)

4 · Join cardinality

"Rows produced" on a Join box. If your inputs were 10M × 10M and output is 100B, you have a join explosion — typically a missing join key or an unintended cartesian cross. The query will be slow no matter the warehouse.

5 · Pruning on the TableScan

Check the Filter line under TableScan. If your WHERE is on a column that benefits from clustering but no clustering is set, the profile will show you're scanning far more than needed.

The cheat sheet for "why is this slow?"

The view that unlocks everything else

-- Queries by cost (credits), last 7 days
SELECT
  query_id,
  query_text,
  warehouse_name,
  TOTAL_ELAPSED_TIME/1000.0 AS elapsed_s,
  bytes_scanned/1e9 AS gb_scanned,
  credits_used_cloud_services
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time > DATEADD(day, -7, CURRENT_TIMESTAMP())
ORDER BY elapsed_s DESC
LIMIT 20;

Any optimization conversation starts here. Find the 10 most expensive queries; profile those. 90% of a warehouse's cost usually comes from <5 queries.

Mapping to RDBMS tuning you've already done

If you've tuned PostgreSQL, MySQL, or MSSQL Server — same diagnostic loop, different plan viewer:

• Find hot queries → find hot operators → fix or throttle.
• EXPLAIN ANALYZE in Postgres ↔ Query Profile in Snowflake.
• Index usage / sequential scan ↔ Partition pruning ratio.
• Sort memory / work_mem ↔ Spill to storage.