How to View Execution Plans in Snowflake

How do I view and interpret execution plans in Snowflake?

EXPLAIN shows Snowflake’s execution plan, letting you diagnose and tune query performance.

Welcome to the Galaxy, Guardian!

Oops! Something went wrong while submitting the form.

Description

What does EXPLAIN do in Snowflake?

EXPLAIN returns Snowflake’s compiled execution plan before a query runs. Reading the plan helps you understand joins, pruning, and parallelism, guiding index-like clustering and rewrite decisions.

How do I generate an execution plan?

Run EXPLAIN USING TEXT, EXPLAIN USING JSON, or simply EXPLAIN before a SELECT, INSERT, UPDATE, or MERGE. Snowflake parses the statement and outputs the physical steps without executing it.

Which format should I choose—TEXT or JSON?

Use TEXT for quick, human-readable insight.Choose JSON when you need to feed the plan into scripts or dashboards, or want node-level statistics via GET_QUERY_OPERATOR_STATS.

How can I spot slow parts of a query?

Look for SCAN nodes with large estimated rows, BROADCAST joins on big tables, and re-partition operations.These often signal the need for clustering keys or predicate pushdown.

Best practices for execution-plan analysis?

Start small

Test individual sub-queries with EXPLAIN before examining the whole CTE chain.

Compare plans

Save pre- and post-optimization JSON plans in Galaxy’s Collections to track improvements and share with teammates.

Leverage Query Profile

After running the query, open Query Profile to validate the estimated vs.actual statistics you saw in EXPLAIN.

How do I change a plan that scans too many partitions?

Add a clustering key on the high-cardinality column used in your filter. Then rerun EXPLAIN to confirm the SCAN node shows fewer micro-partitions.

Common mistakes

Using EXPLAIN on views only

EXPLAIN on a view shows the view text, not the underlying tables. Wrap the view in a SELECT to see the real plan.

Ignoring stale statistics

If tables were bulk-loaded recently, run ALTER TABLE ...REFRESH for more accurate row-count estimates.

Need a faster way?

Galaxy’s AI copilot can auto-annotate JSON plans, highlight bottlenecks, and suggest clustering keys—all inside the editor.

Why How to View Execution Plans in Snowflake is important

How to View Execution Plans in Snowflake Example Usage


-- Identify most expensive query steps in a weekly revenue report
EXPLAIN USING JSON
SELECT  o.order_date::date       AS day,
        SUM(oi.quantity*p.price) AS revenue
FROM    Orders     o
JOIN    OrderItems oi ON oi.order_id = o.id
JOIN    Products   p  ON p.id       = oi.product_id
WHERE   o.order_date >= DATEADD(week,-4,CURRENT_DATE)
GROUP BY 1
ORDER BY 1;

How to View Execution Plans in Snowflake Syntax


EXPLAIN [ USING { TEXT | JSON } ] [ VERBOSE ] <SQL_statement>;

-- Ecommerce example
EXPLAIN USING TEXT
SELECT  c.id,
        SUM(oi.quantity * p.price) AS lifetime_value
FROM    Customers AS c
JOIN    Orders     AS o  ON o.customer_id = c.id
JOIN    OrderItems AS oi ON oi.order_id   = o.id
JOIN    Products   AS p  ON p.id          = oi.product_id
WHERE   c.created_at >= DATEADD(year,-1,CURRENT_DATE)
GROUP BY c.id;

Common Mistakes

Assuming EXPLAIN executes the query. It only compiles. Use Query Profile for actual runtimes.
Trusting the TEXT plan for automation. Use JSON for machine-readable details; TEXT may truncate long nodes.