dbt incremental merge vs delete insert strategy

TL;DR

Merge (dbt's default incremental strategy) upserts: it updates changed rows and inserts new ones, matched on the unique key. Delete+insert deletes the affected key range and re-inserts, which is simpler and sometimes faster when most of the partition changes. Pick merge when updates are scattered; pick delete+insert when changes cluster in recent partitions or your warehouse merge is slow.

The query

dbt incremental merge vs delete insert strategy

Use this when

• Building a new incremental model and choosing a materialization strategy
• Incremental runs are slow and you suspect the strategy
• Late-arriving data updates historical partitions

Not for

• Append-only event tables (use the append strategy)
• Models small enough to full-refresh cheaply
• Sources that never change after landing

Steps

1. Understand what merge does. dbt builds a temp table of new rows, then issues a MERGE matching on unique_key. Rows that match get updated, new ones get inserted. It touches only matched rows, so it is efficient for scattered changes.

Expected output: you can explain the merge statement dbt generates for your model.

1. Understand delete+insert. With a custom strategy (or on adapters that support it), dbt deletes rows in the target that fall in the incoming key range, then inserts all incoming rows. No row-by-row matching, which avoids merge overhead on some warehouses.

Expected output: you can state when your warehouse handles deletes plus bulk inserts better than merges.

1. Look at your change pattern. If 1% of rows change across the whole table, merge wins. If 80% of this month's partition rewrites every run, delete+insert on the partition is usually faster and simpler to reason about.

Expected output: a measured or estimated fraction of rows changed per run, which points at the right strategy.

1. Set the strategy explicitly in the model config so the choice is visible, and add a test on the unique key. Do not leave it as tribal knowledge.

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

Expected output: the model config names the strategy, and dbt test passes on the unique key.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_0oWEphyh6rmeH4qAW4DjiA