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.