Lesson 2 of 4 in this module

11/14 in track

Incremental vs full refresh

sql12 min

Overview

is_incremental() becomes a WHERE on a high-watermark column. You write that filter. dbt's merge wrapper is still the CLI.

On this page8 sections

The decision

The orders model rebuilds years of historical transactions every night, even though only yesterday's rows are new. The batch run takes hours and consumes unnecessary warehouse compute credits.

An incremental model solves this by processing only the new or changed records since the last run, merging them into the existing warehouse table.

Should you rebuild entire tables or process only new rows?

An incremental model processes only new or updated rows since the last dbt run, instead of rebuilding the entire table from scratch.

For a table that gains 10,000 rows per day but has 10 million total, an incremental model processes 10,000 rows instead of 10 million.

Incremental models exchange rebuild simplicity for runtime speed by tracking a high-water mark timestamp.

dbt achieves this with a watermark: a timestamp or sequence number that marks how far you have already processed. The model includes a filter like WHERE updated_at > (SELECT MAX(updated_at) FROM this_table) that only picks up new rows.

In Jinja, dbt uses is_incremental() to conditionally add this filter. On the first run when no table exists, it processes everything. On subsequent runs, it processes only new data.

Words you will see in this lesson

Six words. The rest of the lesson uses only these.

WordWhat it means
IncrementalOnly process rows that are new since the last run
Full refreshRebuild the whole table from scratch
WatermarkThe latest timestamp already loaded, used to find what is new
Unique keyThe column dbt uses to decide whether to insert or update a row
Late dataA row that arrives after its day has already been processed
MergeUpdate rows that already exist and insert the ones that are new

What is at stake

What happens to compute bills and runtimes as tables grow?

Imagine a click-stream table that grows by 50 million events per day. After a year, it has 18 billion rows. Rebuilding this table from scratch every night would take hours and cost hundreds of dollars in warehouse compute.

  1. Day 1: Full scan50M rows scanned in 2 mins
  2. Day 180: Full scan9B rows scanned in 75 mins
  3. Day 365: Full scan18B rows scanned, pipeline timeouts
  4. Incremental shiftScans only today's 50M rows in 3 mins

An incremental model processes only today's 50 million new events, finishing in minutes at a fraction of the cost. The tradeoff is complexity: if your watermark logic has a bug, you can miss rows or process duplicates. A full refresh is always correct but slow.

Option A vs Option B

How does a MERGE statement handle late-arriving records?

The incremental pattern has three parts: the SELECT query, the watermark filter, and the unique key. When rows arrive late, the merge logic determines whether old records get updated or duplicates appear.

Watermark, lookback, unique key. Predict what happens to a Monday file that lands Wednesday.

Tracking changes reliably requires choosing the right balance between processing speed and lookback coverage for delayed source data.

Which incremental strategy fits your warehouse and data pattern?

When a model is configured with materialized='incremental', dbt needs to know how to combine newly extracted rows with the existing warehouse table. dbt supports 4 core incremental strategies across cloud platforms:

Comparison of dbt incremental processing strategies across cloud warehouses.

Incremental StrategyUnderlying SQL ExecutionSupported EnginesBest Use Case
merge (default)Executes an ANSI MERGE statement matching on unique_key.Snowflake, BigQuery, Databricks, Postgres.Fact tables with updates to existing rows (order status transitions).
delete+insertExecutes DELETE WHERE key IN (batch), then INSERT.Redshift, Postgres.Warehouses lacking native MERGE or where MERGE performance is slow.
insert_overwriteReplaces entire physical partitions matching the incoming data batch.BigQuery, Snowflake, Spark, Databricks.High-volume date-partitioned event tables where updating partitions is faster than row-by-row scanning.
appendExecutes a direct INSERT INTO target.All warehouses.Immutable append-only telemetry streams and click logs.

Choosing merge provides clean row-level upserts, while insert_overwrite is ideal for large partitioned tables where partition replacement is cheaper than row-by-row matching.

How does Slim CI accelerate deployment pipelines without full runs?

In large multi-project repositories with thousands of models, running dbt build on every pull request is too slow and expensive. Running 2,000 models on every commit can consume thousands of dollars in warehouse compute credits.

dbt Slim CI execution command using state comparison and deferral (illustration, not run here)

The defer flag instructs dbt that if an upstream model was not modified in this PR, dbt queries the existing production table instead of rebuilding it in the staging schema. Slim CI cuts pull request runtimes from 2 hours down to 4 minutes.

A worked comparison

How does dbt compile full refresh queries versus incremental queries?

SQLIncremental pattern: full vs filtered (illustration, not run here)
-- What dbt generates for an incremental model:

-- First run (table does not exist): process everything
SELECT order_id, updated_at, order_total AS amount
FROM orders;

-- Subsequent runs (table exists): process only new/updated rows
SELECT order_id, updated_at, order_total AS amount
FROM orders
WHERE updated_at > (SELECT MAX(updated_at) FROM analytics.fct_orders);

