Incremental Models in Depth

Why Rebuilding a Multi-Billion-Row Table Every Run Is Unacceptable

A table materialization rebuilds a model completely, every single time dbt runexecutes. dbt drops (or renames out) the existing table, runs the model's full SELECTstatement from scratch, and writes the entire result set back as a brand-new table. For a small dimension table with ten thousand rows, this is trivial — it finishes in under a second and nobody notices. For a fact table tracking every order, page view, or sensor reading a company has ever recorded, this stops being trivial extremely quickly.

Picture fct_orders at a mid-size e-commerce company with three years of history: two billion rows. The model's SELECT joins orders to customers, products, promotions, and shipping — a non-trivial amount of compute per row. Rebuilt as a full table on every hourly run, this query scans and reprocesses all two billion rows every single hour, even though only the last hour's few thousand new and updated orders actually need to be reflected. The warehouse bill for this pattern grows in direct proportion to how much history accumulates — the query gets slower and more expensive every single day, forever, for no benefit, because the vast majority of those two billion rows have not changed since the previous run and did not need to be touched at all.

An incremental model materialization does exactly this. On the first run (or a full-refresh), it behaves like a table materialization — it builds the entire table from the model's SELECT. On every subsequent run, it runs a different, narrower query: process only the rows that are new since the last run, and merge them into the existing table rather than rebuilding it. The existing two billion rows sit untouched on disk. Only the new slice is computed and written.

Turning a Model Incremental: the config Block

Making a model incremental starts with one line in its config() call. Everything else — the actual incremental filtering logic — is added on top of this base configuration using Jinja, covered in Part 03.

{{
  config(
    materialized='incremental'
  )
}}

select
    event_id,
    user_id,
    page_url,
    event_time,
    session_id
from {{ ref('stg_page_views') }}

As written, this model works, but it is not actually doing anything smarter than a table materialization yet — on every run, dbt still has no idea which rows are "new," so without anis_incremental() block (Part 03), the raw SQL is identical on every run, and dbt falls back to appending the entire result of that SELECT again, duplicating every row that was already there. The config only tells dbt how to materialize the model — it does not, by itself, filter anything. The filtering logic has to be written explicitly, which is the entire point of Part 03.

Every one of these lives inside the same config() call. A fully specified incremental model — the shape you will write in almost every real project — combines all four:

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

The is_incremental() Jinja Block Is the Entire Idea

