Warehouses and Cost in Production

Per-workload warehouses, query tags, resource monitors, statement timeouts, and the spend controls a Snowflake/dbt/Airflow stack needs.

Every model in this pipeline runs on Snowflake compute, and Cosmos points all of it at whatever warehouse the Airflow connection names. That’s a fine default and a terrible steady state. A select over a source table and a window function across the full lineitem history are not the same shape of work, they should not run on the same size of machine, and when the monthly credit number moves you want to name the model that moved it — not shrug at a warehouse ten workloads share. This chapter is about making compute a dial you set per model, tagging every query so a credit traces back to the work that spent it, and installing the guardrails that stop a broken incremental from teaching you the cost lesson at production scale. The Snowflake series covered the credit model, resource monitors, and account usage views as concepts; here they stop being concepts and become config in a dbt_project.yml and a macro in macros/.

One warehouse per shape of work

Snowflake warehouses are sized by doubling — XS is one credit an hour, S is two, M four, L eight — and billed per second past a sixty-second minimum. That pricing makes the obvious mistake expensive: running thin staging models on the same warehouse as a heavy mart means either you oversize the warehouse and pay M rates for a select *, or you undersize it and the mart spills to remote storage and crawls. The fix is to stop thinking about a warehouse and start thinking about a small fleet of them, each matched to a workload.

Create two, sized for the two ends of your DAG:

CREATE WAREHOUSE transform_xs
  WAREHOUSE_SIZE = 'XSMALL'
  AUTO_SUSPEND = 60 AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

CREATE WAREHOUSE transform_l
  WAREHOUSE_SIZE = 'LARGE'
  AUTO_SUSPEND = 60 AUTO_RESUME = TRUE
  INITIALLY_SUSPENDED = TRUE;

AUTO_SUSPEND = 60 parks each one a minute after the last query, so between DAG runs neither idles on the clock; AUTO_RESUME wakes it on the next statement. Now you need a way to send stg_orders to the small one and orders_enriched to the large one without editing a line of SQL — and dbt has exactly that config.

You set it declaratively in dbt_project.yml, folder by folder, so the routing is a property of where a model lives rather than something scattered through model files:

models:
  tpch:
    staging:
      +snowflake_warehouse: TRANSFORM_XS
    marts:
      +snowflake_warehouse: TRANSFORM_L
      # the one genuinely heavy mart wants more still:
      orders_enriched:
        +snowflake_warehouse: TRANSFORM_XL

Everything under models/staging/ builds on the XS; everything under models/marts/ on the L; the one wide fan-in that joins lineitem to orders, part, and supplier gets its own XL. A model with no override falls through to the warehouse the Airflow connection carries — so you only annotate the exceptions. You can also drop the override inline when it’s truly one model’s quirk: {{ config(snowflake_warehouse='TRANSFORM_XL') }} at the top of the file.

The mechanism underneath is worth knowing, because it’s what makes the next section’s claim true. dbt-snowflake doesn’t do anything clever: before it builds a model with a snowflake_warehouse set, it issues a USE WAREHOUSE TRANSFORM_XL, runs the model’s SQL, and reverts to the connection’s default afterward. The warehouse switch is a session command dbt sends around each node — nothing more.

Does the override survive Cosmos?

This is the question that matters for our stack, because Cosmos builds the dbt profile in memory from the Airflow connection, and it would be reasonable to worry that a per-model warehouse set in dbt_project.yml gets flattened away in that translation. It doesn’t — and the reason is that the override never travelled through Cosmos in the first place.

Walk the path. Cosmos parses your project into a manifest at render time; every model’s snowflake_warehouse config is baked into its node in that manifest. When the DAG runs, each Cosmos task shells out to dbt run --select stg_orders (or orders_enriched, task by task). It’s dbt-snowflake, inside that per-model invocation, that reads the node’s config and emits the USE WAREHOUSE. Cosmos supplies only the connection — the account, user, role, and the default warehouse — via SnowflakePrivateKeyFileProfileMapping. The per-model override rides in the compiled SQL dbt is executing, a layer Cosmos doesn’t touch. So the connection’s warehouse is the floor; the dbt config raises specific models above it; and no operator_args wiring is required to make that happen.

