Incremental Processing & Backfills

Why incremental, and the risk it carries

A full refresh — truncate and recompute everything, every run — is gloriously simple and gloriously correct: there's no state, no watermark, nothing to drift. It's also the first thing that stops scaling. When the source is billions of rows, reprocessing all of history every hour burns money and blows past your freshness SLA. Incremental processing reads only the slice that changed since last time and folds it into the existing table.

The trade you're making is stark and worth saying out loud in an interview:

The guardrail is a habit: keep full-refresh cheap enough to run periodically (weekly, or in a lower environment) and reconcile incremental against it. Incremental is an optimization layered on a correct baseline, never a replacement for having one.

The five load strategies

Every incremental pipeline is one of a handful of write patterns. Knowing when each is correct — and when it double-counts — is table stakes.

dbt as the worked example

dbt is the shop's transform tool, so make its incremental model second nature. The mechanics: a model materialized incremental runs a full build the first time (and on --full-refresh), and on every subsequent run wraps your WHERE filter in is_incremental() so it only reads new rows. A unique_key plus an incremental strategy tells dbt how to fold that slice in.

{{
  config(
    materialized       = 'incremental',
    unique_key         = 'order_id',
    incremental_strategy = 'merge',
    on_schema_change   = 'append_new_columns',
    partition_by       = {'field': 'event_date', 'data_type': 'date'}
  )
}}

WITH src AS (
    SELECT
        order_id,
        customer_id,
        amount,
        event_ts,
        CAST(event_ts AS DATE) AS event_date,
        source_updated_at
    FROM {{ ref('stg_orders') }}

    {% if is_incremental() %}
    -- LOOKBACK, not a bare '=': overlap the boundary so late/edited
    -- rows within the window are re-read and re-merged, not missed.
    WHERE source_updated_at >= (
        SELECT DATEADD('day', -3, MAX(source_updated_at)) FROM {{ this }}
    )
    {% endif %}
)

-- collapse any duplicates in the re-read window to one row per key
SELECT * FROM (
    SELECT src.*,
           ROW_NUMBER() OVER (
               PARTITION BY order_id ORDER BY source_updated_at DESC
           ) AS rn
    FROM src
)
WHERE rn = 1

Key config choices and their alternatives:

• incremental_strategy — merge (upsert on unique_key; the general default on Snowflake/BigQuery/Databricks), insert_overwrite (replace whole partitions; excellent for late data on partitioned warehouses), delete+insert, and append. It maps directly onto the five strategies above; the adapter decides which are available.
• unique_key — the grain that makes re-runs idempotent. Can be a list for a composite key. Without it, merge degrades to append and you get duplicates.
• on_schema_change — append_new_columns, sync_all_columns, ignore, or fail. Governs what happens when the model's columns drift from the table's; pick deliberately rather than being surprised mid-run.

Microbatch, event_time & snapshots

dbt's microbatch strategy formalizes the whole late-data-plus-backfill pattern so you stop hand-rolling it. You declare an event_time column and a batch_size, and dbt splits the run into one idempotent batch per period, each of which overwrites its own slice. A lookback re-processes the last N periods every run to absorb stragglers, and — crucially — backfills become "just run these batches over this range," in parallel and independently retryable.

{{
  config(
    materialized  = 'incremental',
    incremental_strategy = 'microbatch',
    event_time    = 'event_ts',   -- the column dbt buckets batches on
    batch_size    = 'day',        -- one batch per event day
    lookback      = 3,            -- re-process the last 3 days each run
    begin         = '2024-01-01'  -- earliest event_time for backfills
  )
}}

SELECT
    order_id,
    customer_id,
    amount,
    event_ts,
    source_updated_at
FROM {{ ref('stg_orders') }}
-- No is_incremental() plumbing needed: dbt injects the event_time
-- filter per batch, so each day is recomputed from raw and overwritten.
-- Backfill a range with:
--   dbt run --event-time-start 2024-01-01 --event-time-end 2024-02-01

