dbt Materializations

A materialization controls what dbt creates in your warehouse when it runs a model. Different materializations have different trade-offs between storage cost, query speed, and rebuild time. Choosing the right materialization for each layer of your project makes your pipeline faster and cheaper.

The Four Standard Materializations

Materialization    Warehouse Object Created    Data Stored?
--------------     ------------------------    ------------
view               SQL View                    No
table              Physical Table              Yes
incremental        Physical Table (partial)    Yes (new rows only)
ephemeral          Nothing (CTE only)          No

View

A view is a saved SQL query. When someone queries the view, the database runs the underlying SELECT at that moment. No data is stored on disk. The view always returns current results from the source tables.

When to Use Views

• Staging models that read directly from raw source tables
• Models that are queried rarely and do not need fast performance
• Intermediate models that are only used by one downstream model

Pros and Cons

Pros:
  Always up to date (no stale data)
  Zero storage cost
  Fast to rebuild (just replace the view definition)

Cons:
  Slow to query on large datasets (reruns SQL every time)
  Cannot be indexed

Setting View Materialization

-- Option 1: in the model file
{{ config(materialized='view') }}
select ...

-- Option 2: in dbt_project.yml (applies to all models in staging/)
models:
  my_project:
    staging:
      +materialized: view

Table

A table copies the results of your SELECT and stores them on disk as a physical table. Every time dbt runs the model, it drops the old table and creates a new one from scratch.

When to Use Tables

• Mart models that analysts query frequently in dashboards
• Models that aggregate large amounts of data (aggregations are precomputed)
• Any model where query speed matters more than rebuild time

Pros and Cons

Pros:
  Fast to query (data is precomputed and stored)
  Can be indexed for additional speed
  Predictable query performance

Cons:
  Uses warehouse storage
  Full rebuild every run (slow for very large datasets)
  Data is only as fresh as the last dbt run

Setting Table Materialization

{{ config(materialized='table') }}
select ...

Incremental

An incremental model adds only new or changed rows to an existing table instead of rebuilding the whole table. On the first run, dbt builds the full table. On subsequent runs, dbt inserts only rows that are new since the last run.

A Delivery Analogy

Table materialization:
  Every day: throw away the warehouse, rebuild it from scratch

Incremental materialization:
  Day 1: build the warehouse from scratch
  Day 2: add only yesterday's new deliveries
  Day 3: add only today's new deliveries

Basic Incremental Model

{{ config(materialized='incremental') }}

select
    order_id,
    customer_id,
    order_date,
    amount
from {{ source('ecommerce', 'orders') }}

{% if is_incremental() %}
  where order_date > (select max(order_date) from {{ this }})
{% endif %}

{% if is_incremental() %} adds the WHERE clause only on incremental runs. On the first full run, it skips the WHERE and loads all rows. {{ this }} refers to the existing incremental table itself.

When to Use Incremental

• Event tables with millions of new rows per day
• Log tables that grow continuously
• Any table where a full rebuild takes too long

Ephemeral

An ephemeral model creates no database object at all. Instead, dbt inlines the model's SQL as a Common Table Expression (CTE) inside any model that references it.

A Cooking Analogy

View/Table: "Make a bowl of salsa, put it in the fridge, serve from there"
Ephemeral:  "Chop the tomatoes right into the dish being prepared"

Example

-- models/int_orders_with_flags.sql (ephemeral)
{{ config(materialized='ephemeral') }}

select
    order_id,
    amount,
    case when amount > 1000 then true else false end as is_large_order
from {{ ref('stg_orders') }}

-- models/fct_orders.sql
select
    o.order_id,
    o.is_large_order,
    c.full_name
from {{ ref('int_orders_with_flags') }} o   ← dbt inlines this as CTE
join {{ ref('stg_customers') }} c
  on o.customer_id = c.customer_id

The compiled SQL for fct_orders becomes:

with int_orders_with_flags as (
    -- contents of int_orders_with_flags.sql inlined here
    select order_id, amount,
      case when amount > 1000 then true else false end as is_large_order
    from dbt_dev.stg_orders
)

select
    o.order_id,
    o.is_large_order,
    c.full_name
from int_orders_with_flags o
join dbt_dev.stg_customers c on o.customer_id = c.customer_id

When to Use Ephemeral

• Intermediate logic you want to keep modular but do not need to query independently
• Small helper transformations used by exactly one downstream model

Choosing the Right Materialization

Layer           Typical Materialization    Reason
-----------     -----------------------    ------
Staging         view                       Always fresh, no storage cost
Intermediate    view or ephemeral          Rarely queried directly
Marts (small)   table                      Fast for dashboards
Marts (large)   incremental                Efficient for big datasets
Helper logic    ephemeral                  Modular, no warehouse clutter

Overriding Materializations

Set defaults in dbt_project.yml and override per model using the config block:

# dbt_project.yml – default for each folder
models:
  my_project:
    staging:
      +materialized: view
    intermediate:
      +materialized: view
    marts:
      +materialized: table

-- Override for one specific large model
{{ config(materialized='incremental') }}
select ...

The config block in the model file always wins over the folder default in dbt_project.yml.