Course contents

The Analytics Engineer’s Field Guidedbt 3: ship it like a teamStep 13: Query tags

Query tags

Part 2 · dbt · step 13

Query tags: put a name on every credit

A few lines in dbt_project.yml turn Snowflake's bill into a report you can group by team.

· · ·

The bill went up 40% last month. Ismail wants to know which team caused it, and Snowflake's query history has the answer in front of him: thousands of rows of SQL text, warehouse names and run times, with nothing saying who any of it belongs to.

A query tag is the missing column. It's a label set on a session (one open connection, like the one a dbt run opens), and Snowflake stores it with every query that session runs. A run that was anonymous arrives already labelled:

query log, untagged
query_textcreate table as select…
warehouse_nameTRANSFORM_WH
elapsed4 min 12 s
whose is it?nobody knows
→
+query_tag
query log, tagged
query_textcreate table as select…
warehouse_nameTRANSFORM_WH
elapsed4 min 12 s
query_tag{"team_name": "finance"}

That one extra field is the difference between an investigation and a GROUP BY. Each team gets its own line in a cost dashboard, each product its own curve, and the monthly cost review becomes a filter change instead of a meeting.

Set the tag once, in dbt_project.yml

You don't tag queries by hand. Set a small JSON tag in dbt_project.yml, carrying the two fields you'll group the bill by, team_name and product_name. The + marks a config rather than a folder name, as with +materialized in step 4:

# dbt_project.yml
models:
  my_project:
    +query_tag: '{"team_name": "data", "product_name": "platform"}'   # every model
    marts:
      +query_tag: '{"team_name": "finance", "product_name": "revenue"}'  # models/marts/ wins
  • Staging and intermediate carry the project default: the data team owns that plumbing.
  • Seeds, snapshots and tests aren't under models:. They take the same +query_tag under their own seeds:, snapshots: and data_tests: keys.
  • A query_tag on the profile target is the session default for everything dbt runs. A model's tag replaces it while that model builds.

Override it on the one model that needs it

A folder-level tag covers 95% of the project. For the odd model another team pays for, put a config block at the top of the model file:

-- models/marts/dim_customer.sql
{{ config(
    query_tag = '{"team_name": "growth", "product_name": "crm"}'
) }}

Under the hood, dbt-snowflake runs alter session set query_tag before building the model and puts the previous tag back afterwards. Model config wins over folder config, and folder over project: the usual dbt hierarchy.

Three rules keep tags useful:

  • JSON, never free text, so Snowflake can parse it.
  • Stable keys with few distinct values (team_name, product_name), so a GROUP BY means something.
  • One convention everywhere. Most BI tools can send a query tag from their Snowflake connection settings. Use the same keys, and dashboard spend lands in the same buckets.

To check it worked, run a model, then list your own last queries in a worksheet, with no delay. query_history() is a table function (you query it like a table) found in every database, so pick any database first:

SELECT query_tag, query_text
FROM TABLE(information_schema.query_history())
ORDER BY start_time DESC
LIMIT 20

Read it back: one query, credits per team

First, what QUERY_HISTORY actually is

Snowflake logs every query run against your account: by hand, by dbt, by a BI tool. It keeps that log for a year in a view called SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY (rows show up within about 45 minutes). You query the view with plain SQL in a Snowsight worksheet. One row per query, and the columns you'll actually use:

Column
What it holds
query_id
A unique id per query, the key you join on.
query_text
The SQL that ran, in full.
query_tag
Your label. Empty unless you set one.
warehouse_name
Which warehouse burned the compute.
start_time, total_elapsed_time
When it ran, and for how long.
user_name, role_name
Who ran it, which is why step 1's user and service accounts matter.

It holds what ran, where, when and who, but not the compute cost. Credits live in a second view:

  • QUERY_ATTRIBUTION_HISTORY: one row per query with the compute credits it used, plus query_tag, warehouse_name and start_time. A cost-per-tag report needs nothing else.
  • A credit has a fixed price in your contract, a few dollars. Credits times price is money.

For the SQL text or the role too, join the two views:

SELECT a.query_id, a.credits_attributed_compute, q.role_name, q.query_text
FROM snowflake.account_usage.query_attribution_history a
JOIN snowflake.account_usage.query_history q ON q.query_id = a.query_id

Three things to know about the credits view:

  • Rows can take up to eight hours to appear.
  • Queries under roughly 100 ms aren't listed.
  • Idle time isn't attributed. A warehouse keeps billing until its auto-suspend timer fires, and those seconds belong to no query. So the total sits below your bill.

Who can read them. On a trial, ACCOUNTADMIN already can. USERLAND_ROLE can't: the views live in the shared SNOWFLAKE database. An admin opens both to a cost-reading role, once:

CREATE ROLE IF NOT EXISTS FINOPS_ROLE;
GRANT DATABASE ROLE SNOWFLAKE.GOVERNANCE_VIEWER TO ROLE FINOPS_ROLE;
GRANT ROLE FINOPS_ROLE TO USER your_login;   -- then USE ROLE FINOPS_ROLE

The query itself

Parse the JSON tag, sum the credits, group by week. That's the whole report Ismail can hand to anyone:

WITH tagged AS (
    SELECT try_parse_json(query_tag) AS tag,   -- NULL instead of an error if not JSON
           date_trunc(week, start_time) AS week,
           warehouse_name,
           credits_attributed_compute
    FROM snowflake.account_usage.query_attribution_history
    WHERE start_time > dateadd(week, -8, current_date())
)
SELECT week,
       tag:team_name::string    AS team_name,   -- tag:key reads a JSON field
       tag:product_name::string AS product_name,
       warehouse_name,
       sum(credits_attributed_compute) AS credits
FROM tagged
WHERE tag IS NOT NULL               -- untagged or non-JSON tags drop out
GROUP BY 1, 2, 3, 4
ORDER BY week, credits DESC

The whole loop: from dbt run to a cost dashboard

dbt run
tags each query
→
attribution history
tag + credits
→
GROUP BY tag
sum credits
→
Cost per team
a clear answer

That last box is the FinOps deliverable (FinOps: managing cloud cost as a shared team habit). Plug the query above into your BI tool and you have the consumption dashboard: spend by team, by product, by warehouse, week over week. When a spike appears, the tag names the owner before anyone opens a ticket.

Tag by what you'll slice
Only tag what you'll later group by: team, product, environment. A clever tag you never query is noise. Environment often comes free: Alice's userland runs use DEV_WH and production uses PROD_WH, so warehouse_name already keeps her dev runs out of finance's production bill.

Lesson quiz

  1. What is a Snowflake query tag for?

    1. Marking queries so the result cache keeps their results longer
    2. Hinting the optimizer to prioritise one team's queries
    3. Labelling queries so cost can be grouped by team or product
    4. Routing each query to the warehouse named in the tag
  2. The marts folder sets +query_tag in dbt_project.yml, and fct_orders sets its own query_tag in a config block. Which tag do fct_orders' queries carry?

    1. The model's own tag, since model config wins
    2. Neither, since conflicting tags cancel out
    3. Both tags, merged into one JSON string
    4. The folder tag, since dbt_project.yml wins
  3. The profile target sets a query_tag, dbt_project.yml sets one for all models and another for models/marts/, and fct_refunds in marts sets its own in config(). Which tag do fct_refunds's build queries carry?

    1. All of them, merged into one JSON object
    2. The tag in fct_refunds's own config()
    3. The profile target's tag, the session default
    4. The marts folder's tag