Snowflake Query History: Accessing, Examples, and Troubleshooting

As data engineers and administrators, having comprehensive visibility into the queries running on your Snowflake instance is crucial. Snowflake’s query history feature provides a detailed log of all executed queries, enabling you to monitor performance, troubleshoot issues, and optimize resource usage. Understanding how to effectively access and utilize this feature can significantly enhance your ability to manage and optimize your data environment.

In this blog, we will explore the fundamentals of accessing Snowflake query history, provide practical examples of how to leverage this feature for various use cases, and share troubleshooting techniques to resolve common issues. Whether you’re looking to identify long-running queries, analyze resource consumption, monitor user activity, or ensure data security, mastering Snowflake query history is key to maintaining a robust and efficient data platform. Let’s dive into the essential strategies and best practices for managing query history in Snowflake.

What is Snowflake Query History?

Snowflake’s Query History feature is an essential tool for data engineers and administrators to monitor, analyze, and optimize their SQL queries and data operations. It provides a comprehensive view of all the queries executed within a Snowflake account, including details about query performance, resource usage, and execution statistics.
Key components of Snowflake Query History include:

1. Query Details: Each entry in the query history contains information like query ID, SQL text, execution status (e.g., success, failure), start and end times, duration, and the role and user who executed the query.
2. Filtering and Searching: Users can filter and search the query history based on various criteria such as time range, user, role, warehouse, status, and SQL text.
3. Usage and Performance Analysis: Query history can be used to analyze query performance, identify long-running queries, and optimize resource usage.
4. Auditing and Compliance: Maintaining a query history is crucial for auditing purposes, ensuring compliance with regulatory requirements, and tracking user activity.
5. Accessing Query History: Users can access query history through the Snowflake web interface, SQL commands, or the Snowflake Information Schema views like QUERY_HISTORY, QUERY_HISTORY_BY_*, and LOGIN_HISTORY.

Accessing Query History in Snowflake

To get query history snowflake offers multiple interfaces, allowing users to choose the method that best suits their needs. These interfaces provide a comprehensive view of all executed queries, enabling detailed analysis and optimization of data operations.
Here is how to check query history in snowflake:

• Web Interface
  The Snowflake web interface provides a user-friendly, graphical view of query history. This interface is particularly useful for quick, on-the-fly analysis. Within the web interface, users can easily filter queries based on various criteria such as execution time, user, and warehouse. This capability allows data engineers and administrators to quickly pinpoint and investigate specific queries, facilitating efficient monitoring and troubleshooting.
• SQL Commands
  For more programmatic access, Snowflake offers several SQL commands that allow users to query the history of their data operations. Commands like QUERY_HISTORY, QUERY_HISTORY_BY_USER, and QUERY_HISTORY_BY_WAREHOUSE are designed to fetch detailed historical data programmatically. These commands are highly flexible, enabling users to integrate query history retrieval into automated workflows or custom monitoring solutions.
  For example, to retrieve query history for a specific time range, the following SQL command can be used: SELECT *FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(DATE_RANGE_START => ‘2024-07-01 00:00:00’,DATE_RANGE_END => ‘2024-07-07 23:59:59’));This command fetches all queries executed between July 1, 2024, and July 7, 2024, providing a detailed view of query activity within this period.
• API Access
  Snowflake also provides API access for query history, making it possible to integrate this data into custom monitoring and analytics tools. The API offers the same detailed query information available through the web interface and SQL commands, but with the added flexibility of integration into third-party applications and dashboards. This capability is especially valuable for organizations that require a centralized view of their data operations across multiple platforms.

Key Components of Query History

Understanding the key components of Snowflake’s query history is crucial for data engineers and administrators aiming to monitor, analyze, and optimize their SQL queries and data operations by accessing a query history table in snowflake. These components provide comprehensive insights into query performance, resource utilization, and user activity, facilitating efficient management and troubleshooting.

1. Execution Statistics
   Execution statistics are at the core of Snowflake’s query history. They include details such as the start and end times of each query, the duration of execution, and the status (success or failure). These statistics help in identifying long-running queries and optimizing them for better performance. They also provide insights into the overall efficiency of query operations within the Snowflake environment to those with the appropriate Snowflake query history permissions.
