Developing Incremental Models

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 | 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

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

Workflow

1. Confirm Incremental is Needed

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

If 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.

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

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

7. Test Incremental Logic

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

# 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:

-- 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:

{% 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:

{% 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:

{% 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:

# Weekly full refresh
dbt build --select  --full-refresh

Incremental Strategy Reference

Append (Simplest)

{{ 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)

{{ 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)

{{ 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)

{{ 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 (onschemachange set)
• [ ] Periodic full refresh scheduled

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: AltimateAI
• Source: AltimateAI/data-engineering-skills
• License: MIT
• Homepage: https://www.altimate.ai/

Install and usage instructions live in the source repository linked above.