Query History in Snowflake Information Schema
If you've ever tried to figure out why a query took 47 minutes instead of 4, you're already thinking about this the right way. The INFORMATION_SCHEMA.QUERY_HISTORY table function is Snowflake's built-in answer to that question. It's not a physical table you query directly. It's a table function you call with SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(...)). That syntax matters because the parameters control what you see and how fast it runs. Here's the straightforward part. You can filter by account, database, schema, user, session, warehouse, and time range. The default time range is eight hours. If you're investigating something from last Tuesday, you need to specify it. Everything else falls out from there.
Using the Snowflake Information Schema query_history Function
I keep coming back to this function because it's the first place I look when anything is slow or expensive. Here's a query I run almost every week: SELECT query_id, query_text, warehouse_name, execution_time, bytes_scanned, bytes_written, credits_used, start_time, end_time FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(RESULT_LIMIT => 50, TIME_RANGE_START => DATEADD('hour', -24, CURRENT_TIMESTAMP()))) ORDER BY start_time DESC; The RESULT_LIMIT parameter is important. The default is 10,000 rows. If you don't set it and your warehouse ran a lot of queries in the time window, Snowflake will truncate your results silently. You'll never know some of those queries happened. Set it explicitly. Make it bigger if you need to. I usually run with 50,000 when doing end-of-day reviews.
One thing people miss: credits_used. The column exists but it's only populated for queries that actually consumed virtual warehouse credits. Statements like USE DATABASE or SET variables won't have a value there. When I'm auditing cost leaks, I filter on queries where credits_used is not null and execution_time is high. That gets me the real spenders, not the noise. Another common mistake is relying on query_text alone for long queries. Snowflake truncates query_text at a certain length. If your query is a massive join with subqueries and CTEs, you'll get the first portion and then it cuts off. There's no parameter to change this. The workaround is using RESULT_SCAN on the query_id after the fact, but that only works within five minutes of execution. After that, the result is gone. I learned this the hard way when a colleague ran a 12GB materialized CTE, it failed, and we spent twenty minutes trying to reconstruct the query from the truncated text before someone remembered to check the raw result output. The table function also exposes fields like error_code, error_message, and compilation_error, which you should always check. A query can return successfully from Snowflake's perspective but have warnings embedded in the output. WARNING columns appear in the result metadata even when the status is SUCCESS. I wrote a simple monitor query that flags any execution with warnings > 0 so I catch data quality issues before they propagate into reports.
Get the Full Details
Performance on the function itself scales with the time range and result limit. Querying the last 72 hours with a result limit of 100,000 typically returns in under two seconds. Going back seven days blows up past thirty seconds on a moderately active account. If you're doing regular reporting off this data, pull into a tracking table instead of re-querying the function every time. A simple INSERT INTO query_tracking SELECT * FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(...)) run hourly keeps your history durable and your dashboards fast. The main limitation nobody talks about is visibility. INFORMATION_SCHEMA.QUERY_HISTORY only shows queries you have access to based on your role. ACCOUNT_ADMIN can see everything. A role with only USAGE on a single database will only see queries against that database, even if the same session touched others. This tripped me up once when I was troubleshooting a cross-database pipeline failure. The query history looked clean because I was querying it from a limited role. Switching to ACCOUNTADMIN revealed the actual failing step in a different schema. Always verify your role scope before declaring something invisible. Another limitation is retention. Query history lives in the function for nine days by default on standard accounts. Enterprise and higher get longer. If you need audit trails beyond that, this function is not your solution. You need to copy the data out yourself or rely on ACCEL or a custom tracking table. I built a daily extract job that copies the last 24 hours of query history into a permanent table with a watermark column. It costs about twelve dollars a month in warehouse credits on a XS warehouse and has saved me more times than I can count.
If you need granular per-byte-scanned breakdowns or want to track query patterns over months, look at ACCOUNT_USAGE.QUERY_HISTORY instead. It's a persistent view, not a function, and it gives you the same columns plus additional metadata like rows_inserted, rows_updated, and external_access_calls. The tradeoff is a slight delay. ACCOUNT_USAGE data lags behind real-time by about fifteen to thirty minutes. For alerting and monitoring, INFORMATION_SCHEMA.QUERY_HISTORY is faster. For historical analysis and cost modeling, ACCOUNT_USAGE is better. Use both depending on the question. The query_hash and query_hash_version columns are useful if you're deduplicating similar queries. Two queries can have different query_text but identical query_hash if they're structurally the same with different literal values. I use this pattern to aggregate runtime statistics by query shape rather than by individual execution. It cuts report generation time significantly when you're dealing with parameterized queries that change values on every run. Finally, there's a practical tip that's worth repeating. Use the EXTERNAL_STATEMENT_ID column if you're working with external functions or streaming data. It ties queries to their external call contexts and helps separate warehouse-side execution from external service latency. Without it, you're guessing about where time is being spent in integrated pipelines.
This function is workhorse infrastructure. It does what it says without fanfare. The details that matter are the parameters, the role visibility, the retention window, and knowing when to move past it into ACCOUNT_USAGE. Treat it like any other operational tool. Understand its limits and build around them.
