Skip to main content
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.
FieldDescription
cursorOutput column used to track incremental position
cursor_typetimestamp or integer
cursor_grainTime grain for timestamp cursors: second, minute, hour, day, month, year
cursor_startLower bound floor; the cursor will never replay before this value
cursor_inputsMap 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 all upstream inputs and determine the replay window.

​
Lookback

Lookback extends the start of the replay window backwards to re-process recent data. This is useful for handling late-arriving records: 
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 depends on an upstream model with a coarser time grain, SQLBuild automatically widens the replay window to the largest participating grain. For example, an hourly model downstream of a daily model will process in day-sized batches to prevent empty 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 widens 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 controls how much data to reprocess:
ValueEffect
forwardRun the normal incremental delta from the cursor (default)
fullFull table rebuild
bounded-14dReplay 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 Planning and Change Detection: 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:
ValueEffect
append_new_columnsAdd new columns to the target table (default)
sync_all_columnsAdd, drop, and alter columns to match the delta
ignoreLog and continue without schema changes
failReject the build with an error