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.
| Word | What it means |
|---|---|
| Incremental | Only process rows that are new since the last run |
| Full refresh | Rebuild the whole table from scratch |
| Watermark | The latest timestamp already loaded, used to find what is new |
| Unique key | The column dbt uses to decide whether to insert or update a row |
| Late data | A row that arrives after its day has already been processed |
| Merge | Update 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.
- Day 1: Full scan50M rows scanned in 2 mins
- Day 180: Full scan9B rows scanned in 75 mins
- Day 365: Full scan18B rows scanned, pipeline timeouts
- 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 Strategy | Underlying SQL Execution | Supported Engines | Best 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+insert | Executes DELETE WHERE key IN (batch), then INSERT. | Redshift, Postgres. | Warehouses lacking native MERGE or where MERGE performance is slow. |
| insert_overwrite | Replaces 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. |
| append | Executes 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.
# Run only modified models and downstream dependencies in CI dbt build --select state:modified+ --defer --state path/to/prod/artifacts/
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?
-- 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?
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.
| Component | Purpose | Example |
|---|---|---|
| Watermark column | Identifies new/changed rows | updated_at, created_at, _loaded_at |
| Watermark filter | Skips already-processed rows | WHERE updated_at > max(existing) |
| Unique key | Prevents duplicates on MERGE | order_id |
| Strategy | How to handle matches | merge (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:
-- 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_id | updated_at | amount |
|---|---|---|
| ORD-0001 | 2026-08-02 19:33:28 | 612.07 |
| ORD-0002 | 2026-08-03 20:40:58 | 446.70 |
| ORD-0003 | 2026-08-06 11:58:04 | 1104.32 |
| ORD-0004 | 2026-08-11 06:43:33 | 339.90 |
| ORD-0005 | 2026-08-14 08:06:40 | 261.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.
Pick a reliable timestamp like updated_at or ingested_at that strictly advances.
- watermark
- timestamp
Include a 2-3 day lookback buffer in the WHERE clause to catch late-arriving records.
- lookback
- late data
Set unique_key to match the exact grain of the model so MERGE updates existing records.
- unique_key
- merge grain
Run dbt run --full-refresh periodically to clear subtle drift and sync schema changes.
- full-refresh
- schema sync
- Define unique_key in the model config matching the table grain.
- Wrap the watermark filter in an if is_incremental block targeting {{ this }}.
- Add a lookback window to absorb late-arriving records without manual re-runs.
- Select the appropriate strategy like merge for upserts or insert_overwrite for partitioned tables.
- Use Slim CI with state deferral to avoid unnecessary rebuilds during PR testing.
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.
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
{{ 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.
| Run | What changed in the source | Rows the model selects | Target table afterwards |
|---|---|---|---|
| 1 (build) | Orders 1 (09:00) and 2 (09:30) exist | Everything | 1 placed, 2 placed |
| 2 | Order 2 became shipped (10:15); order 3 is new (10:30) | updated_at after 09:30: orders 2 and 3 | 1 placed, 2 shipped, 3 placed |
| 3 | Order 4 appears with updated_at 10:05 (late) | updated_at after 10:30: nothing | Unchanged. Order 4 is missing |
| 4 | Same data, but the filter looks back 1 hour | updated_at after 09:30: orders 2, 3, 4 | 1, 2 shipped, 3, 4 placed (no duplicates) |
| 5 | Same data again | Orders 2, 3, 4 again | Identical 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
{% 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.
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.
| Situation | Do you need --full-refresh? | Why |
|---|---|---|
| Business formula in the select changed | Yes | Old rows were computed with the old formula |
| A new column was added | Not always | on_schema_change = 'append_new_columns' adds the column; old rows hold NULL until rebuilt |
| The source is corrected for last month | Yes, or a targeted backfill | The watermark has long passed those rows |
| A normal day of new orders | No | That is exactly what incremental is for |
| Source rows were hard-deleted | Yes, or add delete handling | Incremental 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.