Skip to main content

πŸ“œ 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:

  • MONITOR privilege on the warehouse, or
  • ACCOUNTADMIN role 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.