Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. insert_overwrite vs merge

dbt · Incremental Models & Performance

insert_overwrite vs merge

Harddbt-24
insert_overwritemergepartitionsincremental

Question

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

Solution

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
batch of changed order_ids → MERGE into fct_orders

insert_overwrite

  • Recomputes and replaces whole partitions (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
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
  • Natural key 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. Late-arriving data with a lookback window often pairs with insert_overwrite."

PreviousNext