Course contents

The Analytics Engineer’s Field GuideAirflow 3: running it for a teamStep 6: Running dbt with Cosmos

Running dbt with Cosmos

Part 3 · Airflow · step 6

Running dbt with Cosmos: one Airflow task per model

Don't hand-write one Airflow task per model. Cosmos generates the whole DAG from dbt.

· · ·

You could write an Airflow task for every dbt model by hand, but that breaks the moment Alice adds a model, and it throws away everything dbt already knows about order and tests. Cosmos (the astronomer-cosmos package) reads your dbt project and builds the Airflow DAG for you: one task per model, in the same order as your ref() graph, with tests wired in.

The orchestration problem (before the tool)

Start with the need. Thomas wants Airflow to run Alice's dbt project every morning. The obvious way is one task that shells out to dbt run. It works, and it's a black box:

One dbt run task
One model fails, all failThe task is red. Which of the 80 models broke? Open the logs and read.
Retry means rerun everythingNo way to re-run just the one model that failed.
The DAG drifts from dbtAirflow has no idea what models exist. Add one and nothing here changes.
Tests are buriedA failed test is one more line in a wall of output.
A task per model
You see the one that brokeThe failed model is a red box in the graph.
Retry just that modelRe-run one node, leave the rest.
Stays in sync with dbtThe graph is generated from the project.
Tests are their own tasksA failure stops only its branch.

That's the problem Cosmos solves: it turns the opaque single task into a graph where every model is a first-class Airflow task. One picture says it, your dbt folder goes in, an Airflow DAG with the same shape comes out:

dbt/ folder
  • model_hotel_1.sql
  • model_hotel_2.sql
  • model_booking_1.sql
  • model_booking_2.sql
  • + their ref() calls
  • + tests in schema.yml
reads
COSMOS① reads the ref() graph② writes the Airflow DAG
writes

Airflow DAG

model_hotel_1run ✓ → test ✓
model_hotel_2run ✓ → test ✓
model_booking_1run ✓ → test ✓
model_booking_2run ✓ → test ✓

every model = its run, then its own data-quality tests

Look at the small print inside each task: run, then test. Data quality is baked into the graph itself, every model runs and is immediately checked by its own schema.yml tests before its children may start. Everything below is how.

One production note
Cosmos reads your dbt project every time Airflow parses the DAG file. By default it runs dbt ls and caches the result, which is fine for most projects. Past a few hundred models, have CI build manifest.json and point Cosmos at it: ProjectConfig(manifest_path=...) plus RenderConfig(load_method=LoadMode.DBT_MANIFEST), no dbt call at parse time.

The whole DAG in a few lines

# dags/analytics__ismafrost__dbt.py
from cosmos import DbtDag, ProjectConfig, ProfileConfig
from cosmos.profiles import SnowflakePrivateKeyPemProfileMapping
from datetime import datetime

profile_config = ProfileConfig(           # the pro profile, built at run time
    profile_name="my_project",
    target_name="pro",
    profile_mapping=SnowflakePrivateKeyPemProfileMapping(conn_id="snowflake_pro"),
)

dbt_dag = DbtDag(
    project_config=ProjectConfig("/usr/local/airflow/dbt/my_project"),
    profile_config=profile_config,
    schedule="0 6 * * *",                  # every day at 06:00
    start_date=datetime(2024, 1, 1),
    dag_id="analytics__ismafrost__dbt",   # team__domain__type, step 8
    tags=["owner:analytics", "domain:ismafrost", "criticality:high"],
)

