Materializations & incremental models

In the last lesson every model became a view by default. That's only one of several options. A materialization is dbt's word for how a model gets persisted in the warehouse — and choosing the right one is one of the highest-leverage decisions in a dbt project, because it directly controls build time and cost. This lesson covers the four materializations and then goes deep on the one that trips up almost everyone: incremental.

What a materialization is

Recall from the mental-model lesson that dbt wraps your SELECT in a CREATE statement. A materialization decides which CREATE statement — and therefore what physical object exists in the warehouse and how it's refreshed on each dbt run. You set it with a config block at the top of a model (or as a default in dbt_project.yml):

{{ config(materialized='table') }}

select ...

There are four built-in materializations. Three are simple; the fourth (incremental) needs the rest of this lesson.

The three simple materializations

view (the default)

dbt creates a view — a saved query that stores no data; it re-runs against the underlying tables every time someone queries it.

• Build cost: ~zero. dbt run just (re)defines the view; no data is computed at build time.
• Query cost: paid every time the view is queried, because the logic re-executes.
• Freshness: always live — a view reflects the current state of its inputs.
• Use it for: lightweight transformations, staging models, anything queried infrequently.

table

dbt drops and fully recreates a physical table on every dbt run, executing your SELECT and storing the result.

• Build cost: the full query runs at build time, every run.
• Query cost: cheap and fast — it's a real table; no logic re-runs.
• Freshness: as fresh as your last dbt run.
• Use it for: models that are expensive to compute but queried often (e.g. dashboard-facing marts) — pay once at build, query cheaply many times.

The view-vs-table choice is a classic compute trade-off: view shifts cost to query time; table shifts cost to build time. Query something rarely → view. Query something constantly → table.

ephemeral

An ephemeral model is never built as its own object. Instead, dbt inlines its SQL as a CTE (Common Table Expression — a WITH ... AS (...) subquery) directly into any model that ref()s it.

• Build cost: none — nothing is created in the warehouse.
• Use it for: small, reusable bits of logic you want to keep DRY but don't need to expose as their own table or view. The downside: it can't be queried or tested on its own (it has no physical existence), and over-use makes compiled SQL hard to read.

The problem incremental solves

Now the hard one. Take ShopFlow's fact_sales (ShopFlow — see Meet ShopFlow) once the store has been running for years: two billion order line items, growing by 50 million a day as new orders come in. Materialize it as a table and every single dbt run drops and rebuilds all two billion rows — scanning every order line ever placed, every time, to add one day's sales. That's absurdly slow and expensive. You don't want to rebuild history; you want to process only the new rows and append/merge them into the existing table.

That's exactly what the incremental materialization does: on the first run it builds the whole table, and on every run after that it processes only the new or changed rows and adds them to what's already there.

How incremental works: is_incremental()

An incremental model has to behave differently on its first run (build everything) versus later runs (only the new rows). dbt gives you a flag for this: the is_incremental() macro. It returns:

• false on the first run, or when you do a --full-refresh (rebuild from scratch), or if the table doesn't exist yet.
• true on subsequent runs, when the table already exists and you're adding to it.

An incremental run has two separate decisions: which source rows to select, then how to apply those rows to the target. A merge strategy handles the second decision; it cannot recover a row the SELECT excluded.

For ShopFlow's fact_sales, filtering on order_ts alone is insufficient: an old order can arrive late, or its line-item quantity can be corrected without changing when the order was placed. This example instead assumes that ingestion adds a non-null UTC _loaded_at timestamp to both staging tables and refreshes it whenever that row's current value changes. This is ingestion metadata added for this example, not a field in the original ShopFlow source schema. Each staging table must already contain one current row per key: order_id for orders and (order_id, product_id) for line items.

The model carries the later of the two input load timestamps as source_loaded_at. It selects a three-day overlap and merges by the order-line key. The overlap is an example policy, not a universal safe lateness limit. This is illustrative dbt SQL for an adapter supporting merge; timestamp/interval syntax varies by warehouse.

{{ config(
    materialized='incremental',
    incremental_strategy='merge',
    unique_key=['order_id', 'product_id']
) }}

with source_rows as (
    select
        oi.order_id,
        oi.product_id,
        o.customer_id,
        o.order_ts,
        oi.quantity * oi.unit_price as line_revenue,
        greatest(o._loaded_at, oi._loaded_at) as source_loaded_at
    from {{ ref('stg_order_items') }} oi
    join {{ ref('stg_orders') }} o on oi.order_id = o.order_id
)
select * from source_rows
{% if is_incremental() %}
where (select max(source_loaded_at) from {{ this }}) is null
   or source_loaded_at >= (
       select max(source_loaded_at) - interval '3 days'
       from {{ this }}
   )
{% endif %}

Read the two halves separately:

• Selection: {{ this }} means this model's existing target table. max(source_loaded_at) is its high-water mark. IS NULL selects all source rows when the target exists but is empty. Without that branch, comparisons to a null maximum select no rows. This requires no arbitrary earliest timestamp. On the first run the is_incremental() block is absent, so the model reads all inputs.
• Overlap: >= includes rows exactly on the cutoff. Subtracting three days reselects a bounded slice, including rows that became visible late within that allowance. Equal timestamps are safe to reselect because application is keyed, rather than a blind append.
• Application: merge updates an existing (order_id, product_id) or inserts a new one. A changed order header reselects its lines; a changed line item reselects that line, even if order_ts is old. Deduplicate staging inputs deterministically and test non-null, unique keys before this step; unique_key is a matching rule, not a uniqueness constraint.

