Incremental dbt Under Airflow Backfills

The retry and catchup hazards of stateful dbt models, full-refresh decisions, and data-interval-aware incremental patterns.

Every chapter so far sold you the same promise: a task per model, and each task retries on its own. When int_order_payments flakes on a warehouse timeout, Airflow reruns that node and leaves the twenty models around it alone. It’s the whole reason Cosmos beats a single dbt build in a BashOperator. But that promise has a hole in it, and the hole is exactly where incremental models live. A retry is only safe if the thing being retried is idempotent — running it twice produces the same result as running it once. Most dbt models are idempotent by construction: a table model is create or replace, so a second run just rebuilds the same table. An incremental model is not. It appends to state. And “append to state, then retry the append” is how you get a table with every order counted twice.

This chapter is about that hazard and its relatives. Incremental models plus Airflow’s two favorite tricks — automatic retries and catchup — interact in ways that quietly corrupt data instead of loudly failing. The bookshop has been a toy so far; the moment its fct_orders grows past a few million rows and someone makes it incremental to save money, all of these edges become real at once.

How an incremental model actually loads

A quick refresher, because the hazard lives in the mechanics. An incremental model runs one of two ways. On its first build (or with --full-refresh), dbt runs the model’s select and creates the table from scratch. On every subsequent run, dbt wraps that same select in a filter you write with is_incremental(), and takes only the new rows, then combines them with what’s already in the table. The combine step is where everything is decided.

Here’s a bookshop fct_orders written the naive way — the way that looks fine in a demo and detonates in production:

-- models/marts/fct_orders.sql
{{
  config(
    materialized='incremental',
    incremental_strategy='append',
  )
}}

select
    order_id,
    customer_id,
    ordered_at,
    amount
from {{ ref('stg_orders') }}

{% if is_incremental() %}
  where ordered_at > (select max(ordered_at) from {{ this }})
{% endif %}

Read what this does on an incremental run. {{ this }} is dbt’s reference to the model’s own existing table. The filter selects rows from stg_orders newer than the newest ordered_at already loaded, and the append strategy does exactly what it says: insert into fct_orders (...) select .... No key, no dedup, no merge. Just an insert.

Now the retry. Airflow runs the fct_orders task. dbt computes max(ordered_at) as, say, 2026-07-03 23:00, selects the 4,000 orders newer than that, and begins the insert. The insert commits — the 4,000 rows are now in the table — but then the task fails afterward: the connection drops on the final ack, the pod is evicted, the operator’s post-run bookkeeping raises. Airflow sees a failed task and does what you told it to. It retries.

On the retry, dbt runs the model again from the top. It computes max(ordered_at) again — but the table already contains the 4,000 rows from the first attempt, so max(ordered_at) is now higher, and the filter correctly skips them. In that narrow case you’re lucky. But the append strategy has no barrier: if the failure happened after the select was materialized into a temp relation but before or during the insert’s commit boundary — and depending on your warehouse’s transaction semantics for insert ... select — you can land in a state where the retry re-selects an overlapping window and inserts it a second time. On warehouses without transactional DDL for the temp-table dance, or when the “new rows” boundary is >= instead of >, or when late-arriving rows share an ordered_at with the watermark, the double-load is not a corner case. It’s the default failure mode of append under retries.

The corruption is silent. No task turns red on the second run — the retry succeeds. You find out when revenue is 8% high and finance asks why.

It’s worth being precise about why the warehouse doesn’t save you here, because the details decide how bad it gets. dbt’s incremental build is not a single atomic statement. It’s a small sequence: materialize the filtered select into a temporary relation, then apply that relation to the target — for append, a plain insert into target select * from tmp. On a warehouse with fully transactional DML (Postgres, say), a mid-insert failure rolls the insert back and the retry is clean. But many analytics warehouses treat insert ... select as committed work the moment the statement returns, with no enclosing transaction around the temp-build-then-insert dance, and a task that dies after the insert commits but before Airflow records success leaves those rows permanently in the table. The retry then runs the whole model again. Whether that retry double-loads comes down to a race between two facts: did the first insert’s rows push max(ordered_at) high enough that the retry’s > filter now excludes them? If the newest rows in the batch all share the same ordered_at as an earlier row — common with midnight-boundary timestamps or coarse date grains — a > filter can re-include the boundary rows, and a >= filter re-includes them every time. None of this is exotic; it’s the ordinary texture of real timestamp data, and append has no defense against any of it.

