dbt Incremental Models: Strategies That Hold Up in Prod

Your model ran fast on day one, then the second week hit: late rows, a backfill request, a column add in the source, and now your dbt incremental model is dropping updates or spinning forever. This guide shows how to choose and operate the right incremental strategy—append, merge, delete+insert, and microbatch—so it holds up in production. We’ll cover unique_key correctness, lookback windows with is_incremental(), schema drift, and when not to use incremental at all. You’ll get concrete dbt SQL and patterns we use when standing up platforms for teams like those I’ve worked with. If you only need the headline: pick merge with a solid unique key, set a measured lookback, test for duplicates, plan for backfills, and monitor lag. The details—and the traps—are below.

What an incremental model in dbt really is (and when to use it)

An incremental model in dbt is a table built once, then updated by processing only new or changed data on subsequent runs. The goal is to reduce time and cost on large datasets, not to avoid thinking about correctness. It’s a materialization that trades complexity for speed. You still own the data contract and the failure modes.

Use incremental loading when:

• Your upstream source emits an append-only or mostly-immutable log (e.g., CDC, event stream) with a reliable timestamp or watermark.
• The table is big enough that rebuilding the entire table every day hurts SLAs or budgets.
• You can define a stable unique_key that represents the target row in your model.

Don’t use it when:

• The table is small or medium (e.g., a 40M-row orders table on an X-Small warehouse that still rebuilds in minutes). A full rebuild is simpler and safer.
• Your logic depends on re-scanning the whole dataset each time (e.g., dense window functions without a partitioning scheme).
• You can’t trust the source data to keep a stable primary key or last-updated watermark.

Incremental is not just a switch. You must configure the incremental materialization and choose behavior for late arrivals, duplicates, and schema changes. If you only append, you’ll be fine until the day a partner fixes a bug and re-sends a month of corrections. If you must incrementally recompute derived metrics, consider microbatching with a lookback window or a hybrid approach.

Append vs merge vs delete+insert vs microbatch: production trade-offs

Four patterns show up in real pipelines. Each behaves differently when the unexpected hits.

Strategy	How it works	Pros	Cons	Good for
append	Insert new rows only	Fast, simple	Can’t update existing rows; duplicates if key not enforced	Immutable event logs
merge	Upsert by unique_key	Handles late updates, idempotent	More expensive than pure insert; needs correct key	Orders, users, CDC
delete+insert	Delete changed partitions, reinsert window	Simple SQL, no adapter-specific MERGE needed	Wide deletes can be slow; requires clean partition filter	Partitioned facts
microbatch	Process since the last high-watermark with overlap	Controls blast radius; predictable runtime	Requires reliable watermark and window tuning	Large facts with late data

Example append config:

-- models/events.sql
{{
  config(
    materialized='incremental',
    incremental_strategy='append',
    on_schema_change='append_new_columns'
  )
}}
select * from {{ source('app','events_raw') }}
{% if is_incremental() %}
  where event_ts > (select coalesce(max(event_ts), '1900-01-01') from {{ this }})
{% endif %}

Example merge config (the common default where supported):

-- models/orders.sql
{{
  config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='order_id',
    on_schema_change='ignore'
  )
}}
select * from {{ ref('stg_orders') }}
{% if is_incremental() %}
  where updated_at > (select coalesce(max(updated_at), '1900-01-01') from {{ this }})
{% endif %}

Delete+insert shines on partitioned warehouses. You delete the partitions touched by your lookback, then insert the window. Microbatch is a scheduling pattern—break the window into chunks to keep predictable dbt runs. Choose the merge strategy when you must update existing rows safely; choose append when you truly never update existing.

is_incremental() and lookback windows that don’t lose late data

The core mistake is filtering exactly “since the last” value seen. Real sources drift and arrive late. Always overlap with a lookback window and deduplicate by unique_key. This pattern handles late-arriving data, keeps cost bounded, and avoids compaction jobs.

