A materialization decides how dbt builds a model in the warehouse — as a view, a table, ephemeral CTE, or incrementally. Choosing the right one balances freshness, speed, and cost.
The same model SELECT can be built four ways, and the choice is a performance/cost trade-off. A view is always fresh but computes on every read; a table is fast to read but must be rebuilt; ephemeral inlines the SQL into downstream models; incremental appends only new rows. You set this per model, defaulting simple staging models to views and heavy or large models to tables or incremental.
view CREATE VIEW — recomputes on every query.
always fresh, no storage; slow if queried a lot. Good: staging.
table CREATE TABLE — full rebuild each run.
fast reads; recomputes everything. Good: marts that fit in a rebuild.
ephemeral not built at all — inlined as a CTE into downstream models.
good for small reusable logic you don't want to materialize.
incremental only process NEW rows, append/merge into an existing table.
essential for huge event tables (next lesson's deep dive).
Rule of thumb: staging = view, marts = table, huge fact tables = incremental.You declare a materialization in a config() block at the top of the model, or as a default in dbt_project.yml for a whole folder. Setting sensible folder-level defaults (staging as views, marts as tables) and overriding per model keeps the project consistent and cheap.
-- per-model, at the top of the .sql file
{{ config(materialized='table') }}
select ...
-- ...or set defaults per folder in dbt_project.yml
-- (models in models/staging build as views, models/marts as tables)Configuring materializations by folder in dbt_project.yml applies a convention across many models at once, so individual models only override when they differ. This is where a project’s layering (staging → intermediate → marts) becomes concrete and enforced.
# dbt_project.yml
models:
analytics:
staging:
+materialized: view # thin, always-fresh renames/casts
intermediate:
+materialized: ephemeral # reusable logic, not materialized
marts:
+materialized: table # business-ready, queried often
+schema: analytics # build marts into a dedicated schemaA dbt project reads best as layers, mirroring the medallion pattern from the Data Modeling course. Staging models clean and rename one source each (one-to-one with raw tables); intermediate models join and reshape; marts are the final star-schema tables BI consumes. This structure keeps each model simple and makes lineage obvious.
raw sources
|
STAGING stg_orders, stg_customers — 1:1 with a source, light cleanup
| (rename, cast, filter deleted)
INTERMEDIATE int_orders_joined — joins/reshapes, reusable building blocks
|
MARTS fct_orders, dim_customers — final star schema, what BI queries
Each layer is small and single-purpose. This is the medallion
architecture (bronze/silver/gold) expressed as dbt models.