The fix is the merge key, not the retry policy

The instinct is to reach for the retry config: turn retries off on incremental models, or make the operator smarter. That’s backwards. The retry is not the problem; the retry is a test the model failed. The fix is to make the model idempotent so that running it twice is genuinely safe, and then leave retries on.

Idempotent means the merge strategy with a unique_key:

-- models/marts/fct_orders.sql
{{
  config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='order_id',
  )
}}

select
    order_id,
    customer_id,
    ordered_at,
    amount,
    updated_at
from {{ ref('stg_orders') }}

{% if is_incremental() %}
  where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Two changes carry the weight. First, incremental_strategy='merge' with unique_key='order_id' means dbt no longer blindly inserts. It builds a temp table of the new rows and runs a merge into fct_orders using tmp on fct_orders.order_id = tmp.order_id when matched then update ... when not matched then insert .... Run that twice with the same input and the second run matches every row on order_id and updates in place — no new rows, no doubles. The append became a upsert. Retries are now safe by construction, because a merge keyed on a stable business key is idempotent whatever window it reselects.

Second — and this is subtler — the filter moved from ordered_at to updated_at. A merge keyed on order_id still needs to see a changed order to update it. If an order placed yesterday gets its amount corrected today, ordered_at hasn’t changed, so an ordered_at-based watermark would never re-select it. updated_at (a row-level “last modified” that your source or staging layer maintains) catches both new and mutated rows, and the merge reconciles them. Watermark on the column that reflects change, merge on the key that reflects identity.

This is the correction to the earlier chapters’ per-model-retry pitch. “Every task retries independently” is a feature only for idempotent tasks. For incremental models it’s a loaded gun, and the safety is the merge key. Write incrementals as merges with a real unique_key, and the retry story the whole series has been selling becomes true again. Write them as appends and every retry is a coin flip on your row counts.

One more knob is worth setting deliberately here: the operator’s retry count itself. Cosmos forwards retries through operator_args, so you can dial it per DAG (or per node with the finer-grained config from the earlier chapters):

operator_args={"retries": 2, "retry_delay": timedelta(minutes=5)}

The temptation, once you’ve been burned by a double-load, is to set retries=0 on incremental models and call it safe. Resist it. Zero retries doesn’t make the model idempotent — it just trades a silent data bug for a loud pipeline failure, so now a transient warehouse blip pages someone at 3am for a job that would have succeeded on a second attempt. The right shape is the opposite: fix the idempotency with a merge key, then keep retries on, because a keyed merge genuinely doesn’t care how many times it runs. Retries are a reliability feature you get to keep only after you’ve earned it with an idempotent model.

--full-refresh per task, on purpose

Sometimes you do want to blow away the incremental state and rebuild from scratch: the model’s logic changed, the watermark drifted, or a backfill left the table wrong and you want a clean slate. That’s --full-refresh, and the discipline is to make it a deliberate operation rather than a flag someone types at 2am and forgets.

Cosmos lets you pass it per task. The cleanest way is operator_args, which forwards kwargs to the underlying dbt operators; full_refresh=True maps to dbt’s --full-refresh:

from cosmos import DbtDag, ProjectConfig, RenderConfig, ExecutionConfig

DbtDag(
    project_config=ProjectConfig("/usr/local/airflow/include/dbt/bookshop"),
    render_config=RenderConfig(select=["fct_orders"]),
    execution_config=ExecutionConfig(
        dbt_project_path="/usr/local/airflow/include/dbt/bookshop",
    ),
    profile_config=profile_config,
    operator_args={"full_refresh": True},
    schedule=None,
    dag_id="bookshop_full_refresh",
)