Everything about incremental models comes down to one piece of Jinja: {% if is_incremental() %}. The code inside this block only runs on an incremental run — not on the very first run (when the table doesn't exist yet) and not on a full-refresh (when dbt is deliberately rebuilding from scratch). On those two runs, dbt needs the full, unfiltered SELECT to populate the entire table. On every other run, dbt needs the narrow, filtered SELECT that only picks up new rows. One SQL file has to express both, and is_incremental() is the switch between them.

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

select
    event_id,
    user_id,
    page_url,
    event_time,
    session_id
from {{ ref('stg_page_views') }}

{% if is_incremental() %}

  -- this filter only applies on an incremental run:
  where event_time > (select max(event_time) from {{ this }})

{% endif %}

{{ this }} is a special Jinja variable that refers to the model's own current database relation — for this model, the actual fct_page_views table that already exists from the previous run. The subquery select max(event_time) from {{ this }}asks the existing table "what is the newest event you already contain?" and the outerwhere clause then asks the source, stg_page_views, for only rows newer than that. This is the entire mechanism: look at what you already have, then pull only what's newer.

Internally, is_incremental() returns true only when all three of these hold at once: the model is configured as materialized='incremental'; the target relation already exists in the warehouse; and dbt is not running with --full-refresh. Any one of those being false collapses it back to a full build. This is why a brand-new incremental model's very first dbt run always does a full historical load — there's nothing in{{ this }} yet for the filter to compare against, so dbt correctly skips the{% if %} block entirely rather than erroring on a table that doesn't exist.

Inspecting exactly what dbt compiled, for either branch

Because is_incremental() changes what SQL actually runs depending on the run type, it is easy to be unsure which branch a given run actually took. dbt compile resolves all Jinja, including is_incremental(), and writes the resulting plain SQL to thetarget/compiled/ directory — a direct way to confirm, before ever running the model, exactly which version of the query a given invocation will execute.

dbt compile --select fct_page_views
# then inspect the generated file, e.g.:
cat target/compiled/my_project/models/marts/fct_page_views.sql

# On a normal run against an existing table, the WHERE clause from
# inside is_incremental() will be present in the compiled output.
# Run with --full-refresh and recompile, and that WHERE clause
# disappears entirely from the compiled SQL — direct proof of
# which branch is_incremental() actually took for that invocation.

This is the single most reliable way to debug a suspected divergence between incremental and full-refresh behavior (Part 06) — rather than reasoning about what the Jinja should do, compile both variants and diff the actual generated SQL directly.

unique_key: Telling dbt What "The Same Row" Means

event_time > max(event_time) is enough for pure append-only data — a page view event is immutable once it happens; it is never edited after the fact. But a large share of real fact tables are not pure append-only. An order can be placed, then have its status updated to "shipped" a day later, then "delivered" three days after that — the same order_idreappearing in the source with an updated updated_at timestamp and different column values each time. Here, simply appending every new-looking row produces three separate rows for one order instead of one row reflecting its current state.

unique_key tells dbt which column (or combination of columns) identifies "the same logical row" across runs, so that when a row with a matching key shows up again, dbt can update the existing row in place instead of blindly inserting a duplicate.

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

select
    order_id,
    customer_id,
    order_status,
    total_amount,
    updated_at
from {{ ref('stg_orders') }}

{% if is_incremental() %}
  where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

unique_key can also be a list, for models where no single column is unique on its own — a common case in event or line-item level fact tables where the natural key is composite.

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

Picking the wrong unique_key — a subtler failure than picking none at all

A unique_key that is not actually unique in the source data is a quieter failure mode than omitting one entirely. If two rows in a single incremental batch share the sameunique_key value — for example, two updates to the same order arriving within the same run because the source system emitted both a status-change event and a totals-recalculation event for it — a merge statement's behavior when the same key appears twice in the source side of the join is warehouse-dependent: some engines error outright, others silently apply one of the two updates and discard the other, non-deterministically. Either outcome is worse than an obvious failure, because the second is silent.

with orders as (

    select
        order_id,
        customer_id,
        order_status,
        updated_at,
        row_number() over (
            partition by order_id
            order by updated_at desc
        ) as row_num
    from {{ ref('stg_orders') }}

    {% if is_incremental() %}
    where updated_at > (select max(updated_at) from {{ this }})
    {% endif %}

)

select
    order_id, customer_id, order_status, updated_at
from orders
where row_num = 1   -- keep only the newest version of each order_id
                    -- within THIS batch, before it ever reaches the MERGE

This pattern — a row_number() window function partitioned by the intendedunique_key, keeping only the newest row per key — is the standard defense against a batch containing more than one update for the same key, and is worth adding by default to any incremental model where the source could plausibly emit more than one event per key in a single incremental window.

append, delete+insert, and merge — Three Different Mechanics

incremental_strategy controls the actual SQL dbt generates to combine the new, filtered rows with the existing table. The three you will use in practice behave very differently, and picking the wrong one for a given model's update pattern produces either duplicate rows or silently-stale data.

append — insert only, no updates, ever

append is the simplest possible strategy: dbt runs the filtered is_incremental()SELECT and inserts every row it returns straight into the existing table. It never checks whether a row with a matching key already exists, and it never updates or deletes anything. It is the fastest and cheapest strategy by a wide margin, because it is a single, unconditionalINSERT INTO ... SELECT with no matching or scanning of the existing table required.

insert into analytics.fct_page_views
select
    event_id, user_id, page_url, event_time, session_id
from analytics_staging.stg_page_views
where event_time > (select max(event_time) from analytics.fct_page_views)

The catch: append cannot handle late-arriving updates to existing rows. If the sameevent_id is somehow re-emitted by the source with a corrected value, appendinserts it as a second row rather than replacing the first — it has no concept of "the same row" because it never looks at unique_key at all. append is the right choice only for genuinely immutable, append-only event data: page views, clickstream events, IoT sensor readings — data that is written once and never edited again.

delete+insert — delete matching keys, then insert the new batch

delete+insert runs in two explicit steps: first, delete every row in the existing table whose unique_key matches a key present in the new, filtered batch; second, insert the entire new batch. Net effect: any row whose key reappears gets replaced wholesale by its newest version, and any row with a genuinely new key is added. This is a common default on Snowflake and BigQuery, where it is often implemented efficiently using a temporary table holding the new batch.

-- Step 1: delete existing rows whose key appears in the new batch
delete from analytics.fct_orders
where order_id in (
    select order_id from analytics_staging.stg_orders__dbt_tmp
)

-- Step 2: insert the entire new/updated batch
insert into analytics.fct_orders
select * from analytics_staging.stg_orders__dbt_tmp

Because this runs as two separate statements, a failure between them (a killed session, a warehouse timeout) can leave the table in a transiently inconsistent state — rows deleted but not yet reinserted — unless the warehouse wraps both steps in a single transaction, which Snowflake and BigQuery both do for this exact reason. Even so, delete+insert is inherently a two-statement operation and therefore slightly more overhead than a single native MERGE.

merge — one native MERGE statement, insert-or-update in a single operation

merge generates a single native SQL MERGE statement: for every row in the new batch, if a row with a matching unique_key already exists in the target table, update it in place; otherwise, insert it as a new row. This is the most warehouse-native mechanism available on platforms that support MERGE natively — Snowflake, BigQuery, Databricks, and Redshift all do — and it is typically the preferred and default choice on Snowflake specifically, where MERGE is a well-optimized, single-statement operation.

merge into analytics.fct_orders as target
using analytics_staging.stg_orders__dbt_tmp as source
on target.order_id = source.order_id

when matched then update set
    customer_id   = source.customer_id,
    order_status  = source.order_status,
    total_amount  = source.total_amount,
    updated_at    = source.updated_at

when not matched then insert (
    order_id, customer_id, order_status, total_amount, updated_at
) values (
    source.order_id, source.customer_id, source.order_status,
    source.total_amount, source.updated_at
)

Because it is one atomic statement rather than two, merge avoids the transient inconsistency window of delete+insert and is generally the fastest option when the warehouse's query optimizer can push the join efficiently — which is essentially always true on modern cloud warehouses at the row volumes incremental models are built for.

A decision framework — walking through the actual choice

In practice, picking a strategy comes down to answering two questions about the model's source data, in order. First: can a row that has already landed ever be changed or corrected later? If the honest answer is no — the data is genuinely append-only, like an immutable event log — thenappend is correct, and adding unique_key or a fancier strategy only adds overhead for no benefit. If the answer is yes, move to the second question: does the target warehouse support an efficient native MERGE statement? Snowflake, BigQuery, Databricks, and Redshift all do, which makes merge the right default. On an adapter whereMERGE is poorly optimized or unsupported, delete+insert is the fallback that still correctly handles updates to existing rows.

Can an existing row's data ever be corrected or updated after it first lands?
│
├── No (genuinely immutable events) ──────────────► incremental_strategy='append'
│
└── Yes (rows can be corrected/updated) ──► Does the warehouse support native MERGE well?
                                              │
                                              ├── Yes (Snowflake, BigQuery, Databricks) ──► incremental_strategy='merge'
                                              │
                                              └── No / unsure ─────────────────────────────► incremental_strategy='delete+insert'

A common real mistake is answering the first question wrong by assumption rather than by checking the actual source system. "Orders never change once placed" sounds true until a refund, a status correction, or a support-tooling backfill script proves otherwise months later — exactly the pattern in this module's Real World section. When in doubt, default to merge with aunique_key set; the cost of an unnecessary key check on genuinely immutable data is small, while the cost of silently accumulating duplicate rows under append on data that turned out to be mutable is a real, compounding data quality bug.

dbt run --full-refresh, and the Classic Bug Class It Exists to Fix

dbt run --full-refresh (or dbt build --full-refresh) forces dbt to drop and completely rebuild an incremental model from scratch — running the full, unfiltered SELECT as if the model had never existed before, exactly as it would on its very first run. Every reason to reach for it comes down to the same underlying need: the incrementally-built table and the full-rebuild-from-scratch table have stopped agreeing, and a full-refresh is how you force them back into agreement.

The divergence bug: incremental runs and full-refresh runs producing different results

This is the single most consequential bug class in incremental model design, and it is subtle precisely because both code paths "work" individually — the bug is that they silently disagree with each other. It happens when the WHERE clause inside the is_incremental()block does not exactly match the implicit scope of the full, unfiltered SELECT that runs outside it.

select
    order_id,
    customer_id,
    order_status,
    total_amount,
    updated_at
from {{ ref('stg_orders') }}
where order_status != 'cancelled'   -- ← this filter applies on EVERY run

{% if is_incremental() %}
  and updated_at > (select max(updated_at) from {{ this }})
{% endif %}

-- Looks fine. But now: what happens to an order that WAS 'cancelled'
-- at the time it first landed (correctly excluded), and later gets its
-- status corrected back to a valid, non-cancelled state?
--
-- On the FULL-REFRESH path: the current stg_orders row for that order
-- is scanned fresh, its order_status is now valid, and it IS included.
--
-- On the INCREMENTAL path: the filter only looks at updated_at > max(updated_at).
-- If that order's updated_at was NOT bumped when its status changed
-- (a common upstream mistake), the incremental run never sees it at all —
-- it silently stays missing from the table.
--
-- Result: dbt run keeps the order missing forever.
--         dbt run --full-refresh brings it back.
-- Two runs of the "same" model produce two different tables.

The fix is not a clever trick — it is discipline: the condition that decides which rows are eligible to appear in the model at all (here, order_status != 'cancelled') must be evaluated consistently regardless of which path runs, and the incremental filter's job is only to narrow which rows to re-scan for changes, never to silently redefine which rows are eligible to exist in the model. In practice this usually means either filtering on a column that reliably updates whenever anything relevant about the row changes, or widening the incremental window to re-scan slightly more than the strict minimum, trading a little extra compute for correctness.

A useful habit for catching this class of bug before it reaches production: periodically rundbt run --full-refresh against a model in a staging environment and diff its row count and key aggregates against the incrementally-built production table. If they ever disagree, the incremental filter has drifted from the full-refresh scope, and that is the signal to go looking for exactly the kind of hidden extra WHERE condition shown above.

A Real Incremental Fact Table: fct_orders End to End

Bringing every piece together: a real fct_orders model using unique_key, the merge strategy, and an incremental filter on updated_at — the shape this exact model takes in the large majority of production dbt projects.

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

with orders as (

    select
        order_id,
        customer_id,
        order_placed_at,
        order_status,
        updated_at
    from {{ ref('stg_orders') }}

    {% if is_incremental() %}
    where updated_at > (select max(updated_at) from {{ this }})
    {% endif %}

),

order_items as (

    select
        order_id,
        sum(line_item_amount) as items_subtotal,
        count(*) as line_item_count
    from {{ ref('stg_order_items') }}
    group by 1

),

final as (

    select
        orders.order_id,
        orders.customer_id,
        orders.order_placed_at,
        orders.order_status,
        orders.updated_at,
        order_items.items_subtotal,
        order_items.line_item_count
    from orders
    left join order_items
        on orders.order_id = order_items.order_id

)

select * from final

Trace what happens on each kind of run. On the very first dbt run, the target tablefct_orders does not exist, so is_incremental() is false, thewhere clause is skipped entirely, and every historical order is processed and inserted — a full build, identical to what a table materialization would do. On every subsequentdbt run, the table exists, is_incremental() is true, and the query only pulls orders whose updated_at is newer than the newest one already infct_orders — typically a few thousand rows instead of the full historical volume. Themerge strategy then updates any order whose status changed (shipped, delivered, refunded) in place, and inserts any genuinely new order as a new row.

Notice that updated_at is chosen deliberately, not order_placed_at. A filter on order_placed_at would only ever catch brand-new orders — it would never re-scan an existing order whose status later changed, because that order's placed_atvalue never changes even though the row itself does. This is exactly the divergence trap from Part 06: the incremental filter column must track "this row changed," not "this row was created."

# A backfill is a full-refresh scoped, in practice, by first fixing upstream
# data and then forcing a rebuild — dbt itself has no partial-range refresh
# built into the base incremental materialization, so the standard pattern is:

dbt run --select fct_orders --full-refresh

# For very large tables where a full rebuild is too expensive just to fix
# 3 months of data, some teams instead add a manual backfill script that
# runs the model's SQL with an explicit date range substituted in place of
# is_incremental()'s filter, and MERGEs just that range back in.

The compute difference between the two paths is the entire reason incremental models exist in the first place, and it is worth making concrete with real numbers for this exact model.

This is the practical payoff in one table: routine runs stay cheap and roughly constant no matter how much history accumulates, while the expensive full-scan path is reserved for the rare occasions — first build, a logic change, a backfill — where it is genuinely unavoidable.

Schema Changes, Late-Arriving Data, and Monitoring an Incremental Model

on_schema_change — what happens when the model's columns change

Unlike a table materialization, which simply drops and recreates the table with whatever columns the current SELECT produces, an incremental model's existing table already has a fixed schema from a previous run. If the model's SQL is edited to add a new column, dbt needs to decide what to do about the mismatch between the existing table's schema and the new SELECT's schema.

append_new_columns is the most common real-world choice — it lets a model evolve incrementally without a mandatory full-refresh on every schema change, while sync_all_columnsis used when keeping the table strictly matched to the model definition matters more than preserving old, now-unused columns.

-- Before: fct_orders has order_id, customer_id, total_amount, updated_at
-- Model SQL is edited to add a new column:

select
    order_id,
    customer_id,
    total_amount,
    discount_amount,   -- newly added
    updated_at
from {{ ref('stg_orders') }}

{% if is_incremental() %}
  where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

-- With on_schema_change='append_new_columns':
-- dbt ALTERs the existing fct_orders table to add discount_amount
-- (as a nullable column), backfilling NULL for every row that already
-- existed, then proceeds with the normal incremental merge for new rows.
-- Old rows show NULL for discount_amount until they happen to be
-- re-processed by a future incremental run — they are NOT retroactively
-- backfilled with a real value automatically.

Late-arriving data — the limit of any incremental filter

Every incremental filter makes an assumption: that the source system won't hand you a row whoseevent_time or updated_at is older than the window the filter already scanned past. Late-arriving data breaks that assumption — a mobile client that buffers events offline and syncs them three days later will produce a row with an event_time from three days ago, arriving in the source table today. A naive where event_time > max(event_time)filter never catches it, because by the time it lands, the incremental window has already moved past that timestamp.

{% if is_incremental() %}
  where event_time > (
    select dateadd('hour', -3, max(event_time)) from {{ this }}
  )
{% endif %}

-- Re-scans a 3-hour overlap on every run, trading a small amount of
-- extra compute for tolerance to events that arrive a few hours late.
-- Combined with unique_key + merge, re-scanning the overlap safely
-- updates any row that already exists rather than duplicating it.

Monitoring — the signal that an incremental model is unhealthy

The single most useful health check for an incremental model in production is comparing its incrementally-built row count and key aggregates (sums, counts) against a periodic full-refresh run in a non-production environment, exactly as described in Part 06. A second useful signal is simply tracking how many rows each incremental run actually processes over time — a sudden jump from a few thousand rows per run to several million is usually a sign that the source'supdated_at column stopped updating reliably somewhere upstream, silently forcing the filter to re-scan far more history than intended.

An audit query pattern for ongoing confidence, without a full-refresh every time

Running a full --full-refresh diff is the most thorough check, but it is also the most expensive — it means paying the full rebuild cost just to validate correctness. A cheaper, lightweight check that catches a large share of real divergence bugs without a full rebuild is an audit query comparing row counts and a business-critical aggregate between the incremental table and its immediate upstream source, scoped to a recent window.

-- Run this periodically (e.g. as a dbt test, or a scheduled check) against
-- the last 7 days of data, comparing the incremental model to its source:

with source_side as (
    select count(*) as source_row_count
    from {{ ref('stg_orders') }}
    where updated_at > current_date - interval '7 days'
),

fact_side as (
    select count(*) as fact_row_count
    from {{ ref('fct_orders') }}
    where updated_at > current_date - interval '7 days'
)

select
    source_side.source_row_count,
    fact_side.fact_row_count,
    source_side.source_row_count - fact_side.fact_row_count as row_count_diff
from source_side, fact_side
where source_side.source_row_count != fact_side.fact_row_count

A nonzero row_count_diff for a recent window is an early warning sign worth investigating well before it grows large enough for a stakeholder to notice in a dashboard. This kind of audit query can itself be wired up as a dbt singular test — exactly the pattern covered in the next module in this track — turning an ad hoc sanity check into an automatically enforced assertion that runs on every dbt build.

Five Misconceptions About Incremental Models

Three Incremental Model Incidents, Three Different Warehouses

5 Interview Questions — With Complete Answers

Five Mistakes Engineers Make Building Their First Incremental Models

Incremental Model Errors — And Exactly Why They Happen