You can prove it rather than trust it. After a run, ask Snowflake which warehouse each model’s queries actually landed on:

SELECT
  warehouse_name,
  COUNT(*)                          AS statements,
  ROUND(SUM(total_elapsed_time)/1000, 1) AS wall_seconds
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE start_time >= DATEADD('hour', -2, CURRENT_TIMESTAMP())
  AND query_text ILIKE 'create%table%tpch%'
GROUP BY warehouse_name
ORDER BY wall_seconds DESC;

You’ll see TRANSFORM_XS, TRANSFORM_L, and TRANSFORM_XL as distinct rows, which is the override surviving end to end.

Where operator_args and dbt_cmd_flags do come in is when you want the warehouse chosen at runtime rather than baked into the project — the backfill case. A twelve-month backfill of orders_enriched is a different workload from its nightly one-day slice, and you may want it on an XL only for that run. Make the model read a var with a sane default:

{{ config(snowflake_warehouse=var('mart_warehouse', 'TRANSFORM_L')) }}

Then let the backfill DAG pass a different value through Cosmos, which forwards flags to the underlying dbt command:

DbtTaskGroup(
    # ...project_config, profile_config as before...
    operator_args={"vars": {"mart_warehouse": "TRANSFORM_XL"}},
)

Nightly runs omit the var and get TRANSFORM_L; the backfill DAG sets it and the same models build on the XL — the warehouse becomes a run-time parameter without a code change. dbt_cmd_flags=["--vars", "{mart_warehouse: TRANSFORM_XL}"] on the render config does the same thing at the DAG level if you’d rather set it once for the whole graph. That’s the seam: static routing lives in dbt_project.yml, dynamic routing rides through Cosmos as a flag.

Tag every query with the model and the DAG run

Sizing decides how much each model costs. Attribution decides whether you can find out. When ten models share TRANSFORM_L, WAREHOUSE_METERING_HISTORY will happily tell you the warehouse burned forty credits yesterday and nothing about which model spent them. The fix is a query tag — a label Snowflake stamps onto every statement in a session and surfaces in QUERY_HISTORY — and the trick is to make dbt set it automatically, per model, with the Airflow DAG run folded in so a credit traces back not just to a model but to the run of the pipeline that built it.

dbt-snowflake gives you the hook: a set_query_tag() macro it calls before each node and an unset_query_tag() after. Both are no-ops by default; override them in your project’s macros/ and every model starts labelling itself:

-- macros/query_tag.sql
{% macro set_query_tag() -%}
  {% set tag = {
      "dbt_model":     this.identifier,
      "invocation_id": invocation_id,
      "dag_id":        env_var('AIRFLOW_CTX_DAG_ID',     'adhoc'),
      "dag_run":       env_var('AIRFLOW_CTX_DAG_RUN_ID', 'manual'),
      "task":          env_var('AIRFLOW_CTX_TASK_ID',    'local')
  } %}
  {% set original = get_current_query_tag() %}
  {% do run_query("alter session set query_tag = '" ~ tojson(tag) ~ "'") %}
  {{ return(original) }}
{%- endmacro %}

{% macro unset_query_tag(original_query_tag) -%}
  {% if original_query_tag %}
    {% do run_query("alter session set query_tag = '" ~ original_query_tag ~ "'") %}
  {% else %}
    {% do run_query("alter session unset query_tag") %}
  {% endif %}
{%- endmacro %}

Every piece here earns its place. this.identifier is the model being built. invocation_id is dbt’s own per-run UUID, so you can separate two runs of the same model. And the three env_var reads are the cross-tool seam: Airflow injects AIRFLOW_CTX_DAG_ID, AIRFLOW_CTX_DAG_RUN_ID, and AIRFLOW_CTX_TASK_ID into the environment of every task it runs, and because each Cosmos task is an Airflow task, dbt sees them for free. The 'adhoc'/'manual' defaults mean the same macro still works when you run dbt build from your laptop — it just tags those queries as manual instead of crashing on a missing variable. The set returns the prior tag so unset can restore it rather than blindly clearing, which keeps the macro polite if something else set a tag upstream.

Now every CREATE TABLE Cosmos triggers arrives in QUERY_HISTORY wearing a JSON tag like {"dbt_model":"orders_enriched","dag_run":"scheduled__2026-06-28T...","task":"orders_enriched_run",...}. You wrote no SQL to make that happen and you’ll never have to remember to.