For a single task you can also drop to raw flags with dbt_cmd_flags=["--full-refresh"], which is handy when you want other flags along with it. Either way, the important design choice is the last two arguments: schedule=None and a distinct dag_id. This is not the nightly DAG. It’s a separate, manually triggered DAG whose only job is to rebuild the incremental models from scratch, and it does not run on a timer.

Better still, let the operator choose which model to refresh and confirm they meant it. But this is exactly where the two-clock rule from Chapter 07 comes back to collect, so it’s worth being careful.

The tempting thing to write is RenderConfig(select=["{{ params.model }}"]). It cannot work. RenderConfig is consumed at parse time — it’s what Cosmos hands to dbt ls on the scheduler to decide which tasks exist at all. params don’t exist at parse time; they’re supplied when someone triggers a run. The scheduler would pass the literal seven-character string {{ params.model }} to --select, match zero models, and render an empty DAG. Jinja in a RenderConfig is not a template, it’s a typo with extra steps.

The fix follows from the same rule. If the set of tasks can’t vary per run, then don’t ask it to: use one task whose dbt command is templated, rather than a task-per-model graph you’re trying to reshape at run time. Cosmos’s operators expose select and full_refresh as real template_fields, so they render per run exactly as you’d want:

from airflow.sdk import Param, dag
from cosmos.operators.local import DbtRunLocalOperator


@dag(
    dag_id="bookshop_refresh",
    schedule=None,
    params={
        "model": Param("fct_orders", type="string"),
        "full_refresh": Param(False, type="boolean"),
    },
)
def bookshop_refresh():
    DbtRunLocalOperator(
        task_id="refresh_model",
        project_dir="/usr/local/airflow/include/dbt/bookshop",
        profile_config=profile_config,
        select=["{{ params.model }}"],              # a template_field — renders at run time
        full_refresh="{{ params.full_refresh }}",   # also a template_field
    )


bookshop_refresh()

Two things make this work where the DbtDag version didn’t. The graph is fixed — one task, always — so nothing needs to be known at parse time. And full_refresh is declared bool | str on the Cosmos operator precisely because it’s templated: when Jinja hands it the string "False", Cosmos runs it through to_boolean rather than testing it for truthiness, so an unticked checkbox really does mean no --full-refresh. (That is a genuine footgun avoided by the library, not by you — a plain if self.full_refresh: would see the non-empty string "False" and cheerfully rebuild your table.)

Now a full refresh is an explicit act: open the DAG, pick the model, tick the box, trigger. The scheduled pipeline never full-refreshes on its own — it merges nightly and stays cheap — and the destructive rebuild lives behind a form someone has to fill in. The distinction matters because --full-refresh on a large fct_orders can be an hour of warehouse time and a drop table that briefly leaves the model empty for anyone querying it. That belongs on a named path, not a checkbox in the daily job.

catchup=True does not rewind your state

Here’s the interaction that surprises people who know Airflow well and dbt less well. Airflow’s catchup=True means that if a DAG has been paused or newly deployed, the scheduler will replay every missed interval — one DAG run per day since the start_date. It’s a genuinely useful feature for backfilling time-partitioned work. And it does almost nothing useful for a naive incremental model, because the incremental model has no idea what interval it’s running for.

Walk through it. You deploy bookshop_daily with catchup=True and a start_date of three weeks ago. Airflow dutifully creates 21 DAG runs, one per day, and starts working through them: the run “for” July 1, then July 2, then July 3. Each run executes the same fct_orders model. But fct_orders filters on where updated_at > (select max(updated_at) from {{ this }}) — it looks at the table’s current state, not at the DAG run’s data interval. So the July 1 run loads everything up to now. The July 2 run finds max(updated_at) is already current and loads nothing. The July 3 run loads nothing. Twenty of your twenty-one “historical” runs are no-ops, because the model’s notion of “new” is the table, and the table doesn’t rewind just because Airflow labeled this run “July 2.”

