Incremental model
An incremental model is a transformation that processes only new or changed source data on each run and merges it into an existing table, instead of rebuilding the whole table from scratch every time.
Overview
As a table grows, rebuilding it completely on every pipeline run gets slower and more expensive—reprocessing years of historical orders every time a new day of data arrives wastes compute on rows that haven't changed. An incremental model solves this by processing only the new or changed rows since the last run and merging them into the existing table, so run time stays roughly proportional to the size of the new data rather than the size of the whole table.
The term is most associated with dbt, where materialized='incremental' is one of the standard materialization strategies (alongside table, view, and ephemeral), but the underlying pattern—process new data, merge into existing state—predates dbt and shows up in any hand-rolled pipeline that avoids full reprocessing.
How dbt incremental models work
A dbt incremental model uses the is_incremental() macro to add a filter that only applies on incremental runs (not on the first run, when the table doesn't exist yet):
Copy code
{{
config(
materialized='incremental',
unique_key='order_id'
)
}}
SELECT
order_id,
customer_id,
order_date,
amount
FROM {{ source('raw', 'orders') }}
{% if is_incremental() %}
WHERE order_date > (SELECT max(order_date) FROM {{ this }})
{% endif %}
On the first run, the whole source table is selected and the target table is created. On every subsequent run, only rows newer than the current maximum order_date in the target are selected, and dbt merges them in according to the configured strategy.
Incremental strategies
Different adapters support different merge strategies, configured via incremental_strategy:
- append: just insert the new rows (fastest, but can create duplicates if a row is reprocessed).
- merge: upsert new rows by
unique_key, updating existing rows and inserting new ones. - delete+insert: delete rows matching the new batch's keys, then insert the new batch (a common strategy where native
MERGEisn't available).
dbt-duckdb, the DuckDB adapter for dbt, supports the standard incremental materialization; because DuckDB is fast and has no cluster warm-up cost, incremental models on DuckDB are often chosen for query-time efficiency and to avoid re-scanning large Parquet/S3 datasets on every run, rather than purely for cost reasons the way they might be on a metered cloud warehouse.
Related terms
A backfill is the process of re-running a pipeline over a range of historical data—typically to populate a new table, fix a bug, or apply changed logic to past periods.
Batch processing →Batch processing is the execution of data processing jobs on accumulated groups (batches) of records at scheduled intervals, rather than processing each record as it arrives.
MERGE statement →MERGE INTO is a SQL statement that inserts, updates, or deletes rows in a target table based on how they match rows from a source table, in a single atomic operation.
ALTER TABLE statement →The ALTER TABLE statement allows you to modify the structure of an existing database table without having to recreate it from scratch.
INSERT statement →The INSERT statement is a fundamental SQL command used to add new rows of data into a table.
CREATE TABLE AS SELECT (CTAS) →CREATE TABLE ... AS SELECT (CTAS) creates a new table and populates it in one statement, using the result of a query to define both its schema and its data.
FAQS
Once a table is large enough that full rebuilds take too long or cost too much, and the source data is mostly append-only (new rows arrive, old rows rarely change). Small tables are usually simpler and safer as full rebuilds.
Incremental models are faster to run but more complex to reason about and to fix when something goes wrong—correcting historical data usually requires a full-refresh or a targeted backfill, since normal incremental runs only touch new rows.
It returns true only when the model is being run incrementally against an already-existing target table (not a full refresh and not the first run), which is what lets a single model definition handle both the initial build and subsequent incremental runs.