Trace a run whose target maximum is 2026-01-10 00:00:00, giving a cutoff of 2026-01-07 00:00:00:

Input or target state	Selected?	Why / result after keyed application
Target exists but is empty	All source rows	The IS NULL branch avoids the null-maximum trap
Two different lines loaded exactly at the cutoff	Both	>= preserves boundary ties; both keys are retained
Order placed January 1, first loaded January 11	Yes	Selection uses load time, not order placement time
January 1 line corrected and loaded January 11	Yes	The line's new load timestamp selects it; its existing key is updated
Same source state retried	Rows within the current overlap	Selected keys receive the same values; earlier target rows remain, so the final target is unchanged
Previously unseen row with load time January 6	No	It is outside this overlap; merge never receives it

A watermark records progress; it does not prove all older data has arrived. A bounded overlap needs a measured lateness assumption and reconciliation or an explicit backfill for anything outside it. A reliable CDC/change marker can provide a stronger selection mechanism. Hard deletes also need an explicit deletion path: a disappeared source row cannot be selected by this model. See dbt's incremental-model guidance for the selection, unique-key and adapter details.

Test the selection yourself

After the Jinja and testing lessons, Review an incremental model lets you challenge an illustrative LLM response with empty-target, boundary, late-data, correction and retry fixtures. It runs locally without a model account. The SQLite simulation checks selection and a reference keyed update; it does not replace testing the real dbt adapter and warehouse.

Incremental strategies: how new rows get combined in

Filtering to new rows is half the job. The other half is: once you have those new rows, how do you put them into the existing table? That choice is the incremental strategy, and it's where merge-vs-overwrite confusion lives. There are four common strategies (availability varies by adapter):

append

Just INSERT the new rows. Fastest, simplest. Use when rows are immutable and never duplicated — pure event logs where a row, once written, never changes. Danger: if a row you already loaded shows up again, you get a duplicate — append does no deduplication.

merge (the most common)

Run a SQL MERGE: for each new row, if a row with the same unique_key already exists, update it; otherwise insert it. This is "upsert" (update-or-insert). Use when rows can change after first being written — a ShopFlow order whose status goes placed → paid → shipped, which must update the existing stg_orders row, not duplicate it. The unique_key config tells dbt how to match a new row to an existing one.

-- models/staging/stg_orders.sql (as an incremental upsert on the order header)
{{ config(materialized='incremental', incremental_strategy='merge', unique_key='order_id') }}

On an incremental run, dbt loads the new rows into a temp table, then issues roughly:

merge into analytics.stg_orders as target
using new_rows as source
  on target.order_id = source.order_id        -- match on unique_key
when matched then update set ...               -- order existed -> update its status
when not matched then insert ...;              -- new order -> insert it

delete+insert

DELETE the rows that match your new batch's keys, then INSERT the new batch. Achieves an upsert-like result on warehouses without an efficient MERGE. Use when merge isn't well-supported or you want to fully replace a set of keys.

insert_overwrite

Replace data one partition at a time. The model is partitioned (e.g. by day); on each run dbt computes the affected partitions and overwrites those whole partitions, leaving the rest untouched. Use when you reprocess data in chunks — "rebuild today's and yesterday's partitions" — common on partitioned warehouses like BigQuery. It's idempotent at the partition grain: re-running for the same day yields the same result, no duplicates.

Choosing a strategy

Immutable event logs → append (fastest, but watch for duplicates). Mutable records with a stable key → merge (the safe default; needs unique_key). Partitioned reprocessing → insert_overwrite (idempotent per partition). delete+insert when merge isn't available. With deduplicated inputs and non-null unique keys, merge makes overlapping selections safe to apply again. It does not fix an incomplete input selection.

The honest trade-off: incremental is faster but riskier

Incremental models are the biggest performance win in dbt and the biggest source of subtle bugs. The risks are real:

• A wrong watermark filter silently misses data. Select changes using a reliable change/load marker, handle an empty target, and include boundary ties. A lookback only covers its stated window; recover older omissions with reconciliation/backfill. merge can apply selected rows safely, but it cannot recover excluded rows.
• append with re-delivered rows duplicates data. Use merge if rows can repeat.
• Schema changes and historical values are separate concerns. Where supported by the adapter, on_schema_change='append_new_columns' can add columns and 'sync_all_columns' can synchronize top-level columns. These settings do not backfill values in old rows. Choose a targeted backfill or --full-refresh when historical values or changed transformation logic must be recomputed; review adapter support and cost before rebuilding a large table. See dbt's schema-change options.

The mental model: first run builds everything; later runs select new, changed, and overlapping rows, then apply them with the chosen strategy. Check both selection completeness and safe application; faster runs are useful only if the result stays correct.

Why it matters

A materialization decides what each model becomes: a view (no stored data, pays at query time), a table (fully rebuilt each run, cheap to query), ephemeral (inlined as a CTE, never built), or incremental (build once, then process only new/changed rows). Incremental is the key to scale: is_incremental() filters to new rows on later runs, and the strategy — append, merge, delete+insert, or insert_overwrite — decides how those rows merge into the existing table. It's the most powerful and most error-prone dbt feature, so reason about the watermark and strategy deliberately. Next we'll see the Jinja machinery ({% if %}, {{ this }}) that made incremental possible — and how to test your models so a bad incremental load can't ship silently.

Next: Jinja, macros, packages & testing →