Catchup replays the schedule. It does not replay your data. {{ this }} and max(event_time) are state read at execution time; they are blind to data_interval_start unless you wire the interval in yourself. So catchup on a self-watermarking incremental doesn’t backfill history — it just runs the same “load everything new” logic twenty-one times, and only the first run does anything.

There’s a second, nastier face of this. Suppose the model is interval-aware — you pass the data interval in as a dbt var and filter on it (we’ll build that in the next section). Now catchup’s 21 runs each load a different day, which is what you wanted. But if those runs execute concurrently, you have multiple merge into fct_orders statements racing, each with a different window, contending for the same table. Depending on the warehouse, that’s lock contention at best and interleaved partial writes at worst.

The guard is max_active_runs=1:

DbtDag(
    project_config=ProjectConfig("/usr/local/airflow/include/dbt/bookshop"),
    render_config=RenderConfig(select=["fct_orders"]),
    execution_config=ExecutionConfig(
        dbt_project_path="/usr/local/airflow/include/dbt/bookshop",
    ),
    profile_config=profile_config,
    schedule="@daily",
    start_date=datetime(2026, 6, 13),
    catchup=True,
    max_active_runs=1,
    dag_id="bookshop_daily",
)

max_active_runs=1 forces the historical runs to execute in order, one at a time — July 1 finishes before July 2 starts. For an incremental model whose whole job is to mutate one shared table, serialized runs are not a performance compromise; they’re a correctness requirement. Overlapping MERGEs into the same target is a data race, and max_active_runs=1 is how you say “this table has one writer at a time.”

Microbatch: making the data interval the unit of work

The real fix for “catchup replays the schedule but not the data” is to make the data interval be the batch. dbt’s microbatch strategy is built for exactly this. Instead of you hand-writing an is_incremental() filter, you declare an event_time column and a batch_size, and dbt slices the model into time buckets and builds each bucket as its own idempotent select and insert-overwrite.

-- models/marts/fct_orders.sql
{{
  config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='ordered_at',
    batch_size='day',
    lookback=1,
  )
}}

select
    order_id,
    customer_id,
    ordered_at,
    amount
from {{ ref('stg_orders') }}

Notice there is no is_incremental() block at all. dbt manages the windows. For each batch, it automatically adds where ordered_at >= [batch start] and ordered_at < [batch end] and replaces that day’s partition rather than appending to it. That replacement is the property that makes microbatch idempotent per batch: rebuilding the July 2 window overwrites the July 2 rows, so running it twice — a retry, a manual rerun, whatever — lands the same data. No watermark to drift, no double-load. lookback=1 tells dbt to also reprocess the prior batch on each run, which sweeps up late-arriving rows that landed after their day’s window already ran.

Now the piece that ties it back to Airflow. dbt microbatch is driven by --event-time-start and --event-time-end, and Airflow hands you exactly those two values on every run as the data interval. So the bridge is passing the interval through Cosmos as dbt vars or flags:

Before this works, check your interval. Every window in this section relies on data_interval_start and data_interval_end being different values. On a default Airflow 3 install they are not: a cron schedule uses CronTriggerTimetable, whose data interval is zero-width, so >= start AND < end matches nothing and this task silently does no work. Set AIRFLOW__SCHEDULER__CREATE_CRON_DATA_INTERVALS=True (the foundations scheduling chapter explains why, and what the alternative is). If you inherit a DAG and the numbers look wrong, print both bounds first — if they’re equal, you’ve found it.

DbtDag(
    project_config=ProjectConfig("/usr/local/airflow/include/dbt/bookshop"),
    render_config=RenderConfig(select=["fct_orders"]),
    execution_config=ExecutionConfig(
        dbt_project_path="/usr/local/airflow/include/dbt/bookshop",
    ),
    profile_config=profile_config,
    operator_args={
        "dbt_cmd_flags": [
            "--event-time-start", "{{ data_interval_start }}",
            "--event-time-end", "{{ data_interval_end }}",
        ],
    },
    schedule="@daily",
    start_date=datetime(2026, 6, 13),
    catchup=True,
    max_active_runs=1,
    dag_id="bookshop_microbatch",
)

