Course contents

The Analytics Engineer’s Field GuideAirflow 2: writing DAGsStep 5: Sensors, retries & branches

Sensors, retries & branches

Part 3 · Airflow · step 5

Pipelines that survive the real world

Real data is late, warehouses hiccup, and Mondays are special. This lesson is how a DAG survives all three without waking anyone.

· · ·

Thomas's loader finishes around 6:10 most mornings, so the dbt DAG runs at 7:00, "to be safe". Then one day the loader takes ninety minutes, dbt builds on yesterday's rows, and nobody gets an error, the dashboards are just quietly wrong. A clock offset isn't a dependency, it's a guess. This lesson replaces every guess in the pipeline with a declared behaviour: wait properly, retry properly, branch properly, fail loudly.

Sensors: wait for the data, not for a time

One sentence: a sensor is a task that waits for a condition and only releases its downstream tasks when it's true. Why it exists: "the data has landed" is a fact you can check, so check it instead of hoping 7:00 is late enough. Think of a kitchen: operators cook, the sensor is the timer watching the oven, doing nothing itself, just refusing to let the next step start too early.

⏳
Sensor waits
checks every 5 min
→
Condition true
today's rows are there
→
Run dbt
on fresh data, always
6:10 on fast days, 7:40 on the slow one, correct on both
# wait until today's raw orders have loaded, THEN build
from airflow.providers.common.sql.sensors.sql import SqlSensor

wait_for_orders = SqlSensor(
    task_id="wait_for_raw_orders",
    conn_id="snowflake_pro",
    sql="SELECT count(*) FROM RAW_DB.PUBLIC.orders WHERE loaded_date = current_date",
    mode="reschedule",    # free the worker slot between checks
    poke_interval=300,    # check every 5 minutes
    timeout=3600,        # give up after 1 hour, don't wait forever
)

wait_for_orders >> run_dbt   # dbt only starts once the sensor passes
poke vs reschedule vs deferrable
In poke mode a sensor holds a worker slot the whole time it waits. In reschedule mode it releases the slot between checks. For anything that might wait more than a few minutes, use reschedule, so a waiting task doesn't block the rest of the platform. Sensors that support deferrable=True go one step further and hand the wait to the triggerer, using no worker slot at all. SqlSensor has no deferrable mode, so reschedule is the right setting here.

Assets: when the upstream is your own DAG

A sensor polls, and polling is right when the data comes from something you don't control, like the external loader. When the upstream is another DAG on the same Airflow, polling is a detour: Airflow already knows the moment the producer finished. Airflow 3 calls the thing in between an asset (Airflow 2 said dataset). The producer task declares the asset it updates, the consumer DAG is scheduled on it, and Airflow starts the consumer when the producer task succeeds.

from airflow.sdk import Asset, dag, task

raw_orders = Asset("raw_orders")                # one name both DAGs agree on

@dag(schedule="0 5 * * *")
def load_orders():
    @task(outlets=[raw_orders])                 # success = "raw_orders was updated"
    def load():
        ...
    load()

@dag(schedule=[raw_orders])                     # no clock: runs when the asset updates
def build_orders():
    ...

load_orders()
build_orders()

Three rules to know. Only a successful producer task emits the update, so a failed load never triggers dbt. A consumer listing several assets waits until all of them have been updated since its last run. And the Cosmos DbtDag in the next lesson accepts schedule=[raw_orders] just the same. The UI shows the producer, the asset and the consumer as one graph, so the dependency is visible instead of hidden in two cron strings.

Sensor or asset?
A sensor for data you don't control (the external loader, a file from a partner). An asset for data produced by another DAG you own.
· · ·

Retries and timeouts: absorb the blips

One sentence: a retry re-runs a failed task automatically after a pause, a timeout kills a task that runs too long. Why they exist: many production failures are transient, a dropped connection, a queued warehouse, and a retry turns them into non-events. Others hang forever, and a timeout turns "stuck since 3am" into a clean failure you can alert on.

from datetime import datetime, timedelta

default_args = {
    "retries": 2,                              # 3 attempts total
    "retry_delay": timedelta(minutes=5),       # base pause before a retry
    "retry_exponential_backoff": True,         # each wait roughly doubles, with jitter
    "execution_timeout": timedelta(minutes=30), # no task may run longer
    # (Airflow 2's SLAs for "succeeded but slow" were removed in Airflow 3.0;
    #  Airflow 3.1 added Deadline Alerts to replace them)
}

@dag(schedule="0 6 * * *", start_date=datetime(2024,1,1), catchup=False,
     default_args=default_args)          # every task inherits all of it
