Model Materialization

A model materialization is the strategy by which a transformation model’s result is persisted in the warehouse. dbt, where the term is most used, defines materializations as “strategies for persisting dbt models in a warehouse” and makes the choice a single configuration line. For most strategies the model’s SQL does not change; where its output lives, and how it is refreshed, does — incremental models add a filter or a batch configuration, as described below.

This is a different question from whether a database engine materializes a CTE while running one query — see CTE materialization for that. Here the decision is about durable objects, made by the modeler rather than the optimizer.

The built-in strategies

dbt’s documented advice is to start with views and change only when a performance problem appears, to use tables for the models that BI tools and many downstream models read, and to treat incremental as something to adopt when full runs become too slow rather than as a default.

Incremental does not mean change detection

The phrase “since the last run” makes incremental sound like the tool works out what changed. It does not. You either write a filter that runs only on incremental executions or, with dbt’s microbatch strategy (v1.9 and later), configure an event-time column, a batch size, and a lookback from which the tool derives each batch’s window; either way, what the rule selects is what gets processed, and neither is change detection — so the behavior, including what happens to history, comes from four choices you make.

• The filter. Usually “rows newer than the maximum timestamp already in this table.” A source row corrected today but dated last month does not satisfy that, so it is never picked up — the silent miss that makes incremental models drift from their sources. A lookback window, re-selecting the last several days each run, catches corrections whose timestamp falls inside those days; a row corrected today but still dated last month stays outside a filter on event date, so catching it needs a filter on a change timestamp, a lookback long enough to cover the correction period, or a separate backfill.
• Whether a unique key is declared — which matters only to the strategies that use one. Under a merge strategy, a selected row whose key already exists updates that row instead of adding a second; with no key declared, the same selection appends and a re-selected row becomes a duplicate. An append strategy ignores the key entirely and never looks for an existing row, and a partition-replacement strategy works on whole partitions rather than matching keys. So read this setting together with the next one, not on its own.

The strategy. Append inserts selected rows. Merge updates matching keys and inserts new keys. Partition or range replacement rewrites the covered scope and can remove rows absent from the replacement, so that replacement must be complete for its declared scope. Merge affects matched keys; replacement affects its declared partitions or ranges. Neither scope is described adequately by the word incremental.

• Whether the run is a full refresh. A full refresh rebuilds the table from the model definition, which discards anything the incremental history had accumulated that the current definition cannot reproduce.

Two consequences are worth stating plainly. An incremental model can reprocess and change old rows — it is not an archive, and treating it as one is how a “historical” table quietly changes under a report. And deleted source rows do not disappear from it under append or merge; they remain until something removes them, so a model that must reflect deletions needs either a replacement strategy covering the affected range or an explicit delete step.

The question to answer before choosing incremental, then, is not “is the build too slow” but “how far back may a source row change, and what should happen when one does.” The filter, the lookback, and the strategy are that answer written down.

The line that matters operationally

View materialization replaces the view definition; queries evaluate that definition against its inputs. Table materialization recomputes its result. Incremental materialization uses prior target state and can append, update, or replace selected data according to its strategy. That state and the selection rule determine which past results survive.

• A full rebuild recomputes everything. Fix the SQL, run it, and the output reflects the new logic over all the input that is still available — which corrects an earlier mistake when the inputs and reference history it needs are retained and the fix has been verified, and which loses whatever the raw layer no longer holds.

With incremental processing, a corrected query does not necessarily revisit all affected history. Rows outside the selected scope can retain an earlier error; selected rows may be corrected by a merge update or a validated range replacement without a separate delete operation. Decide whether the fix requires full recomputation or a bounded repair.

Design repeated execution for the selected strategy. Matching keys can make updates repeatable; publishing the same verified complete partition can also be repeatable without row-by-row key matching. Plain append usually duplicates reselected records unless another mechanism prevents it. Define input completeness, concurrency, and publication behavior as part of that contract.

A useful question before choosing incremental: if this model turned out to be wrong today, could it be rebuilt from scratch this week? When the answer is no, the saving in build time has been paid for with a reconciliation problem. How this fits into transformation as code is worked through in Transformation as Code.

References:

• dbt Documentation, Materializations.
• dbt Documentation, Incremental models.
• dbt Documentation, Microbatch incremental models.