Query Profiling & EXPLAIN Plan

Snowflake · Performance Deep Dive

Theory

Query Profile, opened from a query's details in Snowsight, shows the executed plan as a graph of operators such as TableScan, Filter, Join and Aggregate, with the time and rows for each. Key statistics include partitions scanned versus partitions total (pruning), bytes spilled to local or remote storage (memory pressure), and percentage scanned from cache. The most expensive nodes are listed so you can see where time went.

EXPLAIN shows the plan Snowflake would use without running the query. It is compiled in Cloud Services and does not use a warehouse, so it is a cheap way to check join order and partition estimates. For example, a join producing far more rows than either input in the profile is a sign of a missing or wrong join condition.

Why we use it

Guessing at why a query is slow wastes time and credits. The profile shows the actual bottleneck: poor pruning, spilling, an exploding join or queuing, so you fix the right thing.

When to use it

Open Query Profile for any slow or costly query before resizing a warehouse. Use EXPLAIN to sanity-check a big new query before running it. Use QUERY_HISTORY views to find which queries to profile in the first place.

Reading the Query Profile

The profile breaks a query into operator nodes (scan, join, aggregate, sort) with the percentage of total time and bytes each one consumed — the biggest boxes are where optimization effort pays off.

Spotting Common Problems

A huge 'bytes scanned' on a table scan node with a small 'partitions scanned after pruning' usually means poor clustering; a disproportionately slow join often means a missing filter pushed too late in the plan.

EXPLAIN vs Running the Query

EXPLAIN <query> returns the planned steps without executing anything, useful for sanity-checking an expensive query's shape before committing warehouse credits to actually running it.

How it works

  1. Slow query found in query history
  2. Open its Query Profile
  3. Partitions scanned close to total? Yes: Improve filters or clustering for pruning. No: Check spilling.
  4. Bytes spilled to local or remote storage? Yes: Use a larger warehouse or reduce data processed. No: Check joins for row explosion.
  5. Fix the bottleneck and compare the profile again

Example

SELECT QUERY_ID, TOTAL_ELAPSED_TIME/1000 AS elapsed_seconds,
       PARTITIONS_SCANNED, PARTITIONS_TOTAL, PERCENTAGE_SCANNED_FROM_CACHE,
       BYTES_SPILLED_TO_LOCAL_STORAGE
FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(RESULT_LIMIT => 20))
ORDER BY TOTAL_ELAPSED_TIME DESC;

Key points

Snowflake topics

Snowflake Fundamentals

Warehouses & Performance

Staging & SnowSQL

Tables & Data Handling

Cloud Integrations

Storage & Optimization

Security & Governance

Views, Pipelines & Automation

Data Types & SQL Fundamentals

Semi-Structured Data Deep Dive

Advanced Features

Data Sharing & Collaboration

Cost Management & Monitoring

Databases, Schemas & Objects

Performance Deep Dive

Advanced Security & Governance

Stored Procedures & UDFs

Ecosystem & Integrations

SnowPro Certification Prep

T
Tech Town
Log InSign Up Free
Home SQL Python DSA Power BI Snowflake Databricks Spark Gen AI Practice Problems Contest Blog

Mastering Snowflake

The modern data cloud, from architecture to SnowPro certification. Click any topic in the sidebar to open it right here.

{{ sectionCount }}
Sections
{{ topicCount }}
Topics
Free
Forever
Hands-on practice Practice Snowflake SQL Sharpen the SQL you use in Snowflake: 150 query problems in a live editor with instant checking. Start practising →
Section {{ sec.num }}

{{ sec.label }}

Snowflake  /  {{ current.sectionLabel }}  /  {{ current.name }}

{{ current.name }}

Theory

{{ current.def }}

Why we use it

{{ rich.why }}

When to use it

{{ rich.when }}

{{ sec.h }}

{{ sec.body }}

{{ sec.body }}

Diagram

{{ rich.dg.caption }}

{{ tb.title }}
{{ c.v }}
{{ rich.dg.op }}
➜
{{ rich.dg.output.title }}
{{ c.v }}
{{ nd.label }}
{{ nd.sub }}
{{ nd.label }}
{{ nd.sub }}
{{ rich.dg.left.title }}
{{ it }}
{{ rich.dg.right.title }}
{{ it }}
On screen — where to click

{{ rich.ui.title }}

{{ u.text }}
  1. {{ st.n }}{{ st.text }}
{{ rich.ui.title }}
{{ u.text }}
Flowchart — how it works
{{ nd.text }}
{{ nd.text }}
◇ {{ nd.text }}
Yes → {{ nd.yes }}
No → {{ nd.no }}
Syntax
{{ current.syntax }}
Example
{{ current.example }}
Want to run this yourself? Unlike the SQL and Python courses, Snowflake needs a real account — there's no in-browser engine for it. Click Copy above, then paste it into a free Snowflake trial (30 days, no card required) and run it in Snowsight.
Key Points — click to check off as you learn
  • {{ pt.mark }} {{ pt.text }}

Share this topic