Skip to content
Tutorials Center
My learning

Topics

Lesson 36 of 37 ยท Projects and Practice

Project: Idempotent Backfills and Reruns in Airflow

Learn data intervals, catchup and backfills by building an idempotent partitioned pipeline: compute run dates, rerun safely, avoid duplicates and handle late data.

Sooner or later you need to re-run the past: a bug was fixed, a source changed, or you created a DAG for data that goes back a year. Airflow calls this a backfill. Whether backfills are safe or disastrous depends on one property, idempotency: running the same task for the same interval gives the same result however many times you do it. In this project you build that property into a small pipeline and test it.

Airflow cannot run on this site. Airflow snippets are read-only. The runnable examples are stand-ins built with plain Python and SQLite that mimic data intervals and reruns.

Logical date and data interval

A daily DAG run does not process "now". A run that starts on March 2 at 00:00 processes the data interval of March 1 (from 2025-03-01 00:00 to 2025-03-02 00:00). Airflow provides:

  • data_interval_start and data_interval_end (the interval),
  • logical_date (equal to the interval start for time-based schedules, in Airflow 3 may be None for manually triggered runs, so prefer the interval fields),
  • ds (the start date as YYYY-MM-DD), ds_nodash, and ts.

Using these instead of datetime.now() is what lets Airflow re-run any past interval exactly.

Python Stand-in: daily intervals

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Catchup and backfills

  • catchup=True (a DAG argument): when you unpause a DAG whose start_date is in the past, Airflow creates a run for every missed interval. Handy for history, dangerous for DAGs that were never meant to replay (sending emails!). Most new DAGs use catchup=False.
  • A backfill is an explicit request to run a date range, usually when the DAG already exists: in Airflow 3 from the UI or airflow backfill create --dag-id orders_etl --from-date 2025-01-01 --to-date 2025-01-31. It creates runs for those intervals, in order, with concurrency limits (max_active_runs).
  • You can also clear (rerun) existing task instances from the UI.
Python
@dag(schedule="@daily", start_date=pendulum.datetime(2025, 1, 1),
     catchup=False, max_active_runs=3)
def partitioned():
    ...

The idempotency rules

  1. Read by interval, not by "latest". WHERE order_date >= '{{ data_interval_start }}' and < '{{ data_interval_end }}'.
  2. Write by partition, replacing it. Delete-then-insert, INSERT OVERWRITE, MERGE/upsert on a natural key, or write a file named after the date and overwrite it.
  3. No appends without a key: INSERT blindly, then a retry duplicates rows.
  4. No side effects that cannot be repeated (sending the same email every rerun) without a guard such as a "sent" table.
  5. Do not depend on previous runs' state unless you set depends_on_past=True deliberately.
  6. Keep time out of the logic: no now(), no random IDs.
flowchart LR
  I["interval<br/>2025-03-01"] --> R["read source rows<br/>for that day"] --> W["replace partition<br/>2025-03-01"]
  I2["same interval again"] --> R2["read same rows"] --> W2["replace again<br/>same final state"]

Same input interval in, same final table state out.

A partitioned pipeline to backfill

This stand-in keeps a "source" of events for five days and a target table. The task for one interval reads that day's events and replaces that day's partition. We backfill all days, then rerun two of them to prove nothing is duplicated.

Python Stand-in: backfill and rerun without duplicates

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Compare with a non-idempotent version that just inserts:

Python Stand-in: why blind inserts break reruns

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

A retry after a partial failure creates the same damage, which is why idempotency matters even without backfills.

Late-arriving data

Sources sometimes deliver data late: yesterday's file shows up with corrections today. Options:

  • Reprocess a window: each run replaces the last 3 days instead of just one (cheap and simple).
  • Trigger on arrival with a sensor or asset update.
  • Periodic reconciliation: a weekly backfill job that rebuilds the last month.

Idempotent partition replacement makes all of these safe.

A safe backfill routine

  1. Test on one interval and inspect the output.
  2. Choose the range and set max_active_runs low (1 to 3) to protect the source and the warehouse; use a pool.
  3. Disable alerts or notifications that would spam, and guard side effects.
  4. Run the backfill, watch for failures, and clear failed instances to retry them.
  5. Verify counts per partition against the source.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A daily DAG run that starts on March 2 at 00:00 normally processes which data?

    A run starts after its data interval has ended, so the run on March 2 covers March 1.
  2. 2. What does idempotent mean for a task?

    Idempotency makes retries, reruns and backfills safe.
  3. 3. Which write strategy is NOT safe for reruns?

    A blind INSERT appends the same rows again on every rerun.
  4. 4. Why use datetime.now() inside a task rarely?

    Date logic must come from the data interval so any past interval can be reproduced exactly.

Summary

  • Use the data interval (ds, data_interval_start/end) for all date logic.
  • catchup replays missed intervals automatically; backfills replay a range on demand.
  • Make every write replace its partition or upsert on a key, so reruns and retries are safe.
  • Limit concurrency and guard side effects when backfilling; handle late data by reprocessing a window.

Next: Airflow Practice Questions and Exercises