4. Materializations

A materialization is dbt’s answer to one question: when this model runs, what statement should the warehouse actually receive?

Same SELECT, five different wrappers.

The five

   view            create view. no storage.
                   query cost paid on every read.

   table           create table as select.
                   full rebuild each run.
                   storage cost, fast reads.

   incremental     table, but after the first run
                   only new/changed rows are
                   processed.

   ephemeral       no object at all. inlined into
                   downstream models as a CTE.

   materialized    hands the refresh to the
   view            warehouse. it keeps the result
                   fresh itself.

Set it anywhere config goes:

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

view

The default in a fresh project.

create or replace view analytics.marts.customer_orders as (
    select ... -- your model
);

Nothing is computed at build time. dbt stores the query text. The work happens every time somebody selects from it.

• Build time: instant, regardless of data volume.
• Storage: none.
• Read cost: the full query, every read, every time.
• Freshness: perfect. Always reflects the underlying tables right now.

Views compose badly. A view on a view on a view collapses into one enormous query at read time. Five layers of staging views over a large table and a dashboard times out — not because any layer is wrong, but because the warehouse is executing all of them at once.

Use for: staging models (thin renames over raw tables), anything small, anything queried rarely.

table

create or replace table analytics.marts.customer_orders as (
    select ... -- your model
);

All the work at build time, none at read time. Every dbt run drops and rebuilds the whole thing.

• Build time: proportional to the data.
• Storage: a full copy.
• Read cost: a plain table scan. Fast.
• Freshness: as of the last dbt run.

Most warehouses do this atomically — building into a temporary relation and swapping it in — so readers see the old table until the new one is complete rather than an empty one mid-build.

Use for: marts, anything queried by dashboards, anything with expensive joins or aggregations, anything more than one or two layers deep.

The default choice for a mart is table. Storage is cheap; recomputing a join across a hundred dashboard loads is not.

incremental

The one with real complexity, and the one that matters at scale.

{{ config(
    materialized = 'incremental',
    unique_key   = 'order_id'
) }}

select * from {{ ref('stg_orders') }}

{% if is_incremental() %}
  where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

First run: the table does not exist, is_incremental() is false, the where clause disappears, and dbt does a full create table as select.

Every run after: the table exists, is_incremental() is true, the filter applies, and dbt processes only new rows — then merges them in.

   run 1   ████████████████████  50M rows, 40 min
   run 2   ▌                     80k rows, 25 sec
   run 3   ▌                     91k rows, 27 sec

That difference — forty minutes against twenty-five seconds — is why incremental models exist. It is also why they are the largest source of quiet wrongness in dbt projects, because a filter that is subtly wrong silently drops rows.

Chapter 5 is entirely about them: the strategies, unique_key, late-arriving data, on_schema_change, and when to give up and use a table.

ephemeral

{{ config(materialized='ephemeral') }}
select ... -- some intermediate step

Nothing is created in the warehouse. No view, no table. When a downstream model refs it, dbt pastes the SQL in as a CTE.

-- downstream model, compiled
with __dbt__cte__int_orders_cleaned as (
    select ... -- the ephemeral model's SQL
)
select * from __dbt__cte__int_orders_cleaned

• No storage, no object, no clutter in the warehouse.
• You cannot query it. It does not exist. Debugging means reading compiled SQL.
• Referenced by three models, its SQL appears three times and is computed three times.
• It cannot be tested directly, since tests need something to select from.

Use sparingly. The honest advice is that ephemeral looks tidier than it is. Deep ephemeral chains produce compiled SQL thousands of lines long that no human can debug, and the storage they save is worth pennies. A view is almost always the better answer; keep ephemeral for a genuinely trivial step used once.

materialized view

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

dbt creates the object; the warehouse keeps it fresh, not dbt. Snowflake refreshes continuously in the background, BigQuery incrementally, Postgres only on explicit REFRESH.

• Freshness without a dbt run.
• The warehouse absorbs the maintenance cost, continuously and often invisibly.
• Support and semantics vary sharply by adapter.
• They break the dbt mental model: the object updates when dbt is not running, so “the state of the warehouse after dbt run” is no longer a complete description.

Use when you need near-real-time freshness on a query too expensive to run live, and your warehouse’s implementation genuinely fits. Read that warehouse’s restrictions first — most forbid outer joins, some aggregates, and non-deterministic functions.

Choosing

   Is it a thin rename over a source?
      └─▶ view

   Do dashboards hit it?
      └─▶ table

   Is a full rebuild too slow or too expensive?
      └─▶ incremental  (and read chapter 5)

   Is it a trivial step used once, that
   nobody will ever need to inspect?
      └─▶ ephemeral

   Do you need freshness between dbt runs,
   and does your warehouse support it well?
      └─▶ materialized view

   Otherwise
      └─▶ table

Start every model as a view or a table. Move to incremental when a rebuild becomes painful — with a measurement, not a guess. Premature incrementalisation buys a small amount of compute and a large amount of subtle bug surface.

Custom materializations

Materializations are macros. You can write your own:

{% materialization insert_only, default %}
  ...
{% endmaterialization %}

Almost nobody should. The built-in five plus incremental strategies cover essentially everything, and a custom materialization is code your whole team must understand to reason about any model using it. The legitimate cases are adapter-level — a warehouse feature dbt does not model — and those usually belong in an adapter or a package rather than your project.

Where the objects land

Two related surprises worth naming here, both covered fully in chapter 11.

Custom schemas concatenate by default. A model configured with +schema: marts and a profile schema of dbt_sarah lands in dbt_sarah_marts, not marts. This is deliberate — it keeps developers isolated — and it is the single most-asked dbt question. Overriding generate_schema_name changes it.

{{ this }} is the model’s own relation. Available inside a model, mostly used in incremental filters as above. It resolves to the same fully-qualified name ref() would give a downstream model.

What to take from this chapter

• A materialization is the DDL wrapper around your SELECT — the same query, a different statement.
• view: no build cost, no storage, full query cost on every read. Right for staging. Stacks badly.
• table: full rebuild each run, fast reads. The default answer for a mart.
• incremental: process only new rows after the first run. Enormous speedups, and the biggest source of silent wrongness — chapter 5.
• ephemeral: inlined as a CTE, creates nothing, cannot be queried or tested. Use rarely; a view is usually better.
• materialized_view: the warehouse maintains freshness, not dbt. Powerful, adapter-specific, and it breaks the “warehouse state = last dbt run” model.
• Start with view or table. Go incremental when you have measured the pain, not before.
• Custom materializations are almost never the answer.
• Custom schemas concatenate — dbt_sarah + marts = dbt_sarah_marts. Chapter 11 explains how to change it.

Next: incremental models in depth — the strategies, unique_key, late-arriving data, and the failure modes that make them dangerous.