The short version
On dbt-postgres, merge runs one Postgres MERGE that updates rows whose unique_key already exists and inserts the rest; delete+insert deletes every target row whose key is in the new batch, then inserts the batch; append inserts the batch and ignores unique_key entirely, so a changed row shows up twice. With a clean, unique key, merge and delete+insert leave the same table. They differ when the key is not unique: merge fails with "MERGE command cannot affect row a second time", delete+insert succeeds and keeps the duplicates. None of the three removes rows that were deleted in the source.
I ran all of this on dbt-core 1.11.7 with dbt-postgres 1.10.0 against PostgreSQL 16.14. The SQL blocks below are copied from target/run/, blank lines trimmed.
Which strategies dbt-postgres supports
The Postgres configurations page lists four: append (the default without a unique_key), merge, delete+insert (the default with one) and microbatch. It gives no version for merge, but dbt's merge on Postgres compiles to a MERGE statement, and MERGE arrived in PostgreSQL 15. On a 14 or older server, stay with delete+insert.
For is_incremental(), {{ this }} and lookback windows, see dbt incremental models, shown with real runs.
The test model
One source table, raw_orders, 1,000 rows. Three copies of the same model, orders_append, orders_delete_insert and orders_merge, differing only in incremental_strategy. All three have the same unique_key:
{{ config(
materialized='incremental',
incremental_strategy='merge', -- or 'delete+insert', or 'append'
unique_key='order_id'
) }}
select order_id, customer_id, status, amount, updated_at
from {{ source('shop', 'raw_orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
Merge vs delete+insert vs append: the SQL each one runs
After the first build I changed 5 orders from new to paid and added 3 new ones, then ran the project:
UPDATE 5
INSERT 0 3
21:36:22 1 of 3 OK created sql incremental model ...orders_append [INSERT 0 8 in 0.18s]
21:36:22 2 of 3 OK created sql incremental model ...orders_delete_insert [INSERT 0 8 in 0.05s]
21:36:22 3 of 3 OK created sql incremental model ...orders_merge [MERGE 8 in 0.06s]
In each case dbt first wrote the 8 selected rows to a temporary table, then ran one of these. append:
insert into "postgres"."dbt_incremental_merge_vs_delete_insert"."orders_append" ("order_id", "customer_id", "status", "amount", "updated_at")
(
select "order_id", "customer_id", "status", "amount", "updated_at"
from "orders_append__dbt_tmp173622372298"
)
delete+insert:
delete from "postgres"."dbt_incremental_merge_vs_delete_insert"."orders_delete_insert" as DBT_INTERNAL_DEST
where (order_id) in (
select distinct order_id
from "orders_delete_insert__dbt_tmp173622526515" as DBT_INTERNAL_SOURCE
);
insert into "postgres"."dbt_incremental_merge_vs_delete_insert"."orders_delete_insert" ("order_id", "customer_id", "status", "amount", "updated_at")
(
select "order_id", "customer_id", "status", "amount", "updated_at"
from "orders_delete_insert__dbt_tmp173622526515"
)
merge:
merge into "postgres"."dbt_incremental_merge_vs_delete_insert"."orders_merge" as DBT_INTERNAL_DEST
using "orders_merge__dbt_tmp173622579636" as DBT_INTERNAL_SOURCE
on ((DBT_INTERNAL_SOURCE.order_id = DBT_INTERNAL_DEST.order_id))
when matched then update set
"order_id" = DBT_INTERNAL_SOURCE."order_id","customer_id" = DBT_INTERNAL_SOURCE."customer_id","status" = DBT_INTERNAL_SOURCE."status","amount" = DBT_INTERNAL_SOURCE."amount","updated_at" = DBT_INTERNAL_SOURCE."updated_at"
when not matched then insert
("order_id", "customer_id", "status", "amount", "updated_at")
values
("order_id", "customer_id", "status", "amount", "updated_at")
The result:
model | rows_ | keys
append | 1008 | 1003
delete+insert | 1003 | 1003
merge | 1003 | 1003
order_id | status | updated_at (orders_append)
1 | new | 2026-10-01 00:01:00+00:00
1 | paid | 2026-10-02 09:00:00+00:00
append had a unique_key in its config and did nothing with it. The docs say as much: append "doesn't check for duplicates or verify whether a record already exists in the destination". Use it for event tables where a row never changes once written.
Merge and delete+insert agree here. The difference is mechanical: merge updates the matched row in place, and delete+insert removes it and writes a new one. With a unique key the end state is identical.
What a non-unique unique_key does to each strategy
This is where they part. Order 6 got two changes in the same load, paid at 10:05 and shipped at 10:10, so the batch carried order_id = 6 twice:
3 of 3 ERROR creating sql incremental model ...orders_merge [ERROR in 0.06s]
Database Error in model orders_merge (models/orders_merge.sql)
MERGE command cannot affect row a second time
HINT: Ensure that not more than one source row matches any one target row.
m | order_id | status | updated_at
append | 6 | new | 2026-10-01 00:06:00+00:00
append | 6 | paid | 2026-10-02 10:05:00+00:00
append | 6 | shipped | 2026-10-02 10:10:00+00:00
delete+insert | 6 | paid | 2026-10-02 10:05:00+00:00
delete+insert | 6 | shipped | 2026-10-02 10:10:00+00:00
merge | 6 | new | 2026-10-01 00:06:00+00:00
Merge failed and rolled back, so orders_merge still holds the old row. That is Postgres following the standard: the MERGE notes say a target row should join to at most one source row, and a second UPDATE or DELETE of the same row raises a cardinality violation. delete+insert ran green: it deleted the old row once and inserted both new ones. dbt's own page describes delete+insert as useful "when a unique_key isn't truly unique"; what that means in practice is it will not stop you.
The merge failure has a gap. When the duplicated key is brand new, nothing in the target matches, so there is no second update to catch. Order 1004 arrived twice in one batch:
3 of 3 OK created sql incremental model ...orders_merge [MERGE 3 in 0.06s]
m | order_id | status | updated_at
delete+insert | 1004 | new | 2026-10-02 10:20:00+00:00
delete+insert | 1004 | paid | 2026-10-02 10:25:00+00:00
merge | 1004 | new | 2026-10-02 10:20:00+00:00
merge | 1004 | paid | 2026-10-02 10:25:00+00:00
Both strategies inserted two rows. Postgres docs say a repeated INSERT causes a uniqueness violation, which needs a unique index on the target, and my models had none. The next change to order 1004 shows the other difference:
2 of 3 OK created sql incremental model ...orders_delete_insert [INSERT 0 1 in 0.06s]
3 of 3 OK created sql incremental model ...orders_merge [MERGE 2 in 0.05s]
m | order_id | status | updated_at
delete+insert | 1004 | shipped | 2026-10-02 11:00:00+00:00
merge | 1004 | shipped | 2026-10-02 11:00:00+00:00
merge | 1004 | shipped | 2026-10-02 11:00:00+00:00
delete+insert deleted both copies and the table healed. Merge updated both copies (MERGE 2) and now holds two identical rows. The incremental models page warns that a key with more than one row in the table or the new rows "may fail depending on your database and incremental strategy". On Postgres it fails only for merge, and only some of the time.
The fix is the same for both: make the batch unique before dbt sees it. On Postgres, DISTINCT ON with ORDER BY ... updated_at desc keeps the latest row per key:
select distinct on (order_id)
order_id, customer_id, status, amount, updated_at
from {{ source('shop', 'raw_orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}
order by order_id, updated_at desc
With order 30 arriving twice, the plain merge model failed again and this one wrote MERGE 1, one row, shipped. Add a unique test on the key so duplicates that came in before the fix get caught.
A composite unique_key
For an order-lines table the grain is order_id plus line_no. The unique_key reference says to pass a list. dbt turned unique_key=['order_id', 'line_no'] into a row comparison for delete+insert:
delete from "postgres"."dbt_incremental_merge_vs_delete_insert"."lines_delete_insert" as DBT_INTERNAL_DEST
where (order_id, line_no) in (
select distinct order_id, line_no
from "lines_delete_insert__dbt_tmp173727621301" as DBT_INTERNAL_SOURCE
);
and an and of equalities for merge:
on (
DBT_INTERNAL_SOURCE.order_id = DBT_INTERNAL_DEST.order_id
) and (
DBT_INTERNAL_SOURCE.line_no = DBT_INTERNAL_DEST.line_no
)
An updated line and a new line went through both models correctly. Then a gift line with line_no null arrived, and later an update to it:
m | order_id | line_no | qty | updated_at
delete+insert | 7 | None | 1 | 2026-10-03 08:00:00+00:00
delete+insert | 7 | None | 2 | 2026-10-03 09:00:00+00:00
merge | 7 | None | 1 | 2026-10-03 08:00:00+00:00
merge | 7 | None | 2 | 2026-10-03 09:00:00+00:00
null = null is not true in SQL, so neither the in nor the on matched and both models kept the old version. The docs say the same: key columns should not contain nulls, or the model "may fail to match rows and generate duplicate rows". Coalesce the column in the model.
Rows deleted in the source: none of them delete
I deleted orders 10 and 11 from raw_orders and added one unrelated order:
DELETE 2
INSERT 0 1
1 of 3 OK ...orders_append [INSERT 0 1 in 0.18s]
2 of 3 OK ...orders_delete_insert [INSERT 0 1 in 0.05s]
3 of 3 OK ...orders_merge [MERGE 1 in 0.06s]
m | order_id | status
append | 10 | new
append | 11 | new
delete+insert | 10 | new
delete+insert | 11 | new
merge | 10 | new
merge | 11 | new
delete+insert deletes target rows whose key is in the new batch, and a row that vanished from the source is never in the batch. dbt's merge has no delete branch either. Postgres 16 MERGE has only WHEN MATCHED and WHEN NOT MATCHED in its synopsis; WHEN NOT MATCHED BY SOURCE, which could delete target rows with no source row, is in PostgreSQL 17, and dbt's generated SQL above does not use it.
dbt's CDC guide says it directly: incremental models must handle deletes in your merge or a separate delete statement. With hard deletes and no tombstone rows, the simplest separate statement is a post_hook, which on Postgres runs in the same transaction as the build:
post_hook="delete from {{ this }} t
where not exists (select 1 from {{ source('shop', 'raw_orders') }} s
where s.order_id = t.order_id)"
DELETE 3
1 of 1 OK created sql incremental model ...orders_merge_deletes [MERGE 0 in 0.20s]
raw | model
1000 | 1000
The 3 deleted orders left the model, though dbt's status line says MERGE 0 because it reports the model statement, not the hook. The hook reads the whole source and the whole model on every run, which gives back part of what incremental saved. If your loader writes a deleted flag instead, filter on it. If you need to keep the history of deleted rows, that is a snapshot's job: dbt incremental vs snapshot.
incremental_predicates: faster, and a duplicate trap
incremental_predicates is a list of SQL expressions that dbt adds to the statement without checking their syntax, an advanced option for when data volume is large enough to justify performance work. I gave both strategies the same one:
incremental_predicates=["DBT_INTERNAL_DEST.updated_at > timestamptz '2026-10-01 12:00+00'"]
delete+insert appended it to the delete:
where (order_id) in (
select distinct order_id
from "pred_delete_insert__dbt_tmp173727790510" as DBT_INTERNAL_SOURCE
)
and DBT_INTERNAL_DEST.updated_at > timestamptz '2026-10-01 12:00+00'
merge put it in the join condition:
on (DBT_INTERNAL_DEST.updated_at > timestamptz '2026-10-01 12:00+00') and ((DBT_INTERNAL_SOURCE.order_id = DBT_INTERNAL_DEST.order_id))
Then two orders were refunded: order 900, last loaded at 15:00 on October 1, inside the predicate, and order 20, last loaded at 00:20, outside it:
m | order_id | status | updated_at
delete+insert | 20 | new | 2026-10-01 00:20:00+00:00
delete+insert | 20 | refunded | 2026-10-03 08:00:00+00:00
delete+insert | 900 | refunded | 2026-10-03 08:00:00+00:00
merge | 20 | new | 2026-10-01 00:20:00+00:00
merge | 20 | refunded | 2026-10-03 08:00:00+00:00
merge | 900 | refunded | 2026-10-03 08:00:00+00:00
Order 20 is now in both tables twice. The predicate hid the old row, so delete+insert did not delete it and merge treated the refund as not matched and inserted it. Use a predicate only on a column that tells you how old a row can be when it changes again, such as an event date for late events with a known limit, and never narrower than that.
Timings at 500,000 rows, with and without an index on the key
big_raw held 500,000 orders. Each round updated 100,000 existing orders and added 100,000 new ones, so each model took 200,000 rows. I then rebuilt the source at 500,000 rows and repeated with an index on order_id, added through dbt-postgres' indexes config. dbt time is what dbt printed; statement time is the SQL status line in logs/dbt.log for the final statement (for delete+insert, one line covers the delete and insert sent together).
| 200,000-row batch, 3 runs | dbt time, no index | statement, no index | dbt time, index | statement, index |
|---|---|---|---|---|
| append | 0.34, 0.33, 0.33s | 0.12, 0.09, 0.09s | 0.52, 0.49, 0.54s | 0.29, 0.26, 0.27s |
| delete+insert | 0.46, 0.50, 0.52s | 0.34, 0.37, 0.40s | 0.66, 0.71, 0.74s | 0.55, 0.57, 0.60s |
| merge | 0.55, 0.54, 0.61s | 0.42, 0.40, 0.46s | 0.66, 0.60, 0.66s | 0.53, 0.46, 0.52s |
With a batch that size the index made every strategy slower: each of the 200,000 writes also had to update it. I did not run EXPLAIN on the large batch. Then the same again with 2,000-row batches (1,000 updates, 1,000 new):
| 2,000-row batch, 3 runs | dbt time, no index | statement, no index | dbt time, index | statement, index |
|---|---|---|---|---|
| append | 0.21, 0.19, 0.19s | 0.002, 0.001, 0.002s | 0.20, 0.21, 0.21s | 0.007, 0.009, 0.007s |
| delete+insert | 0.12, 0.12, 0.12s | 0.039, 0.043, 0.040s | 0.12, 0.12, 0.13s | 0.046, 0.047, 0.045s |
| merge | 0.18, 0.18, 0.19s | 0.094, 0.097, 0.107s | 0.14, 0.13, 0.13s | 0.052, 0.049, 0.048s |
Here the index halved the merge statement. append ran first in each invocation and nearly all of its 0.2 seconds is outside the insert. EXPLAIN on a 2,000-row temp table against the indexed models showed both strategies probing the index:
Merge on big_merge dbt_internal_dest
-> Nested Loop Left Join
-> Seq Scan on src dbt_internal_source
-> Index Scan using d39fb70fae019a50802297cf983a7a84 on big_merge dbt_internal_dest
Index Cond: (order_id = dbt_internal_source.order_id)
dbt's strategy page says merge can be expensive for large tables because it scans the entire destination. On Postgres with an index on the key and a small batch it did not. At this size the gaps are tenths of a second: pick the strategy for its behaviour, and add the index when batches are small next to the table.
Which one to pick
append for immutable events. merge when the key is truly unique and you would rather have a failed run than a silent duplicate, plus merge_update_columns if some columns must never be overwritten. delete+insert on Postgres older than 15, or when you replace a whole set of rows per key. Deduplicate the batch for both. How a hand-written MERGE from a stored procedure maps onto these configs is in MERGE statements in dbt.