In dbt, how do the append, merge, delete+insert and insert_overwrite incremental strategies differ?

## Where the strategy sits An incremental dbt run happens in two parts. Your model SQL, filtered by the `is_incremental()` block, produces a set of candidate rows into a temporary relation. Then dbt executes a second statement that combines that relation with the existing target table. `incremental_strategy` chooses that second statement: ```sql {{ config( materialized='incremental', incremental_strategy='merge', unique_key='order_id' ) }} ``` The strategy is where idempotency lives. Your filter decides *what* gets reprocessed; the strategy decides whether reprocessing the same row twice corrupts the table. ## append Plain `insert into target select * from candidates`. No key matching, no scan of the target, nothing to go wrong at write time — and no protection whatsoever. Rerun the model after a partial failure, widen a lookback window, or receive a restated record, and you get a second copy. It is the right choice for genuinely immutable event streams where the filter guarantees each row is emitted exactly once, and for staging layers where downstream models deduplicate anyway. It is the wrong choice anywhere a rerun is plausible, which in practice is most places. ## merge dbt generates a `MERGE INTO target USING candidates ON target.<unique_key> = candidates.<unique_key>` with a `WHEN MATCHED THEN UPDATE` and a `WHEN NOT MATCHED THEN INSERT`. Rerunning the same rows updates them in place rather than duplicating them, which is what makes the model safe to re-execute. Caveats worth naming in an interview: - Without a `unique_key`, dbt's merge has no matched branch and behaves as insert-only. - The merge joins candidates against the whole target on the key. On a huge fact table that scan can dominate the run; `incremental_predicates` lets you add conditions that restrict the target side so the engine can prune partitions or clusters. - Duplicate keys within the candidate set produce a nondeterministic merge — an error on some engines, an arbitrary winner on others. - It never deletes. Rows removed at source stay in the target. Merge is the default on Snowflake, BigQuery and Databricks with Delta. ## delete+insert Two statements: `delete from target where unique_key in (select unique_key from candidates)`, then `insert`. Semantically close to merge, and the practical option on adapters where merge is unavailable or where a bulk delete plus insert plans better than a row-matching merge — Redshift and Postgres workloads often land here. Its sharper edge is what happens between the two statements. In a warehouse without a transaction wrapping both, there is a window where rows have been deleted and not yet reinserted, and a reader can see missing data. Adapters differ in how they wrap this; it matters for tables that are queried continuously. ## insert_overwrite Identity is by **partition**, not by row key. dbt determines which partitions the candidate rows fall into and replaces those partitions wholesale, dropping whatever was there before. There is no key matching and no target-wide join, so it is typically the cheapest idempotent write on a large partitioned table. It requires the table to be partitioned — `partition_by` on BigQuery, a partitioned table on Spark or Databricks — and it changes the correctness contract: rerunning a day replaces that entire day. That is exactly right for a pipeline where each run recomputes complete days, and exactly wrong for a model that emits a partial slice of a day, because the missing rows are silently deleted. On BigQuery it comes in two shapes: a dynamic mode that derives the partitions from the candidate rows, and a static mode where you declare the partitions to replace via a `partitions` config, which lets the engine prune the overwrite scope precisely. ## Choosing A workable decision path: 1. Is the table partitioned by the same grain the run recomputes, and does each run produce complete partitions? → `insert_overwrite`, cheapest and robustly idempotent. 2. Otherwise, is there a reliable row identifier and does the source restate rows? → `merge` (or `delete+insert` if merge is unsupported or plans badly). 3. Is the stream strictly immutable, append-only, with a filter that cannot re-emit a row, and no rerun risk? → `append`. Write the choice down in the model, because the failure modes are silent: append duplicates, merge that never deletes, insert_overwrite that erases a partial partition. None of them raise an error at run time. ## Adapter reality Strategy support is not uniform. Snowflake supports append, merge and delete+insert; BigQuery supports merge and insert_overwrite; Spark and Databricks support append, insert_overwrite and merge on table formats that allow it; Postgres and Redshift default to append with delete+insert available. Newer dbt versions add a `microbatch` strategy (dbt 1.9) that splits an incremental run into time-bounded batches driven by an `event_time` config. Always confirm against the adapter's own documentation rather than carrying assumptions from one warehouse to another.