-- models/fct_payments.sql
{{
  config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key='payment_id'
  )
}}
with src as (
  select * from {{ source('app','payments') }}
  {% if is_incremental() %}
    -- Overlap 3 days to catch late updates; tune with observability
    where updated_at > dateadd(day, -3, (select coalesce(max(updated_at), '1900-01-01') from {{ this }}))
  {% endif %}
)
select ... from src;

How many days to go back? Measure it on your warehouse, not by folklore:

• Compute the distribution of updated_at - inserted_at (or CDC lag) in the source over 30 days.
• Pick a percentile that balances cost vs safety—e.g., the 99th percentile delay plus a buffer. Revisit monthly.
• Log the max/min update time processed each incremental run; alert if it regresses.

For partitioned tables (BigQuery or partitioned Snowflake data via date columns), align filters to the partition boundary, then add an extra partition overlap. Example for BigQuery SQL:

{% if is_incremental() %}
  where _PARTITIONDATE >= date_sub(current_date(), interval 4 day)
{% endif %}

When using dbt, prefer a merge strategy with a proper unique key plus a lookback instead of an ever-increasing watermark only. It’s more forgiving to pipeline hiccups and clock skew.

Backfills and full refresh without drama

You will need to backfill. Make it a design goal on day one. There are three safe ways to do it:

1. Narrow full refresh of a single model: dbt run --select fct_payments --full-refresh. Use during off-hours if the table is huge.
2. Partition backfill using delete+insert over a date range. This avoids reprocessing the entire table.
3. Temporary staging model to isolate a backfill window, then merge it into the target table.

Example: targeted backfill macro, then run via the command-line interface:

-- macros/backfill_window.sql
{% macro backfill_window(model_name, start_date, end_date) %}
  {% set relation = ref(model_name) %}
  {% do run_query("delete from " ~ relation ~ " where order_date between '" ~ start_date ~ "' and '" ~ end_date ~ "'") %}
  {% do log('Deleted window for backfill: ' ~ start_date ~ ' - ' ~ end_date, info=True) %}
  {% do run_query("insert into " ~ relation ~ " select * from " ~ relation ~ "__tmp where order_date between '" ~ start_date ~ "' and '" ~ end_date ~ "'") %}
{% endmacro %}

For very large datasets, microbatch a backfill by month to cap memory and query time. If your model uses incremental_strategy='append', a full refresh is the only way to fix historical logic changes—another reason many teams default to merge.

If your repo is already slow, do not pile on backfills without tuning. Start with our checklist in dbt run slow? Why it happens and 7 fixes that work, or get a structured plan through dbt Repo Performance.

Unique keys that are actually unique (joins, updates, dedupe)

Most broken incremental models share one cause: the unique_key isn’t truly unique after the model’s joins and transformation. Test and enforce this like you would a production index.

• Define the key on the target table, not just upstream. If you join orders to order_items, order_id alone likely duplicates rows. Consider a composite key (e.g., order_id||'-'||item_id) or hash of business columns.
• Add a unique test on the final model and fail fast. Duplicates today mean silent mis-merges tomorrow.
• When keys change in the source (yes, it happens), treat it as a data migration. Don’t hope the merge will guess right.

Example composite key with a stable hash:

-- models/fct_order_items.sql
{{
  config(materialized='incremental', incremental_strategy='merge', unique_key='pk')
}}
with base as (
  select
    concat(cast(order_id as string), '-', cast(item_id as string)) as pk,
    *
  from {{ ref('stg_order_items') }}
  {% if is_incremental() %}
    where updated_at > (select coalesce(max(updated_at), '1900-01-01') from {{ this }})
  {% endif %}
)
select * from base;

For warehouses like Snowflake and BigQuery, merge provides idempotency when the key is right. If you must dedupe incoming new rows before merging, stage first:

with ranked as (
  select *, row_number() over (partition by pk order by updated_at desc) as rn
  from {{ source('raw','order_items_cdc') }}
)
select * from ranked where rn = 1

