Materializations: View, Table, Ephemeral

Every dbt model is a SELECT. The materialization decides what dbt wraps around it — and that decision is where all the cost and speed trade-offs live.

The same model, four ways

-- models/marts/daily_revenue.sql
select
    ordered_at,
    count(*)    as orders,
    sum(amount) as revenue
from {{ ref('stg_orders') }}
group by 1

view

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

12:03:11  1 of 1 START sql view model main.daily_revenue ................. [RUN]
12:03:11  1 of 1 OK created sql view model main.daily_revenue ............ [OK in 0.04s]

create view "bookshop"."main"."daily_revenue__dbt_tmp" as (
    select ordered_at, count(*) as orders, sum(amount) as revenue
    from "bookshop"."main"."stg_orders"
    group by 1
);

Nothing is computed at build time. Every query against daily_revenue re-runs the aggregation.

table

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

12:04:29  1 of 1 START sql table model main.daily_revenue ................ [RUN]
12:04:29  1 of 1 OK created sql table model main.daily_revenue ........... [OK in 0.07s]

create table "bookshop"."main"."daily_revenue__dbt_tmp" as (
    select ordered_at, count(*) as orders, sum(amount) as revenue
    from "bookshop"."main"."stg_orders"
    group by 1
);

Computed once per run, then cheap to read — and stale until the next run. dbt builds into a __dbt_tmp relation and swaps it in at the end, so readers never see a half-built table.

ephemeral

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

12:05:47  Found 5 models, 2 seeds, 431 macros
12:05:47
12:05:47  1 of 1 START sql table model main.country_revenue .............. [RUN]
12:05:47  1 of 1 OK created sql table model main.country_revenue ......... [OK in 0.06s]
12:05:47
12:05:47  Done. PASS=1 WARN=0 ERROR=0 SKIP=0 TOTAL=1

daily_revenue does not appear in the run at all — it was inlined as a CTE into the model that referenced it. Nothing exists in the warehouse to query, and nothing exists to test:

12:06:30  Encountered an error:
Compilation Error in test unique_daily_revenue_ordered_at
  Cannot test an ephemeral model 'daily_revenue' — it has no relation in the database

incremental

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

select ordered_at, count(*) as orders, sum(amount) as revenue
from {{ ref('stg_orders') }}
{% if is_incremental() %}
where ordered_at > (select max(ordered_at) from {{ this }})
{% endif %}
group by 1

12:08:15  1 of 1 START sql incremental model main.daily_revenue .......... [RUN]
12:08:15  1 of 1 OK created sql incremental model main.daily_revenue ..... [OK in 0.09s]

Full table on the first run, then only new rows merged in on later ones. It is the answer to “this table is too big to rebuild nightly”, and it has enough sharp edges to deserve its own lesson — that is lesson 6.

Choosing

The default that works for most projects:

# dbt_project.yml
models:
  bookshop:
    staging:
      +materialized: view
    intermediate:
      +materialized: ephemeral
    marts:
      +materialized: table

Staging models are thin renames — a view costs nothing and never goes stale. Marts are joined and aggregated and read repeatedly by dashboards, so pay the cost once at build. Move a mart to incremental only when its build time actually hurts.

The trap is stacked views. A view on a view on a view compiles to one enormous query, and the warehouse re-runs every layer on each read:

12:14:02  Finished running 12 view models in 0 hours 0 minutes and 0.41 seconds (0.41s).

Fast to build, and the dashboard on top of it takes 40 seconds. Build time is not the metric to optimise — a slow dbt run with a fast warehouse is usually the right trade.

Where config comes from

Three places, least to most specific:

# 1. dbt_project.yml — a folder default
models:
  bookshop:
    marts:
      +materialized: table
      +tags: ['nightly']

# 2. models/marts/_marts.yml — one model
models:
  - name: daily_revenue
    config:
      materialized: view

-- 3. the model file itself — wins over both
{{ config(materialized='table', tags=['hourly']) }}

dbt run --select daily_revenue

12:18:44  1 of 1 START sql table model main.daily_revenue ................ [RUN]
12:18:44  1 of 1 OK created sql table model main.daily_revenue ........... [OK in 0.07s]

“table” — the in-file config won. Most projects set folder defaults in dbt_project.yml and override in the file only where a model genuinely differs. Scattering {{ config() }} calls everywhere makes the project’s storage strategy impossible to see at a glance.

Note that +tags merged rather than replaced: this model carries both nightly and hourly. Materializations override, list configs like tags accumulate.

Where the model lands

{{ config(materialized='table', schema='reporting', alias='revenue_by_day') }}

12:22:05  1 of 1 START sql table model main_reporting.revenue_by_day ..... [RUN]
12:22:05  1 of 1 OK created sql table model main_reporting.revenue_by_day  [OK in 0.07s]

Note main_reporting, not reporting. By default dbt appends a custom schema to the target schema, which surprises everyone once. It is deliberate — it keeps two developers writing to dev_alice_reporting and dev_bob_reporting rather than colliding — and it is overridable with a generate_schema_name macro (lesson 7).

Rebuilding from scratch

dbt run --full-refresh

12:25:30  1 of 1 START sql incremental model main.daily_revenue .......... [RUN]
12:25:30  1 of 1 OK created sql incremental model main.daily_revenue ..... [OK in 0.09s]

--full-refresh drops and recreates incremental models rather than appending. It does nothing to views and tables, which are rebuilt every run anyway. When an incremental model’s numbers look wrong, this is the first thing to try — and lesson 6 explains why it works.

Practice

# table
12:31:02  1 of 1 OK created sql table model main.customer_orders ......... [OK in 0.08s]
# view
12:31:20  1 of 1 OK created sql view model main.customer_orders .......... [OK in 0.03s]

Compilation Error in test not_null_int_orders_enriched_order_id
  Cannot test an ephemeral model 'int_orders_enriched' — it has no relation in the database

models:
  bookshop:
    marts:
      +materialized: view

-- models/marts/daily_revenue.sql
{{ config(materialized='table') }}

12:36:41  1 of 3 START sql view model main.customer_orders ............... [RUN]
12:36:41  2 of 3 START sql table model main.daily_revenue ................ [RUN]
12:36:41  3 of 3 START sql view model main.country_revenue .............. [RUN]

12:36:41  Done. PASS=3 WARN=0 ERROR=0 SKIP=0 TOTAL=3

duckdb bookshop.duckdb -c "select table_schema, table_name from information_schema.tables order by 1,2"

┌────────────────┬──────────────────┐
│  table_schema  │    table_name    │
├────────────────┼──────────────────┤
│ main           │ customer_orders  │
│ main           │ raw_customers    │
│ main           │ raw_orders       │
│ main           │ stg_orders       │
│ main_reporting │ revenue_by_day   │
└────────────────┴──────────────────┘

Next: incremental models — building only what changed, and the ways that goes wrong.