2. Resource Utilization
   Resource utilization data provides information on the compute resources used by each query. This includes the virtual warehouses involved, the amount of compute time consumed, and the level of resource consumption. Understanding resource utilization helps in optimizing warehouse sizes and configurations, ensuring cost-effective and efficient use of Snowflake’s computing capabilities.
3. Query Text
   The actual SQL text of each executed query is another vital component of Query History. Having access to the exact query text allows data engineers to review and refine their SQL code. This is particularly useful for debugging purposes and for optimizing complex queries that might be consuming excessive resources.
4. User and Role Information
   Query History tracks which user and role executed each query. This information is essential for user activity monitoring and security audits. It helps in identifying who is responsible for specific queries, ensuring accountability, and enhancing security measures by tracking user actions.
5. IP Address
   Logging the IP address from which each query was executed adds an additional layer of security monitoring. It helps in detecting unauthorized access and in tracking suspicious activities, contributing to a more secure data environment.
6. Performance Analysis
   Performance analysis tools within query history allow users to delve into the specifics of query execution. This includes query profiling and execution plans, which provide insights into the performance characteristics of queries. These tools help in identifying bottlenecks and inefficiencies, enabling data engineers to optimize their queries for better performance.
7. Error Tracking
   Error tracking is a critical feature of query history, providing detailed error messages and codes for failed queries. This information aids in debugging and resolving issues quickly. Additionally, Snowflake supports mechanisms for retrying failed queries, ensuring reliability and consistency in data operations.
8. Historical Data Retention
   Snowflake retains query history for a specified period, allowing for historical analysis and long-term monitoring. This Snowflake query history retention capability enables users to perform trend analysis and track the evolution of query performance over time. Historical data can also be exported for offline analysis or integration with other monitoring tools, providing flexibility in how data is reviewed and utilized.
9. Data Export
   The ability to export query history data enhances its utility by allowing for offline analysis or integration with third-party monitoring and analytics tools. This flexibility ensures that data engineers can leverage query history insights in the way that best fits their operational needs.

Best Practices for Managing Snowflake Query History

Managing query history in Snowflake is crucial for optimizing performance, troubleshooting issues, and ensuring efficient use of resources. This guide covers the best practices for effectively managing Snowflake query history, aimed at data engineers and administrators who want to harness the full potential of Snowflake’s capabilities.

1. Understand Snowflake Query History
   Snowflake’s query history provides a detailed log of all queries executed within your account. This includes SQL statements, user information, execution times, and more. Understanding this log is the first step toward effective management.
   Key Components of Query History:
   • Query Text: The SQL statement executed.
   • Execution Time: Duration of the query.
   • User Information: The user who executed the query.
   • Resource Consumption: CPU and memory usage.
   • Status: Success or failure of the query.
2. Accessing Query History
   You can access query history via the Snowflake web interface or through SQL queries using the QUERY_HISTORY view in the INFORMATION_SCHEMA.
   Example SQL Query:
   SELECT *
   FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
   WHERE START_TIME > DATEADD(day, -7, CURRENT_TIMESTAMP());
   This query retrieves all queries executed in the last seven days.
3. Regular Monitoring and Auditing
   Regularly monitoring and auditing your query history helps in identifying performance bottlenecks, unauthorized access, and inefficient queries. Monitoring Best Practices:
   • Set up Automated Reports: Schedule regular reports that summarize query performance and resource usage.
   • Threshold Alerts: Configure alerts for queries that exceed certain thresholds for execution time or resource usage.

   Example SQL for Monitoring:

   SELECT QUERY_ID, EXECUTION_STATUS, TOTAL_ELAPSED_TIME, ROWS_PRODUCED

   FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

   WHERE TOTAL_ELAPSED_TIME > 10000;  — Threshold in milliseconds

4. Optimize Query Performance
   Analyzing query history is essential for optimizing query performance. Identify long-running queries and understand their execution plans.Steps to Optimize:
   1. Identify Slow Queries: Use the query history to find queries with high execution times.
   2. Examine Execution Plans: Use EXPLAIN plans to understand query execution paths.
   3. Optimize SQL Statements: Rewrite inefficient queries and ensure proper indexing.

   Example Optimization Query:

   EXPLAIN SELECT * FROM large_table WHERE condition = ‘value’;

