Bird
Raised Fist0
SnowflakeHow-ToBeginner · 4 min read

How to Use Query History in Snowflake: Syntax and Examples

Use the SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY or INFORMATION_SCHEMA.QUERY_HISTORY views to access query history in Snowflake. Query these views with filters like START_TIME or USER_NAME to see past queries and their details.
📐

Syntax

Snowflake provides two main views to access query history: SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY and INFORMATION_SCHEMA.QUERY_HISTORY. You can query these views using standard SQL SELECT statements with optional filters.

Key columns include:

  • QUERY_ID: Unique identifier of the query.
  • QUERY_TEXT: The SQL text of the query.
  • START_TIME: When the query started.
  • USER_NAME: The user who ran the query.
  • EXECUTION_STATUS: Status like SUCCESS or FAILED.

Example syntax:

sql
SELECT QUERY_ID, QUERY_TEXT, START_TIME, USER_NAME, EXECUTION_STATUS
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME >= DATEADD(day, -7, CURRENT_TIMESTAMP())
ORDER BY START_TIME DESC;
💻

Example

This example shows how to retrieve the last 5 queries run by a specific user in the past day, ordered by start time descending.

sql
SELECT QUERY_ID, QUERY_TEXT, START_TIME, USER_NAME, EXECUTION_STATUS
FROM INFORMATION_SCHEMA.QUERY_HISTORY
WHERE USER_NAME = 'JOHN_DOE'
  AND START_TIME >= DATEADD(day, -1, CURRENT_TIMESTAMP())
ORDER BY START_TIME DESC
LIMIT 5;
Output
QUERY_ID | QUERY_TEXT | START_TIME | USER_NAME | EXECUTION_STATUS ------------------------------------|---------------------------------|--------------------------|-----------|----------------- 01a2b3c4-d5e6-789f-0123-456789abcdef | SELECT * FROM SALES WHERE ID=10 | 2024-06-10 14:23:45.123 | JOHN_DOE | SUCCESS 02b3c4d5-e6f7-890a-1234-56789abcdef0 | UPDATE CUSTOMERS SET STATUS='A' | 2024-06-10 13:50:12.456 | JOHN_DOE | SUCCESS 03c4d5e6-f7a8-901b-2345-6789abcdef01 | DELETE FROM ORDERS WHERE DATE < '2024-01-01' | 2024-06-10 12:30:00.789 | JOHN_DOE | SUCCESS 04d5e6f7-a8b9-012c-3456-789abcdef012 | INSERT INTO LOGS VALUES (...) | 2024-06-10 11:15:30.000 | JOHN_DOE | SUCCESS 05e6f7a8-b9c0-123d-4567-89abcdef0123 | SELECT COUNT(*) FROM USERS | 2024-06-10 10:00:00.000 | JOHN_DOE | SUCCESS
⚠️

Common Pitfalls

Common mistakes when using query history in Snowflake include:

  • Not filtering by START_TIME, which can return huge result sets and slow queries.
  • Using INFORMATION_SCHEMA.QUERY_HISTORY for long periods; it only keeps data for 7 days, while ACCOUNT_USAGE.QUERY_HISTORY keeps 1 year.
  • Not having the right privileges to access ACCOUNT_USAGE views.

Example of a wrong query that returns too many rows:

sql
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY;
📊

Quick Reference

ViewRetention PeriodAccess ScopeNotes
INFORMATION_SCHEMA.QUERY_HISTORY7 daysDatabase-levelFaster, limited to last 7 days
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY1 yearAccount-levelIncludes all queries across account, requires privileges
✅

Key Takeaways

Use SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY for up to 1 year of query data.
Filter query history by START_TIME to avoid large, slow queries.
INFORMATION_SCHEMA.QUERY_HISTORY shows recent 7 days of queries per database.
Ensure you have proper privileges to access ACCOUNT_USAGE views.
Order results by START_TIME DESC to see the latest queries first.