Four built-in materializations. Each is a tradeoff between build cost, query cost, freshness, and complexity.
view
{{ config(materialized='view') }}
- Build cost: cheap (just metadata).
- Query cost: every query re-runs the SELECT.
- Freshness: always current.
- Best for: staging models, light transformations.
table
{{ config(materialized='table') }}
- Build cost: full rebuild each run.
- Query cost: cheap.
- Freshness: only updated on rebuild.
- Best for: marts queried often, heavy aggregations.
incremental
{{ config(materialized='incremental', unique_key='order_id') }}
SELECT * FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
WHERE order_placed_at > (SELECT MAX(order_placed_at) FROM {{ this }})
{% endif %}
- Build cost: cheap after initial.
- Query cost: cheap.
- Freshness: as of last run.
- Best for: large event/log tables.
ephemeral
{{ config(materialized='ephemeral') }}
- Build cost: zero (no warehouse object).
- Query cost: inlined as CTE downstream, re-evaluated each query.
- Freshness: always current.
- Best for: reusable intermediates used in 1-2 places.
Decision tree
How big is the source?
├─ Small (<1GB) → view
└─ Large
├─ Append-only → incremental
├─ Heavy aggregation queried often → table
└─ Used in only one downstream model → ephemeral
Folder-level defaults
# dbt_project.yml
models:
my_project:
staging:
+materialized: view
intermediate:
+materialized: ephemeral
marts:
+materialized: table
events:
+materialized: incremental
Common picks per layer
| Layer | Default |
|---|---|
| staging | view |
| intermediate | ephemeral (small) or table (larger) |
| marts | table |
| event-style marts | incremental |
Things to watch
- Don't make every staging model a table — wasteful.
- Don't make every mart a view — re-runs aggregations on every query.
- Don't reach for incremental too early — adds complexity.
- Stacked ephemerals get ugly — materialize once depth is uncomfortable.
Switching materializations
Change the config() and rerun. dbt handles the transition (drops view, creates table, etc.). No migration step needed.
Takeaway
view for cheap fresh; table for cheap reads; incremental for big append; ephemeral for inline reuse. Set defaults at folder level; override per-model only when needed.