dbt Incremental Model Development

Choose the right strategy. Design the unique_key carefully. Handle edge cases.

When to Use Incremental

Scenario	Recommendation
Source data < 10M rows	Use `table` (simpler, full refresh is fast)
Source data > 10M rows	Consider `incremental`
Source data updated in place	Use `incremental` with `merge` strategy
Append-only source (logs, events)	Use `incremental` with `append` strategy
Partitioned warehouse data	Use `insert_overwrite` if supported

Default to
table
unless you have a clear performance reason for incremental.

Critical Rules

ALWAYS test with
--full-refresh
first before relying on incremental logic
ALWAYS verify unique_key is truly unique in both source and target
If merge fails 3+ times, check unique_key for duplicates
Run full refresh periodically to prevent data drift

Workflow

1. Confirm Incremental is Needed

bash

# Check source table size
dbt show --inline "select count(*) from {{ source('schema', 'table') }}"

If count < 10 million, consider using

table

instead. Incremental adds complexity.

2. Understand the Source Data Pattern

Before choosing a strategy, answer:

Is data append-only? (new rows added, never updated)
Are existing rows updated? (need merge/upsert)
Is there a reliable timestamp? (for filtering new data)
What's the unique identifier? (for merge matching)

bash

# Check for timestamp column
dbt show --inline "
  select
    min(updated_at) as earliest,
    max(updated_at) as latest,
    count(distinct date(updated_at)) as days_of_data
  from {{ source('schema', 'table') }}
"

3. Choose the Right Strategy

Strategy	Use When	How It Works
`append`	Data is append-only, no updates	INSERT only, no deduplication
`merge`	Data can be updated	MERGE/UPSERT by unique_key
`delete+insert`	Data updated in batches	DELETE matching rows, then INSERT
`insert_overwrite`	Partitioned tables (BigQuery, Spark)	Replace entire partitions

Default:

merge

is safest for most use cases.

Note: Strategy availability varies by adapter. Check the dbt incremental strategy docs for your specific warehouse.

4. Design the Unique Key

CRITICAL: unique_key must be truly unique in your data.

bash

# Verify uniqueness BEFORE creating model
dbt show --inline "
  select {{ unique_key_column }}, count(*)
  from {{ source('schema', 'table') }}
  group by 1
  having count(*) > 1
  limit 10
"

If duplicates exist:

Add more columns to make composite key
Add deduplication logic in model
Use
```
delete+insert
```
instead of
```
merge
```

5. Write the Incremental Model

sql

{{
    config(
        materialized='incremental',
        incremental_strategy='merge',  -- or append, delete+insert
        unique_key='id',               -- MUST be unique
        on_schema_change='append_new_columns'  -- handle new columns
    )
}}

select
    id,
    column_a,
    column_b,
    updated_at
from {{ source('schema', 'table') }}

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

6. Build with Full Refresh First

ALWAYS verify with full refresh before trusting incremental logic.

bash

# First run: full refresh to establish baseline
dbt build --select <model_name> --full-refresh

# Verify output
dbt show --select <model_name> --limit 10
dbt show --inline "select count(*) from {{ ref('model_name') }}"

7. Test Incremental Logic

bash

# Run incrementally (no --full-refresh)
dbt build --select <model_name>

# Verify row count changed appropriately
dbt show --inline "select count(*) from {{ ref('model_name') }}"

8. Handle Schema Changes

Set

on_schema_change

based on your needs:

Setting	Behavior
`ignore` (default)	New columns in source are ignored
`append_new_columns`	New columns added to target
`sync_all_columns`	Target schema matches source exactly
`fail`	Error if schema changes

Common Incremental Problems

Problem: Merge Fails with Duplicate Key

Symptom: "Cannot MERGE with duplicate values"

Cause: Multiple rows with same unique_key in source or target.

Fix:

sql

-- Add deduplication using a CTE (cross-database compatible)
with deduplicated as (
    select *,
        row_number() over (partition by id order by updated_at desc) as rn
    from {{ source('schema', 'table') }}
    {% if is_incremental() %}
    where updated_at > (select max(updated_at) from {{ this }})
    {% endif %}
)
select * from deduplicated where rn = 1

