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. The model’s SQL does not change; where its output lives, and how it is refreshed, does. Details follow dbt documentation checked in September 2026.

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 write a filter that runs only on incremental executions, and whatever that filter selects is what gets processed — so the behaviour, 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, trades cost for catching those.
• 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, which decides what a selected row actually does. Append adds rows and never touches existing ones, whatever key is declared. Merge updates rows matched on the unique key and inserts the rest. A partition- or range-replacement strategy deletes and rewrites whole periods — which will remove existing rows in the periods the run covers, whether or not the source still has them. Past data is only untouched under append, and only outside the replaced range otherwise.
• 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

Views and tables are rebuilt from scratch on every run. Incremental models add to what is already there. That single difference decides how much of a past mistake you inherit.

• A full rebuild is self-correcting. Fix the SQL, run it, and the output reflects the new logic over all the input — at the cost of recomputing everything.
• An incremental model is not. Rows written by an earlier, wrong version stay until something removes them, so a logic fix has to be paired with a decision about history: rebuild fully, or reprocess a bounded range.
• Incremental logic has to handle repeats. Re-running a window must update rather than duplicate, which is the ordinary requirement that a load be keyed and repeatable.

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; Apache Airflow Documentation, Asset Scheduling.