dbt on Snowflake, Orchestrated by Airflow

Cosmos renders your dbt-on-Snowflake project as a task-per-model Airflow DAG — reusing the one connection that already holds your key-pair credentials.

Three posts in, you have three tools that each know how to log in to Snowflake — and no single graph that ties them together. dbt has a profile. Airflow has a connection. Both hold a copy of the same private key, which is one copy too many. This post collapses that duplication and, in the same move, turns the dbt run into something Airflow can actually see inside: one task per model, each with its own retry, its own logs, its own square in the grid.

Cosmos is the piece that does it. You built it warehouse-agnostic in the Cosmos deep-dive; here it points at Snowflake, and the interesting parts are all where the two tools meet — who holds the credentials, which warehouse each model bills to, where dbt actually runs, and how Cosmos learns the shape of your project without paying for it at every scheduler heartbeat.

One place for credentials

The naive way to run dbt from Airflow is a BashOperator that shells out to dbt run. It works, and it’s a black box — the whole project is one task, one retry, one log file, and when model number forty of sixty fails you rerun all sixty. Worse, that approach needs a profiles.yml sitting on the worker with the Snowflake private key in it, which means your key now lives in two places: the Airflow connection from post 3, and a YAML file on disk.

Cosmos removes both problems. It reads your dbt project’s manifest, renders each model as its own Airflow task, and — this is the part that matters for Snowflake — builds the dbt profile in memory from an existing Airflow connection. No profiles.yml ships to the worker. The key lives in exactly one place, the connection you already created.

The bridge is a profile mapping. The connection from post 3 stored an encrypted .p8 key by path, with the passphrase in the connection’s password field, so the mapping that matches it exactly is the encrypted-private-key-file one:

from cosmos import (
    DbtDag,
    ProjectConfig,
    ProfileConfig,
    ExecutionConfig,
    RenderConfig,
)
from cosmos.profiles import SnowflakeEncryptedPrivateKeyFilePemProfileMapping

profile_config = ProfileConfig(
    profile_name="tpch",
    target_name="prod",
    profile_mapping=SnowflakeEncryptedPrivateKeyFilePemProfileMapping(
        conn_id="snowflake_default",
        profile_args={
            "database": "ANALYTICS",
            "schema": "DBT_TPCH",
        },
    ),
)

The mapping reads the snowflake_default connection — the account, user, role, warehouse, the private_key_file path, and the passphrase — and assembles the equivalent of a profiles.yml target at runtime. profile_args layers on the two things a connection doesn’t naturally carry: which database and schema dbt should build into. profile_name and target_name just have to match what your dbt_project.yml expects; nothing on disk has to agree with them because, in this mode, nothing on disk exists.

Cosmos ships a mapping per Snowflake auth shape, and the one you pick has to match how the connection stored the key. SnowflakeUserPrivateKeyProfileMapping reads an inline, unencrypted key from private_key_content in the connection extra — reach for it if you held the key as base64 content instead of a file path. SnowflakeEncryptedPrivateKeyPemProfileMapping is the same idea for an encrypted inline key. And SnowflakeUserPasswordProfileMapping pulls a password out of the connection — the legacy path, for the auth method Snowflake is retiring. Match the mapping to the connection you built in post 3 and the profile assembles itself; mismatch it and you get a render-time error we’ll come back to at the end.

Or check in a profiles.yml — and why you usually shouldn’t

ProfileConfig has a second constructor that’s worth understanding precisely because the title of this post implies a choice. Instead of a mapping, you can hand Cosmos a path to a real profiles.yml:

profile_config = ProfileConfig(
    profile_name="tpch",
    target_name="prod",
    profiles_yml_filepath="/opt/airflow/dbt/tpch/profiles.yml",
)

Now Cosmos doesn’t build a profile at all — it points dbt at the file you checked in, exactly as if you’d run dbt by hand. This is the right call in a couple of real situations: when your profiles.yml uses dbt features the mapping doesn’t model (a custom query_tag, a reuse_connections toggle, per-target threads you tune by hand), or when the same dbt project has to run identically inside and outside Airflow and you want one source of truth for the profile that both honor.

