Query profiling and the query plan in Snowflake - Commands & Configuration

Introduction

When you run a query in Snowflake, it can take time and resources. Query profiling helps you see how your query runs step-by-step. The query plan shows the path Snowflake takes to get your data. This helps you find slow parts and fix them.

When a query takes too long to finish and you want to find the slow part.

When you want to understand how Snowflake reads and processes your data.

When you want to improve your query to use less compute and cost less.

When you want to check if your query uses indexes or filters efficiently.

When you want to compare different versions of a query to see which is faster.

Commands

This command shows the query plan in a readable text format. It explains how Snowflake will run the query before actually running it.

Terminal

EXPLAIN USING TEXT SELECT * FROM sales WHERE region = 'North';

Expected Output

Plan

----

|-- Filter: region = 'North'

|-- Scan: sales

This command shows the most recent query run in your session with details like execution time and bytes scanned. It helps you profile the query performance.

Terminal

SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION()) ORDER BY START_TIME DESC LIMIT 1;

Expected Output

QUERY_ID  | QUERY_TEXT                                | START_TIME           | TOTAL_ELAPSED_TIME | BYTES_SCANNED

123456789 | SELECT * FROM sales WHERE region = 'North' | 2024-06-01 12:00:00 | 1500               | 1048576

This command shows detailed profiling information for the query with ID 123456789. It breaks down time spent on each step of the query.

Terminal

SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_PROFILE('123456789'));

Expected Output

STEP_ID | OPERATION            | DURATION_MS | ROWS_PROCESSED

1       | Scan sales           | 500         | 10000

2       | Filter region='North' | 1000        | 2000

Key Concept

If you remember nothing else from this pattern, remember: the query plan shows how Snowflake runs your query, and profiling shows where time is spent.

Common Mistakes

Running EXPLAIN without USING TEXT and expecting readable output.

The default EXPLAIN output is in JSON, which is harder to read for beginners.

Use EXPLAIN USING TEXT for a simple, readable query plan.

Not checking query history to find the correct QUERY_ID before profiling.

Without the right QUERY_ID, you cannot get accurate profiling details.

Always query INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION to get the latest QUERY_ID.

Ignoring the bytes scanned and elapsed time in query history.

These metrics help identify if the query is scanning too much data or taking too long.

Use these metrics to focus your optimization efforts.

Summary

Use EXPLAIN USING TEXT to see a simple query plan before running your query.

Check your recent queries in INFORMATION_SCHEMA.QUERY_HISTORY_BY_SESSION to find query IDs and performance details.

Use INFORMATION_SCHEMA.QUERY_PROFILE with a query ID to see detailed step-by-step timing and row counts.

Practice

1. What does the EXPLAIN command in Snowflake do?

easy

A. Shows the steps Snowflake will take to run a query

B. Runs the query and returns the results

C. Deletes the query from history

D. Optimizes the query automatically

Solution

1. Step 1: Understand the purpose of EXPLAIN

   The EXPLAIN command shows the query plan, which details how Snowflake will execute the query step-by-step without running it.

2. Step 2: Differentiate EXPLAIN from other commands

   Unlike running the query or deleting history, EXPLAIN only provides the plan, not results or changes.

3. Final Answer:

   Shows the steps Snowflake will take to run a query -> Option A

4. Quick Check:

   EXPLAIN = Shows query plan [OK]

Hint: EXPLAIN previews query steps without running it [OK]

Common Mistakes:

• Thinking EXPLAIN runs the query
• Confusing EXPLAIN with query deletion
• Assuming EXPLAIN optimizes automatically

2. Which of the following is the correct syntax to get the query plan for a SELECT statement in Snowflake?

easy

A. SHOW PLAN FOR SELECT * FROM my_table;

B. EXPLAIN SELECT * FROM my_table;

C. DESCRIBE QUERY SELECT * FROM my_table;

D. PLAN QUERY SELECT * FROM my_table;

Solution

1. Step 1: Recall Snowflake syntax for query plans

   Snowflake uses the EXPLAIN keyword before a query to show its plan.

2. Step 2: Verify syntax correctness

   Only EXPLAIN SELECT ... is valid; other options are not recognized commands.

3. Final Answer:

   EXPLAIN SELECT * FROM my_table; -> Option B

4. Quick Check:

   EXPLAIN + query = correct syntax [OK]

Hint: Use EXPLAIN before your query to see the plan [OK]

Common Mistakes:

• Using SHOW PLAN which is not valid in Snowflake
• Trying DESCRIBE QUERY which is incorrect
• Using PLAN QUERY which is not a command

3. Given the query plan shows a step with a large number of rows scanned and a high cost, what does this indicate?

medium

A. Snowflake will skip this step automatically

B. The query is efficient and fast

C. This step may slow down the query and use more resources

D. The query will return no results

Solution

1. Step 1: Interpret rows scanned and cost in query plan

   A large number of rows scanned and high cost means this step requires more work and resources.

2. Step 2: Understand impact on query performance

   Such steps usually slow down the query and increase resource usage, not speed it up or skip it.

3. Final Answer:

   This step may slow down the query and use more resources -> Option C

4. Quick Check:

   High rows scanned = slower step [OK]

Hint: High rows scanned means slower query step [OK]

Common Mistakes:

• Assuming high cost means faster query
• Thinking Snowflake skips costly steps
• Confusing cost with query result count

4. You ran EXPLAIN SELECT * FROM orders WHERE order_date > '2023-01-01'; but the plan shows a full table scan. What is a likely reason?

medium

A. There is no index or clustering on order_date

B. The query syntax is incorrect

C. Snowflake does not support filtering on dates

D. The table is empty

Solution

1. Step 1: Analyze why full table scan occurs

   Full table scans happen when no index or clustering helps filter data efficiently.

2. Step 2: Check other options for validity

   The query syntax is correct, Snowflake supports date filters, and an empty table would not cause a full scan.

3. Final Answer:

   There is no index or clustering on order_date -> Option A

4. Quick Check:

   No index = full scan [OK]

Hint: No index or clustering causes full scans [OK]

Common Mistakes:

• Thinking syntax error causes full scan
• Believing Snowflake can't filter dates
• Assuming empty table causes full scan

5. You want to optimize a slow query that joins two large tables. The query plan shows a costly step performing a large shuffle of data between nodes. What is the best approach to reduce this cost?

hard

A. Increase the warehouse size without changing the query

B. Add more columns to the SELECT clause

C. Run the query without a WHERE clause

D. Use clustering keys to co-locate join keys and reduce data shuffle

Solution

1. Step 1: Understand data shuffle in join operations

   Data shuffle happens when data must move between nodes to match join keys, which is costly.

2. Step 2: Use clustering keys to optimize joins

   Clustering keys organize data so join keys are stored together, reducing shuffle and improving speed.

3. Step 3: Evaluate other options

   Adding columns or removing filters increases data processed; increasing warehouse size helps but does not reduce shuffle.

4. Final Answer:

   Use clustering keys to co-locate join keys and reduce data shuffle -> Option D

5. Quick Check:

   Clustering keys reduce shuffle cost [OK]

Hint: Clustering keys reduce costly data shuffle in joins [OK]

Common Mistakes:

• Adding columns increases data, not reduces cost
• Removing WHERE clause slows query
• Relying only on warehouse size ignores shuffle