π Execution Plan in Snowflake
π What is an Execution Plan?β
In Snowflake, an Execution Plan is like a blueprint or roadmap that describes how your SQL query will be executed.
Imagine youβre planning a road trip πΊοΈ.
You can take many possible routes, but Google Maps picks the best path depending on traffic, distance, and time.
Snowflake does the same for your SQL query:
- It decides which tables to scan
- Whether pruning can skip unnecessary data
- What type of join to use (Hash Join, Merge Join, etc.)
- The exact order of operations until the result is ready
π Execution Plan = the strategy Snowflake chooses before running the query.
π― Why Does It Matter?β
Understanding Execution Plans helps you:
β¨ Predict Performance β You can spot expensive operations (like full table scans).
β¨ Optimize Queries β Identify bottlenecks, missing pruning, or bad joins.
β¨ Save Credits β Efficient plans = fewer scanned partitions = lower cost.
π How Do You See an Execution Plan?β
Two ways:
1οΈβ£ SQL Commandβ
EXPLAIN USING TEXT
SELECT c.customer_id, SUM(o.amount)
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-01-01'
GROUP BY c.customer_id;
2οΈβ£ Snowsight (UI)β
Run the query β Open Query Details β Query Profile β Explain Plan
π Youβll see a visual execution tree (DAG) with timings and data flow.
π§ͺ Hands-on Example (End-to-End)β
ποΈ Step 1: Create Tablesβ
CREATE OR REPLACE TABLE customers (
customer_id INT,
customer_name STRING,
city STRING
);
CREATE OR REPLACE TABLE orders (
order_id INT,
customer_id INT,
order_date DATE,
amount NUMBER(10,2)
);
π₯ Step 2: Insert Sample Dataβ
INSERT INTO customers VALUES
(1, 'Ravi', 'Chennai'),
(2, 'Priya', 'Bangalore'),
(3, 'Arjun', 'Hyderabad'),
(4, 'Meena', 'Coimbatore'),
(5, 'Karthik', 'Madurai');
INSERT INTO orders VALUES
(101, 1, '2024-12-15', 500.00),
(102, 1, '2025-01-10', 700.00),
(103, 2, '2025-02-05', 1200.00),
(104, 3, '2024-11-20', 300.00),
(105, 3, '2025-03-01', 900.00),
(106, 4, '2025-01-25', 1500.00),
(107, 5, '2025-02-10', 200.00);
π Some rows are before 2025 β will be pruned π Some rows are after 2025 β will be processed
π Step 3: Run Execution Planβ
EXPLAIN USING TEXT
SELECT c.customer_id, SUM(o.amount)
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-01-01'
GROUP BY c.customer_id;
ποΈ Example Walkthroughβ
Query:β
SELECT c.customer_id, SUM(o.amount)
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-01-01'
GROUP BY c.customer_id;
π§ What Happens Behind the Scenes?β
πΉ Step 1: Table Scan (Orders)
Snowflake scans ORDERS, but applies filter early:
o.order_date >= '2025-01-01'
π Older data is skipped using partition pruning
πΉ Step 2: Table Scan (Customers) Full scan (small dimension table)
πΉ Step 3: Join Snowflake performs a Hash Join:
- Builds hash table on
CUSTOMERS - Probes using filtered
ORDERS
πΉ Step 4: Aggregation
GROUP BY c.customer_id
SUM(o.amount)
πΉ Step 5: Output Final aggregated result is returned
π Sample Execution Plan Outputβ
GlobalStats:
partitionsTotal=...
partitionsAssigned=...
Operations:
1) Result
2) Aggregate (GROUP BY CUSTOMER_ID)
3) Hash Join
Join Condition: CUSTOMER_ID
4) Filter (ORDER_DATE >= '2025-01-01')
5) TableScan ORDERS
6) TableScan CUSTOMERS
π Verify Query Resultβ
SELECT c.customer_id, SUM(o.amount)
FROM customers c
JOIN orders o
ON c.customer_id = o.customer_id
WHERE o.order_date >= '2025-01-01'
GROUP BY c.customer_id;
Expected Output:
| CUSTOMER_ID | TOTAL_AMOUNT |
|---|---|
| 1 | 700 |
| 2 | 1200 |
| 3 | 900 |
| 4 | 1500 |
| 5 | 200 |
β‘ Advanced: View Actual Execution Profileβ
SELECT LAST_QUERY_ID();
SELECT SYSTEM$QUERY_PROFILE('<query_id>');
π Or use Snowsight β Query Profile (recommended)
Youβll see:
- Execution DAG
- Time per step
- Bytes scanned
- Partition pruning effectiveness
π Bonus: Simulate Large Dataβ
INSERT INTO orders
SELECT
SEQ4(),
UNIFORM(1,5,RANDOM()),
DATEADD(DAY, UNIFORM(-500, 100, RANDOM()), CURRENT_DATE),
UNIFORM(100, 2000, RANDOM())
FROM TABLE(GENERATOR(ROWCOUNT => 100000));
π Re-run EXPLAIN and observe:
- Increased partitions
- More pruning impact
- Heavier joins
π Key Takeawaysβ
π Execution Plan = Snowflakeβs strategy before execution β‘ Filters are applied as early as possible π Hash Join is commonly used for joins π° Partition pruning is critical for performance
π Think of it like reading the recipe before cooking β it doesnβt cook the meal, but it tells you the exact steps to follow.