Skip to content
Tutorials Center
My learning

Topics

Lesson 30 of 41 ยท Incremental Models and Materializations

Late-Arriving Data and Full Refresh

Keep incremental models correct when rows arrive late or change: lookback windows, deduplication, on_schema_change and when to use dbt run --full-refresh.

The naive incremental filter, where occurred_at > (select max(occurred_at) from {{ this }}), has a quiet flaw. It assumes that tomorrow's rows are always newer than everything you have already loaded. Real pipelines do not behave like that: mobile apps upload events hours after they happened, source systems retry failed loads, and batch jobs backfill old dates. A row that arrives late but carries an old timestamp is skipped forever.

This lesson covers the common fixes, plus the escape hatch when an incremental table drifts from the truth: the full refresh.

The problem in numbers

SQL A late row falls through the filter

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

The source has three rows, the target two. Event 3 happened before the current maximum, so it never passes the filter. No test fails, no error appears: the numbers are just wrong.

Fix 1: filter on load time, not event time

Use a column set when the row reaches your warehouse, such as loaded_at, which always moves forward:

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

This catches late rows, as long as the loaded column is trustworthy. Combine it with unique_key so a re-delivered row replaces the old one.

Fix 2: a lookback window

If you only have an event time, reprocess a recent window every run so slightly late data is picked up:

SQL
{% if is_incremental() %}
  where occurred_at >= (
    select dateadd('day', -3, max(occurred_at)) from {{ this }}
  )
{% endif %}

(dateadd is Snowflake style; other warehouses spell it differently. A cross-database dbt.dateadd() macro exists.) The window is a trade-off: a wider window catches later data but reprocesses more. With unique_key set, reprocessed rows replace themselves instead of duplicating. With the microbatch strategy, the lookback setting does the same job.

SQL Lookback window plus key

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

All three events are now present, and events 1 and 2 were reprocessed without becoming duplicates.

Fix 3: deduplicate the batch

Sources may send the same key twice in one batch. A merge fails ("multiple source rows matched") or inserts both. Keep the latest per key before the incremental write:

SQL
select * from (
    select
        *,
        row_number() over (
            partition by order_id order by updated_at desc
        ) as rn
    from {{ ref('stg_orders') }}
    {% if is_incremental() %}
      where updated_at >= (select max(updated_at) from {{ this }})
    {% endif %}
)
where rn = 1

on_schema_change

What if the upstream model gets a new column? By default an incremental model ignores it (ignore). Set on_schema_change to control that:

Value Behavior
ignore (default) New columns are not added; run keeps working
fail The run errors until you handle the change
append_new_columns New columns are added; old rows get NULL
sync_all_columns Adds new columns and drops removed ones

Use fail or append_new_columns for tables people rely on, so schema drift is noticed.

Full refresh

Incremental models can drift: logic changed, a bug loaded bad data, a source was corrected for old months. To rebuild a model from scratch, run:

Terminal
dbt run --select fct_events --full-refresh

dbt then treats is_incremental() as false and rebuilds the whole table. Some practices:

  • Run full refreshes on a schedule (weekly, monthly) for tables where drift is likely.
  • Protect expensive models from accidental refreshes with full_refresh: false in config: the flag is then ignored for that model.
  • Always run a --full-refresh in CI on a small sample so the first-run path is tested too; it is a different code path from the incremental path.
flowchart TD
  P["Incremental table looks wrong"] --> A{"Bug in logic or old data corrected?"}
  A -->|"Yes"| F["dbt run --full-refresh"]
  A -->|"No: late rows missed"| L["Switch to loaded_at filter or lookback window"]
  F --> T["Run tests on the rebuilt table"]
  L --> T

Fix the cause, then rebuild.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Why can filtering on event time with max(occurred_at) lose rows?

    Rows that arrive after newer events but carry older timestamps are skipped by the filter.
  2. 2. What does a lookback window combined with unique_key accomplish?

    Reprocessed rows replace their earlier versions by key, so late data is captured safely.
  3. 3. What does on_schema_change: fail do?

    The run errors, which makes schema drift visible so you can decide how to handle it.
  4. 4. What does dbt run --full-refresh do to an incremental model?

    is_incremental() is false, so the entire select runs and replaces the table.

Summary

  • Event-time filters miss late-arriving rows; prefer loaded_at or a lookback window.
  • Use unique_key (and batch deduplication) so reprocessing never duplicates.
  • on_schema_change decides what happens when columns are added upstream.
  • --full-refresh rebuilds from scratch; schedule it and protect costly models.

Next: Packages and dbt_utils