Finally, verify uniqueness in CI and prod. Add schema tests, compare counts before/after, and sample conflicting keys. If you can’t prove uniqueness, don’t rely on merge; switch to delete+insert by partition.

Late-arriving data, schema drift, and source volatility

Two things will bite you after launch: late data and schema changes. You can mitigate both.

• Late data: Use the lookback pattern plus dedupe as shown earlier. Track lag KPIs in your warehouse and adjust. If you see spikes after long weekends, widen the window temporarily.
• Schema changes: Use on_schema_change deliberately. Options like ignore, fail, and adapter-supported append_new_columns decide whether to block the job or accept additions. Blocking may be right for finance facts; appending can keep ingestion flowing for wide event tables.
• Safe selects: Instead of select *, select named columns and coalesce new nullable fields. Use safe_cast-style functions (or explicit casts) to tame type drift.

Example: column-aware select using a macro to ignore unexpected columns while preserving known ones:

-- macros/select_known_cols.sql
{% macro select_known_cols(from_rel, cols) %}
    select
    {%- for c in cols %}
      {{ c }}
      {%- if not loop.last %}, {% endif %}
    {%- endfor %}
    from {{ from_rel }}
{% endmacro %}

-- models/events_clean.sql
{% set cols = ['event_id','user_id','event_ts','event_type','payload'] %}
{{ config(materialized='incremental', incremental_strategy='append', on_schema_change='append_new_columns') }}
{{ select_known_cols(source('app','events_raw'), cols) }}
{% if is_incremental() %}
  where event_ts > (select coalesce(max(event_ts), '1900-01-01') from {{ this }})
{% endif %}

When schema changes affect joins, add a model-level flag to switch behavior during a transition window, then backfill. And document the contract—what fields you guarantee downstream—so consumers aren’t surprised by silent type changes.

When incremental is the wrong answer (and what to do instead)

Incremental adds moving parts: filters, keys, and windows. Sometimes a plain table rebuild on a decent warehouse is cheaper in time and risk.

• Small-to-mid facts: If your daily full build on Snowflake completes in minutes, keep it simple. Leverage micro-partition pruning and clustering by your primary date, not incremental mode.
• Derived aggregates: If the logic must touch broad history each day (e.g., rolling 365-day cohorts), incremental logic becomes fragile. Consider a well-clustered table and recompute.
• Wide joins across hot dimensions: Multiple volatile joins make the unique_key ambiguous. Prefer a fresh build or a materialized view upstream.

Alternatives that keep you fast without incremental:

• Use native partition and clustering (BigQuery partitioned by date, clustered by user_id; Snowflake cluster keys on date columns that match pruning filters). See cost levers in Snowflake Cost Optimization.
• Stage immutable logs incrementally, then fully rebuild downstream curated marts that are smaller.
• Cache-heavy reporting layers in BI rather than pushing complex incremental logic into the model.

Ask: does incremental make my model easier to reason about and operate? If not, keep the sql simple and let the warehouse work. We summarize migration trade-offs in dbt Cloud Migration: Move Without Breaking Production.

How to configure strategies (with real dbt SQL)

Here are canonical configs you can drop into a dbt model. Adjust keys and filters to your data set.

Append (immutable events):

{{
  config(materialized='incremental', incremental_strategy='append', on_schema_change='append_new_columns')
}}
select ...
from {{ source('app','events_raw') }}
{% if is_incremental() %}
  where event_ts > (select max(event_ts) from {{ this }})
{% endif %}

Merge strategy (typical for facts with updates):

{{
  config(materialized='incremental', incremental_strategy='merge', unique_key='id')
}}
with src as (
  select * from {{ ref('stg_table') }}
  {% if is_incremental() %}
    where updated_at > dateadd(hour, -24, (select coalesce(max(updated_at), '1900-01-01') from {{ this }}))
  {% endif %}
)
select * from src

Delete+insert (partitioned facts):

