Query Lifecycle

Every SQL statement in Snowflake follows the same execution path through three layers:

Query Execution Flow

Flow diagram: 1) User submits SQL. 2) Cloud Services Layer parses, optimises, and creates execution plan. 3) Virtual Warehouse executes the plan against micro-partitions in cloud storage. 4) Results returned via Cloud Services Layer. Each step labelled with what happens: parse/compile, optimise/prune metadata, distribute to compute nodes, scan/join/aggregate, return result set.

1. Cloud Services — parses SQL, resolves objects, checks privileges, generates optimised execution plan, determines which micro-partitions to scan
2. Virtual Warehouse — executes the plan: scans micro-partitions, applies filters, performs joins, aggregations, and sorts
3. Cloud Services — returns results to client, caches result for potential reuse

The Query Profile

The Query Profile in Snowsight is your most important diagnostic tool. It shows the actual execution plan as a tree of operator nodes.

Query Profile Operator Tree

Tree diagram of a typical Query Profile: Result node at top, below it an Aggregate node, feeding into a Join node with two branches — left branch shows TableScan on ORDERS with Filter, right branch shows TableScan on CUSTOMERS. Each node shows percentage of total time, rows processed, bytes scanned. The Join node consumes 45% of time. The TableScan shows partitions scanned 120 of 5000 total, indicating good pruning.

Key Operator Nodes

What to Look For

• Partitions scanned vs total — Is pruning effective?
• Bytes spilled to local/remote storage — Is the warehouse too small?
• Percentage bars — Which operators consume the most time?
• Exploding joins — Does a join produce far more rows than its inputs?

1-- Get the last query's ID
2SELECT LAST_QUERY_ID();
3
4-- Retrieve operator stats for any query
5SELECT *
6FROM TABLE(GET_QUERY_OPERATOR_STATS(LAST_QUERY_ID()));

Micro-Partition Pruning

Pruning is Snowflake’s primary query optimisation. Every micro-partition stores column-level metadata (min/max values). When your WHERE clause specifies a range or equality predicate, Snowflake compares the filter against each partition’s metadata and skips partitions that cannot contain matching rows.

Micro-Partition Pruning in Action

Eight micro-partitions labelled MP1-MP8 with date ranges. A WHERE clause for dates in February prunes 6 partitions (greyed out) and scans only 2 partitions (highlighted green). Shows: Partitions scanned 2 of 8 total — 75% pruning efficiency.

1-- Run a filtered query and check Query Profile for partition stats
2SELECT order_id, customer_id, order_date, total_amount
3FROM sales.orders
4WHERE order_date BETWEEN '2024-06-01' AND '2024-06-30'
5AND region = 'EMEA';
6
7-- In Query Profile, click TableScan node:
8-- Partitions scanned: 150 / Partitions total: 12,000
9-- = 98.75% of partitions pruned

Spilling to Disk

When a query’s intermediate data exceeds warehouse memory, Snowflake spills data through a hierarchy:

Spilling Hierarchy

Three-tier pyramid: Top (green) = Memory/RAM (fastest, all processing ideally here). Middle (amber) = Local SSD (slower, data spills when memory exceeded). Bottom (red) = Remote Cloud Storage S3/Blob/GCS (slowest, severe performance degradation). Arrows point downward labelled Overflow.

1SELECT query_id, query_text, warehouse_name, warehouse_size,
2     execution_time / 1000 AS exec_seconds,
3     bytes_spilled_to_local_storage,
4     bytes_spilled_to_remote_storage
5FROM snowflake.account_usage.query_history
6WHERE bytes_spilled_to_remote_storage > 0
7ORDER BY bytes_spilled_to_remote_storage DESC
8LIMIT 20;

Query History

EXPLAIN Command

1-- Logical plan without execution (zero credits)
2EXPLAIN USING TABULAR
3SELECT c.name, SUM(o.total) AS total_spend
4FROM customers c
5JOIN orders o ON c.id = o.customer_id
6WHERE o.order_date >= '2024-01-01'
7GROUP BY c.name
8ORDER BY total_spend DESC;
9
10-- EXPLAIN shows operators and expected rows
11-- Query Profile shows ACTUAL execution after completion

Warehouse Sizing Strategy

ALTER WAREHOUSE analytics_wh SET WAREHOUSE_SIZE = 'X-LARGE';

ALTER WAREHOUSE analytics_wh SET
  MAX_CLUSTER_COUNT = 3
  SCALING_POLICY = 'STANDARD';

ALTER WAREHOUSE dashboard_wh SET WAREHOUSE_SIZE = 'X-SMALL';

Scale Up vs Scale Out

Common Anti-Patterns

Cheat Sheet

Practice Quiz

Flashcards

Resources

• Query Profile Documentation
• Query History Views
• EXPLAIN Command
• Warehouse Sizing Guide

Next Steps

• Caching Mechanisms
• Clustering Keys
• Search Optimisation Service