dbt Incremental Models: Handling Late and Corrected Insurance Data

Rebuilding a table from scratch on every run is the simplest and safest thing dbt can do. It's also the most expensive. Once a premium or claims transaction table reaches tens of millions of rows across many years of account, a full rebuild every day wastes compute on history that hasn't changed.

Incremental models fix that: on each run, dbt processes only new or changed rows and merges them into the existing table. They're also where most dbt mistakes happen, especially with insurance data, where corrections and late transactions are normal. This post covers how they work and the patterns I use to keep them correct.

A basic incremental model

 1: -- models/marts/fct_claims.sql
 2: {{
 3:   config(
 4:     materialized = 'incremental',
 5:     unique_key   = ['claim_ref', 'bordereau_month'],
 6:     incremental_strategy = 'merge'
 7:   )
 8: }}
 9: 
10: select
11:     claim_ref,
12:     umr,
13:     section_no,
14:     bordereau_month,
15:     claim_status,
16:     paid_this_month,
17:     outstanding_reserve,
18:     fees_paid,
19:     original_ccy,
20:     loaded_at
21: from {{ ref('stg_claims') }}
22: 
23: {% if is_incremental() %}
24:   where loaded_at > (select max(loaded_at) from {{ this }})
25: {% endif %}

Three pieces make it work:

• is_incremental() is true when the table already exists and you're not running a full refresh. On the first run, the filter is skipped and everything loads.
• {{ this }} refers to the existing target table, so the model can find its own high-water mark.
• unique_key tells dbt how to match incoming rows to existing ones. With the merge strategy, matches are updated and new keys are inserted.

Choosing a strategy

Strategy	What happens	Use it when
append	New rows are inserted, nothing is updated	Truly append-only data, such as an audit log
merge	MERGE on unique_key	Rows can be corrected later. The usual choice.
insert_overwrite	Replaces whole partitions	Data is reloaded a period at a time
replace_where (Databricks)	Replaces rows matching a predicate	Replacing a bordereau month in one go

Adapters differ in which strategies they support and what the default is, so check yours. On Snowflake and Databricks, merge is the common choice for transaction tables.

The late-data problem

The filter loaded_at > max(loaded_at) assumes rows always arrive in order. In insurance they often don't:

• A corrected bordereau for March arrives in May.
• The feed is reprocessed upstream and old rows come back with older load timestamps.
• A transaction's business date is months before the date it was received.

A strict high-water mark can miss these rows forever. The fix is a lookback window: reprocess a few days or weeks before the high-water mark on every run, and let the MERGE deal with rows that haven't actually changed.

1: {% if is_incremental() %}
2:   where loaded_at >= (
3:       select dateadd(day, -{{ var('lookback_days', 7) }}, max(loaded_at))
4:       from {{ this }}
5:   )
6: {% endif %}

The lookback costs a little extra compute and buys a lot of safety. Adjust it per model through a variable, for example dbt build --vars '{lookback_days: 45}' after a known upstream reload. (Date functions differ between warehouses. dateadd works on Snowflake and Databricks SQL. Check yours.)

Deduplicate before merging

If the incoming batch has two versions of the same key, a MERGE either fails ("multiple source rows matched") or picks one at random, depending on the platform. Always reduce the batch to one row per key, keeping the latest:

 1: with source as (
 2:     select * from {{ ref('stg_claims') }}
 3:     {% if is_incremental() %}
 4:       where loaded_at >= (select dateadd(day, -7, max(loaded_at)) from {{ this }})
 5:     {% endif %}
 6: ),
 7: latest as (
 8:     select *
 9:     from source
10:     qualify row_number() over (
11:         partition by claim_ref, bordereau_month
12:         order by loaded_at desc
13:     ) = 1
14: )
15: select * from latest

Only update what changed

With the lookback, most incoming rows are identical to what's already there. Updating them anyway rewrites files for nothing. On adapters that support it, merge_update_columns limits the update to the columns that can actually change:

1: {{
2:   config(
3:     materialized = 'incremental',
4:     unique_key   = ['claim_ref', 'bordereau_month'],
5:     incremental_strategy = 'merge',
6:     merge_update_columns = ['claim_status', 'paid_this_month', 'outstanding_reserve', 'fees_paid', 'loaded_at']
7:   )
8: }}

Schema changes

When you add a column to an incremental model, the existing table doesn't have it. on_schema_change controls what happens:

1: {{ config(materialized='incremental', unique_key='claim_ref', on_schema_change='append_new_columns') }}

append_new_columns adds new columns to the table but leaves existing rows null for them. If the new column needs to be populated for history, plan a full refresh.

Full refreshes

1: dbt build --select fct_claims --full-refresh

A full refresh rebuilds the table from scratch. You'll need it after logic changes that affect history, such as a new currency conversion rule or a corrected commission calculation. Two tips:

• For very large tables, protect them from accidental full refreshes with full_refresh = false in the config, and remove it deliberately when needed.
• Make sure the model gives the same result whether it's run incrementally or from scratch. A periodic comparison of totals by year of account, between an incremental table and a full rebuild in dev, is a good way to catch logic that only works one way.

Checklist

• Use merge with a real business key, not a surrogate.
• Add a lookback window. Insurance data is rarely on time.
• Deduplicate the batch to one row per key.
• Limit updates to columns that change.
• Decide on_schema_change deliberately.
• Test that incremental and full-refresh results match.