Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Four materializations

dbt · dbt Fundamentals

Four materializations

Easydbt-03
materializationviewtableincrementalephemeral

Question

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

Solution

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

The four core ones

view         → CREATE VIEW (query runs when you select from it)
table        → CREATE TABLE AS SELECT (full rebuild each run)
incremental  → only process new/changed rows (merge/append/etc.)
ephemeral    → no object; SQL is inlined into downstream models (CTE)

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 CTE
  • Good for tiny shared cleanup steps you do not want as separate tables

Config example

{{ 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."

PreviousNext