Materializations: View, Table, Incremental, Ephemeral

A Model Is a SELECT; a Materialization Decides What It Becomes

The previous module established that a dbt model file contains a single SELECT statement — no `CREATE TABLE`, no `INSERT`, just a query describing what the data should look like. But a SELECT statement, on its own, is not persisted anywhere. It runs, returns rows, and those rows evaporate the moment the query finishes, exactly like running a SELECT in a SQL client and never doing anything with the result set.

A materialization is the strategy dbt uses to persist that SELECT's result as an actual object in the warehouse — something that continues to exist after the run finishes, that other queries and other dbt models can read from. It is the answer to the question "when dbt runs this model, what does it actually build?" The same SELECT statement can become a view, a table, an inlined fragment of another query, or an incrementally-updated table, entirely depending on one config value — the underlying business logic in the SELECT does not have to change at all.

There are four materializations covered in this module: view, table,ephemeral, and incremental. Every dbt project uses a mix of all four, because different models have genuinely different usage patterns — a lightly-transformed staging model queried only by one or two downstream models has a completely different ideal materialization than a hundred-million-row fact table hit by a dashboard refreshing every five minutes.

view — Cheap to Build, Recomputed Every Time It Is Queried

view is dbt's default materialization — if a model has no explicit materialization configured anywhere, this is what it gets. A view materialization compiles the model's SELECT statement into a CREATE OR REPLACE VIEW ... AS (your select) statement and runs that against the warehouse. Crucially, a view stores no data of its own. It stores only the query definition. Every time anything queries the view — a downstream dbt model, a BI dashboard, an analyst's ad hoc SELECT — the warehouse re-executes the underlying SELECT from scratch, against whatever the underlying tables currently contain.

-- models/staging/stg_customers.sql
{{ config(materialized='view') }}

SELECT
  customer_id::varchar AS customer_id,
  LOWER(TRIM(email))   AS email,
  signup_ts::timestamp AS signup_ts,
  country_code
FROM {{ source('raw', 'customers') }}
WHERE customer_id IS NOT NULL

CREATE OR REPLACE VIEW dev_asil.stg_customers AS (
  SELECT
    customer_id::varchar AS customer_id,
    LOWER(TRIM(email))   AS email,
    signup_ts::timestamp AS signup_ts,
    country_code
  FROM analytics.raw.customers
  WHERE customer_id IS NOT NULL
);

Because a view stores no data, running dbt run against a view-materialized model is fast — it is just re-defining the view, not recomputing or rewriting any actual rows. The cost is pushed entirely onto whoever queries the view afterward: every single read againststg_customers re-runs that SELECT against the raw source table, every time, with no caching of the result between queries. For a lightly-transformed staging model that a handful of downstream models read from occasionally, this trade-off is exactly right — the transformation logic is trivial to recompute, and there is no benefit to paying storage and rebuild cost for a precomputed copy nobody is hammering with queries.

table — Rebuilt Every Run, Fast to Query Afterward

A table materialization compiles the model into aCREATE OR REPLACE TABLE ... AS (your select) statement. Unlike a view, this actually executes the SELECT once, at build time, and writes the resulting rows to disk as a physical table. Every subsequent query against that table reads the already-computed rows directly — no recomputation, no re-running the underlying joins and aggregations.

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

SELECT
  o.order_id,
  o.customer_id,
  o.order_ts,
  o.status,
  o.total_usd,
  c.country_code,
  c.signup_ts
FROM {{ ref('stg_orders') }} o
LEFT JOIN {{ ref('stg_customers') }} c
  ON o.customer_id = c.customer_id
WHERE o.status != 'test_order'

CREATE OR REPLACE TABLE dev_asil.fct_orders AS (
  SELECT
    o.order_id,
    o.customer_id,
    o.order_ts,
    o.status,
    o.total_usd,
    c.country_code,
    c.signup_ts
  FROM dev_asil.stg_orders o
  LEFT JOIN dev_asil.stg_customers c
    ON o.customer_id = c.customer_id
  WHERE o.status != 'test_order'
);

