Query profiling and the query plan in Snowflake - Step-by-Step Execution

Process Flow - Query profiling and the query plan

Submit SQL Query

↓

Query Parser

↓

Query Optimizer

↓

Generate Query Plan

↓

Execute Query Plan

↓

Collect Profiling Data

↓

Show Query Profile & Plan

The query is parsed, optimized, and a plan is created. The plan runs, collecting data. Finally, the profile and plan are shown.

Execution Sample

Snowflake

EXPLAIN USING TEXT SELECT * FROM orders WHERE amount > 100;
-- Then run
SELECT * FROM orders WHERE amount > 100;

Shows the query plan text, then runs the query to collect profiling data.

Process Table

Step	Action	Details	Result
1	Submit Query	SELECT * FROM orders WHERE amount > 100	Query received by Snowflake
2	Parse Query	Check syntax and semantics	Query parsed successfully
3	Optimize Query	Find best plan for filtering amount > 100	Query plan created with filter pushdown
4	Generate Query Plan	Plan includes scan of orders table with filter	Plan ready for execution
5	Execute Query Plan	Scan orders, apply filter amount > 100	Rows matching condition retrieved
6	Collect Profiling Data	Measure time, bytes scanned, rows processed	Profiling data collected
7	Show Query Profile & Plan	Display execution steps and resource usage	User sees detailed query plan and profile

💡 Query execution completes and profiling data is available for analysis

Status Tracker

Variable	Start	After Parsing	After Optimization	After Execution	Final
Query Text	SELECT * FROM orders WHERE amount > 100	Parsed	Optimized	Executed	Completed
Query Plan	None	None	Scan orders with filter	Used to fetch rows	Available for profile
Rows Processed	0	0	0	N rows matching filter	N rows returned
Bytes Scanned	0	0	0	X bytes scanned	X bytes recorded
Execution Time	0	0	0	Measured during execution	Total time recorded

Key Moments - 3 Insights

Why does the query plan show a filter operation before scanning all rows?

Because the optimizer pushes the filter down to reduce data scanned, as shown in step 3 and 4 of the execution table.

What does the profiling data tell us after query execution?

It shows resource usage like bytes scanned and execution time, helping understand query efficiency, as collected in step 6.

Why is the EXPLAIN command useful before running the query?

It shows the planned steps without running the query, so you can see how Snowflake will execute it, as in step 1 and 7.

Visual Quiz - 3 Questions

Look at the execution table, at which step is the query plan created?

A Step 4

B Step 3

C Step 2

D Step 5

💡 Hint

Check the 'Generate Query Plan' action in the execution table rows.

According to the variable tracker, when are rows processed counted?

A After Parsing

B After Optimization

C After Execution

D At Start

💡 Hint

Look at the 'Rows Processed' variable values in the variable tracker.

If the filter condition changes, how does the query plan step change?

A The plan stays the same

B The filter operation in the plan updates

C Parsing step changes only

D Profiling data changes only

💡 Hint

Refer to the 'Optimize Query' and 'Generate Query Plan' steps in the execution table.

Concept Snapshot

Query profiling shows how a query runs and uses resources.
The query plan is the step-by-step plan Snowflake makes to run the query.
Use EXPLAIN to see the plan before running.
Profiling data includes time, rows, and bytes scanned.
Optimizers push filters early to reduce work.
Profiling helps find slow or costly queries.

Full Transcript

When you submit a query in Snowflake, it first gets parsed to check for errors. Then the optimizer creates a plan to run the query efficiently. This plan includes steps like scanning tables and applying filters. When the query runs, Snowflake collects profiling data such as how many rows were processed, how many bytes were scanned, and how long it took. You can use the EXPLAIN command to see the query plan before running the query. This helps you understand how Snowflake will execute your query and can help you improve performance by adjusting your SQL or data. Profiling data after execution shows the actual resource usage and helps identify bottlenecks.

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