Snowflake Query History
Introduction
Tracking and auditing database activity is a crucial part of securing and optimizing any data warehouse. Snowflake is a well-liked cloud data platform that provides strong tools for getting detailed information about queries on your account. In this article, we will explore the Snowflake query history view and the QUERY_HISTORY table functions in detail. You’ll learn how to use these features to gain valuable insights into query activity, performance, and access patterns.
What is the Snowflake query_history View?
The query_history view in Snowflake shows all queries executed in your account. It is read-only. The information is available for the past 7 days for Standard Edition. For Enterprise Edition and above, it is available for the past 14 days.
This information is available for the past 7 days for Standard Edition, and 14 days for Enterprise Edition and above. It provides detailed information about each query, including:
Query text
Execution status (e.g. completed, failed, running)
Queried objects (tables, views, etc.)
User who ran the query
Query start and end time
Number of rows returned
Bytes scanned
And more
Snowflake automatically populates this view and does not require any setup or configuration on your part. You can query this snowflake access audit view just like any other snowflake view.
Example of Querying query_history
Here is a basic example of using the query_history view. This view displays the 10 most recent queries run by the current user.
SELECT query_text, start_time, execution_status FROM query_history WHERE user_name = current_user() ORDER BY start_time DESC LIMIT 10;
This query selects the SQL text, start time, and execution status of queries from the query_history view. It filters for only those queries executed by the current user, orders the results with the most recent queries first, and limits the output to 10 rows.
The QUERY_HISTORY Table Functions
In addition to the query_history view, Snowflake provides a set of table functions for accessing query history data. These functions allow you to retrieve query history from further back in time than the 7 or 14 day retention period of the view.
The QUERY_HISTORY functions come in three variants:
QUERY_HISTORY() – Returns 14-day query history in micro-partition structure for optimized queries
QUERY_HISTORY_BY_SESSION() – Returns 14-day query history with additional session-level details
QUERY_HISTORY_BY_USER() – Returns 14-day query history with additional user-level details
Example of Using QUERY_HISTORY
Suppose you want to find the top 5 users who have scanned the most data in the past 14 days. You could use this snowflake table function like this:
SELECT user_name, sum(bytes_scanned) as total_bytes_scanned
FROM TABLE(QUERY_HISTORY())
WHERE start_time >= dateadd('day', -14, current_timestamp())
GROUP BY user_name
ORDER BY total_bytes_scanned DESC
LIMIT 5;This query retrieves the query history for the past 14 days using the QUERY_HISTORY function. It sums the bytes_scanned column for each user, orders the results descending by total bytes scanned, and takes the top 5 users.
Filtering Query History Results
The query_history view and QUERY_HISTORY functions support a wide range of SQL predicates for filtering results. Some common predicates you might use include:
query_id – The unique identifier of a query
query_type – The type of query (e.g. SELECT, INSERT, CREATE TABLE)
user_name – The name of the user who executed the query
start_time and end_time – The timestamps when the query started and finished
execution_status – The status of the query (e.g. RUNNING, COMPLETED, FAILED)
database_name, schema_name, table_name – The names of the database objects accessed by the query
Here’s an example that finds all failed queries that accessed a specific table in the past day:
SELECT *
FROM query_history
WHERE execution_status = 'FAILED'
AND table_name = 'my_table'
AND start_time >= dateadd('day', -1, current_timestamp());Use Cases for Query History
The Snowflake query history feature has many valuable use cases, including:
Auditing and Security
By tracking all queries executed against your Snowflake account, query_history enables you to:
Monitor for suspicious activity or unauthorized access
Investigate security incidents
Ensure compliance with data governance policies
Provide an audit trail for regulatory requirements
Query Optimization
Query history data can help you optimize query performance by:
Identifying the most resource-intensive queries
Analyzing query patterns over time
Detecting and troubleshooting query errors or timeouts
Tuning indexing, clustering, and partitioning strategies
Chargeback and Cost Allocation
The bytes scanned and execution time data in query history allows you to:
Attribute Snowflake costs to specific users, teams, or projects
Implement chargeback or showback models
Encourage query efficiency and limit runaway costs
Accessing Query History Securely
Note that by default, the query_history view and QUERY_HISTORY functions are only accessible by the ACCOUNTADMIN role. To grant other roles access, you’ll need to use the GRANT IMPORTED PRIVILEGES command.
Be cautious when granting access to query history, as it may contain sensitive information. Consider creating a separate role with limited privileges specifically for audit and monitoring purposes.
Summary and Conclusion
Snowflake’s query history feature provides essential visibility into the queries that users run against your account. You can use the query_history view and QUERY_HISTORY table functions for various purposes. These include improving auditing, optimizing query performance, and tracking usage costs. Additionally, these tools offer more benefits as well.
The examples covered in this article illustrate just a few of the many ways you can derive insights from query history data. As you explore further, consider how this information could enhance security, efficiency, and governance within your own Snowflake environment.
DataSunrise provides user-friendly and flexible tools for managing Snowflake security, audit rules, dynamic data masking, and compliance. Visit our team for an online demo to see these capabilities in action!
