Every incremental model story starts the same way. A team builds a dbt model over an events table. It works fine in development. Three months into production, the table has grown to two hundred million rows, the dbt run takes ninety minutes, and someone's weekly Slack message about dashboard freshness has become a daily one.

The fix is an incremental model. But incremental models have failure modes that basic materializations don't. Understand the mechanism fully, and they're the most powerful tool in your dbt toolkit. Understand them partially, and they'll produce data that looks correct and isn't — which is a worse outcome than a failing run.


Why `table` doesn't scale

Think about restocking a grocery store. Every night, you could empty every shelf and rebuild the whole store from the warehouse truck — guaranteed correct, completely absurd for a store that only sold three hundred items that day. Or you could just restock what moved: walk the aisles, find the gaps, refill them. That's the entire pitch of an incremental model. A table materialization is "empty every shelf." An incremental model is "just restock what sold" — much faster, but only correct if your restocking logic actually accounts for what changed, what got returned, and what arrived late from the supplier.

dbt gives you three core materializations out of the box: view, table, and incremental.

A view is nearly free to build — it's just a saved query — but runs the full computation every time it's queried. Fine for simple lookups, slow for anything complex.

A table pre-computes and stores the result. Fast to query, but every dbt run drops the table and rebuilds it from scratch. On a 10-million-row model with three joins, that's fine. On a 500-million-row event stream running hourly, you're paying for a full warehouse scan every sixty minutes.

An incremental model solves this by behaving like a table on the first run, then only processing and merging new data on subsequent runs. The expensive full rebuild becomes a cheap incremental load. The tradeoff: the model is now stateful, which introduces complexity you need to manage explicitly.


The anatomy of an incremental model

The simplest incremental model looks like this:

{{ config(
    materialized='incremental',
    unique_key='event_id'
) }}

SELECT
    event_id
    , user_id
    , event_type
    , event_time
    , session_id
FROM {{ source('app', 'raw_events') }}

{% if is_incremental() %}
WHERE event_time > (SELECT MAX(event_time) FROM {{ this }})
{% endif %}

Two things do most of the work here.

is_incremental() is a Jinja macro that returns true when two conditions are met: the target table already exists in the warehouse, and you are not running with --full-refresh. On the very first run, the table doesn't exist yet, so is_incremental() returns false and the entire source is processed. On every subsequent run, it returns true and only the filtered subset is processed.

{{ this }} refers to the model's own table in the warehouse. The subquery SELECT MAX(event_time) FROM {{ this }} finds the latest timestamp already loaded — the watermark — and pulls only rows newer than that. This is how dbt knows where the last run left off.

The strict > here means the exact watermark row won't be reprocessed. In production you'll want >= combined with a lookback window (covered below) so late-arriving events don't fall through the gap.


Incremental strategies

The incremental_strategy config tells dbt what to do with the new rows once it has them. The strategy you choose determines both the correctness guarantees and the performance characteristics of the model.

append

The simplest strategy: new rows are inserted and nothing else happens. No deduplication, no updates to existing rows.

{{ config(
    materialized='incremental',
    incremental_strategy='append'
) }}

Appropriate when you can guarantee the source never sends duplicates and records never change after they're written — immutable event logs with a reliable deduplication upstream. If your pipeline ever retries (and eventually it will), you'll get duplicate rows with no warning. append trusts the source completely.

merge

The default strategy for most warehouses and the right choice for most production incremental models. For each row in the new batch, if a row with the same unique_key already exists in the target table, update it; otherwise insert it. A proper upsert.

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

The unique_key can be a list of columns for a composite natural key:

{{ config(
    materialized='incremental',
    unique_key=['user_id', 'event_date', 'event_type']
) }}

On Snowflake and BigQuery, omitting unique_key with merge is a compile-time error — dbt won't run at all. On older adapters it may silently fall back to append. Either way the result is wrong. Always provide one.

delete+insert

Deletes all rows matching the unique_key (or the incremental_predicates range) from the target table, then inserts the new batch. No per-row comparison — a clean replacement of the affected segment.

{{ config(
    materialized='incremental',
    incremental_strategy='delete+insert',
    unique_key='event_date'
) }}