The tradeoff is the whole reason the connection mapping exists. A checked-in profiles.yml either hardcodes the Snowflake credentials — a private key in your repo, which is exactly the thing post 3 spent a page telling you never to do — or it defers to env_var() interpolation, which means you’re now managing a second set of environment variables that has to stay in lockstep with the Airflow connection. Either way the credential lives in two places, and two places drift. The connection mapping keeps the number at one: the key sits in Airflow’s secrets store, Cosmos reads it at task time, and there is no YAML on the worker to leak, rotate separately, or forget to update. Unless you have a concrete reason to check in the file, the mapping is the default that keeps this whole series’ “one source of truth for credentials” promise intact.

The rest of the wiring

The profile is the only Snowflake-specific piece. The other three configs are the same shape you’d use against any warehouse:

from datetime import datetime

project_config = ProjectConfig("/opt/airflow/dbt/tpch")

execution_config = ExecutionConfig(
    dbt_executable_path="/opt/airflow/dbt-venv/bin/dbt",
)

tpch_dag = DbtDag(
    project_config=project_config,
    profile_config=profile_config,
    execution_config=execution_config,
    render_config=RenderConfig(select=["path:models/marts"]),
    schedule="@daily",
    start_date=datetime(2026, 6, 1),
    catchup=False,
    dag_id="tpch_transform",
)

ProjectConfig points at the dbt project directory — the one with your models/, dbt_project.yml, and packages.yml. ExecutionConfig names the dbt binary and, by omission, runs in the default LOCAL execution mode. RenderConfig takes dbt’s own selector syntax, so select=["path:models/marts"] renders only the marts subtree — drop it to render the whole project. DbtDag produces a complete Airflow DAG whose graph is the dbt DAG: each TPCH model becomes a discrete task — stg_orders, stg_lineitem, revenue_by_region — with per-model retries and logs, and each dbt test renders as its own task downstream of the model it guards, so a failing not_null is a red square you click into rather than a line buried in a 400-line log. Because every task’s SQL runs inside the Snowflake warehouse named in the connection, compute cost tracks the work, not the wall clock of the DAG.

Per-model warehouse overrides survive the render

Here’s the first thing that’s genuinely Snowflake-specific and easy to get wrong. Snowflake bills by warehouse, and not every model deserves the same size. Your thin staging models are cheap single-scan renames; a wide fan-out mart that joins orders to lineitem across a year is the model that wants an XL for ninety seconds and an XS the rest of the time. dbt-snowflake already knows how to express this — the snowflake_warehouse config overrides the profile’s warehouse for a single model:

-- models/marts/revenue_by_region.sql
{{ config(
    materialized='table',
    snowflake_warehouse='TRANSFORMING_XL'
) }}

select ...

Or in bulk, per folder, in dbt_project.yml:

models:
  tpch:
    marts:
      +snowflake_warehouse: TRANSFORMING_XL
    staging:
      +snowflake_warehouse: TRANSFORMING_XS

The question that matters for this post is: does that override survive Cosmos rendering the model as an Airflow task? It does — and the reason is worth internalizing. Cosmos doesn’t reach into your SQL and reinterpret it; it renders a task that runs dbt run --select revenue_by_region, and dbt itself applies the config() block, issuing a use warehouse TRANSFORMING_XL before the model and resizing back after. The warehouse switch is dbt’s behavior, and Cosmos preserves it precisely because it delegates rather than reimplements. Set the warehouse in dbt config, and every model bills to the right compute with no Airflow wiring at all. This is the cleanest division of labor in the stack: dbt owns which warehouse, Airflow owns when and whether, and neither has to know the other’s job.

