Merge Strategy Incremental in dbt: How It Works and When to Use

In dbt, the merge strategy for incremental models updates existing rows and inserts new rows by matching on a unique key. It uses SQL MERGE statements to efficiently combine new data with existing data, avoiding full table reloads.

How It Works

The merge strategy in dbt incremental models works like updating a contact list on your phone. Imagine you have an old list of contacts and a new list with some updated phone numbers and some new contacts. Instead of rewriting the entire list, you only update the contacts that changed and add the new ones.

In dbt, this is done using a SQL MERGE statement. It compares rows in the existing table with rows in the new data based on a unique key (like a contact ID). If a match is found, it updates the existing row; if not, it inserts the new row. This saves time and resources by only changing what is necessary.

Example

This example shows a dbt incremental model using the merge strategy. It updates or inserts rows based on the id column.

jinja-sql

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

select
    id,
    name,
    updated_at
from source_table

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

Output

This code will update rows in the target table where the <code>id</code> matches and the <code>updated_at</code> is newer, or insert new rows if the <code>id</code> does not exist.

When to Use

Use the merge strategy when you want to efficiently update existing records and add new ones without rebuilding the entire table. It is ideal for tables with a unique key and frequent incremental updates, such as customer data, event logs, or product catalogs.

For example, if you have a sales table that receives daily updates with new transactions and corrections to previous data, the merge strategy lets you keep your table current without expensive full reloads.

Key Points

• The merge strategy uses SQL MERGE statements to update and insert rows.
• It requires a unique key to match rows between new and existing data.
• It improves performance by avoiding full table rebuilds.
• Best for tables with frequent incremental changes and unique identifiers.

Key Takeaways

The merge strategy updates existing rows and inserts new ones using a unique key.

It uses SQL MERGE statements to efficiently combine data without full reloads.

Ideal for incremental updates on tables with unique identifiers.

Helps keep data fresh while saving processing time and resources.