Now catchup does the right thing. The July 2 DAG run has data_interval_start = 2026-07-02 and data_interval_end = 2026-07-03, and it passes those straight to dbt’s microbatch, which builds only the July 2 partition and overwrites it. The July 3 run builds July 3. Twenty-one historical runs now backfill twenty-one distinct days, each one idempotent, each one a partition replacement rather than a blind append. Combined with max_active_runs=1, they run in order and never contend. This is the shape you want for any large time-series fact table under backfill: the Airflow data interval is the dbt batch window, and every batch is safe to rebuild.

The lookback deserves a second look, because it’s where late data and the data interval negotiate. Orders don’t always land in the warehouse on the day they were placed — a delayed ingestion, a corrected record, an event that arrived out of order all mean the July 2 window might gain rows after the July 2 run already finished. lookback=1 tells dbt that every run should also rebuild the immediately prior batch, so tonight’s July 3 run silently re-does July 2 as well and sweeps up anything that arrived late. Because each batch is a partition replacement, redoing July 2 is free of side effects — it overwrites, it doesn’t duplicate. Widen lookback to match how late your data realistically arrives (a lookback of 3 for a source that can be three days behind), and accept that you’re paying to rebuild those trailing partitions on every run in exchange for eventual correctness. It’s the microbatch analogue of the merge’s updated_at watermark: a deliberate choice about how far back “new” reaches.

If you don’t pass the flags, dbt microbatch falls back to its own default window (recent batches relative to now), which is fine for a plain nightly run but ignores catchup entirely — you’d be back to “the schedule replays but the data doesn’t.” The --event-time-start/--event-time-end handoff is the whole point.

The other non-idempotent node: snapshots

Incremental models are the famous hazard, so they get the airtime. But this chapter’s thesis — Airflow’s reliability features assume idempotent work — has a second violator, and it is in some ways worse, because there is no merge key that rescues you from it.

A dbt snapshot records how a row changed over time. It reads the source, compares it to the snapshot table, and closes out any row whose tracked columns moved: stamping dbt_valid_to on the old version and inserting a new one with dbt_valid_from set to now. That “now” is the whole problem. A snapshot is stamped with wall-clock time, not with your data interval — dbt has no --event-time-start/--event-time-end for snapshots, and microbatch doesn’t apply to them.

Follow what that does to the two tricks this chapter has been teaching:

Catchup is meaningless on a snapshot. Deploy a Cosmos DAG with catchup=True and a start_date three weeks back, and Airflow will dutifully create 21 runs. Each one runs dbt snapshot. Each one reads today’s source table — because that’s the only state there is — and stamps its rows with today’s timestamp. You don’t get three weeks of history. You get, at best, one real version row and twenty no-ops; at worst, twenty spurious versions all dated today, and a slowly-changing dimension that claims the customer moved house twenty times this morning. History you didn’t capture cannot be backfilled. That sentence is the whole snapshot chapter in the dbt series, and it collides head-on with the instinct to fix a gap by replaying the schedule.

A retry can open a spurious version. The snapshot’s merge is not the same shape as an incremental’s. If the task dies after the merge commits but before Airflow records success, the retry re-reads the source, finds the row it just closed out, and — depending on how the timestamps land — can write another version boundary. A unique_key doesn’t save you, because the snapshot’s grain is the version, and a new version is exactly what it was asked to produce.

So the rules are blunt, and they’re different from the incremental rules:

  • Never put a snapshot in a catchup=True DAG. Give snapshots their own DAG, on a plain schedule, with catchup=False. Their cadence is “how often do we want to notice a change,” which has nothing to do with the data interval of anything else.
  • Never --full-refresh a snapshot unless you mean “throw away all recorded history and start again from the current state,” which is almost never what anyone means when they type it.
  • Run them on a schedule you’d be happy to miss. A missed incremental run catches up on the next one. A missed snapshot run means a change that happened in the gap was never seen, and never will be. That’s the honest trade of the pattern, and the only mitigation is to run it often enough that the gap doesn’t matter.