When you do want Airflow to have a say per node, operator_args and dbt_cmd_flags are the two hooks. dbt_cmd_flags appends flags to the underlying dbt command for every task Cosmos renders — dbt_cmd_flags=["--no-partial-parse"] to force a clean parse, say. operator_args sets Airflow operator attributes on the rendered tasks — retries, pools, execution timeouts — which we’ll use in a moment. The rule of thumb: warehouse and materialization are dbt’s job (config), scheduling and resource governance are Airflow’s job (operator_args), and you’ll almost never need to cross the streams.

Choosing where dbt runs

ExecutionMode decides where the dbt process lives, and the default — LOCAL — is both the right first choice and a specific liability worth naming. Under LOCAL, Cosmos shells out to the dbt on the worker (or the dbt_executable_path you named), sharing the worker’s Python interpreter. Zero extra moving parts, lowest latency, all your logs in one place. The catch is the shared interpreter: dbt-snowflake pins its own transitive dependencies — snowflake-connector-python, cryptography, protobuf — and so does the Airflow Snowflake provider you installed in post 3. When those pins disagree, one of them loses, and you find out at astro dev restart as a pip resolution error or, worse, a silent downgrade that breaks an operator you weren’t touching. dbt-snowflake and apache-airflow-providers-snowflake both lean on the Snowflake connector, so this collision is more likely here than it was on, say, Postgres.

The fix is to stop sharing the interpreter. ExecutionMode.VIRTUALENV has Cosmos build a private virtualenv, pip-install a pinned dbt-snowflake into it from a py_requirements list, and run dbt there — same worker, separate dependency tree, no image to build. ExecutionMode.KUBERNETES (and DOCKER, and the cloud variants) run each dbt task in its own container, which buys you per-model compute sizing and a hard security boundary at the cost of building an image and arranging for the Snowflake credentials to cross into the container as env vars. The full menu, the parse-cost implications, and how the project and credentials reach a container that isn’t the worker are the entire subject of the execution-modes chapter in the sibling Airflow + dbt series — none of that theory changes on Snowflake, so I won’t re-derive it here. The Snowflake-concrete takeaway is narrow: because dbt-snowflake shares the connector library with the Airflow provider, VIRTUALENV is a more common necessity on this stack than on others, and reaching for it the day you see a snowflake-connector-python version fight is not over-engineering — it’s the intended fix.

How Cosmos reads your project

Before Cosmos can render a single task it has to answer “what nodes are in this project?”, and when it answers costs real time. This happens at DAG parse — every few seconds, on the scheduler — so the method matters. RenderConfig.load_method picks it:

from cosmos.constants import LoadMode

render_config = RenderConfig(
    load_method=LoadMode.DBT_MANIFEST,
    manifest_path="/opt/airflow/dbt/tpch/target/manifest.json",
    select=["path:models/marts"],
)

LoadMode.DBT_LS shells out to dbt ls at parse time — the most accurate reading, because it’s what dbt itself sees, and the most expensive, because dbt ls compiles the project against the profile on every parse. On Snowflake that has a second sting the Postgres examples don’t: dbt ls needs to reach the warehouse to compile, so your scheduler now authenticates to Snowflake every few seconds just to answer “what models exist?” — burning a connection, and occasionally a few credits, for pure metadata. LoadMode.DBT_MANIFEST reads a pre-built manifest.json from target/ instead: no subprocess, no compile, no Snowflake round-trip at parse time, just JSON. The tradeoff is freshness — the manifest is a snapshot, so if someone adds a model and the manifest isn’t regenerated, Cosmos renders yesterday’s graph. The one-line rule the internals chapter argues at length: development can afford DBT_LS; production should run on DBT_MANIFEST.

Which raises the operational question manifest mode always raises — where does manifest.json come from on deploy? The wrong answer is “the scheduler builds it,” which is just DBT_LS wearing a manifest’s clothes. The right answer is CI: dbt compile --target prod runs in your build pipeline, produces the manifest as a build artifact, and ships it into the image alongside the DAG. The manifest and the code that produced it travel together, so a DAG never points at a manifest from a different commit. partial_parse=True is the complementary speed-up — it keeps dbt’s own partial_parse.msgpack between invocations so each dbt call re-parses only changed files — but note it only helps on filesystems that persist between runs, which rules it out of the fresh-filesystem container modes unless you mount target/.

