dbt · dbt Fundamentals

Four materializations

Easymaterializationviewtableincrementalephemeral

Interview question

What are the 4 core dbt materializations (view, table, incremental, ephemeral), and when would you use each?

How to answer

The short answer

Materialization = how dbt builds and stores a model in the warehouse.

The 4 core ones

View

  • Cheap to create, always reflects upstream SQL definition
  • Slower for heavy BI queries (recomputes each time)
  • Good for staging or light transforms

Table

  • Full rebuild every dbt run
  • Fast reads for consumers
  • Costly when the model is huge and most rows do not change

Incremental

  • First run builds the full table; later runs process only new/changed data
  • Needs careful is_incremental() filters and usually a unique_key for merges
  • Default choice for large facts

Ephemeral

  • No physical relation created
  • Downstream ref() pastes the SQL as a
  • Good for tiny shared cleanup steps you do not want as separate tables

Config example

SQL
{{ 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 %}

Interview tip: "Start with views for staging, tables for small marts, incremental for large growing facts, ephemeral for shared CTEs you do not want as objects."