Course contents

The Analytics Engineer’s Field GuideSnowflake: operateStep 8: Query history

Query history

Part 4 · Snowflake · step 8

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.

Source
Retention
Latency
Reach for it when
INFORMATION_SCHEMA. QUERY_HISTORY()
7 days
none
"what just ran?" Live debugging. A table function, 100 rows by default: FROM TABLE(INFORMATION_SCHEMA.QUERY_HISTORY(RESULT_LIMIT => 1000)).
ACCOUNT_USAGE. QUERY_HISTORY
365 days
~45 min
trends, regressions, account-wide history over time.
ACCOUNT_USAGE. QUERY_ATTRIBUTION_HISTORY
365 days
up to 8 h
cost. Credits attributed to each query, idle time stripped out. Queries under ~100 ms don't appear.

What'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_HISTORY (the what & how)
QUERY_ATTRIBUTION_HISTORY (the cost)
query_id, query_text, query_tag
query_id, query_tag
user_name, warehouse_name, warehouse_size
user_name, warehouse_name
execution_status, error_message
credits_attributed_compute
total_elapsed_time, compilation_time, execution_time, queued_overload_time
credits_used_query_acceleration
bytes_scanned, partitions_scanned / partitions_total
query_parameterized_hash
bytes_spilled_to_local_storage / _remote_storage, rows_produced
start_time, end_time, no text, no stats

So 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_compute
from snowflake.account_usage.query_attribution_history
where start_time >= dateadd('day', -30, current_timestamp())
order by credits_attributed_compute desc
limit 10;

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:

query (hash-grouped)
warehouse
runs / day
credits / wk
SELECT * FROM fct_events e JOIN dim_menu_item …
BI_WH
288
41.2
MERGE INTO fct_orders …
ETL_WH
24
6.8
SELECT team_name, sum(credits) …
ADHOC_WH
12
2.1

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_hash,
    any_value(warehouse_name)                  as warehouse,
    count(*) / 7                               as runs_per_day,
    sum(credits_attributed_compute)            as credits_7d
from snowflake.account_usage.query_attribution_history
where start_time >= dateadd('day', -7, current_timestamp())
group by query_parameterized_hash
order by credits_7d desc
limit 20;

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_attributed_compute            -- from attribution: the cost
    , qh.query_text                          -- from history: what ran
    , qh.query_tag                           -- which team / dbt model
    , qh.partitions_scanned, qh.partitions_total   -- was it pruning?
    , qh.bytes_spilled_to_remote_storage     -- was it spilling?
from snowflake.account_usage.query_attribution_history ah
join snowflake.account_usage.query_history qh using (query_id)
where ah.start_time >= dateadd('day', -7, current_timestamp())
  and qh.start_time >= dateadd('day', -8, current_timestamp())   -- don't scan a year of history
order by ah.credits_attributed_compute desc
limit 20;

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.

Pair it with your query tags
Remember the query tags from the dbt part? They land in 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".
What attribution doesn't count
Credits are attributed for query execution on warehouses only. Idle time, cloud services, storage and serverless features (Snowpipe, clustering, materialized view refresh) aren't in this view, so its total will always be below your invoice. The gap is mostly idle time, which is exactly what auto-suspend attacks (step 9).
The loop
Attribution history finds the expensive query, the Query Profile explains why it's expensive, you apply the right lever, then history a day later proves the credits went down. Remember the lag: the attribution view can trail by up to eight hours, so check the day after, not the hour after.

Lesson quiz

  1. Which view ranks queries by the compute credits they actually consumed?

    1. ACCOUNT_USAGE.QUERY_HISTORY
    2. INFORMATION_SCHEMA.QUERY_HISTORY()
    3. ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY
    4. ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
  2. You're debugging a query that ran two minutes ago. Which source shows it right now?

    1. ACCOUNT_USAGE.QUERY_HISTORY: immediate, and 365 days deep
    2. INFORMATION_SCHEMA.QUERY_HISTORY(): immediate, but 7 days deep
    3. ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY: refreshed every minute
    4. ACCOUNT_USAGE.QUERY_HISTORY: 7 days deep, with no lag at all
  3. Query A filters order_date = '2026-11-01', query B is identical with '2026-11-02', and query C adds one column to A's SELECT. How does grouping by query_parameterized_hash treat them?

    1. A and B share a hash; C gets one of its own
    2. All three share one hash, since they read one table
    3. A and B share one only if they used one warehouse
    4. Each gets its own hash, like query_id does