Course contents

The Analytics Engineer’s Field Guidedbt 1: build the modelsStep 4: Materializations

Materializations

Part 2 · dbt · step 4

Materializations: view, table, incremental

Same SELECT. You choose what dbt builds from it.

· · ·

Alice's models build fine, but she hasn't told dbt what to build them as. A materialization is that choice: a view, a full table, or a table that only gets the new rows added to it. Same SQL in the file, different object in Snowflake. Four cover nearly every model you'll write. (dbt has a fifth, materialized_view; on Snowflake, part 4 uses a dynamic table instead. Skip both for now.)

VIEW
Stores the SQL, not the data. Every read re-runs the query: always fresh, and whoever queries it pays, in warehouse time.
build: free · read: compute
TABLE
Stores the result. Built once at dbt run, then reads are instant, and stale until the next run.
build: compute · read: cheap
INCREMENTAL
A table that only processes the new rows each run. The big-table compromise: fast builds, fast reads.
build: small · read: cheap
EPHEMERAL
Never built at all. Pasted as a CTE (a WITH ... AS block) into every model that refs it.
build: none · read: n/a

The difference isn't in your SQL, it's in what ends up sitting in Snowflake afterwards:

VIEW

stored in Snowflake:
select order_id, amount from stg…

the query text only

0 rows · runs on every read

TABLE

stored in Snowflake:

every row, written out

every row rewritten, every run

INCREMENTAL

stored in Snowflake:
new

the old rows stay put

only the new row is written
same SELECT in all three files · three very different objects

Ephemeral isn't in the picture because it leaves nothing at all in Snowflake. It gets its own section below.

The decision, in four questions

Ask them in order, stop at the first yes. Q1 is rare, so most models go straight to Q2:

Q1 Is it a thin snippet other models reuse, never queried on its own? → EPHEMERAL
Q2 Is it staging, or light logic that's cheap to recompute on every read? → VIEW
Q3 Is the full rebuild of a big event table costing real time or credits (millions of rows, minutes per run)? → INCREMENTAL
Q4 None of the above? Typically a mart hit often by BI. → TABLE
type
build cost
always fresh?
reach for it when
view (the default)
none
yes
staging, light models
table
full each run
at last run
marts hit often by BI
incremental
new rows only
at last run
huge event tables
ephemeral
none
n/a
small reused logic (a CTE)
In practice
View in staging, table in marts, and stop there. Incremental adds correctness edge cases, so a model earns it when its rebuild starts to hurt (Q3), not before.

Set it on one model

-- models/marts/fct_orders.sql
{{ config(materialized='table') }}

SELECT ...   -- step 2's join, unchanged

Put config() at the top of the file, by convention. It doesn't touch the SELECT: dbt reads it, then drops it from the compiled SQL.

Set it for a whole folder

Usually you set a default per folder in dbt_project.yml: staging and intermediate as views, marts as tables.

# dbt_project.yml
models:
  my_project:              # the project's name: line, not the profile
    staging:               # a folder under models/
      +materialized: view
      +schema: staging
    intermediate:
      +materialized: view
      +schema: intermediate
    marts:
      +materialized: table
      +schema: marts

Under models: comes the project's name, then folders. The + marks a setting, not a folder. dbt often copes without it, but always write it.

+schema gives each layer its own schema. dbt's default rule glues it onto the target's schema: dbt_alice_staging for you, analytics_staging in production (step 5). Step 14 changes the rule: production gets plain STAGING, and your runs go back to dbt_alice. Views you built before stay in DBT_ALICE: drop them.

The same setting can live in three places. The most specific wins: config() in the .sql file beats config: in _models.yml, which beats dbt_project.yml.

Incremental in one breath

An incremental model builds the full table the first time, then only processes new rows after that. You define "new" with is_incremental() and {{ this }} (the table as it already exists). is_incremental() is true only when the table exists and you didn't pass --full-refresh. Otherwise the {% if %} block (Jinja logic, step 2) drops the WHERE.

