How to Analyze Execution Plans in Snowflake

How do I analyze an execution plan in Snowflake?

EXPLAIN shows Snowflake’s step-by-step execution plan so you can diagnose slow queries and optimize them.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

Why use EXPLAIN in Snowflake?

EXPLAIN reveals how Snowflake will execute a SQL statement before it runs. Reviewing the logical or physical plan highlights expensive scans, joins, and data shuffling that cause high warehouse cost and latency.

How do I generate an execution plan?

Prepend EXPLAIN to the statement or enclose it with EXPLAIN USING TYPE = 'logical|physical|json'. Run the command in the Snowflake worksheet or any client. The plan appears instantly, and the query does not execute.

Quick example

EXPLAIN USING TYPE = 'logical' SELECT * FROM Orders o JOIN Customers c ON c.id = o.customer_id;

What do plan nodes mean?

• SCANNED OBJECT shows which micro-partitions are touched.
• FILTER indicates push-down predicates.
• JOIN reveals join type (MERGE, HASH, NESTED LOOP).
• AGGREGATE lists grouping keys and functions.

When should I open Query Profile instead?

After you actually run the statement, Snowsight’s Query Profile visualizes step duration, data volume, and spilled bytes. Use it to confirm assumptions made from EXPLAIN and spot unexpected skew.

Best practices for plan analysis

Start with the heaviest node (largest PARTITIONS or ROWS). Add selective predicates earlier, cluster frequently filtered columns, and convert cross joins to explicit joins. Verify pruning by checking PARTITIONS_SCANNED.

Can I store plans for audits?

Yes. Use EXPLAIN USING TYPE = 'json' and insert the JSON into a VARIANT column for later comparison during regression testing.

Why How to Analyze Execution Plans in Snowflake is important

How to Analyze Execution Plans in Snowflake Example Usage


EXPLAIN USING TYPE = 'physical'
SELECT p.name,
       SUM(oi.quantity) AS units_sold
FROM   OrderItems oi
JOIN   Products   p ON p.id = oi.product_id
WHERE  p.stock > 0
GROUP  BY p.name
ORDER  BY units_sold DESC;

How to Analyze Execution Plans in Snowflake Syntax


EXPLAIN [ USING TYPE = { 'logical' | 'physical' | 'json' } ] <statement>; 

--E-commerce example (logical plan)
EXPLAIN USING TYPE = 'logical'
SELECT c.name,
       SUM(oi.quantity * p.price) AS lifetime_value
FROM   Customers   c
JOIN   Orders      o  ON o.customer_id = c.id
JOIN   OrderItems  oi ON oi.order_id   = o.id
JOIN   Products    p  ON p.id          = oi.product_id
GROUP  BY c.name
ORDER  BY lifetime_value DESC;

Common Mistakes

Running EXPLAIN without USING TYPE and assuming you received the physical plan. By default Snowflake shows the logical plan; add USING TYPE = 'physical' when you need operator-level cost details.
Tuning the plan without executing the query afterward. Always validate changes with Query Profile to ensure real runtime and bytes scanned actually improved.