5. Resource Usage and Cost ManagementSnowflake charges based on the resources consumed by queries. Efficient management of query history can help in cost management by identifying resource-intensive queries.Cost Management Tips:
   • Resource Usage Reports: Generate reports on resource usage by user and query type.
   • Optimize Virtual Warehouses: Adjust the size and concurrency of virtual warehouses based on query history analysis.
   • Use Result Caching: Take advantage of Snowflake’s result caching to reduce resource consumption for repetitive queries.

   Example Resource Usage Query:

   SELECT USER_NAME, WAREHOUSE_NAME, SUM(CREDITS_USED) AS total_credits

   FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

   GROUP BY USER_NAME, WAREHOUSE_NAME;

6. Implement Security Best PracticesMonitoring query history is also a key aspect of maintaining security. Track who is running what queries and ensure compliance with data access policies.Security Practices:
   • User Activity Logs: Regularly review logs for unusual query patterns or unauthorized access.
   • Access Control: Ensure proper role-based access control (RBAC) to limit data exposure.
   • Audit Trails: Maintain audit trails of query execution for compliance purposes.

   Example Security Query:

   SELECT USER_NAME, QUERY_TEXT, EXECUTION_STATUS

   FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY

   WHERE EXECUTION_STATUS = ‘FAILED’

     AND ERROR_CODE = ‘02000’;  — No data found (example error code)

7. Use Third-Party ToolsSeveral third-party tools can help manage and analyze Snowflake query history more efficiently, offering advanced features like automated alerts, visualizations, and detailed reporting.Recommended Tools:
   • Select.dev: Provides detailed monitoring and alerting for Snowflake workloads.
   • Chaos Genius: Offers anomaly detection and advanced analytics for Snowflake usage.
   • Metaplane: Specializes in data observability, including query performance monitoring.

Snowflake in Action: Examples of How to Access and Use Snowflake Query History

Snowflake’s query history feature is a powerful tool for tracking, analyzing, and optimizing query performance. By understanding and utilizing query history, data engineers can ensure efficient use of resources, troubleshoot issues, and enhance overall system performance. This blog will explore various examples of how to access and use Snowflake query history to achieve these goals.

Accessing Snowflake Query History

Snowflake query history can be accessed through the Snowflake web interface or by querying the QUERY_HISTORY view in the INFORMATION_SCHEMA or SNOWFLAKE.ACCOUNT_USAGE schema.
Example SQL Query to Access Query History:
SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME > DATEADD(day, -7, CURRENT_TIMESTAMP());
This query retrieves all queries executed in the last seven days.

Identifying Long-Running Queries

Long-running queries can significantly impact the performance and cost efficiency of your Snowflake instance. Identifying and optimizing these queries is crucial.
Example SQL Query to Find Long-Running Queries:
SELECT QUERY_ID, USER_NAME, EXECUTION_STATUS, TOTAL_ELAPSED_TIME, ROWS_PRODUCED
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE TOTAL_ELAPSED_TIME > 10000 — Threshold in milliseconds
ORDER BY TOTAL_ELAPSED_TIME DESC;
This query lists queries with execution times exceeding 10 seconds.

Analyzing Resource Consumption

Understanding resource consumption helps in managing costs and optimizing performance. You can analyze resource usage by individual queries or by user to identify heavy consumers.
Example SQL Query for Resource Consumption Analysis:
SELECT USER_NAME, WAREHOUSE_NAME, SUM(CREDITS_USED) AS TOTAL_CREDITS
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
GROUP BY USER_NAME, WAREHOUSE_NAME
ORDER BY TOTAL_CREDITS DESC;
This query aggregates total credits used by each user and warehouse, helping you identify top resource consumers.

Monitoring Query Failures

Query failures can disrupt workflows and impact data integrity. Monitoring failed queries helps in diagnosing and resolving issues promptly.
Example SQL Query to Monitor Failed Queries:
SELECT QUERY_ID, USER_NAME, QUERY_TEXT, ERROR_CODE, ERROR_MESSAGE
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE EXECUTION_STATUS = ‘FAILED’
ORDER BY START_TIME DESC;
This query retrieves details of failed queries, including error codes and messages.

Tracking User Activity