SELECT
    DATE(event_time) AS event_date
    , event_type
    , COUNT(*) AS event_count
    , COUNT(DISTINCT user_id) AS unique_users
FROM {{ source('app', 'raw_events') }}

{% if is_incremental() %}
WHERE event_date >= DATEADD(day, -3, (SELECT MAX(event_date) FROM {{ this }}))
{% endif %}

GROUP BY 1, 2

Works well for aggregation models where you want to replace entire date partitions rather than merge individual rows. On column stores like Snowflake, deleting a partition's worth of rows and re-inserting can be faster than row-level merging. Note the anchor is MAX(event_date) FROM {{ this }}, not CURRENT_DATE — if the pipeline fails and re-runs a day later, the window stays anchored to the last date in the target, not to today's wall clock.

insert_overwrite

Replaces entire partitions in a single operation. Native to BigQuery and Spark/Databricks. If you're on Snowflake, delete+insert with a date-level key achieves the same result more portably — use that instead.

Strategy
Best for
Requires
append
Immutable event logs, guaranteed-unique sources
No duplicates at source
merge
Most production models, mutable records
unique_key
delete+insert
Aggregations by date partition
unique_key at partition grain
insert_overwrite
BigQuery / Spark partition replacement
Partition column defined

Which strategy should you use?

Three questions determine the answer: do source records ever change after creation, is the model row-level or aggregated, and which warehouse are you on?

Source records
Model type
Warehouse
Strategy
Immutable — never change after creation, source never resends
Row-level
Any
append
Mutable — records update after creation (orders, users, subscriptions), or source may resend
Row-level
Any
merge
Either
Aggregated by date (daily SUM / COUNT)
Snowflake, Redshift, Postgres
delete+insert
Either
Aggregated by date (daily SUM / COUNT)
BigQuery, Spark / Databricks
insert_overwrite
Either
Any — table is small or full build is fast
Any
table — skip incremental entirely

If in doubt between append and merge, use merge. The merge overhead is small, and the protection against re-runs and source retries is not.


on_schema_change — the quiet killer

You've been running an incremental model in production for two months. You add a new column to the SELECT. The existing table doesn't have that column yet. What happens?

By default: on_schema_change='ignore'. dbt runs without error, inserts rows, and silently drops the new column on the way in. You won't get an alert. The column appears nowhere. The first sign will be someone asking why a metric you just added is all nulls.

dbt will not throw a tantrum about this. It is exceptionally calm about your missing column. You will not be.

The four options:

{{ config(
    materialized='incremental',
    on_schema_change='fail'          -- error on any schema difference (safest)
-- on_schema_change='ignore'              -- default, silently drops new columns
-- on_schema_change='append_new_columns'  -- adds new columns, keeps existing
-- on_schema_change='sync_all_columns'    -- adds new and removes deleted columns
) }}

In production, use fail or append_new_columns. fail forces a deliberate --full-refresh whenever the schema changes — a manual gate that keeps you honest. append_new_columns handles the common case of adding columns without drama, but won't handle renames or type changes.

ignore exists for backwards compatibility, not because it's a sensible default. sync_all_columns will remove columns from the warehouse table if you remove them from your SQL — use with caution in anything accessed by downstream consumers.


incremental_predicates — the performance win

When dbt executes a merge, it compares each row in the incoming batch against the full target table. On a two-billion-row table, even with an indexed join key, this can scan more data than necessary.

incremental_predicates adds a filter on the target table side of the merge, telling the warehouse to only consider rows in a specific range. Snowflake, BigQuery, and Databricks can then skip entire micro-partitions or partitions entirely.

{{ config(
    materialized='incremental',
    unique_key='event_id',
    incremental_predicates=[
"DBT_INTERNAL_DEST.event_time >= dateadd(day, -7, current_timestamp)"
    ]
) }}

DBT_INTERNAL_DEST is the alias dbt uses for the existing target table in the generated MERGE statement. The predicate above restricts the merge to only consider rows from the last 7 days on the target side, allowing the warehouse to skip everything older. On a Snowflake table clustered on event_time, this is often a 10–50× cost reduction on the merge operation.

