Query history and profiling in Snowflake - Step-by-Step Execution

Process Flow - Query history and profiling

User runs a query

↓

Query recorded in history

↓

Query ID generated

↓

User requests profiling info

↓

System fetches query details

↓

Display execution stats and profile

When a query runs, Snowflake records it with an ID. Users can then request detailed history and profiling info to see how the query performed.

Execution Sample

Snowflake

SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE USER_NAME = 'JOHNDOE'
ORDER BY START_TIME DESC
LIMIT 3;

This code fetches the last 3 queries run by user JOHNDOE from the query history.

Process Table

Step	Action	Query ID	User	Start Time	Execution Time	Result Rows
1	Fetch latest query	01a2b3c4-d5e6-7f89-0abc-def123456789	JOHNDOE	2024-06-01 10:00:00	2.5s	1000
2	Fetch second latest query	02b3c4d5-e6f7-890a-bcde-f234567890ab	JOHNDOE	2024-06-01 09:45:00	1.2s	500
3	Fetch third latest query	03c4d5e6-f789-0abc-def1-34567890abcd	JOHNDOE	2024-06-01 09:30:00	3.0s	1500
4	Stop fetching	-	-	-	-	-

💡 Limit reached: 3 queries fetched for user JOHNDOE

Status Tracker

Variable	Start	After 1	After 2	After 3	Final
Query ID	None	01a2b3c4-d5e6-7f89-0abc-def123456789	02b3c4d5-e6f7-890a-bcde-f234567890ab	03c4d5e6-f789-0abc-def1-34567890abcd	03c4d5e6-f789-0abc-def1-34567890abcd
Execution Time	None	2.5s	1.2s	3.0s	3.0s
Result Rows	None	1000	500	1500	1500

Key Moments - 2 Insights

Why does the query history show multiple queries instead of just one?

Because the LIMIT 3 clause in the query fetches the last three queries run by the user, as shown in execution_table rows 1 to 3.

What does the Query ID represent in the profiling?

The Query ID uniquely identifies each query run and helps fetch detailed profiling info, as seen in the variable_tracker showing different IDs per step.

Visual Quiz - 3 Questions

Look at the execution_table, what is the Execution Time of the second latest query?

A 2.5s

B 1.2s

C 3.0s

D Not listed

💡 Hint

Check the Execution Time column in row 2 of the execution_table.

At which step does the system stop fetching queries?

A Step 3

B Step 2

C Step 4

D Step 1

💡 Hint

Look at the Action column in the execution_table for the stop fetching action.

If the LIMIT was changed to 2, how many rows would be fetched according to the execution_table pattern?

A 2

B 1

C 3

D 4

💡 Hint

The LIMIT controls how many queries are fetched; currently 3 rows are fetched with LIMIT 3.

Concept Snapshot

Query History & Profiling in Snowflake:
- Queries are logged with unique Query IDs.
- Use ACCOUNT_USAGE.QUERY_HISTORY to see past queries.
- Filter by USER_NAME and order by START_TIME.
- LIMIT controls how many queries to fetch.
- Profiling shows execution time and rows processed.

Full Transcript

When a user runs a query in Snowflake, the system records it with a unique Query ID and stores details like start time, execution time, and rows returned. Users can query the ACCOUNT_USAGE.QUERY_HISTORY view to see their recent queries. By filtering on USER_NAME and ordering by START_TIME descending, they get the latest queries first. The LIMIT clause restricts how many queries are shown. This helps users profile their queries to understand performance and results. The execution table shows step-by-step fetching of the last three queries for user JOHNDOE, including their execution times and result counts.

Practice

1. What is the main purpose of the QUERY_HISTORY view in Snowflake?

easy

A. To see details of past queries executed in the system

B. To create new tables and schemas

C. To manage user permissions and roles

D. To monitor network traffic between Snowflake and clients

Solution

1. Step 1: Understand the role of QUERY_HISTORY

   The QUERY_HISTORY view stores information about queries that have already run, including their text, execution time, and status. Its main purpose is to see details of past queries executed in the system.

2. Final Answer:

   To see details of past queries executed in the system -> Option A

3. Quick Check:

   QUERY_HISTORY = past query details [OK]

Hint: QUERY_HISTORY shows past queries and their info [OK]

Common Mistakes:

• Confusing QUERY_HISTORY with user management
• Thinking it manages network or security settings
• Assuming it creates or modifies database objects

2. Which SQL clause correctly filters queries executed by a specific user in the QUERY_HISTORY view?

easy

A. FILTER BY USER = 'john_doe'