From a tag to a number of credits

A tag on a query is a label; you still have to turn labels into credits. QUERY_HISTORY gives you timings and bytes but not a per-query charge — historically you’d approximate a query’s share of its warehouse by execution time. Snowflake closes that gap with QUERY_ATTRIBUTION_HISTORY, which assigns actual compute credits to individual queries. Join it to QUERY_HISTORY on query_id, parse the tag, and group:

SELECT
  PARSE_JSON(q.query_tag):dbt_model::string AS model,
  PARSE_JSON(q.query_tag):dag_run::string   AS dag_run,
  ROUND(SUM(a.credits_attributed_compute), 3) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_ATTRIBUTION_HISTORY AS a
JOIN SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY AS q USING (query_id)
WHERE q.start_time >= DATEADD('day', -1, CURRENT_TIMESTAMP())
  AND q.query_tag ILIKE '%dbt_model%'
GROUP BY 1, 2
ORDER BY credits DESC;

That result is the whole discipline in one table: a ranked list of your models by the credits their last day of runs actually cost, grouped by the DAG run that built them. Cost has stopped being a property of infrastructure and become a property of work, which is the only form of it anyone can act on. Swap the GROUP BY to dag_run alone and you get spend per pipeline execution — the number to watch trend after a schema change.

Two honest caveats keep you from over-trusting it. First, QUERY_ATTRIBUTION_HISTORY attributes warehouse compute only, and only for queries substantial enough to attribute — very short statements and pure idle time aren’t charged to any query, so the sum of attributed credits for a warehouse is a little below that warehouse’s line in WAREHOUSE_METERING_HISTORY. Reconcile the two and expect the gap; it’s idle and overhead, not a bug. Second, ACCOUNT_USAGE views lag — metering by up to about three hours — so this is the tool for yesterday’s audit, not for “what did the query I ran ninety seconds ago cost.” That reconciliation query against metering is worth keeping alongside the attribution one:

SELECT warehouse_name, ROUND(SUM(credits_used), 2) AS credits
FROM SNOWFLAKE.ACCOUNT_USAGE.WAREHOUSE_METERING_HISTORY
WHERE start_time >= DATEADD('day', -1, CURRENT_TIMESTAMP())
  AND warehouse_name LIKE 'TRANSFORM%'
GROUP BY warehouse_name ORDER BY credits DESC;

The guardrails: monitors and timeouts

Attribution is visibility, and visibility never stopped a bill — it only explains one after the fact. The object that actually enforces a ceiling is the resource monitor, and in a stack with per-workload warehouses you want them at two scopes. A generous account-level backstop catches anything that runs away as a whole; a tighter per-warehouse cap on the transform warehouses stops a broken model from spending the entire budget by itself:

USE ROLE ACCOUNTADMIN;

-- account backstop: the whole stack can't exceed this in a month
CREATE OR REPLACE RESOURCE MONITOR account_backstop
  WITH CREDIT_QUOTA = 2000 FREQUENCY = MONTHLY START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 80  PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND;
ALTER ACCOUNT SET RESOURCE_MONITOR = account_backstop;

-- tight daily cap on the heavy transform warehouse
CREATE OR REPLACE RESOURCE MONITOR transform_l_cap
  WITH CREDIT_QUOTA = 120 FREQUENCY = DAILY START_TIMESTAMP = IMMEDIATELY
  TRIGGERS
    ON 90  PERCENT DO NOTIFY
    ON 100 PERCENT DO SUSPEND_IMMEDIATE;
ALTER WAREHOUSE transform_l SET RESOURCE_MONITOR = transform_l_cap;

The two verbs are not interchangeable, and choosing them per scope is deliberate. DO SUSPEND lets in-flight statements finish before the warehouse stops accepting new ones — the graceful choice for the account backstop, where you’d rather not kill a nearly-done nightly build to save a few credits. DO SUSPEND_IMMEDIATE kills running statements and rolls them back — the right hard stop on the per-warehouse cap, where the whole point is that the meter stops now. A warehouse and the account can each hold one monitor and both apply, so transform_l is bounded by the lower of its 120-credit day and the account’s 2000-credit month. One limit to carry forward from the Snowflake series: monitors govern user-managed warehouses only — they can’t see the serverless meters — so this covers your dbt compute but not, say, a runaway Snowpipe.