1
Try 1 failsSnowflake connection dropped.
2
Wait 5 minState: up_for_retry, nobody alerted.
3
Try 2 succeedsThe blip is gone, run continues.
4
Or try 3 failsNow, and only now, state: failed.
✓ Alerts fire on final failure, never on a blip a retry fixed.

The alert itself is one callback. A task's on_failure_callback runs only once it has used up its retries, which is exactly the "final failure" above. Notifiers from the Slack provider do the work, kept in one shared file:

# include/notifications.py, shared by every DAG (needs apache-airflow-providers-slack)
from airflow.providers.slack.notifications.slack_webhook import SlackWebhookNotifier

# task level: put it in default_args and every task inherits it
task_failure_alert = SlackWebhookNotifier(
    slack_webhook_conn_id="slack_alerts",   # the webhook lives in an Airflow connection
    text="{{ dag.dag_id }} / {{ ti.task_id }} failed for {{ ds }}: {{ ti.log_url }}",
)

# DAG level: one message per run, used by the canary below
dag_status_callback = SlackWebhookNotifier(
    slack_webhook_conn_id="slack_alerts",
    text="{{ dag.dag_id }}: run {{ run_id }} finished as {{ dag_run.state }}",
)

Add "on_failure_callback": task_failure_alert to the default_args above and the whole DAG is covered.

Retries need idempotency
A retry re-runs the task from the top, so the task must be safe to run twice: replace a date partition instead of appending to it. That property has a name, idempotency, and it's what makes retries and backfills boring instead of dangerous.
· · ·

Trigger rules: when should this task run at all?

One sentence: a trigger rule tells a task which parent outcomes let it start. Why it exists: the default, all_success, is right 95% of the time, but "send the summary whatever happened" and "clean up only if something broke" need different answers.

build_orders ✓succeeded
build_customers ✗failed
both parents feed each child
publish · all_successblocked, one parent failed
daily_summary · all_done▶ runs: parents finished, any state
cleanup_partial · one_failed▶ runs: at least one parent failed
same two parents, three different answers to "should I run?"

Same two parents for everyone, build_orders green and build_customers red. The three children differ only by their trigger rule:

  • publish keeps the default all_success: it needs both parents green, one is red, so it stays blocked. Correct: you don't publish half a morning.
  • daily_summary carries all_done: "run once the parents have finished, whatever their state". It runs, and reports one success and one failure.
  • cleanup_partial carries one_failed: "run only if at least one parent broke". Today it runs and clears the half-written staging rows. On a green morning it would simply be skipped.

One word on the task declares it:

cleanup = SQLExecuteQueryOperator(
    task_id="cleanup_partial",
    conn_id="snowflake_pro",
    sql="DELETE FROM staging WHERE load_date = current_date",
    trigger_rule="one_failed",   # only fires when a build broke
)

Branching: pick a path per run

One sentence: a branch task returns the task_id to follow, the other paths are skipped. Why it exists: some decisions belong to the run itself. Production example: full refresh on Monday, cheap incremental the rest of the week.

from zoneinfo import ZoneInfo
from airflow.sdk import task

@task.branch
def pick_mode(**context) -> str:
    # the run's own date, not the wall clock: a re-run of Monday still full-refreshes
    run_day = context["logical_date"].astimezone(ZoneInfo("Europe/Paris"))
    if run_day.weekday() == 0:   # Monday
        return "full_refresh"
    return "incremental"

pick_mode() >> [full_refresh, incremental]
[full_refresh, incremental] >> publish   # publish needs trigger_rule="none_failed"

That last comment is the classic trap: publish has two parents and one of them is always skipped, so with the default all_success it would never run. none_failed means "run as long as nothing actually failed", skipped parents are fine.

Dynamic task mapping: one task, N instances

One sentence: .expand() creates one task instance per element of a list, at run time. Why it exists: "do the same thing per country" shouldn't mean copy-pasting a task eleven times, or worse, looping inside one giant task that hides which country failed. Each mapped instance gets its own box in the UI, its own log, its own retry.

@task
def list_countries() -> list[str]:
    return ["FR", "DE", "ES"]        # could come from a query

@task
def export_country(country: str):
    ...                              # one export per country

export_country.expand(country=list_countries())   # 3 instances today, 12 tomorrow
· · ·

The canary: test every connection, every day

A tiny DAG that fails first, so the business pipelines don't have to.

Keys expire, PATs hit their rotation date, a role gets revoked in a Snowflake cleanup. Without a canary, you discover it when tomorrow's 06:00 build fails, in the middle of fifteen real alerts. The canary is a small DAG that does nothing useful on purpose: once a day, at a quiet hour, it opens every connection the platform owns and posts the result to chat.

