Materializations

## What a materialization is in dbt A dbt model is a file containing one `SELECT` statement. dbt does not run that `SELECT` as-is — it wraps it in DDL determined by the model's **materialization** config. The materialization is the answer to "what object should exist in the warehouse when this model is built?" The built-in options are `view`, `table`, `incremental` and `ephemeral`. The same SQL can be flipped between them by changing one config line, with no edit to the query itself. You set it either in the model file: ```sql {{ config(materialized='table') }} select ... ``` or as a default for a folder in `dbt_project.yml`, where the model-file config wins over the project-file default. ## The view materialization With `materialized='view'`, dbt runs roughly `create or replace view analytics.stg_orders as (select ...)`. Nothing is computed at build time beyond parsing and validating the SQL, so `dbt run` over a hundred views finishes in seconds and costs almost no warehouse compute. The trade is deferred: a view is a stored query, so the transformation executes **every time someone selects from it**. If a dashboard hits the view a thousand times a day, you pay for that logic a thousand times. Views are also always current — they read whatever is in the underlying tables at query time, so there is no staleness window. A subtle cost is stacking. If `mart_revenue` is a view over `int_orders_joined`, which is a view over `stg_orders`, which is a view over the raw table, a single dashboard query expands into one large nested statement. Most warehouses optimize this reasonably well, but plans get harder to read, and deep view chains over big scans are a common cause of "the dashboard got slow and nobody changed anything." ## The table materialization With `materialized='table'`, dbt runs the equivalent of `create or replace table analytics.dim_customers as (select ...)`, fully rebuilding the object on every run. Reads afterwards are plain scans of stored, physically laid-out data — the warehouse can prune, cluster, cache and compress it, which a view cannot do because a view holds no data. The costs are three: build compute (the query runs in full every `dbt run`), storage, and **staleness** — the table reflects the source as of the last build, so a table refreshed hourly is up to an hour behind while its view sibling is never behind. There is also a brief availability consideration: on most warehouses `create or replace` is atomic enough that readers see either the old or the new version, but a long rebuild means the *content* is old for the duration of the run. ## How to choose Useful defaults that hold across most projects: - **Staging models** (light renaming, casting, filtering, one source each) → `view`. They are cheap to recompute and you want them to reflect the source. - **Intermediate models** → usually `view`, promoted to `table` when they are expensive or reused by several downstream models. - **Marts and BI-facing models** → `table`, because many people query them repeatedly and read latency is what users feel. Promote a view to a table when any of these is true: the SQL is expensive (large joins, window functions, `distinct` over big volumes); it is read far more often than it is built; it is the base of further views; or a BI tool queries it interactively and latency matters. Demote a table to a view when the build is dominating your run time, the model is rarely queried, or the freshness requirement is tighter than your run schedule. ## Where the other materializations fit When a table's full rebuild becomes too slow or expensive — typically a large append-mostly fact table — the next step is `incremental`, which builds the table once and then only processes new or changed rows on later runs. When a model is just reusable interim logic that nothing needs to query directly, `ephemeral` inlines it as a CTE and creates no object at all. Views and tables remain the two you reach for first: incremental adds real correctness complexity, and ephemeral hides logic from anyone outside dbt. ## Things that trip people up Switching a model from view to table (or back) is safe — dbt drops the existing object and creates the new type — but the *name* stays the same, so downstream `ref()`s keep working untouched. A model's materialization is not a property of its SQL; it is an operational decision you can revisit when run times or query patterns change. And because both are fully rebuilt each run, neither view nor table needs any thought about duplicates or late-arriving data — that concern belongs entirely to incremental models.