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

Need a 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

Need a 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

Need a 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

Need a hint?

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