A resource monitor is a blunt instrument, though: it caps aggregate credits over a window and doesn’t care whether one query hung for six hours or a hundred ran cleanly. The finer guardrail is a statement timeout, which kills any single query that runs too long. Set it on the warehouse so it applies to everything that lands there:

ALTER WAREHOUSE transform_l SET
  STATEMENT_TIMEOUT_IN_SECONDS        = 1800   -- kill any query past 30 min
  STATEMENT_QUEUED_TIMEOUT_IN_SECONDS = 300;   -- abort if it waits 5 min to start

STATEMENT_TIMEOUT_IN_SECONDS is the backstop against a model that fans out a join into a Cartesian product and would otherwise burn credits until someone notices — thirty minutes is generous for a mart and merciful to the bill. The effective timeout is the lower of the warehouse setting and any session setting, so a warehouse value is a ceiling no model can raise. STATEMENT_QUEUED_TIMEOUT_IN_SECONDS is the one that matters specifically because of how Cosmos runs: it aborts a query that’s been waiting in the concurrency queue too long, which is exactly the failure mode a wide task-per-model DAG can create when everything piles onto one warehouse at once.

Setting these on the warehouse is the belt; dbt gives you the braces. The profile can carry session_parameters, which dbt applies to every connection it opens, so you can pin a tighter timeout for the transform role without touching the shared warehouse that BI also uses. Because Cosmos builds the profile from the Airflow connection, you pass them as profile_args on the mapping:

profile_mapping=SnowflakePrivateKeyFileProfileMapping(
    conn_id="snowflake_default",
    profile_args={
        "database": "ANALYTICS",
        "schema": "DBT_TPCH",
        "session_parameters": {"STATEMENT_TIMEOUT_IN_SECONDS": 1800},
    },
)

Now every model dbt builds runs under a thirty-minute session ceiling regardless of what the warehouse default is, and an analyst running the same query interactively on the same warehouse keeps the looser warehouse setting. Two dials, two audiences, one warehouse. Which brings us to concurrency.

Concurrency: scale out, not just up

Cosmos renders one Airflow task per model, and Airflow will run as many of them at once as max_active_tasks (and your pools) allow. A DAG with thirty staging models and max_active_tasks=16 means up to sixteen dbt processes hitting the transform warehouse simultaneously. A single-cluster warehouse has a concurrency limit — it runs a handful of queries at a time and queues the rest. At 3am, queuing is harmless: the queue drains, the DAG finishes a few minutes later, nobody’s watching. During the day, when the same warehouse also serves BI dashboards and ad-hoc analysts, queuing means a stakeholder’s dashboard spins because your pipeline filled the lane.

Sizing up doesn’t fix this. A bigger warehouse makes each individual query faster; it does not let more queries run at once — that’s a different axis. The axis you want is scale-out, which on Enterprise edition and above is a multi-cluster warehouse:

CREATE OR REPLACE WAREHOUSE transform_l
  WAREHOUSE_SIZE      = 'LARGE'
  MIN_CLUSTER_COUNT   = 1
  MAX_CLUSTER_COUNT   = 3
  SCALING_POLICY      = 'STANDARD'
  AUTO_SUSPEND = 60 AUTO_RESUME = TRUE;

MIN_CLUSTER_COUNT = 1 means the warehouse costs the same as before when load is light — one cluster, one L’s worth of credits per hour. When enough queries arrive that they’d start queuing, Snowflake spins up a second cluster, and a third, up to MAX_CLUSTER_COUNT = 3; each added cluster bills independently while it’s up, then spins down as load falls. You pay for concurrency only when you’re using it. SCALING_POLICY decides how eagerly: STANDARD adds a cluster the moment a query would otherwise queue — favoring responsiveness, which is what a shared prod warehouse wants — while ECONOMY waits until there’s enough backlog to keep a new cluster busy for several minutes, favoring cost over latency. For the transform-plus-BI mix, STANDARD.