The cost shows up on the build side instead of the query side. Every dbt run that touches this model fully rebuilds the entire table from scratch — every row, every time, regardless of how many rows actually changed since the last run. For a model with a few thousand rows this is irrelevant; for a model with hundreds of millions of rows and an expensive join, a full rebuild on every scheduled run can become the single most expensive step in the entire pipeline, both in compute cost and in wall-clock runtime.

This is the fundamental trade-off between the two most common materializations: viewpays its cost on every read; table pays its cost once per build and stays cheap to read afterward. Neither is universally better — the right choice depends entirely on how often a model is queried relative to how often it is rebuilt, which Part 08's decision framework covers directly.

ephemeral — Not a Database Object At All, Just an Inlined CTE

ephemeral is the materialization most beginners find counterintuitive, because it does not create anything in the warehouse whatsoever — not a view, not a table, nothing queryable on its own. An ephemeral model's SELECT statement is instead inlined as a Common Table Expression (CTE) directly into the compiled SQL of every model that references it through ref(). It exists purely at compile time, as a piece of reusable SQL text that dbt splices into whatever reads from it.

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

SELECT *
FROM {{ ref('stg_orders') }}
WHERE order_id IS NOT NULL
  AND total_usd > 0

-- models/marts/finance/fct_orders.sql
SELECT
  order_id,
  customer_id,
  order_ts,
  status,
  total_usd
FROM {{ ref('int_valid_orders') }}
WHERE status != 'test_order'

WITH int_valid_orders AS (
  SELECT *
  FROM dev_asil.stg_orders
  WHERE order_id IS NOT NULL
    AND total_usd > 0
)

SELECT
  order_id,
  customer_id,
  order_ts,
  status,
  total_usd
FROM int_valid_orders
WHERE status != 'test_order'

Notice there is no dev_asil.int_valid_orders object anywhere in the warehouse after this runs — it never existed as its own table or view. It exists only as the WITHclause inside fct_orders's compiled query. You cannot queryint_valid_orders directly from a BI tool or an ad hoc SELECT, because as far as the warehouse is concerned, it was never built as anything.

This makes ephemeral models genuinely useful for one specific case: small, reusable pieces of logic — a filter, a light transformation, a bit of deduplication — that exist purely to keep a downstream model's SQL readable and DRY, and that are only ever consumed by one or two downstream models. Since nothing is persisted, there is zero storage cost and zero separate build step for it.

incremental — Full Build Once, Then Only New or Changed Rows

incremental is the materialization built specifically for large, growing tables where a full rebuild on every run is wasteful or simply too slow to finish inside a scheduling window. The next module goes deep into the mechanics, strategies, and failure modes of incremental models in full — this section gives you a correct working understanding of what it does conceptually, enough to reason about when to reach for it.

On the very first run — or any run using --full-refresh — an incremental model behaves exactly like a table materialization: it runs the full SELECT against the full underlying data and builds the complete table from scratch. On every subsequent normal run, instead of rebuilding everything, it processes only the rows that are new or changed since the last run and merges or inserts just those rows into the existing table, leaving everything already built untouched.

-- models/marts/finance/fct_orders.sql
{{
  config(
    materialized='incremental',
    unique_key='order_id'
  )
}}

SELECT
  order_id,
  customer_id,
  order_ts,
  status,
  total_usd,
  updated_at
FROM {{ ref('stg_orders') }}

