Skip to main content

πŸš€ 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_IDTOTAL_AMOUNT
1700
21200
3900
41500
5200

⚑ 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.