canary DAGdaily · 11:30
SELECT 1 · per connectionsnowflake_finance · marketing · core…proves auth works
dbt debug · per connectionprofile · key · role · warehouseproves the whole dbt stack
one messagegreen or red, every day
one task per connection: the UI shows exactly which credential died
# dags/platform__connections__canary.py, the platform's smoke alarm
from datetime import timedelta
from pendulum import datetime, timezone

from airflow.sdk import DAG
from airflow.providers.common.sql.operators.sql import SQLExecuteQueryOperator
from cosmos import ProfileConfig
from cosmos.operators.local import DbtLocalBaseOperator
from cosmos.profiles import SnowflakePrivateKeyPemProfileMapping

from include.notifications import dag_status_callback

class DbtDebugOperator(DbtLocalBaseOperator):
    # Cosmos ships no debug operator; its own DbtDocsLocalOperator is built this way
    base_cmd = ["debug", "--connection"]      # dbt's own connection check

SNOWFLAKE_CONNECTIONS = ["snowflake_finance", "snowflake_marketing", "snowflake_core"]
DBT_CONNECTIONS       = ["snowflake_finance", "snowflake_marketing"]

with DAG(
    dag_id="platform__connections__canary",
    schedule="30 11 * * *",                      # a quiet moment, every day
    start_date=datetime(2024, 5, 16, tz=timezone("Europe/Paris")),
    catchup=False,
    tags=["owner:platform", "domain:connections", "criticality:high"],
    default_args={"owner": "platform", "retries": 1, "retry_delay": timedelta(minutes=5)},
    on_success_callback=dag_status_callback,      # chirps on green too, see below
    on_failure_callback=dag_status_callback,
):
    for conn_id in SNOWFLAKE_CONNECTIONS:         # one tiny task per connection
        SQLExecuteQueryOperator(
            task_id=f"test_{conn_id}",
            conn_id=conn_id,
            sql="SELECT 1",                      # cheapest possible round-trip
        )

    for conn_id in DBT_CONNECTIONS:               # and once through dbt's own stack
        DbtDebugOperator(
            task_id=f"test_dbt_{conn_id}",
            project_dir="/usr/local/airflow/dbt/my_project",
            dbt_executable_path="/usr/local/airflow/dbt_venv/bin/dbt",
            profile_config=ProfileConfig(                 # the pro profile, built from the connection
                profile_name="my_project",
                target_name="pro",
                profile_mapping=SnowflakePrivateKeyPemProfileMapping(conn_id=conn_id),
            ),
        )

Two loops because they prove two different things. SELECT 1 proves the connection authenticates. dbt debug --connection proves the dbt side: Cosmos renders the pro profile from the connection, and dbt opens a session with that key, role and warehouse. A connection can pass the first and fail the second (a field the profile mapping needs is missing, say), and you want to know which.

Two details carry the design. Every connection gets its own task, so the UI shows exactly which credential died instead of one red box named "test_everything". And the success callback fires too: a canary that only sings on death is indistinguishable from a dead canary, so one green message a day proves the alarm itself works.

Thomas's rule
If a schedule contains a time chosen "to be safe", there's a dependency you haven't declared yet. Declare it, a sensor for data you don't own, an asset between your own DAGs, plain >> inside a DAG, and the guess disappears.

Lesson quiz

  1. The loader usually finishes at 6:10, so dbt is scheduled at 7:00 'to be safe'. What's the resilient fix?

    1. Move dbt to 8:00 so the safety margin covers the slow mornings
    2. Schedule a second dbt run at 9:00 to fix any morning that was late
    3. A sensor that checks today's rows have landed, then releases dbt
    4. Add retries to the dbt task in case the data turns up late
  2. Your own DAG ingest_orders writes RAW.orders, and your DAG transform_orders must run once it has. What's the Airflow 3 way?

    1. outlets=[Asset(...)] on the producer task, schedule=[Asset(...)] on the consumer
    2. schedule=[Asset(...)] on the producer DAG, outlets=[Asset(...)] on the consumer
    3. A SqlSensor in transform_orders that polls RAW.orders every five minutes
    4. The same cron on both DAGs, with transform_orders starting ten minutes later
  3. A branch task picks full_refresh or incremental each day, and publish has both as parents with the default trigger rule. What happens to publish?

    1. It runs twice, once after each branch
    2. It fails, since a branch can't have a child
    3. It runs after whichever branch ran
    4. It never runs, since one parent is always skipped