For dimensions that change over time, dbt snapshots implement SCD Type 2 for you — capturing a new versioned row with dbt_valid_from/dbt_valid_to each time a tracked column changes:

snapshots:
  - name: dim_customer
    relation: ref('stg_customer')
    config:
      unique_key: customer_id
      strategy: timestamp          # detect change via a reliable updated_at
      updated_at: source_updated_at
      # strategy: check + check_cols: [tier, status]  # when no reliable timestamp

Snapshots give you the effective-dated dimension that as-of joins in Chapter 04 read against. The same tool that loads facts incrementally also versions your dimensions — one mental model, two materializations.

Watermarks & off-by-one bugs

Every incremental pipeline keeps a high-water mark: the marker of "what I've already processed." Choosing the right one is a correctness decision:

Whichever you pick, the two eternal boundary bugs are the same. Off-by-one / strict inequality: WHERE ts > last_watermark silently drops any row sharing the exact boundary timestamp; use >= and dedup. The lost-update window: reading [last_watermark, now) assumes every change in that range is durably visible — but a transaction that committed a moment after your snapshot, with an older timestamp, falls through the crack forever. The robust pattern overlaps the boundary and leans on an idempotent merge so re-reading is free:

-- Robust incremental: OVERLAP the boundary (>= with a lookback), dedup to
-- one row per key, then MERGE. Re-reading the overlap is harmless because
-- the merge is idempotent -- this is what makes it safe to re-run.

MERGE INTO analytics.fct_orders AS tgt
USING (
    WITH bounds AS (
        SELECT COALESCE(MAX(source_updated_at), TIMESTAMP '1970-01-01')
               AS hwm
        FROM analytics.fct_orders
    ),
    changed AS (
        SELECT s.*,
               ROW_NUMBER() OVER (
                   PARTITION BY s.order_id
                   ORDER BY s.source_updated_at DESC
               ) AS rn
        FROM raw.orders s, bounds b
        -- >= plus a 3-day lookback overlap, NOT a strict '>'
        WHERE s.source_updated_at >= DATEADD('day', -3, b.hwm)
    )
    SELECT * FROM changed WHERE rn = 1
) AS src
ON tgt.order_id = src.order_id
WHEN MATCHED AND src.source_updated_at > tgt.source_updated_at THEN UPDATE SET
    tgt.customer_id       = src.customer_id,
    tgt.amount            = src.amount,
    tgt.event_ts          = src.event_ts,
    tgt.source_updated_at = src.source_updated_at
WHEN NOT MATCHED THEN INSERT
    (order_id, customer_id, amount, event_ts, source_updated_at)
    VALUES
    (src.order_id, src.customer_id, src.amount, src.event_ts,
     src.source_updated_at);

The WHEN MATCHED AND src.source_updated_at > tgt.source_updated_at guard is what makes re-processing an overlap idempotent: an older or equal version can never clobber a newer one, so running the same window twice converges to the identical table.

Change data capture

When the source is an OLTP database, CDC is how you get changes incrementally without hammering it. Two families:

• Query-based CDC polls WHERE updated_at > :watermark. Simple, no special access — but it inherits every watermark bug above, cannot see hard deletes (a deleted row just vanishes from the query), and can miss intermediate states between polls.
• Log-based CDC (Debezium reading the MySQL binlog / Postgres WAL / Oracle redo) streams every committed change in commit order. It captures inserts, updates, and deletes, preserves ordering, and doesn't load the source with queries. It's the right answer when completeness or deletes matter — at the cost of running the CDC infrastructure.

Three things interviewers probe on CDC:

• Deletes. A log-based feed emits a delete event (op = d). You choose: hard-delete downstream to mirror the source, or soft-delete (set is_deleted = true, keep the row) to preserve history and keep the load idempotent. Soft-delete is usually right for analytics; you can't un-hard-delete.
• Ordering. Apply changes in commit / log-sequence order, not arrival order. Across Kafka partitions, key your topic by primary key so all changes to one row land in one partition and stay ordered; use the LSN to break ties and reject out-of-order applies.
• Exactly-once into the warehouse. The stream is at-least-once, so you'll see duplicates on retry/replay. Achieve effectively-once by making the sink idempotent: MERGE on the primary key, keyed by log position so an already-applied change is a no-op. Idempotent apply beats trying to make delivery truly exactly-once.