-- models/marts/fct_orders.sql (simplified: SELECT * stands in for step 2's join)
{{ config(materialized='incremental', unique_key='order_id') }}

SELECT * FROM {{ ref('stg_orders') }}
{% if is_incremental() %}
  -- re-read the last 3 days: late rows get caught, unique_key stops duplicates
  WHERE order_date >= (SELECT max(order_date) - INTERVAL '3 days' FROM {{ this }})
{% endif %}

What that saves is the whole point. Same 10M-row table, three nightly runs:

Run 1 · Monday
Run 2 · Tuesday
Run 3 · Wednesday
table
10M rows
10M rows again
10M rows again
incremental
10M rows, first build
50k new rows
50k new rows

the first run is identical · after that, each run reads a few days, not the whole history

Two things that trip people up
With unique_key set, dbt runs a Snowflake MERGE: a row whose key exists is updated, not added twice. And a strict > max(order_date) filter misses late rows. Say a Monday order arrives Tuesday. Tuesday night's filter keeps only dates after Monday, so it's skipped forever. The three-day window re-reads it (about 150k rows, tiny next to 10M).
# normal run
dbt run --select fct_orders

# changed the logic or the columns? drop and rebuild from scratch
dbt run --select fct_orders --full-refresh

incremental_strategy: how the new rows land

materialized='incremental' decides that only new data gets processed. incremental_strategy decides how those rows land in the table. On Snowflake, three answers cover most models, and they're not interchangeable:

Strategy
What it does
Reach for it when
merge (default)
Matches each row on unique_key: updates it if it exists, inserts it if not.
Rows can arrive late, twice, or change (orders get updated). The safe default.
delete+insert
Deletes every row matching the batch's keys, then inserts the batch. No row-by-row matching.
You reprocess whole slices (a full day) at a time, and the merge matching itself is the slow part.
append
Just inserts. No key, no matching, no updates, ever.
Immutable events that are never re-sent.

Careful: for delete+insert, unique_key doesn't mean unique. It names the column that defines each slice to replace:

-- every order_date present in the new batch is deleted, then reinserted whole
{{ config(
    materialized='incremental',
    incremental_strategy='delete+insert',
    unique_key='order_date'
) }}

Microbatch (dbt 1.9+) is the newer option, built for big time-series tables. You write a plain SELECT, with no is_incremental() block, and dbt runs one small query per period:

{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='order_date',
    batch_size='day',
    begin='2024-01-01'
) }}

event_time names the column that dates each row. Rebuilding a past month becomes thirty daily batches. If day 15 fails, dbt retry (rerun what failed last time) redoes day 15 alone.

What happens when the schema changes?

Incremental models have a second classic wound: schema drift. Someone adds or renames a column, and the model keeps appending to the old shape, or fails cryptically. Guard it in the config:

-- refuses to build if the columns drifted
{{ config(
    materialized='incremental',
    unique_key='order_id',
    on_schema_change='fail'
) }}
on_schema_change
What happens
ignore (default)
New columns are silently left out: exactly the drift you're guarding against.
fail
The build stops, and you decide.
append_new_columns
Adds new columns, never drops old ones.
sync_all_columns
Adds and drops columns to match the model. A dropped column's data is gone.

Old rows get null in any added column. To fill them in, rebuild with --full-refresh.

The quiet fourth: ephemeral

An ephemeral model leaves nothing in Snowflake. dbt pastes its SQL as a CTE into every model that ref()s it. Use it for thin, shared snippets. Two cautions: you can't query it in Snowflake, because it isn't there, and a chain of ephemerals compiles into one giant nested query that's painful to debug. The moment you want to look at one, change it to materialized='view'.

Lesson quiz

  1. You don't set any materialization on a model. What does dbt build?

    1. A table
    2. An ephemeral CTE
    3. An incremental table
    4. A view
  2. An incremental model has unique_key='order_id' and filters order_date > (select max(order_date) from {{ this }}). What's the risk?

    1. The model quietly rebuilds the full table from scratch on every run
    2. The merge errors out whenever an order_id already exists
    3. Rows arriving late for a day already loaded are silently skipped
    4. Orders that get updated are inserted again as duplicate rows
  3. fct_daily_sales joins three tables over 2 million rows. BI dashboards query it hundreds of times a day, and a full rebuild takes 40 seconds. Following the four questions, which materialization fits?

    1. Incremental, since dashboards read it so often
    2. Ephemeral, inlined into each dashboard query
    3. View, so every dashboard sees fresh data
    4. Table, rebuilt in full on each run