{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

The is_incremental() check is what makes one file behave two different ways. It evaluates to false on the first run (there is no existing table to compare against yet, and {{ this }} — a reference to the model's own resulting table — doesn't exist), so the WHERE clause is skipped entirely and every row is processed. On every later run, it evaluates to true, so the filter kicks in and only rows newer than whatever is already in the table get processed — dramatically less data scanned and written on a table that might otherwise be rebuilt in full every single run.

{{ config(materialized=...) }} — Setting It on One Model

The most granular way to set a materialization is the config() macro at the top of a single model's SQL file. Every example so far in this module has used this form. It overrides whatever the default or folder-level setting would otherwise be, for that one model only.

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

SELECT
  DATE(order_ts) AS order_date,
  COUNT(*)       AS order_count,
  SUM(total_usd) AS revenue_usd
FROM {{ ref('fct_orders') }}
WHERE status NOT IN ('cancelled', 'fraud')
GROUP BY 1

This is the right tool when one specific model needs to differ from the rest of its directory — most of your marts might default to table, but one particular mart that's rarely queried and cheap to compute might be better off as a view, and setting it explicitly here overrides the folder default without disturbing every other model around it.

dbt_project.yml — Setting It for Whole Directories at Once

Repeating {{ config(materialized='view') }} at the top of every single staging model, and {{ config(materialized='table') }} at the top of every single mart, works but does not scale — it is easy to forget on a new model, and there is no single place to see or change the project's overall convention. `dbt_project.yml` lets you set a default materialization per folder path, applied to every model under it unless that specific model overrides it with its own config() call.

name: 'my_dbt_project'
version: '1.0.0'
profile: 'my_dbt_project'

model-paths: ['models']

models:
  my_dbt_project:
    staging:
      +materialized: view
    intermediate:
      +materialized: ephemeral
    marts:
      +materialized: table
      finance:
        +materialized: table
      large_facts:
        +materialized: incremental

The + prefix on materialized is dbt's config syntax for a setting applied to every model in that folder and its subfolders. This configuration says: everything undermodels/staging/ defaults to a view, everything under models/intermediate/defaults to ephemeral, and everything under models/marts/ defaults to a table — except anything specifically under models/marts/large_facts/, which defaults to incremental instead, since that subfolder-level setting is more specific and wins over the broadermarts default above it.

When both are set, the per-model config() always wins over the folder-level default — this is what lets a folder default to table while one specific model inside it is deliberately set to view without changing the convention for everything else in that folder.

Choosing the Right Materialization for a Model's Actual Usage Pattern

There is no single "best" materialization — the right choice depends entirely on how a specific model is actually used: how often it's queried, how expensive its underlying logic is, how large the underlying data is, and how many other models depend on it. The following framework is the one that shows up, in some form, in almost every real dbt project.

The questions worth asking before setting a materialization

• ✓How many things read from this model? A handful of downstream models favors view or ephemeral; many downstream models or many BI queries favors table.
• ✓How expensive is the underlying SELECT? A trivial cast-and-rename favors view; heavy joins or aggregations favor table so that cost is paid once, not on every read.
• ✓How large is the underlying data, and is it append-heavy or fully mutable? A huge, mostly-append table is exactly what incremental exists for; a small or fully-rewritten-each-run table gains little from incremental's added complexity.
• ✓Is this logic reused by more than one or two downstream models? If so, avoid ephemeral — the recompute-on-every-reference cost compounds with every additional consumer.
• ✓Does anything need to query this object directly and inspect it in isolation for debugging? If yes, it cannot be ephemeral, since ephemeral models never exist as anything queryable on their own.

Applying the Framework to a Real Three-Layer Project

Bringing the whole module together: a realistic small slice of a project, with each model's materialization chosen deliberately based on its actual usage pattern rather than by default habit.

models:
  my_dbt_project:
    staging:
      +materialized: view
    marts:
      finance:
        +materialized: table
      events:
        +materialized: incremental

SELECT
  order_id::varchar AS order_id,
  customer_id::varchar AS customer_id,
  order_ts::timestamp AS order_ts,
  LOWER(status) AS status,
  total_usd::numeric(12,2) AS total_usd,
  updated_at
FROM {{ source('raw', 'orders') }}
WHERE order_id IS NOT NULL

SELECT
  order_id,
  customer_id,
  order_ts,
  status,
  total_usd
FROM {{ ref('stg_orders') }}
WHERE status != 'test_order'

{{ config(unique_key='event_id') }}

SELECT
  event_id,
  customer_id,
  page_url,
  event_ts,
  updated_at
FROM {{ ref('stg_page_view_events') }}

{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

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

-- Overrides the marts default of table: this is a thin passthrough
-- of stg_customers used by exactly one downstream model, and rebuilding
-- it as a table on every run buys nothing.
SELECT customer_id, email, country_code
FROM {{ ref('stg_customers') }}

Every model here got a materialization chosen for a reason grounded in Part 08's framework: staging stays a view because it's cheap and lightly consumed; the finance mart is a table because BI tools hit it repeatedly; the events mart is incremental because page_views is exactly the large, append-heavy table incremental exists for; and one specific product mart deliberately overrides its folder's table default back to a view, because in this one case the folder-level default doesn't actually fit that particular model's real usage pattern.

Switching a Model's Materialization Later — And Why --full-refresh Matters

Materializations are not fixed for a model's lifetime. It is completely normal, and expected, to start a model as a view and later promote it to a table or incremental once its real usage pattern justifies the change, exactly as Part 08's callout recommends. But changing the `materialized` config value alone is not always enough on its own — understanding what dbt actually does when a model's materialization changes between runs avoids a class of confusing, silent problems.

When a model's config changes from `view` to `table`, the next `dbt run` correctly detects this and runs the appropriate `CREATE OR REPLACE TABLE` in place of the old view — dbt handles a view-to-table promotion cleanly on its own, dropping the old view object and replacing it with a table under the same name. The situation that needs care is the reverse direction, and especially anything involving `incremental`.

-- Yesterday: models/marts/events/page_views.sql
{{ config(materialized='table') }}

SELECT event_id, customer_id, page_url, event_ts, updated_at
FROM {{ ref('stg_page_view_events') }}

-- Today: switched to incremental
{{ config(materialized='incremental', unique_key='event_id') }}

SELECT event_id, customer_id, page_url, event_ts, updated_at
FROM {{ ref('stg_page_view_events') }}

{% if is_incremental() %}
  WHERE updated_at > (SELECT MAX(updated_at) FROM {{ this }})
{% endif %}

The first `dbt run` after this change matters more than it looks. Because a table named `page_views` already exists from yesterday's `table` materialization, dbt's incremental logic can get confused about whether that existing object was actually built using the incremental strategy it now expects — the safe move any time a model's materialization changes to or from `incremental`, or any time the underlying schema of an incremental model changes, is to force a complete rebuild with the `--full-refresh` flag.

dbt run --select page_views --full-refresh

--full-refresh tells dbt to ignore whatever already exists under that name, drop it, and rebuild the model completely from scratch — for an incremental model specifically, this meansis_incremental() evaluates to false for that one run regardless of whether a table already exists, exactly as it would on a genuinely first-ever run. This is the same mechanism covered in Part 05; the difference here is recognizing when you need to trigger it deliberately, rather than relying on it only firing automatically on a project's very first run.

dbt run Builds Models; dbt build Builds Models, Tests, Snapshots, and Seeds Together

Every example in this module has shown models being built with `dbt run`. It's worth being precise about what that command actually does relative to its more complete sibling, `dbt build`, because the difference affects how quickly a broken materialization surfaces.

`dbt run` executes models — and only models — in dependency order, applying whatever materialization each one is configured with. It does not run tests. This means a model can build successfully with `dbt run`, showing green in the logs, while quietly violating a `unique` or `not_null` test attached to it — the test simply never ran. `dbt build` runs models, tests, snapshots, and seeds together in one dependency-ordered pass, and critically, it can stop a downstream model from building at all if an upstream test fails.

dbt run
# stg_orders builds successfully
# fct_orders builds successfully, using stg_orders' data as-is
# no tests ran -- a duplicate order_id in stg_orders goes unnoticed

dbt build
# stg_orders builds
# not_null/unique tests on stg_orders run immediately after
# a failing unique test on stg_orders.order_id can block fct_orders
#   from building on top of known-bad data, depending on how the
#   project's test severity and stop-on-failure behavior is configured

This matters directly for materializations because an expensive `table` or `incremental` rebuild is exactly the kind of work you don't want to repeat on top of bad upstream data. A pipeline built around `dbt build` rather than `dbt run` catches a broken assumption at the cheapest possible point — right after the model that introduced it — instead of letting every downstream table or incremental model rebuild on top of it first and only discovering the problem once someone notices a number looks wrong.

Where the Four Built-In Materializations Actually Come From, and How Config Layers Resolve

It's worth knowing that view, table, ephemeral, andincremental are not hardcoded special cases inside dbt's engine — they are themselves written in dbt's own macro language, as reusable Jinja/SQL templates that ship with dbt and its adapters. This is why different warehouse adapters (Snowflake, BigQuery, Postgres, Redshift) can each compile a table materialization slightly differently under the hood — a Snowflake adapter's table materialization macro knows to emit Snowflake-flavored DDL, while a BigQuery adapter's emits BigQuery-flavored DDL, even though both are configured identically from the model's point of view with materialized='table'.

This also means teams with a genuinely unusual persistence need can define their own custom materialization as a macro, though this is uncommon and should be a late resort — reached for only once the four built-in materializations covered in this module have been confirmed not to fit, since a custom materialization means maintaining warehouse-specific DDL logic yourselves instead of relying on dbt's well-tested built-in implementations.

How multiple config sources resolve into one final materialization

Parts 06 and 07 showed materialization set two ways — inline in a model's config() call, and as a folder default in dbt_project.yml. When more than one of these could apply to the same model, dbt resolves them using a fixed precedence, most specific wins:

1. {{ config(materialized=...) }} inside the model's own .sql file       <- wins
2. A schema.yml config block for that specific model
3. The most specific matching folder path in dbt_project.yml
   (models.my_project.marts.finance beats models.my_project.marts)
4. A less specific folder path in dbt_project.yml
   (models.my_project.marts beats models.my_project)
5. dbt's own built-in default (view)                                       <- loses

The practical value of understanding this precedence is debugging a model that seems to be materializing differently than expected. If a model under marts/finance/ is building as a view when the team's convention says marts should be tables, the first thing to check is whether that specific model has its own config(materialized='view') call overriding the folder default — a common source of "why is this one model different" confusion that a quick file-open resolves immediately, once you know the override exists and is expected to win.

Five Misconceptions About Materializations

Measuring the Trade-Off Instead of Guessing at It

Every recommendation in Part 08's decision framework is a starting heuristic, not a substitute for actually measuring what a specific model costs under its current materialization. Most dbt runs log per-model timing, and most warehouses expose query history that can be joined back to dbt's own run metadata — together, these are what turn "this table feels slow to rebuild" into a specific, actionable number.

dbt run --select marts.finance

15:02:01  Running with dbt=1.8.0
15:02:02  1 of 3 START sql table model prod.fct_orders .................. [RUN]
15:02:44  1 of 3 OK created sql table model prod.fct_orders ............. [SUCCESS 1 in 42.11s]
15:02:44  2 of 3 START sql table model prod.daily_revenue ............... [RUN]
15:02:46  2 of 3 OK created sql table model prod.daily_revenue .......... [SUCCESS 1 in 1.83s]
15:02:46  3 of 3 START sql view model prod.customer_lookup .............. [RUN]
15:02:46  3 of 3 OK created sql view model prod.customer_lookup ......... [SUCCESS 1 in 0.09s]
15:02:46
15:02:46  Completed successfully

A 42-second rebuild for fct_orders against a 1.83-second rebuild fordaily_revenue built directly on top of it is exactly the kind of signal Part 08's framework is meant to be checked against with real numbers — if fct_orders keeps growing and that 42 seconds becomes 20 minutes as the underlying order volume grows month over month, that is the concrete trigger for evaluating an incremental conversion, not a vague sense that "this table feels big now."

On the query side, most warehouses (Snowflake's QUERY_HISTORY, BigQuery'sINFORMATION_SCHEMA.JOBS, Redshift's system tables) can be filtered to the queries a view's downstream readers actually issue, which is the other half of the trade-off Part 02 and Part 03 describe — a view's cost shows up spread across every downstream reader's query time, not in the dbt run log at all, so checking dbt's run timing alone would make a heavily-queried view look free when it is actually the more expensive choice in aggregate.

What This Looks Like on Day One

5 Interview Questions — With Complete Answers

Mistakes Beginners Make Constantly

Errors You Will Hit — And Exactly Why They Happen