Incremental Models

Incremental models process only new or changed data instead of rebuilding the entire table. SQLBuild works out where to resume by reading the highest cursor value (timestamp or integer) already in the target table, so there is no state store or checkpoint to maintain. If a model fails for several runs, the next successful build picks up from the last data it actually wrote, with no manual backfilling.

Strategies

append

Inserts new rows without modifying existing data. Optionally uses a cursor (read from the target table’s highest value) to avoid reprocessing the full source on every run.

MODEL (
  materialized incremental,
  incremental_strategy append,
  cursor created_at,
  cursor_type timestamp,
  cursor_grain second,
  append_cursor_inclusive true,
);

SELECT id, customer_id, created_at
FROM __source("raw_events")

When append_cursor_inclusive is true (the default), the lower bound uses >=, which may duplicate the boundary row but avoids missing late-arriving data with the same cursor value. Set to false for an exclusive (>) lower bound if your cursor values are guaranteed unique. Append without a cursor is also valid. The model simply inserts all rows from the source query on every run.

delete_insert

Deletes rows in the cursor range, then inserts the new delta. Requires either cursor or unique_key.

MODEL (
  materialized incremental,
  incremental_strategy delete_insert,
  unique_key [order_id],
  cursor order_id,
  cursor_type integer,
);

SELECT order_id, customer_id, order_status, ordered_at, line_total_cents
FROM __ref("fact_orders")

With a cursor, delete_insert removes rows where the cursor column falls within the replay window, then inserts the new delta. With a unique_key only, it deletes matching rows by key before inserting.

merge

Upserts rows using a unique key. Matched rows are updated; unmatched rows are inserted.

MODEL (
  materialized incremental,
  incremental_strategy merge,
  unique_key [customer_id],
  cursor last_ordered_at,
  cursor_type timestamp,
  cursor_grain second,
  cursor_inputs (
    fact_orders ordered_at,
  ),
);

SELECT
  customer_id,
  MAX(ordered_at) AS last_ordered_at,
  COUNT(*) AS total_orders,
  SUM(line_total_cents) AS total_revenue_cents
FROM __ref("fact_orders")
GROUP BY customer_id

merge always requires unique_key. The cursor controls which upstream rows are scanned; the unique key determines how they’re matched against the target.

Cursors

Cursors define the incremental replay boundary. SQLBuild queries MAX(cursor) from the target table and MIN/MAX from upstream inputs to compute the replay window automatically.

Field	Description
`cursor`	Output column used to track incremental position
`cursor_type`	`timestamp` or `integer`
`cursor_grain`	Time grain for timestamp cursors: `second`, `minute`, `hour`, `day`, `month`, `year`
`cursor_start`	Lower bound floor; the cursor will never replay before this value
`cursor_inputs`	Map of upstream ref/source names to their cursor columns

cursor_inputs

When a model references multiple upstream inputs, cursor_inputs is required to tell SQLBuild which column on each input carries the cursor:

MODEL (
  materialized incremental,
  incremental_strategy delete_insert,
  cursor activity_hour,
  cursor_type timestamp,
  cursor_grain hour,
  cursor_inputs (
    fact_orders ordered_at,
  ),
);

SQLBuild uses these to compute MIN/MAX across the listed inputs and determine the replay window.

Listed inputs bound the window; unlisted inputs do not

Only the inputs you list in cursor_inputs bound the replay window. This is an explicit choice, and it has two consequences worth understanding:

Listed inputs drive the window. Their new data advances the MAX, which is what tells SQLBuild how far to reprocess and which rows of the target to rewrite.
Unlisted inputs are read in full. SQLBuild does not add a cursor filter to them, and they do not bound the window. This is correct for lookup or dimension tables that have no meaningful cursor column: you do not list them, and SQLBuild reads them whole rather than trying to filter on a column that may not exist.

The implication for delete_insert and merge: the target rows that get rewritten are the ones whose cursor falls inside the window derived from the listed inputs. If an unlisted input changes in a way that should affect target rows outside that window, those rows are not rewritten on a normal incremental run. List every input whose new data should drive reprocessing; leave unlisted only the inputs you intend to read in full. To capture changes that fall outside the normal forward window, see Lookback for late-arriving data and Replay on change for model changes.

Lookback

Lookback extends the start of the replay window backwards to re-process recent data. The cursor is forward-moving, so use lookback to capture late-arriving or backfilled records that land just behind the current position:

MODEL (
  materialized incremental,
  incremental_strategy delete_insert,
  cursor event_date,
  cursor_type timestamp,
  cursor_grain day,
  lookback 3d,
);

With lookback 3d, the replay window starts 3 days before the normal cursor position, ensuring that any late-arriving data within that window is picked up.

Microbatch execution

For large incremental ranges, microbatch mode splits the replay window into configurable batches. Each batch is processed serially with its own audit cycle: create delta, run delta audits, apply DML, clean up.

MODEL (
  materialized incremental,
  incremental_strategy delete_insert,
  cursor activity_hour,
  cursor_type timestamp,
  cursor_grain hour,
  cursor_inputs (
    fact_orders ordered_at,
  ),
  incremental_mode microbatch,
  batch_size 1d,
);

Without microbatch mode, the entire replay range is processed in one pass.

Batch size

batch_size controls the window size for each batch. For timestamp cursors, use duration strings like 1d, 6h, 1mo. For integer cursors, use an integer value.

Mixed-grain chains

When a downstream microbatch model reads from an upstream model with a coarser time grain, SQLBuild aligns the replay to the coarsest participating grain (the model’s own grain and its cursor-input grains). This happens on every run that resolves cursor bounds from upstream models, not only when something changes. It is independent of the replay_on_change cascade behavior described below. Alignment does two things: it floors the replay window edges to the coarsest grain, and it coarsens the batch size to that grain. For example, an hourly model downstream of a daily model processes in day-sized batches, so each batch lines up with a unit of upstream data that actually advances instead of producing empty or boundary-straddling windows:

-- Upstream: daily grain, 2d batches
MODEL (
  materialized incremental,
  incremental_strategy delete_insert,
  cursor activity_day,
  cursor_type timestamp,
  cursor_grain day,
  cursor_inputs (
    hourly_order_activity activity_hour,
  ),
  incremental_mode microbatch,
  batch_size 2d,
);

-- Downstream: hourly grain, but aligns to day automatically
MODEL (
  materialized incremental,
  incremental_strategy delete_insert,
  cursor activity_hour,
  cursor_type timestamp,
  cursor_grain hour,
  cursor_inputs (
    daily_activity_rollup activity_day,
  ),
  incremental_mode microbatch,
  batch_size 6h,
);

Replay on change

When a model’s version identity changes (query, config, upstream cascade, or any other change reason), replay_on_change is the explicit, per-model policy for how much data to reprocess. Reprocessing is a policy you set, not an automatic forced rebuild, so a definition change does not silently trigger a full rebuild of large downstream tables. You choose the cost per model:

Value	Effect
`forward`	Run the normal incremental delta from the cursor (default)
`full`	Full table rebuild
`bounded-14d`	Replay the last 14 days of data

The bounded duration supports d (days), h (hours), m (minutes), and s (seconds). For example: bounded-7d, bounded-24h, bounded-30m.

MODEL (
  materialized incremental,
  incremental_strategy delete_insert,
  cursor activity_hour,
  cursor_type timestamp,
  cursor_grain hour,
  replay_on_change full,
);

See Cascade propagation for how replay policies propagate through the DAG and how downstream models can override inherited replay behavior.

on_schema_change

Controls how schema differences are handled at execution time when the incremental delta has different columns than the target table:

Value	Effect
`append_new_columns`	Add new columns to the target table (default)
`sync_all_columns`	Add, drop, and alter columns to match the delta
`ignore`	Log and continue without schema changes
`fail`	Reject the build with an error

Getting Started

dbt Compatibility

Concepts

Python Nodes

Virtual Environments (Alpha)

Integrations

CLI Reference

Incremental Models

Strategies

append

delete_insert

merge

Cursors

cursor_inputs

Listed inputs bound the window; unlisted inputs do not

Lookback

Microbatch execution

Batch size

Mixed-grain chains

Replay on change

on_schema_change

​Strategies

​append

​delete_insert

​merge

​Cursors

​cursor_inputs

​Listed inputs bound the window; unlisted inputs do not

​Lookback

​Microbatch execution

​Batch size

​Mixed-grain chains

​Replay on change

​on_schema_change

Strategies

append

delete_insert

merge

Cursors

cursor_inputs

Listed inputs bound the window; unlisted inputs do not

Lookback

Microbatch execution

Batch size

Mixed-grain chains

Replay on change

on_schema_change