What is an Incremental Model?

How it works

A full refresh recomputes a table from all of its source data every run. That is simple and always correct, but it gets slower and costlier as history grows. An incremental model keeps the existing rows and processes only the delta:

1. Find the delta with a high-water mark, usually updated_at > (SELECT max(updated_at) FROM target), or an ID, a partition date or a change feed (CDC, Snowflake streams, Delta change data feed).
2. Apply it: append for immutable events, or merge on a unique key when rows can change.
3. Keep a lookback: subtract an interval from the watermark (for example three days) so late-arriving rows are not missed; the merge on the key makes reprocessing them harmless.

In dbt this is materialized='incremental' with is_incremental() around the filter and a unique_key for merges; the strategy can be append, merge, delete+insert or insert_overwrite by partition. The model still needs a way back to correctness: a full refresh (dbt run --full-refresh) after logic changes, and a backfill for corrected history.

Example

The first run loads everything; the second run touches only the new order and the one that changed, and merges them on order_id (PostgreSQL 16).

CREATE TABLE src_orders (order_id int, status text, updated_at timestamp);
INSERT INTO src_orders VALUES (1, 'placed', '2026-10-20 09:00'), (2, 'placed', '2026-10-20 10:00');
CREATE TABLE fct_orders (order_id int PRIMARY KEY, status text, updated_at timestamp);

-- One incremental run: only rows newer than what the target already holds.
CREATE OR REPLACE FUNCTION run_incremental() RETURNS int LANGUAGE sql AS $$
  WITH src AS (
    SELECT * FROM src_orders
    WHERE updated_at > (SELECT coalesce(max(updated_at), '-infinity') FROM fct_orders)
  ), merged AS (
    INSERT INTO fct_orders SELECT * FROM src
    ON CONFLICT (order_id) DO UPDATE SET status = excluded.status, updated_at = excluded.updated_at
    RETURNING 1
  )
  SELECT count(*)::int FROM merged;
$$;

SELECT run_incremental() AS rows_processed;          -- first run: full load
INSERT INTO src_orders VALUES (3, 'placed', '2026-10-21 08:00');
UPDATE src_orders SET status = 'shipped', updated_at = '2026-10-21 09:00' WHERE order_id = 1;
SELECT run_incremental() AS rows_processed;          -- next run: only the 2 changed rows
SELECT * FROM fct_orders ORDER BY order_id;

 rows_processed
----------------
              2

 rows_processed
----------------
              2

 order_id | status  |     updated_at
----------+---------+---------------------
        1 | shipped | 2026-10-21 09:00:00
        2 | placed  | 2026-10-20 10:00:00
        3 | placed  | 2026-10-21 08:00:00

Common mistakes

• Filtering on a column that is not updated when a row changes, so updates are silently skipped.
• Using a strict > watermark with no lookback, so rows that arrive late with older timestamps are lost.
• Appending instead of merging when source rows can change, which creates duplicates.
• Changing the model’s logic and never running a full refresh, leaving old rows computed the old way.