The window in the predicate should be at least as wide as your lookback window in the is_incremental() filter. If you pull 3 days of source rows but only search 2 days of the target for matches, rows in the gap get inserted as duplicates instead of updated.

Note that current_timestamp here is intentional — unlike the lookback anchor in your is_incremental() WHERE clause, this predicate is a performance hint to the warehouse about which target partitions to scan, not a data completeness guarantee. A slightly stale value just means the warehouse scans a few extra micro-partitions; it doesn't cause data loss. The anchor that matters for correctness is always the one in your model's WHERE clause.


The full-refresh flag

Any incremental model can be rebuilt from scratch with:

dbt run --select my_model --full-refresh

This drops the existing table and rebuilds it entirely, ignoring the is_incremental() filter. Use it when:

  • You've changed the model's SELECT significantly and the incremental logic would produce incorrect merged results
  • You've changed on_schema_change to fail and the schema drifted
  • The incremental state is corrupted or the watermark is wrong
  • You're testing the model for the first time and want to verify the full build

Always test your incremental model with a --full-refresh before trusting it in production. If the full-refresh result doesn't match what you expect, the incremental run won't either — it just fails more subtly.


Practical patterns

The lookback window

The pattern you should add to nearly every incremental model you write. The naive watermark filter WHERE event_time > MAX(event_time) misses any records that arrive late — events that happened three days ago but only reached your pipeline today. Late-arriving data is common enough that building without a buffer is an optimism you'll pay for.

{% if is_incremental() %}
WHERE event_time >= DATEADD(day, -3, (SELECT MAX(event_time) FROM {{ this }}))
{% endif %}

This reprocesses the last 3 days on every run. The merge strategy handles deduplication — rows already present are updated in place, genuinely new rows are inserted. You process a bit more data; in return you don't have unexplained gaps that surface during a quarterly review.

How wide should the window be? Check the SLA of your slowest upstream source. Webhook retries typically have a 24–72 hour window. Batch exports from third-party systems can be 24–48 hours behind. Three days covers the vast majority of cases. A week is a reasonable conservative choice. Longer than that is usually a sign that something upstream needs fixing.

The companion article on late-arriving data goes deeper on this — what causes it, why it's dangerous, and the full set of patterns for handling it.

Building a surrogate key for deduplication

If the source doesn't provide a reliable unique identifier, build one. dbt_utils.generate_surrogate_key hashes a list of columns into a consistent ID:

SELECT
{{ dbt_utils.generate_surrogate_key(['user_id', 'event_time', 'event_type']) }} AS event_id
    , user_id
    , event_type
    , event_time
    , session_id
    , properties
FROM {{ source('app', 'raw_events') }}

Use this as the unique_key in the config. The merge strategy will handle deduplication on re-runs. Be careful that the columns you choose actually define uniqueness — two clicks at exactly the same millisecond by the same user on the same event type should be rare, but if it happens you'll lose one row silently.

event_time vs ingested_at

Most incremental filters use event_time — when the event happened. But there are two timestamps worth storing:

SELECT
    event_id
    , user_id
    , event_type
    , event_time                              -- when it happened in the product
    , _fivetran_synced        AS ingested_at  -- when the pipeline received it
FROM {{ source('app', 'raw_events') }}

Your incremental filter should use ingested_at if you want to guarantee you process everything the pipeline received since the last run, regardless of when the events happened. Your business logic uses event_time — counting events by the window in which they actually occurred.

Filtering on ingested_at for the incremental logic and event_time for aggregations is the correct split. Using event_time for both looks simpler and will miss late arrivals.

Incremental on a daily aggregation model

Incremental models work on aggregated grains too, not just row-level event tables. Here's a daily subscription revenue model that uses delete+insert to rebuild the last seven days on every run:

{{ config(
    materialized='incremental',
    incremental_strategy='delete+insert',
    unique_key='mrr_date'
) }}

SELECT
    DATE(billing_date) AS mrr_date
    , SUM(mrr_amount)          AS total_mrr
    , COUNT(DISTINCT account_id) AS active_accounts
FROM {{ ref('stg_subscriptions') }}
WHERE status = 'active'

