Query history: find the next thing to fix
The profile explains one query. History explains all of them, and which ones cost the most.
Ismail fixed one query. Now: which one is next, and did the bill actually drop? Snowflake logs every query that runs, and there are three places to read that log. They differ in how far back they go and how fresh they are.
The ACCOUNT_USAGE views live in the shared SNOWFLAKE database. Out of the box only ACCOUNTADMIN can read them. Access comes through database roles on SNOWFLAKE, and they split the views between them: GOVERNANCE_VIEWER covers QUERY_HISTORY, USAGE_VIEWER covers the billing views like WAREHOUSE_METERING_HISTORY and storage metrics. Ask for both (or IMPORTED PRIVILEGES on SNOWFLAKE, which covers everything) before you go looking, otherwise the joins in this lesson and the next fail halfway.
INFORMATION_SCHEMA. QUERY_HISTORY()FROM TABLE( INFORMATION_ SCHEMA. QUERY_ HISTORY( RESULT_ LIMIT = > 1000)).ACCOUNT_USAGE. QUERY_HISTORYACCOUNT_ USAGE. QUERY_ ATTRIBUTION_ HISTORYWhat's in each view: history vs attribution
The two you'll join carry different halves of the story. QUERY_HISTORY has the what and how, QUERY_ATTRIBUTION_HISTORY has the cost. Both key on query_id.
query_id, query_text, query_tagquery_id, query_taguser_name, warehouse_name, warehouse_sizeuser_name, warehouse_nameexecution_status, error_messagecredits_attributed_computetotal_elapsed_time, compilation_time, execution_time, queued_overload_timecredits_used_query_acceleration bytes_scanned, partitions_scanned / partitions_totalquery_parameterized_hashbytes_spilled_to_local_storage / _remote_storage, rows_producedstart_time, end_time, no text, no statsSo attribution history tells you a query was expensive but not why, query history has the text and the stats (pruning, spill) but not the credits. You need both, which is the whole point of the join.
Query Attribution History: rank by real cost
The plain history tells you a query ran. Query Attribution History tells you what it cost: it splits warehouse credits across the queries that actually used them, so you can rank queries by credits and fix the expensive ones first. Start with single executions, they catch the monsters:
-- the 10 most expensive queries in the last 30 days select query_id, user_name, warehouse_name, credits_attributed_ from snowflake.compute account_ where start_time >= dateadd('day', -30, current_usage. query_ attribution_ history timestamp ()) order by credits_attributed_ limit 10;compute desc
That ranks individual runs, so a cheap query that fires 288 times a day never reaches the top 10. Group by query shape (next section) and you get the platform's real to-do list. One line usually dwarfs the rest, and that line is where the next hour of optimisation goes:
A dashboard query firing every 5 minutes with SELECT *: six times the cost of the entire ETL. Why didn't the result cache catch it? fct_events receives new rows every few minutes, and changed data invalidates the cached result. Fix the one red line, most weeks, and you've fixed the bill.
Group the repeats: query_parameterized_hash
query_parameterized_hash is the same for every run of a query that differs only by its literal values (the date, the customer id), so grouping on it turns thousands of rows into one line per query shape. That's how the table above was built:
select query_parameterized_ any_value(hash, warehouse_ count(*) / 7 as runs_name) as warehouse, per_ sum(day, credits_ from snowflake.attributed_ compute) as credits_7d account_ where start_time >= dateadd('day', -7, current_usage. query_ attribution_ history timestamp ()) group by query_parameterized_ order by credits_7d desc limit 20;hash
Join one hash back to QUERY_HISTORY to read a sample of its text, as below.
Join them on query_id: cost plus the reason
That ranking gives you the credits and the query_id, but not the text or the stats that explain the cost. Join back to QUERY_HISTORY on query_id to get all of it in one row, credits, the SQL, and the pruning and spill numbers that tell you which lever to pull:
select ah.credits_ , qh.attributed_ compute -- from attribution: the cost query_ , qh.query_tag -- which team / dbt model , qh.text -- from history: what ran partitions_ , qh.scanned, qh. partitions_ total -- was it pruning? bytes_ from snowflake.spilled_ to_ remote_ storage -- was it spilling? account_ join snowflake.usage. query_ attribution_ history ah account_ where ah.usage. query_ history qh using (query_id) start_ and qh.time >= dateadd('day', -7, current_ timestamp ())start_ order by ah.time >= dateadd('day', -8, current_ timestamp ()) -- don't scan a year of historycredits_ limit 20;attributed_ compute desc
Now the most expensive query arrives with its own diagnosis attached: high credits and partitions scanned near total points at pruning, high credits and remote spill points at an under-sized warehouse or too much data. One query, both halves.
QUERY_HISTORY.query_tag. Group the joined result by the tag and a single GROUP BY answers "which team or model burned the credits", not just "which query".
