Query Execution Flow

Snowflake · Warehouses & Performance

Theory

Every query follows the same path. Cloud Services first parses the SQL, resolves object names and checks that your role has the needed privileges. The optimizer then builds a plan, using micro-partition metadata (min/max values, row counts) to prune partitions that cannot match your filters. The plan is compiled and sent to the virtual warehouse, whose nodes scan the remaining partitions in parallel, join, aggregate and return the result through Cloud Services.

For example, WHERE order_date = '2024-03-01' on a table whose partitions each cover a few days lets the optimizer skip almost every partition before the warehouse reads a single byte.

Why we use it

Knowing which stage does what helps you tune. Compilation time, pruning, scanning, spilling and result return all show up separately in Query Profile, so you can tell which stage is the real bottleneck.

When to use it

Use this mental model whenever you read a Query Profile or decide how to fix a slow query: improve pruning for scan-heavy queries, size up for spilling, and simplify SQL when compilation itself is slow.

Parsing and Planning

Cloud Services first parses your SQL for syntax and checks your role's access rights, then the optimizer builds an execution plan using stored metadata, deciding which micro-partitions can be skipped entirely.

Compilation and Execution

The plan is compiled into physical execution steps, and only then does the assigned virtual warehouse actually scan data and compute results; Cloud Services then returns the final result set to you.

Why This Matters for Tuning

Understanding this flow explains why a warehouse that's too small slows the execution step, while poor filtering or clustering slows the planning step — they're different problems with different fixes.

The Full Query Path

User submits SQL │ ▼ ┌─────────────────────┐ │ CLOUD SERVICES │ 1. Parse SQL │ │ 2. Check permissions │ │ 3. Optimize query plan │ │ 4. Check result cache (hit → return instantly) └──────────┬──────────┘ │ (cache miss) ▼ ┌─────────────────────┐ │ VIRTUAL WAREHOUSE │ 5. Read micro-partitions (prune irrelevant) │ │ 6. Execute in parallel across nodes │ │ 7. Aggregate results └──────────┬──────────┘ ▼ Return Results

How it works

  1. SQL submitted
  2. Parse and check privileges in Cloud Services
  3. Optimize: prune micro-partitions using metadata
  4. Can the result come from the result cache? Yes: Return it with no warehouse used. No: Compile the physical plan.
  5. Warehouse executes the plan in parallel across its nodes
  6. Result returned to the client

Example

-- Parse → Optimize → Compile → Execute → Return results

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