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_HISTORYfor long periods; it only keeps data for 7 days, whileACCOUNT_USAGE.QUERY_HISTORYkeeps 1 year. - Not having the right privileges to access
ACCOUNT_USAGEviews.
Example of a wrong query that returns too many rows:
sql
SELECT * FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY;
Quick Reference
| View | Retention Period | Access Scope | Notes |
|---|---|---|---|
| INFORMATION_SCHEMA.QUERY_HISTORY | 7 days | Database-level | Faster, limited to last 7 days |
| SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY | 1 year | Account-level | Includes 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.
