Choosing a dbt incremental strategy for your dataset

1.Three factors that decide the strategy

An incremental model only transforms the rows that are new or changed since its last run. What happens when those rows reach the existing table is up to the incremental strategy. dbt can append them, upsert them on a key, delete and reinsert them, or replace whole partitions. Whether a model should be incremental in the first place is a separate question, covered under [core materializations](lesson:1.2). This page assumes the answer is yes and asks how it should write.

No strategy is best in every case. Three properties of the dataset and its platform decide the choice:

- Volume: how many rows arrive per run, and how large the destination table already is. - Key reliability: whether the unique_key really identifies exactly one row and is never null. - Platform support: which strategies the adapter for your data platform implements.

A strategy can pass on one factor and fail on another. merge handles changing rows well, but it gets slow on a huge table. append is cheap, but it duplicates any row it sees twice. Weigh all three before you pick.

You set the strategy with the incremental_strategy config. It can go in a single model's config block, or apply to every model from dbt_project.yml. A project-wide default makes sense when most models share the same shape. A model whose data behaves differently overrides that default in its own config.

models:
  +incremental_strategy: "insert_overwrite"

Sources1

2.Row-level strategies: append, merge and delete+insert

append is the cheapest strategy. It inserts the selected rows and never updates or deletes anything already in the table. That suits data that is never revised, such as raw events. For anything else it is risky, because append never checks whether a row is already there. A record that shows up in two runs is inserted twice, and nothing warns you.

merge inserts rows whose unique_key is new and updates rows whose key already exists. That is SCD type 1 behaviour: a change overwrites the old values and no history is kept. Because it matches on the key, merge also stops duplicates that append would let through. By default it overwrites a matched row completely. Two configs narrow that. merge_update_columns updates only the columns you list, and merge_exclude_columns leaves the columns you list untouched. Excluding a column is useful when an original value, such as when a record was first created, should never be overwritten.

{{
  config(
    materialized = 'incremental',
    unique_key = 'id',
    merge_exclude_columns = ['created_at'],
    ...
  )
}}

delete+insert first deletes the target rows that match the incoming keys, then inserts the new versions. Every updated record is replaced in full, so a partial update can never happen. It can be less efficient on large datasets. Two situations still make it the right choice: the key is not truly unique, or the platform has no merge. dbt itself takes this route on warehouses without merge statements, running a delete followed by an insert.

None of these strategies keeps history. If the requirement is SCD type 2, with every past version of a row preserved, use a snapshot rather than delete+insert. Snapshots are covered in [snapshots in YAML](lesson:1.10).

Sources12

3.When the unique key is missing, null or not unique

On most adapters, a missing key does not cause an error. If unique_key is left out, merge and delete+insert fall back to append. The run succeeds and duplicates build up without any warning. BigQuery is the exception for merge: a merge model there must have a key, and leaving it out leads to ambiguous or failing behaviour. insert_overwrite is different again, because it never uses unique_key. It works on whole partitions, not individual rows.

A key can also exist and still be unreliable. If any key column contains nulls, dbt cannot match those rows to existing ones, and the model creates duplicates. You can fix this by wrapping each key column in coalesce, or by building a single-column surrogate key with dbt_utils.generate_surrogate_key. When the grain needs several columns, pass them as a list, for example unique_key = ['user_id', 'session_number']. Avoid a string expression such as concat(user_id, session_number): with a list, dbt can template the columns correctly for each database.

Duplicate key values cause a different failure. If a key appears on more than one row, either in the existing table or in the incoming rows, the run may fail, depending on the database and strategy. The guard is a unique and not_null test on the key in the source or staging layer. If the grain really allows many rows per key value, such as many rows per date_day, merge is the wrong tool. Use delete+insert keyed on that column, so every row for an incoming day is replaced together.

{{
  config(
    materialized='incremental',
    unique_key='date_day',
    incremental_strategy=' ? ',
    ...
  )
}}

Sources345

4.Large tables, partitions and late-arriving data

