← writing
14 November 2023

dbt under Airflow: one task per model, not one per project

A dbt project launched from an orchestrator usually sits in a single task. The graph displayed then does not match the project, and a retry after an error reruns everything. What changes when the graph is handed to the orchestrator, and what it costs.

The usual way to run dbt from Airflow fits on one line: a bash operator, the dbt run command, and the whole project in one task. It works, but with three drawbacks that only show up during an incident. A retry reruns the entire project when only one model failed. The dependency graph dbt knows about stays invisible to the orchestrator, which shows a single box where there are two hundred nodes. And the logs arrive as one block that has to be searched.

Cosmos, published by Astronomer, reads the dbt project and generates Airflow tasks from it: one per model, with the dependencies dbt has already worked out. What the orchestrator can see, it can retry, run in parallel and report on in its logs.

01The graph handed back to the orchestrator

Three configuration objects are enough, and the DAG is derived from the project. `ProjectConfig` says where the project is, `ProfileConfig` how to connect, `ExecutionConfig` which dbt executable to call. That last one matters more than it seems, because dbt and Airflow rarely have compatible dependencies.

python
from datetime import datetime

from cosmos import DbtDag, ExecutionConfig, ProfileConfig, ProjectConfig
from cosmos.profiles import AthenaAccessKeyProfileMapping

profile = ProfileConfig(
    profile_name="warehouse",
    target_name="prod",
    # The dbt profile is built from an Airflow connection of type aws:
    # no profiles.yml to deploy, no secret in the repository.
    profile_mapping=AthenaAccessKeyProfileMapping(
        conn_id="aws_warehouse",
        profile_args={"schema": "marts"},
    ),
)

dag = DbtDag(
    dag_id="warehouse_dbt",
    project_config=ProjectConfig("/usr/local/airflow/dags/dbt/warehouse"),
    profile_config=profile,
    # dbt in its own virtual environment: its dependencies and Airflow's do not
    # hold together, and that is the trap that costs you the day.
    execution_config=ExecutionConfig(
        dbt_executable_path="/usr/local/airflow/dbt_venv/bin/dbt",
    ),
    schedule_interval="0 5 * * *",
    start_date=datetime(2023, 11, 1),
    catchup=False,
    default_args={"retries": 2},
)

Usually the dbt project is not on its own: it follows an ingestion and precedes a publication. `DbtTaskGroup` then produces the same graph inside an existing DAG, and the dependencies are declared as between any two ordinary tasks.

python
from airflow.decorators import dag, task
from cosmos import DbtTaskGroup, ProjectConfig

@dag(schedule="0 5 * * *", start_date=datetime(2023, 11, 1), catchup=False)
def warehouse():
    @task
    def ingest() -> None:
        ...

    transform = DbtTaskGroup(
        group_id="transform",
        project_config=ProjectConfig("/usr/local/airflow/dags/dbt/warehouse"),
        profile_config=profile,
    )

    @task
    def publish() -> None:
        ...

    ingest() >> transform >> publish()

warehouse()
02The profile, where Athena complicates matters

Athena differs from a conventional database: AWS provides the compute, the data is in S3, and a write location is needed for intermediate results. A dbt-athena profile therefore has more fields than a Postgres one, and two of them, the staging directory and the workgroup, have no equivalent elsewhere.

The Cosmos mapping reads these fields from the `extra` block of an Airflow connection of type `aws`, and obtains credentials through the Amazon provider’s usual mechanism. An IAM role therefore works as well as a key, and is preferable.

json
{
  "region_name": "eu-west-1",
  "database": "awsdatacatalog",
  "schema": "marts",
  "s3_staging_dir": "s3://warehouse-athena/results/",
  "work_group": "dbt"
}

Two remarks from experience. First, giving dbt its own Athena workgroup, as above, isolates its usage and allows a limit on bytes scanned. Without that limit, a badly written join is only discovered on the invoice. Second, the adapter installs as `dbt-athena-community`, not `dbt-athena`, a confusion that costs almost everyone half an hour.

03What it costs

The graph has to be known when Airflow parses the file, which is every thirty seconds by default. By default, Cosmos calls dbt at every parse to discover the models, and that is the worst configuration: the scheduler slows down as the project grows. The solution is to produce the manifest when the image is built and give it to Cosmos, which then no longer has to recompute it.

The second cost is less visible: two hundred tasks instead of one means two hundred rows in the metadata database on every run, and a dashboard that can no longer be read at a glance. On a twenty-model project the gain is clear. Beyond a few hundred models, tasks need grouping by folder rather than by model, which Cosmos supports.

04Kubernetes, dbt tests, and the date of this code

I have not tested this setup on Kubernetes, where each task runs in its own container and the trade-off changes: the detailed graph then costs one container start per model, and the benefit of fine-grained retries can disappear. Nor do I cover dbt tests exposed as separate tasks, which Cosmos allows. I am wary of them, because a failing test should stop the pipeline, and making it an independent task weakens that rule.

One last remark, dated. The Athena mapping is a month old as I write: it arrived in version 1.2.0 on 13 October 2023, and five releases followed in six weeks. The project is still changing quickly, and the code above describes its state at that date.