Query profiling and the query plan in Snowflake - Mini Project: Build & Apply

Query profiling and the query plan

📖 Scenario: You are working with a Snowflake data warehouse. You want to understand how your SQL queries run and how to check their performance details. This helps you find slow parts and improve your queries.

🎯 Goal: Build a simple Snowflake SQL script that runs a query, sets up a variable to hold the query ID, and then uses Snowflake's QUERY_HISTORY function to profile the query and see its query plan.

📋 What You'll Learn

Write a SQL query that selects data from a sample table

Store the query ID of the executed query in a variable

Use the QUERY_HISTORY function to get profiling details for that query ID

Retrieve the query plan for the executed query

💡 Why This Matters

🌍 Real World

Understanding query profiling and query plans helps data engineers and analysts optimize their Snowflake queries for better performance and lower costs.

💼 Career

This skill is essential for roles involving data warehousing, cloud data engineering, and performance tuning in Snowflake environments.

1

Run a simple query and capture its query ID

Write a SQL query that selects all columns from the SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER table. Store the query ID of this query in a variable called last_query_id using Snowflake's LAST_QUERY_ID() function.

Snowflake

-- Run the SELECT query and capture its query ID
-- Your code here

Hint

Use SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER; to get all customers. Then use SET last_query_id = LAST_QUERY_ID(); to save the query ID.

2

Configure a query to fetch query history for the last query

Write a SQL statement that selects all columns from the TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) function filtered by the QUERY_ID equal to the variable last_query_id. This will help you see the profiling details of the last query.

Snowflake

SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;
SET last_query_id = LAST_QUERY_ID();
-- Query the QUERY_HISTORY function filtering by last_query_id
-- Your code here

Hint

Use TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) to get query history. Filter with WHERE QUERY_ID = $last_query_id to get details for your last query.

3

Extract the query profile details from the query history

Add a SQL statement that selects the QUERY_TEXT, EXECUTION_STATUS, START_TIME, END_TIME, and TOTAL_ELAPSED_TIME columns from the TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) function filtered by QUERY_ID = $last_query_id. This will show key profiling information about the query.

Snowflake

SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;
SET last_query_id = LAST_QUERY_ID();
SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE QUERY_ID = $last_query_id;
-- Select key profiling columns from QUERY_HISTORY
-- Your code here

Hint

Select the columns QUERY_TEXT, EXECUTION_STATUS, START_TIME, END_TIME, and TOTAL_ELAPSED_TIME from the query history filtered by your query ID.

4

Retrieve the query plan for the executed query

Write a SQL statement that calls the TABLE(INFORMATION_SCHEMA.QUERY_PLAN($last_query_id)) function to get the query plan details for the query stored in last_query_id. This shows how Snowflake executed the query internally.

Snowflake

SELECT * FROM SNOWFLAKE_SAMPLE_DATA.TPCH_SF1.CUSTOMER;
SET last_query_id = LAST_QUERY_ID();
SELECT QUERY_TEXT, EXECUTION_STATUS, START_TIME, END_TIME, TOTAL_ELAPSED_TIME FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE QUERY_ID = $last_query_id;
-- Retrieve the query plan using QUERY_PLAN function
-- Your code here

Hint

Use TABLE(INFORMATION_SCHEMA.QUERY_PLAN($last_query_id)) to get the query plan for your last query.

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