dbt Incremental Strategies

An incremental strategy controls exactly how dbt merges new data into an existing incremental table. Different strategies have different performance characteristics and compatibility with different warehouses. Choosing the right strategy reduces compute cost and ensures data accuracy.

The Four Main Strategies

Strategy        How It Works                            Best For
---------       ------------                            --------
append          Inserts new rows only, no updates       Immutable event logs
merge           Upserts: insert new + update existing   Records that change
delete+insert   Deletes matching rows, inserts fresh    Partitioned data
insert_overwrite Replaces entire partitions             BigQuery, Spark

append

The simplest strategy. dbt appends new rows to the existing table without checking for duplicates or updating existing rows. Use this only when your source data is truly immutable — events that are written once and never updated.

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

select
    event_id,
    event_type,
    event_timestamp
from {{ source('analytics', 'raw_events') }}

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

merge

The most flexible strategy. For each new row, dbt checks whether a row with the same unique_key already exists. If it does, dbt updates the existing row. If it does not, dbt inserts a new row. This is a standard SQL MERGE (also called UPSERT).

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

select
    order_id,
    customer_id,
    status,
    amount_dollars,
    updated_at
from {{ source('ecommerce', 'orders') }}

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

Merge Diagram

Existing table:            Incoming rows:         Result after merge:
order_id | status          order_id | status      order_id | status
---------|--------         ---------|--------     ---------|--------
1001     | pending         1001     | completed   1001     | completed ← updated
1002     | pending         1003     | pending     1002     | pending   ← unchanged
                                                 1003      | pending   ← new insert

delete+insert

dbt first deletes all rows from the target table that match the incoming rows (by unique_key), then inserts all incoming rows fresh. This approach is less efficient than merge but works on databases that do not support the MERGE statement natively.

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

insert_overwrite

Replaces entire partitions rather than individual rows. dbt determines which partitions contain incoming data and replaces those partitions entirely. This strategy requires your table to be partition-configured.

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

select
    order_id,
    order_date,
    amount_dollars
from {{ source('ecommerce', 'orders') }}

{% if is_incremental() %}
  where order_date >= date_sub(current_date(), interval 3 day)
{% endif %}

dbt deletes all rows in the last 3 day-partitions and replaces them with the new data. This is very efficient on BigQuery and Databricks because partition operations are optimized at the storage level.

Warehouse Strategy Compatibility

Strategy          Snowflake  BigQuery  Redshift  Postgres  Databricks
-----------       ---------  --------  --------  --------  ----------
append            Yes        Yes       Yes       Yes       Yes
merge             Yes        Yes       Yes       Yes       Yes
delete+insert     Yes        No        Yes       Yes       Yes
insert_overwrite  No         Yes       No        No        Yes

Specifying Merge Columns

By default, merge updates all columns when it finds a matching unique_key. You can restrict which columns get updated using merge_update_columns:

{{ config(
    materialized='incremental',
    unique_key='customer_id',
    incremental_strategy='merge',
    merge_update_columns=['email', 'city', 'updated_at']
    -- Does NOT update: customer_id, signup_date, original_source
) }}

Partial Incremental Predicates

Some warehouses support incremental_predicates to optimize the merge by scanning only relevant partitions of the target table:

{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_strategy='merge',
    incremental_predicates=[
        "DBT_INTERNAL_DEST.event_date >= dateadd(day, -7, current_date)"
    ]
) }}

This tells the MERGE to look only at the last 7 days of the target table, making the merge dramatically faster for large tables.

Choosing the Right Strategy

Scenario                              Strategy
---------------------------------     ---------------
Events that never change              append
Records with updates (orders, users)  merge
Database without MERGE support        delete+insert
BigQuery/Databricks with partitions   insert_overwrite