Orchestration & Transformation
Airflow and the DAG, dbt and transformation as version-controlled SQL, and what to do when a task fails at 3am.
Something Has To Decide What Runs
Cron gets you scheduling and nothing else: no dependencies, no retries, no history, no visibility into why last Tuesday produced nothing. An orchestrator adds all four, and it is the piece that turns a set of scripts into a system somebody can operate.
Airflow & DAGs
Airflow models a workflow as a directed acyclic graph: tasks are nodes, dependencies are edges, and the scheduler runs each task once its upstreams have succeeded for that logical date.
with DAG(
"daily_revenue",
schedule="0 3 * * *",
start_date=datetime(2026, 1, 1),
catchup=True, # backfill missed runs deliberately
default_args={"retries": 2, "retry_delay": timedelta(minutes=10)},
) as dag:
extract = PythonOperator(task_id="extract_orders", python_callable=extract_orders)
load = PythonOperator(task_id="load_raw", python_callable=load_raw)
mart = BashOperator(task_id="build_mart", bash_command="dbt run --select revenue+")
extract >> load >> mart # the dependency, stated once| Concept | Meaning |
|---|---|
| Logical date | The window a run is for — not when it executed |
| Catchup | Whether missed intervals are run on deploy |
| Retries | Automatic, with backoff, before anyone is woken |
| Sensor | A task that waits for a file, partition or upstream |
| SLA / timeout | Alert when a task is late, not only when it fails |
now() in a transform: the DAG works today and produces nonsense the moment anyone backfills.dbt & Transformation
dbt takes the transformation half and makes it ordinary software: models are SELECT statements in version control, dependencies are inferred from references, and the whole graph is testable and documented.
-- models/marts/revenue_daily.sql
{{ config(materialized='incremental', unique_key='date_day') }}
SELECT
date_trunc('day', o.settled_at) AS date_day,
o.store_id,
SUM(o.net_amount) AS revenue
FROM {{ ref('stg_orders') }} o -- ref() builds the dependency graph
{% if is_incremental() %}
WHERE o.settled_at >= (SELECT MAX(date_day) FROM {{ this }})
{% endif %}
GROUP BY 1, 2| What it brings | Instead of |
|---|---|
| SQL in git, reviewed in pull requests | Queries pasted into a scheduler |
A dependency graph from ref() | A hand-maintained run order |
| Tests as configuration | Hoping the numbers are right |
| Generated documentation and lineage | A stale wiki page |
| Incremental models | Rebuilding a billion rows nightly |
The division of labour that has settled: the orchestrator moves and schedules, and dbt transforms. Airflow triggers dbt run; it does not reimplement what dbt already does.
When It Fails At 3am
Pipelines fail on a schedule you did not choose, so the design question is what the failure does to everything downstream.
| Practice | Why |
|---|---|
| Retry transient errors automatically | Most failures are a timeout, not a bug |
| Fail fast on bad data | Better a missing table than a wrong number in a board pack |
| Alert on lateness, not only errors | A job that never started raises nothing |
| Make every task idempotent | So "clear and rerun" is a safe first response |
| Publish freshness | Consumers should see how old the data is |
Interview Questions
Why not just use cron?
Cron schedules and nothing more. An orchestrator adds dependencies, retries with backoff, run history, backfills and visibility into why a run produced nothing.
What is the logical date in Airflow?
The data window a run is for, as opposed to when it executed. Every task should use it, otherwise backfills recompute old windows with today's date.
What does dbt actually give you?
Transformation as version-controlled SQL: models in git, a dependency graph inferred from ref(), tests as configuration, generated lineage docs, and incremental models.
How do orchestration and dbt divide the work?
The orchestrator schedules and moves data and triggers dbt; dbt owns the transformation graph inside the warehouse. Neither reimplements the other.
Why alert on lateness as well as failure?
A job that never started emits no error. An SLA or freshness alert catches the silent case, which is the one that reaches a dashboard unnoticed.
What is a silent success?
A run that completes green having written nothing or far too little. Asserting row counts and freshness inside the run turns it into an ordinary failure.