Seeds have a milder version of the same problem. Cosmos renders dbt seed as a real task (and under TestBehavior.BUILD, dbt build covers models, snapshots and seeds in one command). That seed task re-COPYs the CSV on every DAG run, which is harmless but wasteful, and DbtSeedMixin carries full_refresh as a template field — so a full-refreshed seed drops and recreates the table. If a seed is genuinely static reference data, there’s a good argument for keeping it out of the nightly DAG entirely and running it on deploy, where it belongs.

The general shape, one more time: Airflow’s retries and catchup are safe exactly to the extent that the node underneath is idempotent. A table model is. A merge-keyed incremental can be made so. A snapshot fundamentally is not, because its output depends on when you ran it — and the fix is not a config flag, it’s keeping it away from the machinery that assumes otherwise.

Scheduler-managed backfills across a Cosmos DAG

Airflow 3.x changed how backfills work, and it matters here. The old airflow backfill CLI daemon is gone; backfills are now scheduler-managed. You ask for one — from the UI, or with airflow backfill create — and the scheduler materializes the historical DAG runs and works through them like any other runs, honoring max_active_runs, pools, and priority:

airflow backfill create \
  --dag-id bookshop_microbatch \
  --from-date 2026-06-01 \
  --to-date 2026-06-30

This is strictly better than catchup for a deliberate history rebuild, because it’s an explicit request for a bounded range rather than a side effect of a start_date. You leave the nightly DAG on catchup=False for normal operation and issue a backfill when you actually need June rebuilt. The scheduler creates 30 runs for June, serializes them under max_active_runs=1, and each run passes its own data interval to dbt microbatch — so June 1 rebuilds the June 1 partition, and so on down the month.

The thing to hold in your head is what the backfill means for incremental state. A backfill does not reset dbt’s state or drop the incremental table. It just runs the model again for each historical interval. So the model’s own strategy decides whether the backfill is safe:

  • Microbatch (or any partition-replacing strategy): each backfilled interval overwrites its window. The backfill is idempotent and correct — this is the combination you want.
  • Merge with unique_key: each backfilled interval upserts its rows. Also safe, though a merge without a time filter reprocesses more than the interval, so pair it with interval-aware filtering if the table is large.
  • Append: a backfill is a catastrophe. Each of the 30 runs appends its window on top of whatever’s already there, and if the windows overlap the table’s existing data — which they will, since June is already loaded — you double- or triple-load the whole month. Never backfill an append-strategy incremental. Full-refresh it instead, once, through the named refresh DAG.

So the backfill machinery is only as safe as the model underneath it. Scheduler-managed backfills give you clean, bounded, serialized replays; they do not give you idempotency. That still comes from the model being a microbatch or a keyed merge. The Airflow side handles when and in what order; the dbt side handles whether running it again is safe. Get both right and a month-long backfill of fct_orders is a one-line command that finishes correct. Get the dbt side wrong and the same command is the fastest way to corrupt a fact table you own.

Final thoughts

The through-line of this chapter is a single sentence the earlier chapters left implicit: Airflow’s reliability features assume idempotent work, and incremental dbt models are not idempotent unless you build them to be. Every hazard here is a version of that. Retries double-load an append. Catchup replays a schedule your watermark can’t see. Backfills multiply rows for a model that only knows how to insert. And every fix is a version of the same answer — make the model safe to run twice, then let Airflow do its job. Merge on a unique_key, or microbatch keyed to the data interval, and the retry becomes a non-event, catchup becomes a real backfill, and airflow backfill create becomes a safe button instead of a foot-gun.

Write a one-line contract at the top of every incremental model that answers four questions: what happens on retry, on backfill, on late data, and when the logic changes. If the honest answer to “what happens on retry” is “it depends,” you have an append masquerading as a pipeline, and Airflow is only making the wrong thing more reliably scheduled.

Next: Alternatives, Secrets, and Multi-Environment dbt

Comments