Problem: No Partition Pruning (Full Table Scan)

Symptom: Incremental runs take as long as full refresh.

Cause: Dynamic date filter prevents partition pruning.

Fix:

sql

{% if is_incremental() %}
-- Use static date instead of subquery for partition pruning
where updated_at >= {{ dbt.dateadd('day', -3, dbt.current_timestamp()) }}
  and updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Problem: Late-Arriving Data is Missed

Symptom: Some records never appear in incremental model.

Cause: Filtering by max(updated_at) misses late arrivals.

Fix: Use a lookback window with a fixed offset from current date:

sql

{% if is_incremental() %}
-- Lookback 3 days to catch late-arriving data
where updated_at >= {{ dbt.dateadd('day', -3, dbt.current_timestamp()) }}
{% endif %}

Alternatively, use a variable for the lookback period:

sql

{% set lookback_days = 3 %}

{% if is_incremental() %}
where updated_at >= {{ dbt.dateadd('day', -lookback_days, dbt.current_timestamp()) }}
{% endif %}

Problem: Schema Drift Causes Errors

Symptom: "Column X not found" after source adds column.

Fix: Set

on_schema_change='append_new_columns'

in config.

Problem: Data Drift Over Time

Symptom: Counts diverge between incremental and full refresh.

Fix: Schedule periodic full refresh:

bash

# Weekly full refresh
dbt build --select <model_name> --full-refresh

Incremental Strategy Reference

Append (Simplest)

sql

{{ config(materialized='incremental', incremental_strategy='append') }}

select * from {{ source('events', 'raw') }}
{% if is_incremental() %}
where event_timestamp > (select max(event_timestamp) from {{ this }})
{% endif %}

No unique_key needed
Fastest performance
Only use for append-only data (logs, events, immutable records)

Merge (Default)

sql

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='id'
) }}

select * from {{ source('crm', 'contacts') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

Requires unique_key
Handles updates and inserts
Most common strategy

Delete+Insert (Batch Updates)

sql

{{ config(
    materialized='incremental',
    incremental_strategy='delete+insert',
    unique_key='id'
) }}

select * from {{ source('orders', 'raw') }}
{% if is_incremental() %}
where order_date >= {{ dbt.dateadd('day', -7, dbt.current_timestamp()) }}
{% endif %}

Deletes all matching rows first
Good for reprocessing batches
Use when merge has duplicate key issues

Insert Overwrite (Partitioned)

sql

{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={'field': 'event_date', 'data_type': 'date'}
) }}

select * from {{ source('events', 'raw') }}
{% if is_incremental() %}
where event_date >= {{ dbt.dateadd('day', -3, dbt.current_timestamp()) }}
{% endif %}

Replaces entire partitions
Best for partitioned tables in BigQuery/Spark
No unique_key needed (operates on partitions)

Anti-Patterns

Using incremental for small tables (< 10M rows)
Not testing with full-refresh first
Using append strategy when data can be updated
Not verifying unique_key uniqueness
Relying on exact timestamp match without lookback
Never running full refresh (causes data drift)
Using merge with non-unique keys

Testing Checklist

Model runs with
```
--full-refresh
```
Model runs incrementally (without flag)
unique_key verified as truly unique
Row counts reasonable after incremental run
Late-arriving data handled (lookback window)
Schema changes handled (on_schema_change set)
Periodic full refresh scheduled

developing-incremental-models

NPX Install

Tags

SKILL.md Content

dbt Incremental Model Development

When to Use Incremental

Critical Rules

Workflow

1. Confirm Incremental is Needed

2. Understand the Source Data Pattern

3. Choose the Right Strategy

4. Design the Unique Key

5. Write the Incremental Model

6. Build with Full Refresh First

7. Test Incremental Logic

8. Handle Schema Changes

Common Incremental Problems

Problem: Merge Fails with Duplicate Key

Problem: No Partition Pruning (Full Table Scan)

Problem: Late-Arriving Data is Missed

Problem: Schema Drift Causes Errors

Problem: Data Drift Over Time

Incremental Strategy Reference

Append (Simplest)

Merge (Default)

Delete+Insert (Batch Updates)

Insert Overwrite (Partitioned)

Anti-Patterns

Testing Checklist