-- Apply a CDC change feed idempotently and in log order.
-- Keep only the latest change per key (by log sequence), then MERGE;
-- deletes become soft-deletes so re-applying is a no-op.

MERGE INTO analytics.dim_account AS tgt
USING (
    SELECT * FROM (
        SELECT
            c.account_id,
            c.name,
            c.status,
            c.op,                 -- 'c' create, 'u' update, 'd' delete
            c.lsn,                -- log sequence number = ordering key
            ROW_NUMBER() OVER (
                PARTITION BY c.account_id ORDER BY c.lsn DESC
            ) AS rn
        FROM raw.account_cdc c
        WHERE c.lsn > (SELECT COALESCE(MAX(last_lsn), 0)
                       FROM analytics.dim_account)
    ) WHERE rn = 1
) AS src
ON tgt.account_id = src.account_id
WHEN MATCHED AND src.lsn > tgt.last_lsn AND src.op = 'd' THEN UPDATE SET
    tgt.is_deleted = TRUE, tgt.last_lsn = src.lsn
WHEN MATCHED AND src.lsn > tgt.last_lsn AND src.op IN ('c','u') THEN UPDATE SET
    tgt.name = src.name, tgt.status = src.status,
    tgt.is_deleted = FALSE, tgt.last_lsn = src.lsn
WHEN NOT MATCHED AND src.op IN ('c','u') THEN INSERT
    (account_id, name, status, is_deleted, last_lsn)
    VALUES (src.account_id, src.name, src.status, FALSE, src.lsn);

Backfills that don't double-count

A backfill is re-running history — after a bug fix, a new column, or a widened lookback. It's where non-idempotent pipelines detonate: run a naive append-based load over last month and you've now got every row twice. The rules that keep backfills safe are exactly the rules that make incremental safe, applied to a wider range.

• Overwrite, don't append. The safest backfill uses insert-overwrite / partition-replace: recompute each affected partition from raw and swap it in, so re-running a partition is a no-op-to-the-result, not an accumulation. Microbatch does this per period for free.
• Partition-scoped and parallel. Backfill by partition so runs are independent, restartable, and bounded — if day 14 of 30 fails, you re-run day 14, not the whole month.
• Separate backfill from steady-state. A backfill shouldn't advance the production watermark or trip freshness alerts. Parameterize the date range explicitly; keep it off the schedule that consumers watch.
• Reconcile after. A backfill is done when its numbers match the source, not when the job exits 0. Row-count and sum recon per partition against source is the acceptance test — the subject of Chapter 06.

-- Backfill a bounded date range by atomically REPLACING whole partitions.
-- Idempotent by construction: re-running the same range yields the same table,
-- because each partition is fully recomputed from raw and swapped, not appended.

-- Spark / Iceberg / Hive dynamic partition overwrite.
-- Spark's default overwrite mode is static, which would replace every partition.
-- Set spark.sql.sources.partitionOverwriteMode=dynamic first (Hive serde tables
-- always overwrite dynamically).
INSERT OVERWRITE TABLE analytics.fct_orders
PARTITION (event_date)
SELECT
    order_id,
    customer_id,
    amount,
    event_ts,
    CAST(event_ts AS DATE) AS event_date
FROM raw.orders
WHERE CAST(event_ts AS DATE) BETWEEN DATE '2024-01-01'
                                 AND DATE '2024-01-31'   -- the backfill window
-- Only the partitions produced by this SELECT are replaced; days outside
-- the window are untouched. Re-run the same range safely any number of times.
;

The leveling ladder

You now have the machinery to process only what changed and to rebuild history safely. The remaining question is how you prove the output is right — and stop a bad load before it reaches consumers: 06 · Reconciliation & Validation.

✓ Check yourself

Answer from memory before opening each one.