{{ config(materialized='incremental', incremental_strategy='delete+insert', unique_key='pk') }}
with windowed as (
  select * from {{ ref('stg_fact') }}
  {% if is_incremental() %}
    where order_date >= dateadd(day, -7, current_date)
  {% endif %}
)
select * from windowed

Microbatch (operational pattern):

# Orchestrate multiple daily jobs passing a --vars window
# Example Airflow or dbt Cloud job with parameters
# vars: {start_ts: '2024-01-01T00:00:00Z', end_ts: '2024-01-01T06:00:00Z'}

{{ config(materialized='incremental', incremental_strategy='merge', unique_key='pk') }}
select *
from {{ source('raw','events') }}
where event_ts >= '{{ var("start_ts") }}' and event_ts < '{{ var("end_ts") }}'

Reference patterns against the dbt Developer Hub for adapter specifics. If your repo struggles with these patterns at scale, we can help you optimize.

Multiple joins and complex logic: making incremental safe

“How are you building your dbt incremental strategy on models with multiple joins?” Carefully. Three practices keep it correct:

1. Stage first: Build narrow staging models per source with their own incremental logic and tests. Join in a downstream model that is either full-refresh or delete+insert by partition.
2. Window the join inputs: In incremental mode, filter each joined table with the same lookback. This bounds the cross-product while still catching updates.
3. Compute the key after joins: Derive the unique_key from the final grain, not a source surrogate.

Example pattern:

{{ config(materialized='incremental', incremental_strategy='merge', unique_key='order_item_pk') }}
with orders as (
  select * from {{ ref('stg_orders') }}
  {% if is_incremental() %}
    where updated_at > dateadd(day, -3, (select max(updated_at) from {{ this }}))
  {% endif %}
), items as (
  select * from {{ ref('stg_order_items') }}
  {% if is_incremental() %}
    where updated_at > dateadd(day, -3, (select max(updated_at) from {{ this }}))
  {% endif %}
)
select
  concat(o.order_id,'-',i.item_id) as order_item_pk,
  o.order_id, i.item_id, greatest(o.updated_at, i.updated_at) as updated_at,
  ...
from orders o
join items i on i.order_id = o.order_id

If this still explodes, split the transformation into layers: incremental staging, then a full-refresh dimension or mart that’s smaller. More depth on repo-level patterns lives in our dbt articles.

Operating playbook: tests, monitoring, and cost

Keeping incremental models healthy is a daily discipline, not a one-time config.

• Tests: Add not_null and unique on the unique_key. Add a custom test that detects duplicate business keys by date. Fail fast.
• Lag monitoring: Store the max timestamp processed each run, compare to now. Alert if the gap grows beyond your SLO.
• Row deltas: Track new rows written per day vs expected volumes. Spikes or drops often signal broken filters.
• Costs: On Snowflake, watch warehouse credit spikes during merges and wide deletes. Tune clustering and pruning; see our deep dive in Snowflake Cost Optimization.
• Orchestration: Schedule critical models earlier, isolate them so retries don’t backlog the entire pipeline. Airflow or dbt Cloud both work; keep dependencies explicit.

When things degrade, look first at the is_incremental filter, then the key, then the partition pruning. If you’re migrating from self-managed to dbt Cloud and want to preserve SLAs while you rework materializations, see dbt Cloud Migration.

---

Quick answers to common questions

• What are incremental models? Tables that update incrementally instead of rebuilding the entire table each run. They rely on an overlap filter and a solid key.
• How many days should I go back using the is_incremental filter? Measure your source delay; use the 99th percentile last run delay plus buffer, and revisit regularly.
• How do you rebuild the incremental model in dbt? Run dbt run --select my_model --full-refresh, or delete+insert a bounded window.
• Is the unique key really unique? Prove it with tests on the model’s final grain; don’t assume the source key survives joins.

If you want a structured review of your incremental strategies, we can help you build incremental models that last. Start a conversation: Vertex Data Consulting.