Bird
Raised Fist0
SnowflakeHow-ToBeginner · 3 min read

How to Use EXPLAIN PLAN in Snowflake for Query Analysis

Use the EXPLAIN command before your SQL query in Snowflake to see the query execution plan. This plan shows how Snowflake processes your query step-by-step, helping you optimize performance.
📐

Syntax

The EXPLAIN command in Snowflake is used by placing it before a SQL statement to display the query execution plan. It does not run the query but shows how Snowflake will execute it.

Syntax parts:

  • EXPLAIN: Keyword to request the execution plan.
  • SELECT ...: Your SQL query to analyze.
sql
EXPLAIN SELECT * FROM my_table WHERE column1 = 'value';
💻

Example

This example shows how to use EXPLAIN to get the execution plan for a simple SELECT query. It helps you see the steps Snowflake will take to run the query.

sql
EXPLAIN SELECT id, name FROM employees WHERE department = 'Sales';
Output
Plan: - Scan table employees - Filter on department = 'Sales' - Output columns: id, name
⚠️

Common Pitfalls

Common mistakes when using EXPLAIN in Snowflake include:

  • Trying to run EXPLAIN on unsupported statements like INSERT or UPDATE. It works only with SELECT, DELETE, MERGE, and some others.
  • Expecting actual query results. EXPLAIN only shows the plan, it does not execute the query.
  • Ignoring the plan details. Understanding the steps helps optimize queries.
sql
EXPLAIN INSERT INTO employees VALUES (1, 'John'); -- This will cause an error

-- Correct usage:
EXPLAIN SELECT * FROM employees;
📊

Quick Reference

CommandDescription
EXPLAIN SELECT ...Shows execution plan for a SELECT query
EXPLAIN DELETE ...Shows execution plan for a DELETE query
EXPLAIN MERGE ...Shows execution plan for a MERGE query
EXPLAIN INSERT ...Not supported, causes error
EXPLAIN UPDATE ...Not supported, causes error
✅

Key Takeaways

Use EXPLAIN before your SELECT query to see how Snowflake will execute it.
EXPLAIN does not run the query; it only shows the execution steps.
It supports SELECT, DELETE, and MERGE statements but not INSERT or UPDATE.
Review the plan to identify filters, scans, and joins for optimization.
Avoid expecting query results when using EXPLAIN.