Incremental strategies (append, merge, delete+insert) in dbt

Introduction

Incremental strategies help update data efficiently by adding or changing only new or changed parts instead of reloading everything.

When you have a large dataset that updates regularly and want to save time by processing only new data.

When you want to keep your data warehouse fresh without reloading all data every time.

When you need to combine new data with existing data without losing old records.

When you want to fix or update some records in your data without deleting everything.

When you want to improve performance by avoiding full table reloads.

Syntax

dbt

incremental_strategy: append | merge | delete+insert

append adds only new rows to the existing table.

merge updates existing rows and inserts new rows based on a key.

delete+insert deletes matching rows and inserts fresh data.

Examples

This adds only new rows to the table without changing existing rows.

dbt

incremental_strategy: append

This updates rows where id matches and inserts new rows if id is new.

dbt

incremental_strategy: merge
unique_key: id

This deletes rows matching id in the new data, then inserts all new rows.

dbt

incremental_strategy: delete+insert
unique_key: id

Sample Program

This dbt model uses the merge strategy to update existing customers and add new ones based on customer_id. It selects only customers updated after the last run.

dbt

version: 2
models:
  - name: customers_incremental
    config:
      materialized: incremental
      incremental_strategy: merge
      unique_key: customer_id

-- SQL to update or insert customers
select
  customer_id,
  customer_name,
  updated_at
from source.customers
where updated_at > (select coalesce(max(updated_at), '1900-01-01') from {{ this }})

Important Notes

Use append when you only add new data and never update old rows.

merge requires a unique key to match rows for update or insert.

delete+insert can be slower but is useful when updates are complex.

Summary

Incremental strategies help update data efficiently without full reloads.

append adds new rows, merge updates and adds rows, delete+insert replaces matching rows.

Choose the strategy based on your data update needs and performance.