The Three Core Materializations
A materialization tells dbt how to physically persist the result of a model's SELECT statement in the warehouse. The view materialization creates a lightweight SQL view that re-runs the query every time it's selected from, the table materialization runs a full CREATE TABLE AS on every dbt run, and the incremental materialization only processes new or changed rows after the first full build.
Cricket analogy: This is like choosing between reading a live scoreboard that recalculates every ball (view), printing a fresh scorecard after every innings (table), or only updating the scorecard with the latest over's runs instead of rewriting the whole card (incremental).
Choosing view vs. table
-- models/marts/dim_customers.sql
{{ config(materialized='table') }}
select
customer_id,
first_name,
last_name,
lifetime_value
from {{ ref('int_customer_orders') }}Views cost almost nothing to build since they're just stored SQL definitions, making them ideal for lightweight staging models that are cheap to re-query, but every downstream query pays the full computation cost each time it hits the view. Tables cost more to build because dbt physically rewrites the whole dataset on every run, but they're much faster to query afterward, which makes them the right choice for large marts models queried repeatedly by dashboards.
Cricket analogy: This is like the tradeoff between a bowler who takes no run-up and bowls instantly every ball (cheap to start, slower per delivery to build momentum) versus a fast bowler with a long run-up (costly setup, but a much faster delivery once bowled).
As a rule of thumb: use 'view' for staging models and small, cheap-to-recompute intermediate models, and reserve 'table' for marts models that are queried frequently by BI tools where query speed matters more than build cost.
Incremental Models
Incremental models solve the problem of tables that are too large to fully rebuild on every run: instead of reprocessing the entire history, dbt appends or merges only the rows that changed since the last run, typically filtered using an is_incremental() macro combined with a max(updated_at) lookup against the existing table. The unique_key config controls whether new rows are simply appended (no unique_key) or merged/upserted against existing rows (unique_key set), which matters enormously for correctness when source data can be updated after it first arrives.
Cricket analogy: This is like a scorer who, instead of re-tallying every run scored since the start of the tournament after each match, only adds the runs from today's match to the running season total.
{{ config(
materialized='incremental',
unique_key='order_id'
) }}
select *
from {{ source('raw', 'orders') }}
{% if is_incremental() %}
where updated_at > (select max(updated_at) from {{ this }})
{% endif %}A common mistake is forgetting the is_incremental() filter's edge cases — if your source table allows late-arriving or backdated updates, a naive max(updated_at) filter can silently miss rows. Consider a lookback window or full-refresh cadence to catch stragglers.
- View: cheap to build, recomputed on every query — best for staging and lightweight models.
- Table: expensive to build via full rebuild, fast to query — best for marts hit by dashboards.
- Incremental: only processes new/changed rows after the initial full build, ideal for very large fact tables.
- The unique_key config determines whether incremental runs append or merge/upsert rows.
- is_incremental() wraps the filtering logic that only applies on non-full-refresh incremental runs.
- Late-arriving or backdated source rows can be missed by a naive max(updated_at) filter — plan a lookback window.
Practice what you learned
1. Which materialization recomputes its result set every time it is queried?
2. What is the primary benefit of the incremental materialization?
3. What does the unique_key config control in an incremental model?
4. What macro is used to conditionally filter rows only during incremental (non-full-refresh) runs?
Was this page helpful?
You May Also Like
SQL Models Basics
Learn how dbt models turn a plain SQL SELECT statement into a managed table or view, and how to organize them into a clean, layered project structure.
ref() and source() Functions
Learn how dbt's ref() and source() functions build the project's dependency graph and enable environment-safe, portable SQL.
Macros
Learn how dbt macros turn repeated Jinja + SQL patterns into reusable, function-like building blocks, and how community packages like dbt_utils extend them further.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
ProgrammingPowerShell Study Notes
Programming · 30 topics