dbt incremental model drifted from source: repair duplicates, missed rows and new columns

#Symptom

The dbt run is green, but the data is wrong:

• Totals in the model are higher than the source, or the same key appears twice.
• Recent rows are present, but older rows that changed in the source still show old values.
• A new source column is missing from the model, or is NULL on older rows.
• A run fails on Snowflake with Duplicate row detected during DML action.

Caution

--full-refresh rebuilds the table from what the source holds today. If the source keeps only 30 days of data, you lose everything older. Check source retention first, and set full_refresh: false on models that can't be rebuilt.

#Step 1: Measure the drift

The examples use analytics.fct_orders, built from analytics.stg_orders, with key order_id and watermark updated_at.

#Duplicates

select order_id, count(*) as copies
from analytics.fct_orders
group by order_id
having count(*) > 1
order by copies desc
limit 50;

#Rows missing or stale compared with the source

select count(*) as missing_in_model
from analytics.stg_orders s
left join analytics.fct_orders t on s.order_id = t.order_id
where t.order_id is null;

select count(*) as stale_in_model
from analytics.stg_orders s
join analytics.fct_orders t on s.order_id = t.order_id
where s.updated_at > t.updated_at;

#A watermark stuck in the future

One bad row can stop the model picking up new data until the calendar catches up.

select max(updated_at) as model_watermark, current_timestamp() as now
from analytics.fct_orders;

select order_id, updated_at
from analytics.fct_orders
where updated_at > current_timestamp()
order by updated_at desc
limit 20;

Timestamps stored in a different time zone can look like future rows. Confirm before you delete anything.

#What actually ran

Read the SQL dbt ran on the last incremental run. It's under target/run/<project>/models/. Check the filter and the merge condition there rather than assuming they match the model source.

#Step 2: Match the finding to the cause

#Step 3: Stop the bleeding

Pause the schedule for this model so no run adds more bad rows while you repair it. Then take a copy.

create table analytics.fct_orders_bak_20261105 clone analytics.fct_orders;

Keep the copy until the repair is verified.

#Step 4: Repair the table

#Remove duplicates

Check this keeps the row you want. This version keeps the latest by updated_at.

insert overwrite into analytics.fct_orders
select *
from analytics.fct_orders
qualify row_number() over (
  partition by order_id
  order by updated_at desc
) = 1;

Then re-run the duplicate query. It should return no rows. INSERT OVERWRITE and QUALIFY are Snowflake syntax here, so on other engines write the deduped rows to a new table and swap it in.

Warning

insert overwrite replaces every row in the table. Run it only with the model paused and only after taking the copy in Step 3.

#Remove future-dated rows

Confirm the row is bad, then correct it in the source first. A wrong value in the source will return on the next run. Next, remove it from the model so the watermark drops back. The next run's lookback or a backfill loads the corrected row.

delete from analytics.fct_orders
where order_id in (/* confirmed bad keys */)
  and updated_at > current_timestamp();

#Backfill the new column

update analytics.fct_orders t
set channel = s.channel
from analytics.stg_orders s
where t.order_id = s.order_id
  and t.channel is null
  and s.channel is not null;

Prefer this to --full-refresh when the source doesn't hold full history.

#Step 5: Fix the model

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='order_id',
    on_schema_change='append_new_columns',
    merge_exclude_columns=['created_at'],
    full_refresh=false
) }}

{% set lookback_days = var('lookback_days', 3) %}

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

{% if is_incremental() %}
where updated_at >= (
    select dateadd(day, -{{ lookback_days }}, max(updated_at)) from {{ this }}
)
{% endif %}

qualify row_number() over (partition by order_id order by updated_at desc) = 1

What each part does:

• unique_key makes the reprocessed window safe, because a matched row is overwritten. Use a list or a dbt_utils.generate_surrogate_key column for compound keys. Key columns must not contain nulls.
• qualify removes duplicates from the batch, so merge doesn't fail on them.
• >= with a lookback picks up late rows and rows that share the watermark timestamp. Set the window from how late your source really gets.
• merge_exclude_columns keeps created_at from being overwritten.- full_refresh=false makes dbt ignore --full-refresh on this model. Remove it deliberately when a rebuild is actually wanted.

#Choosing on_schema_change

#Backfill a window

Because the lookback is a variable and the model merges on a key, you can reprocess a longer window without touching older data:

dbt run --select fct_orders --vars '{lookback_days: 30}'

Warning

Only do this on models with a working unique_key and a key-based strategy. On an append-only model, it duplicates every row in the window.

#Time-series facts: consider microbatch

For large event tables on dbt 1.9+, the microbatch strategy handles lookback and backfills using event_time, batch_size, begin and lookback (default 1). Required configs vary by adapter, so check your adapter's page.

dbt run --select fct_events --event-time-start "2026-10-01" --event-time-end "2026-10-08"

Both flags are required together, and values are UTC.

#Step 6: Verify

dbt run --select fct_orders
dbt run --select fct_orders
dbt test --select fct_orders

Run the model twice on purpose. A second run with no source changes must not change the row count. Then re-run every query from Step 1, resume the schedule and rebuild downstream models with dbt run --select fct_orders+.

#Prevent it next time

Add tests that catch drift. data_tests is the key name from dbt 1.8. Older projects use tests.

models:
  - name: fct_orders
    columns:
      - name: order_id
        data_tests:
          - unique
          - not_null

• Schedule the missing-rows and stale-rows queries as a singular test or a daily monitor.
• Add a singular test that fails when updated_at is in the future, so bad rows raise an alert before they stall the watermark for long.
• Set full_refresh: false on every model whose source doesn't keep full history.