1. A table has 2 billion rows and a nightly full refresh that now misses its freshness target. What do you give up by going incremental, and what keeps the incremental result honest?

   Show answer

   A full refresh is correct for free and costs time and money. Incremental returns the cost and takes on state, boundary conditions and drift from what a full refresh would produce. Keep a full refresh cheap enough to run periodically, or in a lower environment, and reconcile the incremental table against it. See why incremental, and the risk it carries.

2. A dbt model filters WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }}). Name two rows it loses, and the change that makes re-reading safe.

   Show answer

   It loses rows that share the boundary timestamp, because of the strict comparison, and rows that were edited or arrived late with an older timestamp, because they are never re-read. Use >= with a lookback overlap and a unique-key merge. A guard such as updating only when the source version is newer means the overlap re-applies old data without clobbering new data. See watermarks and off-by-one bugs.

3. Pick a load strategy for each: a click log that never changes, an accounts table whose rows are edited, and a daily revenue aggregate you can recompute from raw. Give the reason for each.

   Show answer

   Append for the click log, with a dedup key downstream because replays duplicate rows. Merge on the account key, with a rule for which version is newer. Insert-overwrite for the aggregate, because a re-run replaces the partition instead of accumulating. Choose by the shape of change, not by habit. See the five load strategies.

4. A backfill job runs INSERT OVERWRITE ... PARTITION (event_date) for January on Spark with default settings. What would you expect for February's data, and what do you check or set before the run?

   Show answer

   Spark's default partition overwrite mode is static, so it can replace every partition and wipe February. Set the mode to dynamic so only partitions that receive rows are replaced, and test on a copy by comparing row counts for days outside the window before and after. See backfills that don't double-count.

5. A CDC feed delivers an update, then a delete, for one account, but the delete is consumed first because they landed in different partitions. How do you stop the account being resurrected, and do you hard-delete or soft-delete?

   Show answer

   Key the topic by primary key so changes to one row stay in one partition, and apply by log sequence number, rejecting any change that is not newer than the stored one. Soft-delete in analytics because it keeps history and makes the apply idempotent; you cannot un-hard-delete. Hard-delete only where policy requires erasure. See change data capture.

6. A 30-day backfill fails on day 14. What lets you resume, and what must be true before you call it done?

   Show answer

   Because each partition is recomputed from raw and overwritten, you re-run day 14 and continue; nothing accumulates. It is done when per-partition counts and sums match the source, not when the job exits cleanly, and it should not have advanced the production watermark. See backfills.

Takeaway

• Full refresh buys correctness at a cost in time and money; incremental buys the cost back and risks drift, so keep a full refresh to reconcile against.
• Choose the load strategy from the shape of change: append for immutable events, merge for keyed mutable rows, insert-overwrite when a whole partition can be recomputed.
• A strict greater-than on the high-water mark loses ties and late edits; use an overlap, a dedup and an idempotent merge with a newer-version guard.
• Microbatch makes per-period overwrite, lookback and parallel backfill a configuration rather than hand-rolled code.
• Log-based CDC supplies deletes and commit order; apply it by log position so replays are no-ops, and soft-delete in analytics.
• Check how an engine overwrites partitions before a backfill, because Spark's default is static and can wipe the table.
• A backfill overwrites partitions, runs apart from steady state, and finishes when reconciliation matches the source.

Proving the output is right, and stopping a bad load before consumers see it, is reconciliation and validation. → Reconciliation & Validation

Further reading

• dbt: About incremental models — dbt Labs docs. is_incremental(), unique_key and the full-refresh behaviour.
• dbt: Incremental microbatch — dbt Labs docs. event_time, batch_size, lookback and per-batch backfills.
• dbt: Snapshots — dbt Labs docs. Timestamp and check strategies for SCD Type 2 history.
• Iceberg: Spark Writes — Apache Iceberg docs. MERGE INTO and INSERT OVERWRITE, including static versus dynamic overwrite mode.
• Debezium connector for PostgreSQL — Debezium docs. Change event structure, operation codes, delete tombstones and log positions.