π Query History in Snowflake
π What is Query History?β
In Snowflake, Query History is like a diary of everything that happens in your account π.
It records each SQL query along with details about its execution:
π When it ran (start/end time)
π€ Who ran it (user, role)
βοΈ Where it ran (warehouse)
π How it performed (duration, rows scanned, rows returned)
- β What happened (status: success, failed, queued)
π The data is available in two main system views:
SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORYβ Up to 1 year history (but ~45 min lag).INFORMATION_SCHEMA.QUERY_HISTORYβ 7 days of history, near real-time.
π‘ Why is Query History Important?β
π§ Troubleshooting β Debug long-running or failed queries.
β‘ Performance Optimization β Spot queries waiting in queue β maybe your warehouse is too small.
π΅οΈ Auditing β Track query activity for compliance & governance.
π° Cost Awareness β Detect queries consuming lots of compute credits.
π In short: Query History = Visibility + Control + Optimization.
π₯οΈ Practical Examplesβ
π See Queries from the Last Hourβ
SELECT
query_text,
execution_status,
start_time,
end_time,
total_elapsed_time,
warehouse_name
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(hour, -1, CURRENT_TIMESTAMP())
ORDER BY start_time DESC;
π Find the Top 5 Longest Queries in Last 24 Hoursβ
SELECT
query_id,
user_name,
warehouse_name,
total_elapsed_time/1000 AS total_elapsed_seconds,
query_text
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD(day, -1, CURRENT_TIMESTAMP())
ORDER BY total_elapsed_time DESC
LIMIT 5;
π― Use Case: Query History for Performance Tuningβ
π Scenario: A dashboard is running slow every morning.
π Steps:
1οΈβ£ Use QUERY_HISTORY to check queries running during that time.
2οΈβ£ Identify queries that are queued or consuming high compute.
3οΈβ£ If many are queued β increase warehouse size or enable multi-cluster.
4οΈβ£ If one query is inefficient β optimize SQL (pruning, clustering, materialized views).
π Result: Faster dashboards, smoother performance, and smarter credit usage.
π Best Practicesβ
- β Regularly review long-running queries.
- β Filter by warehouse_name to analyze workload distribution.
- β Combine with warehouse load history for full picture.
- β Set up monitoring/alerts (via Snowflake tasks or BI tools).
β οΈ Limitationsβ
β INFORMATION_SCHEMA.QUERY_HISTORY β Only 7 days of data.
β ACCOUNT_USAGE.QUERY_HISTORY β Lag of ~45 minutes.
β Data volume can be huge in busy accounts β filtering needed.
π Advantagesβ
β¨ Provides full transparency into query behavior. β¨ Essential for tuning warehouses and managing cost. β¨ Helps with auditing & compliance.
π Disadvantagesβ
β οΈ Data lag in ACCOUNT_USAGE may affect near real-time monitoring.
β οΈ No built-in recommendations β you must interpret results.
β οΈ Large accounts may generate millions of rows in history.
π Roles & Privileges Requiredβ
To access Query History views:
MONITORprivilege on the warehouse, orACCOUNTADMINrole for account-level usage.
β Quick Recap
π Query History = Snowflakeβs query diary π Use it to analyze performance, auditing, and costs βοΈ Works with Warehouse Sizing & Optimization for best results
π In training: Always teach where to find it, how to query it, and how to interpret results.