Two more RenderConfig knobs earn their place on Snowflake. dbt_deps=True (equivalently install_deps on some paths) runs dbt deps so your packages.yml dependencies — dbt_utils, and the dbt_snowflake_query_tags package if you’re tagging warehouse queries for cost attribution — are installed before rendering; without it, a ref into a package macro renders a broken task. And test_behavior controls how the tests you saw earlier attach: the default TestBehavior.AFTER_EACH puts each test as its own task right after its model (the granular, click-into-the-red-square behavior), while TestBehavior.AFTER_ALL collapses all tests into a single task at the end, and TestBehavior.NONE skips them. On Snowflake, AFTER_EACH is usually what you want, because a failed test halts that model’s downstream branch before it spends warehouse credits building marts on data you already know is wrong.

Worth knowing how Cosmos maps the rest of a dbt project, because it’s not only models and tests: a source with a freshness block renders as a source-freshness check task, a snapshot becomes a dbt snapshot task, and a seed becomes a dbt seed task — each a real Airflow task on the same Snowflake connection. select and exclude on RenderConfig take dbt’s selector grammar for all of them, so exclude=["tag:hourly"] on a daily DAG or select=["path:models/marts", "path:models/staging"] scopes exactly what renders, tags and paths and all. On TPCH that mapping matters more than it looks: the raw orders and lineitem tables are declared as a dbt source, so a freshness check on them renders as an upstream task that fails the run before Snowflake spends a credit on staging models built from stale data — the source node isn’t just documentation, it’s a gate Cosmos turns into a real task.

The selector also decides how much of Snowflake a single DAG touches. A common split on this stack is a fast staging DAG on a small warehouse (select=["path:models/staging"]) and a heavier marts DAG on a larger one (select=["path:models/marts"]), each with its own schedule — two DbtTaskGroups reading the same manifest and the same connection, differing only in their selector and the pool they run in. Because the selector is dbt’s own grammar, nothing about that split is Cosmos-specific; it’s the same --select you’d type by hand, which is the point — the graph Airflow renders is always exactly the graph dbt would build.

operator_args, env_vars, and living inside a bigger DAG

operator_args is where Airflow’s governance lands on every rendered task. Retries, a Snowflake-specific pool that caps how many models hit the warehouse at once, an execution timeout that kills a runaway query before it bills an hour of XL:

from datetime import timedelta
from cosmos import DbtTaskGroup

render_config = RenderConfig(
    load_method=LoadMode.DBT_MANIFEST,
    manifest_path="/opt/airflow/dbt/tpch/target/manifest.json",
    dbt_deps=True,
)

tpch_tg = DbtTaskGroup(
    group_id="dbt_tpch",
    project_config=project_config,
    profile_config=profile_config,
    execution_config=execution_config,
    render_config=render_config,
    operator_args={
        "retries": 2,
        "pool": "snowflake_transforming",
        "execution_timeout": timedelta(minutes=20),
    },
    env_vars={"DBT_ENV": "prod"},
)

env_vars passes environment variables into the dbt process — useful for anything your dbt_project.yml reads via env_var(), like an env_var('DBT_ENV') that switches the target schema between prod and staging. And the swap from DbtDag to DbtTaskGroup in that snippet is the move the whole rest of this series depends on: a DbtTaskGroup is the same four configs, but it renders as a task group you drop inside a DAG you own, so a non-dbt Snowflake step can bracket it:

from airflow.sdk import dag, task
from airflow.providers.common.sql.operators.sql import SQLExecuteQueryOperator