Tracking user activity is essential for security and compliance. By monitoring query history, you can ensure that users are accessing and manipulating data appropriately.
Example SQL Query to Track User Activity:
SELECT USER_NAME, QUERY_TEXT, EXECUTION_STATUS, START_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME > DATEADD(day, -1, CURRENT_TIMESTAMP())
ORDER BY START_TIME DESC;
This query lists all queries executed by users in the last 24 hours.

Optimizing Query Performance

Optimizing query performance involves analyzing execution plans and making necessary adjustments to SQL statements. Snowflake provides tools to examine query plans and understand execution paths.
Example SQL Query to Retrieve Execution Plans:
EXPLAIN SELECT * FROM large_table WHERE condition = ‘value’;
This command provides the execution plan for a given query, helping you understand and optimize its performance.

Using Result Caching

Snowflake’s result caching can significantly reduce query execution times and resource usage for repetitive queries. By monitoring query history, you can identify opportunities to leverage result caching.
Example SQL Query to Identify Repetitive Queries:
SELECT QUERY_TEXT, COUNT(*) AS EXECUTION_COUNT
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME > DATEADD(day, -7, CURRENT_TIMESTAMP())
GROUP BY QUERY_TEXT
HAVING COUNT(*) > 1
ORDER BY EXECUTION_COUNT DESC;
This query identifies queries that are executed multiple times, indicating potential candidates for result caching.

Ensuring Data Security

Data security is paramount in any data management strategy. Query history can help you monitor access patterns and detect unusual activity.
Example SQL Query to Detect Unusual Activity:
SELECT USER_NAME, QUERY_TEXT, START_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE USER_NAME NOT IN (SELECT USER_NAME FROM SNOWFLAKE.ACCOUNT_USAGE.USERS)
ORDER BY START_TIME DESC;
This query identifies queries executed by unknown or unauthorized users.

Managing Costs

Efficient management of query history can help in controlling and reducing costs. By analyzing query performance and resource usage, you can make informed decisions to optimize spending.
Example SQL Query for Cost Management:
SELECT QUERY_ID, USER_NAME, TOTAL_ELAPSED_TIME, CREDITS_USED
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE CREDITS_USED > 0.5 — Threshold in credits
ORDER BY CREDITS_USED DESC;
This query lists high-cost queries, helping you identify and manage expensive operations.

Troubleshooting Snowflake Query History

Snowflake’s query history feature is an essential tool for monitoring and optimizing your data environment. However, like any powerful tool, it can present challenges that need to be addressed to ensure smooth operation. This blog will guide you through common issues encountered with Snowflake query history and provide effective troubleshooting strategies to resolve them.

1. Incomplete or Missing Query Logs

Sometimes, you might notice that your query history does not show all the expected queries.
Troubleshooting Steps:
Check Time Range: Ensure you are querying the correct time range. Snowflake retains query history for up to 365 days.

SELECT *
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME > DATEADD(day, -30, CURRENT_TIMESTAMP());

Verify Permissions: Ensure you have the necessary permissions to access the query history views. You need the MONITOR privilege on the account or warehouse.

GRANT MONITOR ON ACCOUNT TO ROLE my_role;

2. Difficulty Identifying Resource-Intensive Queries

Finding the queries that consume the most resources can be challenging if you don’t know where to look.
Troubleshooting Steps:
Filter by Execution Time: Use the TOTAL_ELAPSED_TIME column to identify long-running queries.

SELECT QUERY_ID, USER_NAME, TOTAL_ELAPSED_TIME, ROWS_PRODUCED
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE TOTAL_ELAPSED_TIME > 10000 — Threshold in milliseconds
ORDER BY TOTAL_ELAPSED_TIME DESC;

Analyze Resource Consumption: Focus on queries with high credit usage.

SELECT QUERY_ID, USER_NAME, CREDITS_USED
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE CREDITS_USED > 0.5 — Threshold in credits
ORDER BY CREDITS_USED DESC;

3. Noticing Recurrent Query Failures

Persistent query failures can indicate deeper issues in your Snowflake setup.
Troubleshooting Steps:
Identify Failure Patterns: Look for recurring error codes and messages.