B. WHERE USER_NAME = 'john_doe'

C. SELECT USER_NAME FROM QUERY_HISTORY WHERE 'john_doe'

D. HAVING USER_NAME = 'john_doe'

Solution

1. Step 1: Recall SQL filtering syntax

   To filter rows in SQL, the WHERE clause is used with a condition like USER_NAME = 'value'. WHERE USER_NAME = 'john_doe' is valid and standard SQL syntax.

2. Final Answer:

   WHERE USER_NAME = 'john_doe' -> Option B

3. Quick Check:

   Filter with WHERE clause = WHERE USER_NAME = 'john_doe' [OK]

Hint: Use WHERE to filter rows by user name [OK]

Common Mistakes:

• Using FILTER BY instead of WHERE
• Misplacing HAVING without GROUP BY
• Incorrect SELECT syntax without WHERE

3. Given the query:

SELECT query_text, total_elapsed_time FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE execution_status = 'FAILED' ORDER BY start_time DESC LIMIT 1;

What does this query return?

medium

A. The most recent failed query's text and its total elapsed time

B. All successful queries ordered by start time

C. The oldest failed query's text and elapsed time

D. An error because QUERY_HISTORY is not a table

Solution

1. Step 1: Analyze the query clauses

   The query uses TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) to access query history, filters for execution_status = 'FAILED', orders by start_time DESC (most recent first), and limits to 1 row, returning the most recent failed query's text and total elapsed time.

2. Final Answer:

   The most recent failed query's text and its total elapsed time -> Option A

3. Quick Check:

   Filter failed + order desc + limit 1 = most recent failed query [OK]

Hint: ORDER BY DESC + LIMIT 1 gets latest record [OK]

Common Mistakes:

• Thinking QUERY_HISTORY is a normal table
• Confusing oldest vs most recent due to ORDER BY
• Ignoring the WHERE filter on execution_status

4. You wrote this query to find slow queries but it returns no results:

SELECT query_text FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE total_elapsed_time > 1000;

What is the likely issue?

medium

A. The TABLE() function cannot be used with QUERY_HISTORY

B. QUERY_HISTORY does not have total_elapsed_time column

C. The query_text column cannot be selected from QUERY_HISTORY

D. total_elapsed_time is in microseconds, so 1000 is too small a threshold

Solution

1. Step 1: Check the unit of total_elapsed_time

   In Snowflake, total_elapsed_time is measured in microseconds, so 1000 microseconds (1 millisecond) is too small a threshold, and few or no queries exceed it, resulting in no results.

2. Final Answer:

   total_elapsed_time is in microseconds, so 1000 is too small a threshold -> Option D

3. Quick Check:

   Elapsed time unit = microseconds, threshold too low [OK]

Hint: Check units: elapsed time is microseconds, not milliseconds [OK]

Common Mistakes:

• Assuming elapsed time is in seconds or milliseconds
• Thinking QUERY_HISTORY lacks columns
• Misusing TABLE() function syntax

5. You want to profile query performance by grouping queries by user and calculating average execution time. Which query correctly achieves this?

hard

A. SELECT user_name, SUM(total_elapsed_time) FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) WHERE execution_status = 'SUCCESS';

B. SELECT user_name, total_elapsed_time FROM QUERY_HISTORY GROUP BY user_name;

C. SELECT user_name, AVG(total_elapsed_time) AS avg_time FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) GROUP BY user_name ORDER BY avg_time DESC;

D. SELECT user_name, AVG(total_elapsed_time) FROM QUERY_HISTORY WHERE total_elapsed_time > 1000 ORDER BY user_name;

Solution

1. Step 1: Identify correct aggregation and grouping

   To get average execution time per user, use AVG(total_elapsed_time) with GROUP BY user_name from TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()), as in SELECT user_name, AVG(total_elapsed_time) AS avg_time ... GROUP BY user_name ORDER BY avg_time DESC.

2. Final Answer:

   SELECT user_name, AVG(total_elapsed_time) AS avg_time FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) GROUP BY user_name ORDER BY avg_time DESC; -> Option C

3. Quick Check:

   Group by user + AVG + ORDER BY avg_time = SELECT user_name, AVG(total_elapsed_time) AS avg_time FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY()) GROUP BY user_name ORDER BY avg_time DESC; [OK]

Hint: Use GROUP BY user_name with AVG for profiling [OK]

Common Mistakes:

• Missing GROUP BY when using aggregation
• Selecting columns without aggregation
• Not using TABLE() function for QUERY_HISTORY