This is where the pro target from part 2 actually lives. ProfileConfig turns the snowflake_pro connection (the platform's service account and its key) into a dbt profile at run time, so there's no profiles.yml for pro anywhere: not in the repo, not on a laptop. A pre-prod DAG uses the same code with its own connection and target_name="pre_prod".

Every model, then its data quality tests

A generated model isn't "run the whole project". It's a small task group of two scoped tasks, and the second one is the part that matters most: build the model, then immediately run the data quality tests attached to it. Those are the tests Alice wrote in part 2, unique, not_null, accepted_values, relationships, sitting in the YAML next to the model.

# what Cosmos actually executes for the stg_orders group
dbt run  --select stg_orders     # 1. build the table
dbt test --select stg_orders     # 2. its data quality tests, right away
#    both green → children may start
#    test red   → the test task fails, everything downstream is held

That's test_behavior=TestBehavior.AFTER_EACH, the default and the one you want. The alternative, AFTER_ALL, builds the entire project first and tests at the end, which means bad rows have already flowed into every mart before anyone finds out.

Concretely, for the pizzeria's staging layer, that second command runs everything Alice declared in YAML:

# models/staging/_models.yml: what dbt test --select stg_orders executes
  - name: stg_orders
    columns:
      - name: order_id
        data_tests: [unique, not_null]      # grain intact?
      - name: channel
        data_tests:
          - accepted_values:
              arguments:
                values: [dine_in, delivery]
      - name: customer_id
        data_tests:
          - relationships:
              arguments:
                to: ref('stg_customers')
                field: customer_id

Four checks, run on the rows that were just built, before a single downstream model starts. It's the same safety net as part 2, except now it fires every morning at six instead of when someone remembers.

So a duplicate order id doesn't just get reported, it stops the branch it poisons:

stg_orders ✗run ✓ · test ✗ (dupes found)
fct_ordersheld: upstream failed
stg_customers ✓run ✓ · test ✓
dim_customers ✓its branch was clean
export ✓customers ship on time
a failure stops only its own branch · retry just stg_orders from the UI, the rest is already green

Compare that with the single dbt run task: one duplicate order id there would have failed the entire morning for every team. Here, the customers branch shipped on time, the orders branch is one click to retry once the dupe is fixed.

Render only part of your project: select with RenderConfig

By default a DbtDag turns your whole project into tasks. Often you want a DAG for just one area, one mart and everything it needs. Pass a RenderConfig with select (or exclude), using dbt's own graph selectors.

from cosmos import DbtDag, ProjectConfig, RenderConfig

finance_dag = DbtDag(
    project_config=ProjectConfig("/usr/local/airflow/dbt/my_project"),
    profile_config=profile_config,
    render_config=RenderConfig(
        select=["+fct_orders"],   # fct_orders and everything it depends on
    ),
    schedule="0 6 * * *",
    dag_id="finance__orders__dbt",
    tags=["owner:finance", "domain:orders", "criticality:high"],
)

The + is dbt's graph operator, and the side it sits on is the whole trick:

+fct_ordersraw.orders→stg_orders→fct_orders# the model + its parents
fct_orders+fct_orders→revenue_report→dashboard# the model + its children

Put + on the left for upstream (everything the model needs), on the right for downstream (everything that needs the model), or both sides for the full neighbourhood: +fct_orders+.

Same selectors on the CLI
These are dbt's selectors, not a Cosmos invention. dbt run --select +fct_orders on your laptop builds the exact set Cosmos renders into the DAG. You can also select by "tag:finance" or "path:models/marts", or flip it with exclude=[...]. Learn them once, use them both places.

Run a single dbt command: the Local operators

Sometimes you don't want a task per model, you want one Airflow task that runs dbt run and nothing more. Cosmos ships an operator per dbt command. In local execution mode (the simplest one, where dbt is installed in the same environment as Airflow) those are DbtRunLocalOperator, DbtTestLocalOperator, DbtSeedLocalOperator and the rest.

from cosmos.operators.local import DbtRunLocalOperator

run_marts = DbtRunLocalOperator(
    task_id="run_marts",
    project_dir="/usr/local/airflow/dbt/my_project",
    profile_config=profile_config,
    select="+fct_orders",    # the same selectors work here too
)

Reach for a local operator when you want one clean step inside a bigger DAG (load → this single dbt run → refresh a dashboard). Reach for DbtDag when you want a task per model and full per-model visibility. Same engine underneath, you're just choosing the granularity.

Installing it
Add astronomer-cosmos to your Astro project's requirements.txt, then astro dev restart. Put the dbt project in a dbt/ folder at the project root (it lands in /usr/local/airflow/dbt/, the layout the multi-team lesson uses) and point ProjectConfig at it. Cosmos recommends installing dbt itself in a separate virtualenv in the Dockerfile, so its dependencies never fight Airflow's; you then pass its path as dbt_executable_path (through ExecutionConfig on a DbtDag, directly on an operator, as the canary in step 5 does).

The full picture: SQL to scheduled DAG

This is the whole stack, end to end: Alice writes SQL in dbt, Cosmos turns the project into an Airflow DAG, and Astronomer runs that DAG every morning, retries failures and alerts on breakage. Nobody babysits a pipeline again.

dbt project
Alice's models
→
Cosmos
builds the DAG
→
Airflow
on Astronomer
→
Fresh tables
every morning
One team, solved
From "five people, five answers" to a stack with one clear path: Snowflake and dbt to transform and test, your own userland to build safely, and Airflow on Astronomer (with Cosmos) to run it all on schedule.

Lesson quiz

  1. What does Cosmos generate from your dbt project?

    1. A dbt project rebuilt from your existing Airflow DAGs
    2. One Airflow task that runs dbt build on the whole project
    3. A schedule file that dbt itself runs every morning
    4. An Airflow DAG with a task per model, in ref() order
  2. With test_behavior=AFTER_EACH, stg_orders' unique test fails. What happens to the rest of the DAG?

    1. Models downstream of stg_orders stop; independent branches carry on
    2. The failure is logged, and downstream models still run as normal
    3. Cosmos rebuilds stg_orders in a loop until the unique test passes
    4. The whole DAG stops, and every other model is skipped for the day
  3. Your Cosmos DAG uses the default TestBehavior.AFTER_EACH. At 06:20 the unique test on stg_orders fails. What happens to fct_orders, downstream of it, and to dim_customers, on a separate branch?

    1. fct_orders isn't built; dim_customers finishes as usual
    2. The whole DAG stops, dim_customers included
    3. Both build, and the failure shows up in a final test step
    4. fct_orders builds on yesterday's stg_orders rows