{% if is_incremental() %}
AND billing_date >= DATEADD(day, -7, (SELECT MAX(mrr_date) FROM {{ this }}))
{% endif %}

GROUP BY 1

On each run, the last 7 days of partitions are deleted and rebuilt from the staging model. The anchor is MAX(mrr_date) from the target table — not CURRENT_DATE. If the pipeline fails on Monday and re-runs on Tuesday, the window is still "7 days before the last date we successfully loaded," not "7 days before today." This handles late-arriving subscription updates correctly and keeps historical dates stable beyond the window.


Common mistakes

None of these are exotic. They are the same seven ways to shoot yourself in the foot, worn smooth by years of teams finding them independently.

  1. No lookback window. The > MAX(event_time) filter misses late-arriving records. Add a buffer of at least 1–3 days in every incremental filter you write.
  2. Anchoring the lookback window to CURRENT_DATE instead of MAX(target_column). DATEADD(day, -3, CURRENT_DATE) shifts the window every day. If a pipeline fails Monday night and re-runs Tuesday morning, Monday's tail data falls outside the new window and is permanently missed. Always anchor to (SELECT MAX(ts_col) FROM {{ this }}) — it stays fixed to where you actually left off, regardless of when the re-run happens.
  3. Using on_schema_change='ignore' in production. The default silently drops new columns. Switch to fail or append_new_columns. Discovering this after a week of missing data is educational but avoidable.
  4. No unique_key with merge strategy. Without one, dbt falls back to append behavior on most adapters and you accumulate duplicates with no error to investigate.
  5. Not running --full-refresh after structural changes. If you significantly alter the SELECT, incremental logic will merge new-shape rows into old-shape rows. Run a full rebuild after any meaningful schema or logic change.
  6. Incremental on small tables. Tables under a few million rows don't benefit from incrementals. The merge overhead can exceed the savings. Use table materialization and remove the complexity entirely.
  7. Joining two incremental models together. If model_a is incremental and joins model_b (also incremental), model_a only processes its own new rows — it won't pick up historical corrections from model_b that changed rows already in model_a. Plan join dependencies carefully or use a wider lookback window.

When NOT to use incremental

Incremental models are powerful, but they add stateful complexity. There are scenarios where simpler materializations are the better choice.

Type 2 slowly changing dimensions. SCD Type 2 requires comparing every current record against incoming data to detect changes and open/close validity periods. That's a full comparison of the current state — not incremental logic. Use dbt snapshot for this, which is designed exactly for it.

Aggregations that correct history. If you have a daily MRR model and a late-arriving subscription update changes a value from two weeks ago, an incremental model with a 3-day window won't reach back and fix it. For aggregations where historical correctness matters more than speed, use table materialization and accept the full rebuild cost, or use delete+insert with a wide enough window.

Small or medium reference tables. If the full build takes under 30 seconds, incremental adds complexity without meaningful savings. Simplicity is a feature. A model that a new team member can understand in two minutes is worth more than a model that saves three seconds of compute.

When you're uncertain. Start with table and optimise later when the cost becomes visible. Starting with incremental and discovering the edge cases in production is a more expensive lesson.

Takeaways

  • Incremental models are stateful — they remember where they left off, which makes them powerful and requires you to manage that state deliberately.
  • Always set on_schema_change: fail or append_new_columns — the ignore default silently drops new columns. You will not get an error. You will get nulls in production.
  • Add a lookback window to every incremental filter — > MAX(event_time) alone misses late arrivals. Three days is a reasonable default; match it to your source SLA.
  • Use merge + unique_key as your production default — it handles both new inserts and updates correctly, with predictable deduplication behaviour.
  • incremental_predicates unlocks partition pruning on the target table — essential for large Snowflake or BigQuery tables where a full merge scan is cost-prohibitive.
  • Test with --full-refresh before trusting the incremental path — if the full build is wrong, the incremental run will be wrong in a way that's harder to notice.
  • Start with table and migrate to incremental when the cost appears — premature incremental optimization adds complexity before you understand the failure modes.

The next layer of complexity is late-arriving data — events that happened days ago and are only now reaching your pipeline. The incremental model structure above handles it partially; the companion article on late-arriving data covers the full set of patterns and why it matters more than most teams realise.