The two levers now compose cleanly. Per-model sizing (the earlier snowflake_warehouse config) is scale-up: it makes the one genuinely heavy mart finish faster on an XL. Multi-cluster is scale-out: it lets the wide fan of staging models run concurrently without stepping on the dashboards. And the knob that decides whether you even need the second cluster is on the Airflow side — max_active_tasks and your Cosmos pool govern how much concurrency you generate. If your DAG is deliberately throttled to four parallel tasks, a single-cluster warehouse is plenty and multi-cluster would never trigger. Tune the two together: don’t pay for clusters to serve concurrency you’ve capped in Airflow, and don’t uncap Airflow onto a warehouse that can only queue it.

Worked example: pin a spike to a model, then fix it

Put it together with the failure this whole chapter exists to handle. Tuesday morning, the weekly cost habit — glancing at WAREHOUSE_METERING_HISTORY — shows TRANSFORM_L burned 96 credits on Monday against a trailing average near 40. Something in the transform pipeline more than doubled overnight. Without tags you’d be guessing; with them, you drill straight to the model. Run the attribution query narrowed to that warehouse and day, and one row dominates: orders_enriched, 61 credits, up from a typical 9. The tag even carries the dag_run, so you know it was the scheduled nightly build, not a stray manual dbt run.

Now ask Snowflake why it cost that. QUERY_HISTORY for that model’s statement tells the story that credits can’t:

SELECT
  bytes_scanned / POWER(1024, 3)       AS gb_scanned,
  partitions_scanned, partitions_total,
  bytes_spilled_to_remote_storage / POWER(1024, 3) AS gb_spilled_remote,
  execution_time / 1000                AS exec_seconds
FROM SNOWFLAKE.ACCOUNT_USAGE.QUERY_HISTORY
WHERE query_tag ILIKE '%orders_enriched%'
  AND start_time >= DATEADD('day', -2, CURRENT_TIMESTAMP())
ORDER BY start_time DESC;

The numbers are damning: partitions_scanned equals partitions_total — a full-table scan — and there’s remote spill, meaning the join outgrew even the L’s memory. The model is supposed to be incremental, processing one day of new lineitem rows and merging them in. A full scan every night means the incremental predicate stopped filtering. Open the model and the cause is a one-line regression: a refactor moved the where that referenced {{ this }} outside the {% if is_incremental() %} guard, so is_incremental() is now effectively always false and every run rebuilds the entire history.

The fix is two moves, one immediate and one structural. Restore the predicate inside the guard so the model goes back to touching one day, and let the next scheduled run confirm it — the following morning orders_enriched is back under 10 credits and partitions_scanned is a thin slice of the total. Then close the door so the next such regression can’t run for hours before anyone notices: the STATEMENT_TIMEOUT_IN_SECONDS = 1800 you set on transform_l would have killed the runaway full scan at thirty minutes, and the transform_l_cap monitor’s daily 120-credit SUSPEND_IMMEDIATE would have stopped the warehouse cold long before month-end. The guardrails don’t prevent the bug; they bound its blast radius to one night and a fraction of a day’s budget. That’s the trade the whole chapter is arguing for — you will ship a broken incremental eventually, and the difference between a shrug and an incident is whether the tag names it, the timeout caps it, and the monitor refuses to let it run past a line you drew in advance.

Final thoughts

Cost on this stack is not a bill you receive; it’s a system you build, and it has three moving parts that each live in a different tool. Sizing lives in dbt — a snowflake_warehouse config that routes each model to compute that fits it, riding through Cosmos untouched because it was never Cosmos’s to touch. Attribution lives in a macro — a query tag that folds the model, the invocation, and the Airflow DAG run into every statement, so QUERY_ATTRIBUTION_HISTORY can hand you spend per model and per run instead of spend per anonymous warehouse. And enforcement lives in Snowflake — resource monitors at two scopes, statement and queue timeouts on the warehouse, multi-cluster scale-out sized against the concurrency Airflow generates. None of it is cleanup you do after an invoice surprises you. It’s plumbing you install before the first production run, the way you’d wire a breaker into the panel before you switch the house on — because the model that eventually spikes won’t announce itself, and the only thing that turns that spike from an incident into a Tuesday-morning glance is the instrumentation you put there first.

Next: CI/CD and Deployment Across dbt and Airflow

Comments