Skip to content
Tutorials Center
My learning

Topics

Lesson 29 of 41 ยท Incremental Models and Materializations

Incremental Strategies: merge, delete+insert, microbatch

Compare dbt's incremental strategies: append, merge, delete+insert, insert_overwrite and microbatch. See the SQL each one runs and how to choose.

When dbt has the new rows, it still has to decide how to put them into the existing table. That decision is the incremental strategy, set with incremental_strategy in the model config. Which strategies are available depends on the warehouse adapter, and the default differs between them. This lesson explains each one, with the SQL it boils down to.

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

append

The simplest strategy: insert into target select ... from new_rows. No key, no deduplication.

  • Good for immutable, append-only data such as event logs.
  • Dangerous if the same row can arrive twice: you get duplicates.
  • unique_key is ignored.

merge

Uses the warehouse's MERGE statement (Snowflake, BigQuery, Databricks, SQL Server and others): update rows that match unique_key, insert the rest.

SQL
merge into target t
using new_rows s
  on t.order_id = s.order_id
when matched then update set status = s.status, updated_at = s.updated_at
when not matched then insert (order_id, status, updated_at)
  values (s.order_id, s.status, s.updated_at)
  • Handles inserts and updates in one statement.
  • Can be slower on very large targets because the join scans the target.
  • Options: merge_update_columns to update only some columns, merge_exclude_columns to leave some alone.

delete+insert

Two statements: delete target rows whose key is in the batch, then insert the batch.

SQL
delete from target where order_id in (select order_id from new_rows);
insert into target select * from new_rows;

It works on warehouses without MERGE, and often beats merge on columnar warehouses. If the key is not truly unique in the batch, duplicates can survive, so deduplicate the batch first. The earlier lesson simulated exactly this pattern.

You can compare the two outcomes on SQLite, which lacks MERGE but has an equivalent INSERT ... ON CONFLICT:

SQL Upsert: merge in SQLite clothes

Press Run (or Ctrl + Enter) to see the result here.

Order 1 is updated and order 3 is inserted, the same result merge and delete+insert give.

insert_overwrite

Replaces entire partitions. dbt computes the partitions present in the batch (for example the last two dates) and overwrites exactly those. It is the cheapest strategy on warehouses with partitioned tables (BigQuery, Spark, Databricks, Hive-style), because it avoids row-level matching.

SQL
{{ config(
    materialized='incremental',
    incremental_strategy='insert_overwrite',
    partition_by={'field': 'event_date', 'data_type': 'date'}
) }}

Pick it when you can define a partition column and always recompute whole partitions. The simulation is "delete the days you are reloading, insert them again":

SQL Overwriting whole partitions (days)

Press Run (or Ctrl + Enter) to see the result here.

User 3 disappears from day 2, because the whole day was replaced: this is correct behavior for partition overwrites, and a reason to always load complete partitions.

microbatch

Available from dbt 1.9, microbatch splits a big incremental load into time-based batches (hour, day, month) that dbt processes one by one. You declare the time column and batch size, and dbt injects the time filter for you, so you do not write is_incremental().

SQL
{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='occurred_at',
    batch_size='day',
    begin='2024-01-01',
    lookback=2
) }}

select * from {{ ref('stg_events') }}

For this to work, upstream models that are ref'd need an event_time config too, so dbt can filter them per batch. Advantages: each batch is its own query (retry only the failed day), batches can run in parallel, and backfills are a command: dbt run --event-time-start "2024-03-01" --event-time-end "2024-03-31". It suits large time-series tables.

flowchart LR
  A["Batch rows"] --> B{"Strategy"}
  B -->|"append"| C["Insert only"]
  B -->|"merge"| D["Update matches, insert rest"]
  B -->|"delete+insert"| E["Delete keys, insert batch"]
  B -->|"insert_overwrite"| F["Replace whole partitions"]
  B -->|"microbatch"| G["One query per time batch"]

Same batch, five ways to land it.

Which strategy?

Situation Strategy
Append-only events, no repeats append
Rows update by key, warehouse supports MERGE merge
Rows update by key, no MERGE or MERGE is slow delete+insert
Time-partitioned table, whole days reloaded insert_overwrite
Huge time-series, need batches and easy backfills microbatch

Check your adapter's documentation for supported strategies and defaults before you rely on one.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. Which strategy ignores unique_key and only inserts?

    append just inserts the new rows, so it can create duplicates if rows repeat.
  2. 2. What does insert_overwrite replace?

    It overwrites each partition that the batch touches, which is cheap on partitioned tables.
  3. 3. What is special about microbatch?

    With event_time and batch_size, dbt splits the load into batches and filters inputs for you.
  4. 4. You reload day 2 with insert_overwrite and one user from day 2 is missing in the new data. What happens to that user's row?

    Partition overwrite means the partition now contains exactly the new data.

Summary

  • The strategy decides how new rows land: append, merge, delete+insert, insert_overwrite or microbatch.
  • merge and delete+insert use unique_key; insert_overwrite uses partitions.
  • microbatch filters by event_time and processes batches independently.
  • Strategies and defaults depend on the adapter.

Next: Late-Arriving Data and Full Refresh