Building Your First Data Pipeline with Airflow + dbt (2026)

The reference architecture: extract, load, orchestrate, transform

A modern pipeline separates four concerns, and conflating them is the root of most pain:

1. Extract + Load (EL) — getting raw data from sources (APIs, databases, SaaS tools, files) into your warehouse, untransformed. Use a managed connector (Fivetran, Airbyte) or a purpose-built ingestion job. The key principle: land raw data first, transform later (ELT, not ETL). Raw landing tables are your replayable source of truth.

2. Transform (T) — turning raw landing tables into clean, modeled, business-ready tables. This is dbt's job: SQL SELECT statements that dbt materializes as tables/views, with tests, documentation, and dependency tracking.

3. Orchestrate — deciding what runs when, in what order, with what dependencies, and what happens on failure. This is Airflow's job. Airflow should not contain business logic — it triggers the EL jobs and the dbt run, and manages retries/alerting.

4. Observe — knowing the pipeline ran, finished, and produced correct data. Freshness checks, row-count anomaly detection, alerting.

The canonical flow: Airflow triggers ingestion (or ingestion runs on its own schedule and Airflow waits on a sensor) → raw data lands in the warehouse → Airflow triggers dbt build → dbt transforms + tests → observability checks confirm freshness → alert on any failure. Keep each layer doing one thing.

Setting up Airflow the right way

Whether you self-host (Docker Compose for dev, Kubernetes/Helm for prod) or use a managed service (AWS MWAA, Google Cloud Composer, Astronomer), the same hygiene rules apply:

Use the TaskFlow API + decorators, not the legacy operator soup. Modern Airflow (2.x+) lets you write DAGs with @dag and @task decorators — cleaner, easier to test, and Python-native.

One DAG per logical pipeline, not one DAG for everything. A DAG should represent a coherent unit (e.g., 'ingest + transform marketing data'). Giant monolithic DAGs are impossible to reason about.

Never put secrets in DAG code. Use Airflow Connections + Variables, or better, a secrets backend (AWS Secrets Manager, GCP Secret Manager, HashiCorp Vault).

Set sane defaults: retries=2, retry_delay=timedelta(minutes=5), execution_timeout, and crucially catchup=False unless you genuinely want backfill on deploy (a classic footgun that triggers hundreds of historical runs).

Make tasks idempotent. A task that re-runs should produce the same result, not duplicate data. This is non-negotiable for retries to be safe.

Resource isolation: run heavy jobs on KubernetesExecutor or a dedicated worker pool so a runaway task doesn't starve the scheduler. For dbt specifically, the dbt run happens in the warehouse — Airflow just orchestrates — so the Airflow worker stays light.

Structuring your dbt project

dbt is where your transformation logic lives. A clean project uses three layers:

Staging (stg_) — one model per raw source table, doing light cleanup only: renaming columns to a consistent convention, casting types, basic deduplication. One-to-one with sources. Materialized as views (cheap, always fresh).

Intermediate (int_) — the workhorse layer where you join, aggregate, and apply business logic that's too complex for a single mart model. Not exposed to end users.

Marts (fct_, dim_) — business-ready tables organized by domain (finance, marketing, product). These are what BI tools and analysts query. Materialized as tables or incremental models.

Essential practices:

Declare your sources in sources.yml with freshness checks — dbt can warn/error if raw data is stale.

Test everything that matters: unique and not_null on primary keys, relationships for foreign keys, accepted_values for enums. Add dbt-expectations for distribution/range tests on critical columns.

Use ref() and source() exclusively — never hardcode table names. This is what gives dbt its dependency graph and lets it build models in the correct order.

Incremental models for large fact tables — don't rebuild a billion-row table every run; process only new/changed rows with an is_incremental() block.

Document as you go — descriptions in YAML generate a lineage-aware docs site your whole team can browse.

Orchestrating dbt from Airflow

Here's where most first pipelines go wrong. The naive approach is a single BashOperator running dbt build. It works, but you lose per-model visibility — when one model fails, Airflow shows one red box and you have to dig through logs.

The better pattern: Cosmos. Astronomer Cosmos parses your dbt project and renders each dbt model as a native Airflow task, with the real dependency graph. You get per-model retries, per-model logs, and a visual DAG that mirrors your dbt lineage. This is our default recommendation for new projects.

The middle-ground pattern: split dbt into a few logical dbt run --select commands by domain (staging, then marts) as separate Airflow tasks. Less granular than Cosmos but better than one monolithic command, and zero extra dependencies.

Whichever you choose, always run dbt test (or dbt build, which runs + tests together). A pipeline that loads data without testing it is a pipeline that ships bad data confidently.

Pass the run results forward — capture dbt's run_results.json artifact so observability tooling (e.g., Elementary) can track model timing, test outcomes, and freshness over time.

Trigger order matters: ingestion task(s) → dbt build → freshness/observability check → success. Use Airflow's dependency syntax (extract >> load >> transform >> observe) so a failure upstream cleanly halts downstream and alerts.

Testing, idempotency, and observability

A pipeline isn't done when it runs once — it's done when it runs unattended for months and you trust the output. Three pillars:

Testing (catch bad data before it ships). dbt tests at build time are your first line: schema tests on keys, value tests on critical columns, freshness tests on sources. Add dbt-expectations for statistical assertions (a daily revenue figure should be within a sane range). Fail the pipeline — don't just warn — on tests that protect business-critical metrics.

Idempotency (safe to re-run). Every task must be re-runnable without side effects. For loads, use MERGE/upsert keyed on a stable primary key, not blind INSERT. For incremental dbt models, define a robust unique_key and a is_incremental() filter that correctly handles late-arriving data. Test idempotency explicitly: run the pipeline twice and confirm row counts don't change.

Observability (know what happened). Three signals to monitor: freshness (did data arrive on time?), volume (did roughly the expected number of rows land?), and quality (did tests pass?). Tools: Elementary (free, dbt-native, posts to Slack) for most teams; Monte Carlo or Anomalo for enterprise. At minimum, wire Airflow failure callbacks to Slack/PagerDuty with a runbook link.

The goal: when something breaks at 3am, the on-call engineer gets a precise alert (which model, which test, what the expected-vs-actual was) and a runbook — not a cryptic stack trace.

Productionizing: from works-on-my-laptop to 3am-safe

The gap between a demo pipeline and a production one is where reliability lives. The checklist we apply before any client pipeline goes live:

CI/CD for dbt: PRs trigger dbt build against a CI schema (slim CI with state:modified+ to test only changed models cheaply). Nothing merges to main without passing tests. SQLFluff + dbt-checkpoint as pre-commit hooks keep the codebase consistent.

Environment separation: distinct dev / staging / prod warehouse schemas (or databases). Engineers never develop against prod.

Secrets + access: warehouse credentials in a secrets backend, least-privilege service accounts, no personal credentials in pipelines.

Alerting + on-call: failure notifications to a monitored channel, an escalation path, and a written runbook for the top failure modes (source outage, schema change, warehouse credit exhaustion).

Cost guardrails: especially on Snowflake/BigQuery — auto-suspend warehouses, query timeouts, and a monitoring query that flags expensive runs before the bill does.

Documentation handoff: an architecture diagram, the dbt docs site, the runbook, and the observability dashboard — so the pipeline survives the engineer who built it leaving.

This is exactly the playbook we ship in a fixed-scope pipeline engagement (typically USD 30-50K for a batch pipeline, 4-6 weeks). If you'd rather have a senior team build the first one alongside yours so your engineers learn the patterns, that's how most of our clients start.