How to Reduce the Snowflake Cost of Your dbt Models (and Find the Ones That Cost the Most)

TL;DR

• You cannot reduce what you cannot attribute. dbt already writes the model name into every query it sends to Snowflake, so you can price each model with one join.
• Most dbt spend comes from a small number of models. Rank them first, then change only those.
• The levers, in the order they usually pay off: incremental models, a right-sized warehouse per model, a cheaper warehouse for tests, running less often, and slim CI.
• Check the saving the same way you found the cost: credits per model, before and after, over equal windows.
• Do not trade a bill for a slow pipeline. Keep an eye on run time next to credits.

Step 1: put a price on every model

dbt adds a comment to each query it runs. By default the comment is JSON that includes the dbt version, profile and target names and the node ID of the resource that issued the query, and on Snowflake dbt places it at the end of the statement. Snowflake keeps the statement text in QUERY_HISTORY, and QUERY_ATTRIBUTION_HISTORY holds the credits attributed to each query. Join them on QUERY_ID and read the node ID out of the comment.

select
regexp_substr(qh.query_text, '"node_id": "([^"]+)"', 1, 1, 'e', 1) as dbt_node_id,
count(*) as queries,
round(sum(qh.total_elapsed_time) / 1000) as total_seconds,
round(sum(qa.credits_attributed_compute), 2) as credits
from snowflake.account_usage.query_attribution_history qa
join snowflake.account_usage.query_history qh
on qh.query_id = qa.query_id
where qa.start_time >= dateadd(day, -30, current_timestamp())
and qh.query_text ilike '%"app": "dbt"%'
group by 1
order by credits desc
limit 25;

Things to know about this query:

• QUERY_ATTRIBUTION_HISTORY does not include warehouse idle time, queries of roughly 100 milliseconds or less, or serverless costs, and its latency is up to eight hours. A model’s real cost is a little higher than what you see, because the warehouse also idles between runs.
• QUERY_TEXT is cut off at 100,000 characters. A very long statement can lose its trailing comment.
• If you changed query-comment in dbt_project.yml, change the match strings to your format.
• The node IDs include tests (test.…) as well as models. A test that scans a large table every run shows up in this list, and that is often the first saving.

If you prefer tags to comments, dbt supports a query tag at three levels: a default in your profile, a project or folder setting, and a single model.

# dbt_project.yml
models:
my_project:
marts:
+query_tag: marts

-- models/events/sessions.sql
{{ config(materialized='table', query_tag='sessions') }}

Tags are set on the Snowflake session. dbt runs alter session set query_tag when a model with its own tag starts and resets it when the model finishes, so a build that fails halfway can leave later queries with the wrong tag. Treat tag-based numbers as good, not exact. If you want more on tag design, see our Snowflake query tags guide.

Then group by tag:

select qa.query_tag,
count(*) as queries,
round(sum(qa.credits_attributed_compute), 2) as credits
from snowflake.account_usage.query_attribution_history qa
where qa.start_time >= dateadd(day, -30, current_timestamp())
and qa.query_tag <> ''
group by qa.query_tag
order by credits desc;

Step 2: look at the top ten, not the whole project

Take the ten most expensive nodes. For each one write down three things: how often it runs, how long it takes, and what it reads. Credits are roughly the size of the warehouse times the time it runs, so a model is expensive for one of three reasons: it runs too often, it runs too long, or it runs on a warehouse that is bigger than it needs. The levers below map to those three.

Step 3: the levers

1. Build incrementally instead of rebuilding everything

An incremental model transforms only the rows you tell dbt to filter for and inserts them into a table that already exists. dbt’s own documentation says this limits the data that needs to be transformed and reduces compute costs.

{{ config(materialized='incremental', unique_key='event_id') }}
select * from {{ ref('stg_events') }}
{% if is_incremental() %}
where event_time >= (select coalesce(max(event_time), '1900-01-01') from {{ this }})
{% endif %}

Points that matter for cost:

• On Snowflake the default incremental strategy is merge. The supported strategies are merge, append, delete+insert, insert_overwrite and microbatch. insert_overwrite behaves like a truncate and re-insert and overwrites the entire table, so it does not save work on its own.
• A merge can fail with a nondeterministic merge error if the unique_key is not unique. dbt’s documentation suggests delete+insert in that case.
• --full-refresh drops and rebuilds the table from scratch. Use it when the logic changes, not as a default in a scheduled job. A scheduled dbt run --full-refresh turns every incremental model back into a full rebuild.

2. Give heavy models the warehouse they need, and light models a small one

dbt lets you set the warehouse per model, folder or test:

# dbt_project.yml
models:
+snowflake_warehouse: "EXTRA_SMALL"
my_project:
clickstream:
+snowflake_warehouse: "EXTRA_LARGE"

Snowflake’s guidance is to run the same queries on more than one size and match the warehouse to the size and complexity of the queries. Larger sizes scale roughly linearly for large, complex queries, but larger is not necessarily faster for small ones. So the move is not “make everything smaller”. It is to stop running a 40-second model on the same large warehouse as the one model that needs it.

3. Run tests on a smaller warehouse

The same setting works for data tests:

data_tests:
+snowflake_warehouse: "EXTRA_SMALL"

Tests tend to be many small queries. Running them on the same warehouse as your heaviest models keeps that warehouse awake and sized for work the tests do not need.

4. Run less often

Check how often a model is refreshed against how often anyone reads what it feeds. A model built every 15 minutes for a dashboard that is opened weekly is the cheapest saving in the project. If you cannot see who reads what, look at the BI side first.

5. Use slim CI

A pull request that rebuilds the whole project on every push is a recurring bill. dbt’s defer feature lets you run only a subset of models without first building their parents: with --defer and a manifest from a previous run supplied through --state, references to models you did not select resolve to the existing production objects.

dbt build --select state:modified+ --defer --state path/to/prod-artifacts

state:modified+ selects changed models and everything downstream of them. dbt’s documentation warns that mixing datasets across environments can give unexpected results, for example if you apply data limits in development that you do not apply in production, so test the setup before you rely on it.

A note on storage

By default, tables dbt creates on Snowflake are transient, which means no Fail-safe period and a Time Travel retention of one day by default. That lowers storage cost. If you set transient: false on a model, you are choosing to pay for that history.

Step 4: prove it

Re-run the Step 1 query for the same models over a window of the same length after your change, and compare credits per run, not totals. Put run time next to credits. If credits fell and run time doubled, you have moved the cost into waiting.

How Seemore handles this

Seemore Data does the attribution in Step 1 for you. It reads Snowflake query history, extracts tags from the SQL comments and the QUERY_TAG property for dbt (and for Tableau, Looker, Power BI, ThoughtSpot, Airflow and Hex), and lets you slice cost and performance by the tools and workflows behind your queries. For dbt Core, setup uses the dbt-snowflake-query-tags package, as described in Seemore’s documentation. The Usage Based Optimization page covers the pipeline side: ETL visibility, BI optimization and orchestration across the stack.