Query history and profiling in Snowflake - Mini Project: Build & Apply

Query History and Profiling in Snowflake

📖 Scenario: You are a data analyst working with Snowflake cloud data warehouse. Your manager wants you to track recent queries run on the system and profile their execution times to find slow queries.

🎯 Goal: Build a set of SQL queries to retrieve query history and profile query execution times in Snowflake.

📋 What You'll Learn

Use the SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY view to get recent queries

Filter queries executed in the last 7 days

Select query id, user name, query text, start time, end time, and execution time

Calculate execution time in seconds

Order results by execution time descending

💡 Why This Matters

🌍 Real World

Tracking query performance helps optimize data warehouse usage and reduce costs by identifying slow or expensive queries.

💼 Career

Data analysts and cloud engineers often profile query history to troubleshoot performance and improve data workflows.

1

Retrieve recent query history

Write a SQL query to select QUERY_ID, USER_NAME, QUERY_TEXT, START_TIME, and END_TIME from SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY where START_TIME is within the last 7 days.

Snowflake

-- Your SQL query here to select QUERY_ID, USER_NAME, QUERY_TEXT, START_TIME, END_TIME from QUERY_HISTORY for last 7 days

Need a hint?

Use DATEADD(day, -7, CURRENT_TIMESTAMP()) to filter queries from the last 7 days.

2

Add execution time calculation

Add a new column EXECUTION_TIME_SECONDS that calculates the difference between END_TIME and START_TIME in seconds using DATEDIFF function.

Snowflake

SELECT QUERY_ID, USER_NAME, QUERY_TEXT, START_TIME, END_TIME,
-- Calculate execution time in seconds here
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD(day, -7, CURRENT_TIMESTAMP());

Need a hint?

Use Datediff(second, START_TIME, END_TIME) to get execution time in seconds.

3

Filter out queries with null end time

Add a condition to the WHERE clause to exclude queries where END_TIME is NULL.

Snowflake

SELECT QUERY_ID, USER_NAME, QUERY_TEXT, START_TIME, END_TIME,
       DATEDIFF(second, START_TIME, END_TIME) AS EXECUTION_TIME_SECONDS
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD(day, -7, CURRENT_TIMESTAMP())
-- Add condition to exclude NULL END_TIME

Need a hint?

Use AND END_TIME IS NOT NULL to exclude queries still running.

4

Order queries by execution time descending

Add an ORDER BY clause to sort the results by EXECUTION_TIME_SECONDS in descending order.

Snowflake

SELECT QUERY_ID, USER_NAME, QUERY_TEXT, START_TIME, END_TIME,
       DATEDIFF(second, START_TIME, END_TIME) AS EXECUTION_TIME_SECONDS
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD(day, -7, CURRENT_TIMESTAMP())
  AND END_TIME IS NOT NULL
-- Add ORDER BY clause here

Need a hint?

Use ORDER BY EXECUTION_TIME_SECONDS DESC to see slowest queries first.