merge is the usual starting point, but its cost grows with the destination table. To decide what to update and what to insert, it scans the entire target. On a small or medium table that cost doesn't matter. On a fact table of billions of rows, every run pays for that scan.

incremental_predicates keeps merge usable at that scale. It adds SQL conditions that limit how much of the existing table the merge reads. DBT_INTERNAL_DEST refers to the target table and DBT_INTERNAL_SOURCE to the new rows. dbt does not check the syntax of these expressions, so a typo only shows up at run time. Clustering the table on the same column helps the warehouse skip data it doesn't need.

models:
  - name: my_incremental_model
    config:
      materialized: incremental
      unique_key: id
      # this will affect how the data is stored on disk, and indexed to limit scans
      cluster_by: ['session_start']
      incremental_strategy: merge
      # this limits the scan of the existing table to the last 7 days of data
      incremental_predicates: ["DBT_INTERNAL_DEST.session_start > dateadd(day, -7, current_date)"]

When the table is partitioned by date or another key, insert_overwrite often fits better than merge. It replaces only the partitions the new data touches, as whole units, and leaves the rest of the table alone. That makes it a good way to refresh recent or corrected data without rebuilding the full table. It needs no row key at all.

microbatch is built for very large time-series tables, such as multi-year clickstream, IoT or point-of-sale facts. It splits each run into time-based batches, defined by an event_time column, at a grain such as hourly or daily. Each batch can be retried on its own, which makes backfills safer. Choose it when:

- tables reach billions of rows - backfills are slow and risky with the other strategies - late-arriving data is common.

Don't choose it when there is no reliable event_time column, or when you need more control over the incremental logic. The batch configs are covered in [microbatch models](lesson:1.14).

Late-arriving data needs handling whichever strategy you choose. A merge model that filters on event_ts > max(event_ts) will skip rows that land after a later row has already been loaded. Widen the filter with a lookback window, for example one hour before the maximum. microbatch has this built in through lookback, which reprocesses the batches just before the latest one and defaults to 1.

Sources167

5.Matching the strategy to the adapter

A strategy is only an option if the adapter implements it. dbt-databricks and dbt-snowflake support all five built-in strategies: append, merge, delete+insert, insert_overwrite and microbatch. Microsoft Fabric does not support microbatch yet. If a platform has no merge, delete+insert is the fallback for full row replacement.

microbatch does not work the same way on every adapter. On each one, dbt uses the most efficient way available to replace a full batch. That changes what you have to configure. On dbt-postgres, microbatch runs as a merge, so it needs a unique_key to match rows. On BigQuery and Spark, it runs as insert_overwrite, so it needs partition_by.

In practice, start with merge, because it suits most use cases. If the source tables receive frequent updates rather than only appends, move to the warehouse's native change data capture. On Snowflake that means Streams, read by a merge model. Use microbatch only once the scale is massive.

Sources67

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.A merge model with no unique_key fails, so a missing key will always be noticed.Why is that wrong?

   On most adapters, merge without a unique_key behaves like append. The run succeeds and duplicates collect without any warning. BigQuery is the exception.

   Covered in When the unique key is missing, null or not unique

2. 2.delete+insert keeps the old version of a row, so it gives you SCD type 2 history.Why is that wrong?

   delete+insert overwrites rows and does not track history. SCD2 needs snapshots.

   Covered in Row-level strategies: append, merge and delete+insert

3. 3.merge stays cheap however large the table gets, because only the new rows are processed.Why is that wrong?

   merge scans the whole destination table to decide what to update or insert, so its cost grows with the table. Use incremental_predicates, insert_overwrite or microbatch at large scale.

   Covered in Large tables, partitions and late-arriving data

4. 4.A microbatch config that works on Snowflake will work unchanged on Postgres or BigQuery.Why is that wrong?

   Each adapter implements microbatch differently. Postgres also needs unique_key, and BigQuery and Spark also need partition_by.

   Covered in Matching the strategy to the adapter