Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Four incremental strategies

dbt · Incremental Models & Performance

Four incremental strategies

Harddbt-21
incremental_strategymergeappendinsert_overwritedelete+insert

Question

Explain the four common dbt incremental strategies: append, merge, delete+insert, and insert_overwrite.

Solution

Incremental strategy = how the new batch is applied to the existing table. Exact names and availability depend on the adapter (Snowflake, BigQuery, Databricks, etc.).

1. append

Simply insert new rows. No updates to existing keys.

existing table + new rows → bigger table

Use when events are insert-only and duplicates are impossible (or acceptable).

2. merge

Match on unique_key; update matched rows, insert new ones (MERGE/UPSERT).

new batch                   ├─ key exists → UPDATE   └─ key new    → INSERT

Default mental model for slowly changing facts with updates.

3. delete+insert

Delete existing rows that match keys (or a chosen predicate), then insert the new batch. Useful when merge semantics are awkward or the adapter prefers this pattern.

4. insert_overwrite

Replace partitions (or whole segments) by overwriting partition data with the new batch. Popular on BigQuery/Spark-style warehouses with partition columns.

overwrite partition day=2024-01-15 with today's recompute for that day

Config sketch

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='order_id'
) }}

Interview tip: Map strategy to data shape: append for immutable events, merge for upserts by key, insert_overwrite for partition reprocessing, delete+insert when you need replace-by-key without a full merge feature.

PreviousNext