Mastering Snowflake Cost Per Query Attribution for Optimal Cloud Spend
Snowflake bills for warehouse time, not for queries. So when the bill arrives, the first question is always the same: which queries, run by whom, caused it? Snowflake’s QUERY_ATTRIBUTION_HISTORY view answers part of that. This guide shows how to use it, how to add tags so you can charge costs back to teams, and what the numbers leave out.
What “cost per query” means in Snowflake
The QUERY_ATTRIBUTION_HISTORY view, in the ACCOUNT_USAGE schema, gives the warehouse compute cost of each query run in your account over the last 365 days. For queries that run at the same time, Snowflake splits the warehouse cost between them in proportion to the resources each one used.
It is a compute number only, and it leaves a lot out:
- Idle time. Time when a warehouse is running with no query is not attributed to any query.
- Very short queries. Queries of about 100 milliseconds or less are too short to attribute and are not in the view.
- Everything that is not warehouse compute: data transfer, storage, cloud services, serverless features and tokens processed by AI services.
- Adaptive Warehouses. The view has no rows for them. Use
QUERY_METERING_HISTORYinstead.
Data is available from mid-August 2024 and the view can lag by up to eight hours. Besides ACCOUNTADMIN, a role needs the USAGE_VIEWER or GOVERNANCE_VIEWER database role to read it. The other views used below have their own roles: TAG_REFERENCES needs GOVERNANCE_VIEWER and WAREHOUSE_METERING_HISTORY needs USAGE_VIEWER. (Snowflake documentation)
Query logging: nothing to switch on
The old advice to adjust “account settings” to log every query is not needed. The ACCOUNT_USAGE query history views are there for roles with access and keep 365 days. What you control is who can read them and how well your queries are labelled, which is what the rest of this guide covers.
Find the queries that cost the most
Start with the recurring queries, because a modest query that runs thousands of times a month can cost more than one large query. Grouping by query_parameterized_hash treats runs that differ only in their literal values as the same query, as long as those literals sit in simple comparisons such as = or >=.
SELECT query_parameterized_hash,
ANY_VALUE(query_id) AS example_query_id,
ANY_VALUE(warehouse_name) AS warehouse_name,
COUNT(*) AS runs,
SUM(credits_attributed_compute) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY
WHERE start_time >= DATEADD(DAY, -30, CURRENT_TIMESTAMP())
GROUP BY query_parameterized_hash
ORDER BY credits DESC
LIMIT 20;Take the example_query_id from the top rows and look it up in QUERY_HISTORY for the SQL text, the user and the warehouse. Long-running queries, queries that scan a lot of data, and queries that run very often are the ones worth tuning first.
Attribute cost to users and teams
Per user is one change to the same query:
SELECT user_name,
SUM(credits_attributed_compute) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY
WHERE start_time >= DATEADD(MONTH, -1, CURRENT_DATE)
GROUP BY user_name
ORDER BY credits DESC;To roll users up into teams, use object tags, which Snowflake recommends for chargeback and showback. Create a tag such as cost_center, apply it to each user, and join the tag references to the attribution view. The left join keeps users who have no tag yet, so you can see how much cost is still unassigned:
SELECT COALESCE(tr.tag_value, 'untagged') AS cost_center,
SUM(q.credits_attributed_compute) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY q
LEFT JOIN SNOWFLAKE.ACCOUNT_USAGE.TAG_REFERENCES tr
ON tr.domain = 'USER'
AND tr.object_name = q.user_name
AND tr.tag_name = 'COST_CENTER'
WHERE q.start_time >= DATEADD(MONTH, -1, CURRENT_DATE)
GROUP BY COALESCE(tr.tag_value, 'untagged')
ORDER BY credits DESC;If a team has its own warehouses, tag the warehouses instead and attribute their whole metered cost, including idle time. If an application runs queries on behalf of several teams, tag the queries, as in the next section. (Snowflake documentation: attributing cost)
Tag the queries themselves
Some costs cannot be told apart by user, for example when a pipeline tool or an application connects as a single Snowflake user for many teams. Set a query tag at the start of the session, and every query after it carries the tag:
ALTER SESSION SET QUERY_TAG = 'COST_CENTER=finance';Then group the attributed cost by tag, counting untagged queries so you can see how much is still unlabelled:
SELECT COALESCE(NULLIF(query_tag, ''), 'untagged') AS tag,
SUM(credits_attributed_compute) AS compute_credits,
SUM(credits_used_query_acceleration) AS acceleration_credits
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY
WHERE start_time >= DATEADD(MONTH, -1, CURRENT_DATE)
GROUP BY tag
ORDER BY compute_credits DESC;The second credits column is the Query Acceleration Service, which is billed on top of the query’s compute credits.
dbt, Airflow and other tools. dbt can set a default query tag in your profile, and a query_tag setting on any model or folder of models, so each model’s queries carry its own label:
# dbt_project.yml
models:
my_project:
finance:
+query_tag: dbt_finance(dbt documentation: Snowflake configurations) Airflow’s Snowflake connection accepts session-level parameters (Airflow Snowflake provider), which is one place to set a tag. A tag on a connection is the same for every DAG that uses it, so to tell DAGs apart, set the tag per task, for example with an ALTER SESSION SET QUERY_TAG statement at the start of each one. Where a pipeline or BI tool connects as its own Snowflake user, the per-user query above already shows what it costs.
Account for idle time
Per-query numbers add up to less than the bill, because idle time belongs to no query. To see the gap for your account, compare what the warehouses were billed with what was attributed to queries:
SELECT SUM(credits_used_compute) AS billed_credits,
SUM(credits_attributed_compute_queries) AS attributed_to_queries,
SUM(credits_used_compute) - SUM(credits_attributed_compute_queries) AS idle_or_unattributed
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD(MONTH, -1, CURRENT_DATE);If you want every team’s total to add up to the bill, spread the idle credits across teams in proportion to their attributed credits. Snowflake’s documentation includes a worked query for that. The metering view can lag by up to three hours, and credits_attributed_compute_queries is empty for Adaptive Warehouses. (Snowflake documentation: WAREHOUSE_METERING_HISTORY)
Optimize the high-cost queries
Once the expensive queries are on a list, the usual fixes apply:
- Select only the columns and rows you need, and filter early so Snowflake can skip micro-partitions it does not need to read.
- Check whether the query runs on a warehouse that is bigger than it needs, or sits idle between runs.
- For repeated heavy aggregations over data that changes slowly, consider a materialized view (Enterprise Edition), and for large tables that are filtered on the same columns, consider clustering. Both have a running cost, so measure first.
Our guide to the top 17 Snowflake cost optimization techniques has the SQL for each.
Set alerts and budgets
Snowflake’s built-in cost limits work at the level of a warehouse or a budget, and a statement timeout stops a runaway query, but none of them alerts on a single query’s credit cost. A resource monitor can notify you, or suspend a warehouse, when the warehouse reaches a credit limit, but it covers warehouses only. For serverless features and AI services, use a budget, which notifies you when spending passes a limit. (Snowflake documentation: resource monitors) If you want an alert on a particular pattern, such as one query hash passing a credit threshold in a day, schedule a Snowflake alert that runs a query like the first one above.
Make it a habit
Share the cost of the top queries with the people who write them, and agree on a tag for every pipeline. A monthly review of the top 20 recurring queries, the untagged share and the idle-time gap keeps the numbers honest as workloads change.
Conclusion
Attributing Snowflake cost to queries is a method, not a mystery: QUERY_ATTRIBUTION_HISTORY for the compute cost of each query, tags to say who it belongs to, the metering view to see what attribution leaves out, and a regular review of the biggest recurring queries. Start with the first query in this guide today. It takes a minute to run and shows where your compute goes.