@dag(schedule="@daily", start_date=datetime(2026, 6, 1), catchup=False)
def tpch_pipeline():
    resize_up = SQLExecuteQueryOperator(
        task_id="warehouse_xl",
        conn_id="snowflake_default",
        sql="alter warehouse transforming set warehouse_size = 'XLARGE'",
    )

    transform = DbtTaskGroup(
        group_id="dbt_tpch",
        project_config=project_config,
        profile_config=profile_config,
        execution_config=execution_config,
        render_config=render_config,
        operator_args={"retries": 2, "pool": "snowflake_transforming"},
    )

    resize_down = SQLExecuteQueryOperator(
        task_id="warehouse_xs",
        conn_id="snowflake_default",
        sql="alter warehouse transforming set warehouse_size = 'XSMALL'",
    )

    resize_up >> transform >> resize_down

tpch_pipeline()

Now the dbt models sit between two plain Snowflake operators on the same connection — resize the warehouse up before the heavy transform, run the models, shrink it back down — and the whole thing is one DAG with one dependency graph. That’s exactly the shape the next post fills in with real SQL.

Pinning Cosmos for Airflow 3.x

One version note that will save you an afternoon. This series targets Airflow 3.3, which moved DAG-authoring imports to the Task SDK — from airflow.sdk import dag, task — and Cosmos only grew clean 3.x support in its 1.8+ line. Pin it explicitly in requirements.txt (astronomer-cosmos>=1.8) rather than floating, because a Cosmos built against Airflow 2’s operator base classes will import against 3.3 and then fail at render with attribute errors that look like your DAG’s fault. Pin Cosmos, pin dbt-snowflake, and pin the Airflow Snowflake provider together — the three of them share the Snowflake connector, and a coordinated bump is the difference between a boring upgrade and the dependency fight that sent you to VIRTUALENV in the first place.

When it breaks: three render-time failures

Cosmos does its work at DAG parse, which means its failures show up in the scheduler, not the task run — and they read like different problems than they are. Three you’ll actually hit:

Stale manifest. You added revenue_by_customer.sql, deployed, and the model never appears in the grid. Nothing errored; Cosmos is on DBT_MANIFEST and rendered a manifest.json that predates your model. The fix is upstream of Cosmos: regenerate the manifest in CI and ship it with the code. If you find yourself hand-editing manifests to force a model in, you’ve inverted the pipeline — the manifest is a build output, not a source file.

Version mismatch. The DAG import fails with an AttributeError deep in Cosmos or a ModuleNotFoundError for an operator path, and your DAG code looks fine. This is almost always the Airflow-3.3-versus-Cosmos-line problem above, or a dbt-snowflake that pip resolved to a version incompatible with the connector the provider wants. Read the import-time traceback in the scheduler log, not the task log — there is no task log, because rendering never got far enough to make tasks.

Connection not found at render. SnowflakeEncryptedPrivateKeyFilePemProfileMapping(conn_id="snowflake_default") throws at parse time if snowflake_default doesn’t exist in this environment — and the classic trap is CI, where the connection you created locally isn’t defined. Under DBT_LS the failure is loud and immediate because Cosmos tries to build the profile to run dbt ls; under DBT_MANIFEST it can stay hidden until a task actually runs, since parsing a manifest doesn’t need the connection. Either way the fix is to make the connection exist everywhere the DAG parses — via a secrets backend or an AIRFLOW_CONN_SNOWFLAKE_DEFAULT environment variable in CI — and to remember that a mapping named for a connection is a promise that the connection is present, checked when Airflow reads the file.

Final thoughts

The reason this is worth doing isn’t that the grid looks nicer, though it does. It’s that credentials and dependency graph both now have exactly one source of truth. The private key lives in the Airflow connection and nowhere else; the dependency order lives in dbt’s ref() calls and nowhere else; which warehouse each model bills to lives in dbt config and rides through Cosmos untouched. Cosmos is the thin adapter that lets those facts stay single without either tool having to know the other exists — it delegates to dbt for everything dbt already does, and adds only the Airflow-shaped layer around it: retries, pools, a task per node, and a place for the non-dbt Snowflake steps to sit. Get the profile mapping, the load method, and the version pins right once, and every model you write from here is automatically a retryable, observable, correctly-billed Snowflake task the moment you save the file — which is the property that makes the next post, an actual pipeline, boring to run and easy to trust.

Next: Building the TPCH Pipeline

Comments