SELECT QUERY_ID, USER_NAME, ERROR_CODE, ERROR_MESSAGE
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE EXECUTION_STATUS = ‘FAILED’
ORDER BY START_TIME DESC;

Examine Specific Failures: Focus on the most frequent errors.

SELECT ERROR_CODE, COUNT(*) AS FAILURE_COUNT
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE EXECUTION_STATUS = ‘FAILED’
GROUP BY ERROR_CODE
ORDER BY FAILURE_COUNT DESC;

4. Unusual User Activity

Unusual query patterns by users can indicate potential security issues or misuse of resources.
Troubleshooting Steps:
Monitor User Activity: Track queries run by specific users.

SELECT USER_NAME, QUERY_TEXT, START_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME > DATEADD(day, -1, CURRENT_TIMESTAMP())
ORDER BY START_TIME DESC;

Detect Unusual Patterns: Identify queries from unknown or unauthorized users.

SELECT USER_NAME, QUERY_TEXT, START_TIME
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE USER_NAME NOT IN (SELECT USER_NAME FROM SNOWFLAKE.ACCOUNT_USAGE.USERS)
ORDER BY START_TIME DESC;

5. Slow Query Performance

Improving the performance of slow-running queries is critical for maintaining efficiency.
Troubleshooting Steps:
Analyze Execution Plans: Use the EXPLAIN command to understand and optimize query execution paths.

EXPLAIN SELECT * FROM large_table WHERE condition = ‘value’;

Identify Bottlenecks: Look for operations that consume the most time.

SELECT QUERY_ID, TOTAL_ELAPSED_TIME, PARTS_PRODUCED, EXECUTION_STATUS
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE TOTAL_ELAPSED_TIME > 10000 — Threshold in milliseconds
ORDER BY TOTAL_ELAPSED_TIME DESC;

6. Inefficient Use of Result Caching

Queries not leveraging result caching can lead to unnecessary resource consumption.
Troubleshooting Steps:
Identify Repetitive Queries: Find queries that are executed multiple times.

SELECT QUERY_TEXT, COUNT(*) AS EXECUTION_COUNT
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE START_TIME > DATEADD(day, -7, CURRENT_TIMESTAMP())
GROUP BY QUERY_TEXT
HAVING COUNT(*) > 1
ORDER BY EXECUTION_COUNT DESC;
Optimize for Caching: Ensure repetitive queries can take advantage of result caching by avoiding dynamic content.

7. High Query Costs

High query costs can affect your budget and require immediate attention.
Troubleshooting Steps:
Track High-Cost Queries: Identify and review expensive queries.

SELECT QUERY_ID, USER_NAME, TOTAL_ELAPSED_TIME, CREDITS_USED
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE CREDITS_USED > 0.5 — Threshold in credits
ORDER BY CREDITS_USED DESC;

Evaluate Usage Patterns: Review the overall resource usage to identify areas for cost reduction.

SELECT USER_NAME, WAREHOUSE_NAME, SUM(CREDITS_USED) AS TOTAL_CREDITS
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
GROUP BY USER_NAME, WAREHOUSE_NAME
ORDER BY TOTAL_CREDITS DESC;

Leveraging Snowflake Query History

Effectively managing Snowflake query history is essential for optimizing performance, troubleshooting issues, ensuring security, and managing costs within your data environment. By leveraging the comprehensive insights provided by Snowflake’s query history feature, data engineers and administrators can monitor performance, identify and resolve inefficiencies, and maintain a robust and efficient data platform.
In this comprehensive guide, we have explored the fundamentals of accessing Snowflake query history, provided practical examples of how to utilize this feature for various use cases, and shared troubleshooting techniques to address common challenges. Whether you are tracking long-running queries, analyzing resource consumption, monitoring user activity, or optimizing query performance, the strategies and best practices outlined in this guide will help you harness the full potential of Snowflake’s query history capabilities.
Regular monitoring, performance analysis, proactive optimization, and ensuring data security are key to maintaining a high-performing and cost-effective Snowflake environment. By following these best practices and leveraging Snowflake’s powerful tools, you can ensure that your data operations are both efficient and secure, driving better decision-making and business outcomes.

Learn how to attribute data spend and impact, eliminate cost spikes and prioritize data products that bring value. Book a demo today to see Seemore Data in action.