DataSunrise is sponsoring RSA Conference2024 in San Francisco, please visit us in DataSunrise's booth #6178

Snowflake Query History

Snowflake Query History

Snowflake Query History


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

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
WHERE start_time >= dateadd('day', -14, current_timestamp())  
GROUP BY user_name
ORDER BY total_bytes_scanned DESC

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:

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!


Redshift and Athena

Redshift and Athena

Learn More

Need Our Support Team Help?

Our experts will be glad to answer your questions.

General information:
[email protected]
Customer Service and Technical Support:
Partnership and Alliance Inquiries:
[email protected]