-- In dbt Jinja:
-- SELECT order_id, updated_at, order_total AS amount
-- FROM {{ source('jaffle', 'orders') }}
-- {% if is_incremental() %}
-- WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
-- {% endif %}

On run 1, the table does not exist so is_incremental() returns false, executing a full scan. On subsequent runs, it injects the WHERE clause to fetch only rows newer than the existing table max.

How does the high-water mark advance across successive runs?

Watermark advances each run
Run 1: all rows(watermark = Aug25)New rows arriveAug 26Run 2: only Aug26+ rowsWatermark advancesto Aug 26

Run 1 processes all data. Run 2 processes only rows with updated_at after the watermark.

Each scheduled run advances the high-water mark to the newest timestamp it has accepted. A timestamp filter can miss late updates, equal timestamps, or deletes, so production models often add a lookback window and a unique-key merge.

What components assemble a complete incremental model?

Every incremental model relies on four coordinated components to filter, match, and merge incoming batches into the destination table.

ComponentPurposeExample
Watermark columnIdentifies new/changed rowsupdated_at, created_at, _loaded_at
Watermark filterSkips already-processed rowsWHERE updated_at > max(existing)
Unique keyPrevents duplicates on MERGEorder_id
StrategyHow to handle matchesmerge (upsert) or append

Missing any one component causes issues: omitting the unique key causes duplicate rows, while omitting the watermark filter reverts the query to an expensive full scan.

How do you practice incremental filtering in a single-statement query?

For this query tab, there is no {{ this }} table from a previous run. You write a fixed watermark date:

SQLFixed watermark filter on orders
-- Simulating an incremental run with a fixed watermark
SELECT order_id, updated_at, order_total AS amount
FROM orders
WHERE CAST(updated_at AS DATE) >= DATE '2026-08-01'
ORDER BY order_id
LIMIT 5;

Verified output filtering orders updated on or after August 1, 2026.

order_idupdated_atamount
ORD-00012026-08-02 19:33:28612.07
ORD-00022026-08-03 20:40:58446.70
ORD-00032026-08-06 11:58:041104.32
ORD-00042026-08-11 06:43:33339.90
ORD-00052026-08-14 08:06:40261.73

This simulates what dbt generates at runtime: filtering against a boundary date so the query engine processes only the relevant recent slice of rows.

What are the key rules for deploying incremental models?

Moving from full table rebuilds to incremental updates requires deliberate design choices around keys, filters, and backfills.

Incremental pipeline design from watermark to merge
  1. Pick a reliable timestamp like updated_at or ingested_at that strictly advances.

    1. watermark
    2. timestamp
  2. Include a 2-3 day lookback buffer in the WHERE clause to catch late-arriving records.

    1. lookback
    2. late data
  3. Set unique_key to match the exact grain of the model so MERGE updates existing records.

    1. unique_key
    2. merge grain
  4. Run dbt run --full-refresh periodically to clear subtle drift and sync schema changes.

    1. full-refresh
    2. schema sync
  1. Define unique_key in the model config matching the table grain.
  2. Wrap the watermark filter in an if is_incremental block targeting {{ this }}.
  3. Add a lookback window to absorb late-arriving records without manual re-runs.
  4. Select the appropriate strategy like merge for upserts or insert_overwrite for partitioned tables.
  5. Use Slim CI with state deferral to avoid unnecessary rebuilds during PR testing.
Watch outUnique key must match the grain

If your unique key is order_id but the table has multiple rows per order (like one per line item), the MERGE will collapse them incorrectly. The unique key must match the model's declared grain.

RememberThis is the same watermark from CDC

The orchestration track taught LSN watermarks for CDC. Incremental models use the same idea: a high-water mark that advances with each run, skipping already-processed data.

Common beginner questions

What if a row is updated in the source with an older timestamp?

If an order record is corrected in the source database but its updated_at column is backdated, a strict watermark filter misses it. Analytics engineers solve this by adding a lookback window, such as filtering for updated_at greater than the maximum minus 3 days, paired with a unique key merge.

When should an incremental model be avoided?

Avoid incremental models for small tables with under 1 million rows. Full table rebuilds are simpler, self-healing, and fast enough on modern cloud warehouses. Use incremental models when table growth makes nightly rebuilds noticeably slow or expensive.

Why must the unique key match the model grain?

dbt uses the unique key to match incoming records against existing rows during MERGE operations. If the unique key is set to order_id on a line-item table that has multiple rows per order, the merge statement collapses distinct items into a single row, corrupting your table grain.

What comes next

Incremental models process newly inserted or modified rows, but operational updates still overwrite past states in the source system. The next lesson introduces dbt Snapshots: how to capture point-in-time history using Slowly Changing Dimension Type 2.

Practice

Filter rows whose updated_at date is on or after 2026-08-01. Return order_id, updated_at, and amount.

Going deeper: 5 runs of an incremental model

An incremental model keeps its previous output and only processes new or changed rows. That saves time, but it also means the model has memory, and memory can go wrong in ways a full rebuild never does. This section replays 5 runs of one model, shows where it breaks, and shows how each fix works.

The model

