dbt · Incremental Models & Performance

insert_overwrite vs merge

Hardinsert_overwritemergepartitionsincremental

Interview question

When would you choose insert_overwrite instead of merge for an incremental model?

How to answer

The short answer

Both update an incremental table, but they optimize for different change patterns.

merge

  • Matches rows by unique_key
  • Updates changed keys, inserts new keys
  • Great when a small % of historical keys change arbitrarily in time

insert_overwrite

  • Recomputes and replaces whole (e.g. event_date)
  • Ideal when you always reprocess "last N days" of a partitioned fact
  • Avoids row-level merge overhead when partition rewrite is cheaper
Output
recompute day=2024-06-01..2024-06-07
overwrite those partitions in place

Choose insert_overwrite when

  • Table is partitioned by a date (or similar) column
  • Late data is bounded (e.g. only last 3–7 days change)
  • Warehouse makes partition overwrite efficient (BigQuery, Spark/Databricks patterns)

Choose merge when

  • Updates are sparse across many old partitions
  • upserts matter more than partition rewrites
  • You lack a clean partition column for overwrite

Interview tip: "merge is key-oriented; insert_overwrite is partition-oriented. with a lookback window often pairs with insert_overwrite."