SQLmodels/orders_current.sql
{{ config(
    materialized = 'incremental',
    unique_key = 'order_id',
    incremental_strategy = 'delete+insert',
    on_schema_change = 'append_new_columns'
) }}

select order_id, status, amount, updated_at
from {{ source('shop', 'orders') }}

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

On the first run (or with --full-refresh) the table does not exist, so is_incremental() is false and the whole source is selected. On later runs it is true: dbt keeps the existing table, builds only the rows newer than the highest updated_at already stored, and then adds them according to the strategy.

With unique_key set, a changed order replaces its old row. Without it, rows are only appended.

Replay: runs 1 to 5

The source is a current-state table with 1 row per order, which is how most application tables look. Times are updated_at values. Run 3 is the interesting one: order 4 happened at 10:05, but it reached the source only after run 2 had already stored a row for 10:30.

Replayed in DuckDB with the same delete-and-insert logic dbt uses.

RunWhat changed in the sourceRows the model selectsTarget table afterwards
1 (build)Orders 1 (09:00) and 2 (09:30) existEverything1 placed, 2 placed
2Order 2 became shipped (10:15); order 3 is new (10:30)updated_at after 09:30: orders 2 and 31 placed, 2 shipped, 3 placed
3Order 4 appears with updated_at 10:05 (late)updated_at after 10:30: nothingUnchanged. Order 4 is missing
4Same data, but the filter looks back 1 hourupdated_at after 09:30: orders 2, 3, 41, 2 shipped, 3, 4 placed (no duplicates)
5Same data againOrders 2, 3, 4 againIdentical to run 4

Run 3 is the classic bug. The filter assumes rows arrive in updated_at order, but a late row carries an old timestamp. The high-water mark has already moved past it, so it is never selected, and nothing errors.

The fix is two parts that need each other

SQLA lookback window
{% if is_incremental() %}
where updated_at > (
    select max(updated_at) - interval '1 hour' from {{ this }}
)
{% endif %}
  • The lookback re-selects recent rows on every run, so a late row inside the window is caught (run 4).
  • unique_key makes that re-selection safe. Orders 2 and 3 are selected again, but their old rows are removed first, so no duplicates appear (runs 4 and 5).
  • Without unique_key, the same lookback appends the rows again on every run. In our replay each rerun added another copy of orders 2, 3 and 4.

How long should the lookback be? At least as long as your latest-arriving data is late. If payments can arrive up to 2 days late, a 3-day lookback leaves margin. The price is paying to reprocess those days on every run, so measure the lateness instead of guessing a large number.

Watch outPick the right column for the watermark

updated_at is when the row changed in the source. A loaded_at column (when the row reached your warehouse) is often safer for late data, because it always moves forward in the order rows arrive. Event time is the worst watermark when events can arrive late.

Full refresh: when the history must change

An incremental run applies your SQL only to the rows it selects. Old rows keep whatever they were computed with. So if you change a formula, fix a bug, or backfill a source, old rows stay wrong until you rebuild everything.

SituationDo you need --full-refresh?Why
Business formula in the select changedYesOld rows were computed with the old formula
A new column was addedNot alwayson_schema_change = 'append_new_columns' adds the column; old rows hold NULL until rebuilt
The source is corrected for last monthYes, or a targeted backfillThe watermark has long passed those rows
A normal day of new ordersNoThat is exactly what incremental is for
Source rows were hard-deletedYes, or add delete handlingIncremental runs never see rows that are no longer there

The on_schema_change options are ignore (the default: new source columns are not added), fail, append_new_columns, and sync_all_columns (also removes columns that no longer exist). For very large tables you can set full_refresh = false in the model config so that an accidental --full-refresh cannot rebuild it.

Recovering from an outage

Because the filter reads the highest value already stored, an outage needs no special action. If the model did not run for 3 days, the next run selects everything newer than the stored maximum minus the lookback. Running it twice gives the same table, which is what makes incremental models safe to retry.

Try a new scenario

A payments table is updated in place. Rows can arrive up to 2 days after the payment date, and finance changed the fee calculation last week. Choose the watermark column, the lookback, the strategy, and what to do about the fee change.

Work it out, then open the answer

Watermark on loaded_at (or updated_at if it reliably changes on every edit). Lookback of 3 days to cover 2 days of lateness plus margin. unique_key = payment_id with delete+insert or merge, so reprocessed rows replace themselves. The fee change applies a new formula to history, so run --full-refresh once (or backfill the affected date range), and a normal incremental run afterwards. If payments can be deleted at the source, add a plan for that, because incremental runs cannot see deletions.

Why does 'append' plus a lookback produce duplicates?

Append inserts whatever the query returns. The lookback returns recent rows that are already stored, so they are inserted again. Use unique_key, or keep the filter strictly after the stored maximum and accept that late rows are missed.

Close the lesson and explain it

Without scrolling up, explain why run 3 lost order 4, and which two settings together fix it without creating duplicates.

Practicals · load into the editor

After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.

Finished this lesson?

Mark it as read when you could explain it to someone else.

Rate:
Was this useful?