Skip to content
Tutorials Center
My learning

Topics

Lesson 8 of 41 ยท Projects and Models

Materializations: View, Table and Ephemeral

Learn how dbt materializations decide whether a model becomes a view, a table or an inlined CTE, how to configure them, and how to choose between them with trade-offs.

A materialization is the strategy dbt uses to turn your SELECT into something in the warehouse. The same SQL can become a view, a table, or not exist at all as an object (it is inlined into other queries). Choosing the right one is a trade-off between build time, query speed and freshness. This lesson covers the three basic materializations; incremental models and snapshots get their own lessons.

The three basics

Materialization What dbt creates Data is stored Recomputed
view CREATE VIEW No, only the query Every time someone queries it
table CREATE TABLE AS Yes Each dbt run (dropped and rebuilt)
ephemeral Nothing: a CTE in the models that use it No Inside each query that uses it

Plus incremental (add only new rows) and materialized_view (the warehouse maintains it), covered later in this course.

Setting a materialization

Inside the model, at the top:

SQL
{{ config(materialized='table') }}

select * from {{ ref('stg_orders') }}

For a whole folder, in dbt_project.yml:

YAML
models:
  shop_analytics:
    +materialized: view
    marts:
      +materialized: table

Or in a properties YAML file next to your models:

YAML
models:
  - name: fct_orders
    config:
      materialized: table

If you configure nothing, you get view.

View: always fresh, computed on read

A view stores no rows. Every query against it runs the underlying SELECT again. Views are quick to build (milliseconds, nothing is copied) and always show current data. The cost is paid by readers: a slow view makes every dashboard slow, and a view built on views built on views multiplies work.

Here is the difference in action. We create a view and a table from the same query, then insert a new product, and compare:

SQL View versus table: who sees new data? ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

The view counts the new row immediately, while the table keeps the snapshot from when it was built, until the next dbt run rebuilds it. That is the core trade: a table is fast to read but only as fresh as its last run.

Table: fast to read, rebuilt on each run

dbt run drops and recreates a table model every time (or swaps atomically, depending on the adapter). Dashboards read plain stored rows, so queries are fast and cost the warehouse nothing extra. The cost is paid at build time and in storage.

Use tables for models that are queried often, are expensive to compute, or are the final marts that BI tools read.

Ephemeral: no object at all

An ephemeral model is never created in the warehouse. dbt takes its SQL and injects it as a CTE into every model that refs it.

SQL
{{ config(materialized='ephemeral') }}

select order_id, sum(line_total) as items_total
from {{ ref('stg_order_items') }}
group by order_id

If fct_orders refs this model, the SQL that runs is:

SQL
with __dbt__cte__order_item_totals as (
    select order_id, sum(line_total) as items_total
    from analytics.stg_order_items
    group by order_id
)
select * from __dbt__cte__order_item_totals ...

That keeps the warehouse tidy for small helper logic. The drawbacks: you cannot query it directly to debug, it cannot be tested or documented like a real table in every adapter workflow, and if five models use it, its SQL runs five times.

Let's see the mechanism with real Jinja. This stand-in (not dbt) generates the right statement for each materialization:

Python Stand-in: what each materialization generates

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

How to choose

A practical rule used by many teams:

  • Staging models โ†’ view. They are light renames and casts. A view costs nothing to build and always reflects the raw data.
  • Intermediate models โ†’ ephemeral or view. Small helper steps you do not want to clutter the warehouse with.
  • Marts (facts and dimensions) โ†’ table. Read by dashboards and analysts, so make reads fast.
  • Big, append-only data โ†’ incremental. When rebuilding a full table is too slow or costly. See the incremental lessons later in the course.

Revisit the choice when evidence says so: if a view chain is slow, promote the expensive step to a table; if a table's rebuild takes an hour, consider incremental.

Pick a materialization

View Fresh, cheap to build, slow if stacked
Table Fast reads, rebuilt each run
Ephemeral Inlined CTE, no object
Incremental Adds only new rows

Start with views, move to tables when reads get slow.

1 / 2

Common mistakes

  • Everything as a table "to be safe". Builds get slow and storage grows with no benefit for tiny staging models.
  • Deep stacks of views. Ten views on top of each other recompute everything on each read.
  • Ephemeral model used by many models. Its SQL repeats in each. Make it a table or view instead.
  • Forgetting views are not snapshots. If source data changes, view results change, which can surprise someone comparing yesterday to today.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. A dashboard queries a model many times per minute and the logic is expensive. Which materialization fits best?

    A table computes the result once per run and serves cheap reads afterwards.
  2. 2. What does dbt create in the warehouse for an ephemeral model?

    Ephemeral models are injected as CTEs into the models that reference them.
  3. 3. If you configure no materialization, what does dbt build?

    View is the default materialization.
  4. 4. Which is the main drawback of a view?

    Views store no data, so each read runs the query again. They build instantly but can be slow to query.

Summary

  • Materializations control what dbt builds from a SELECT: view, table, ephemeral (plus incremental and others).
  • Set them with config() in the file, folder-level config in dbt_project.yml, or YAML properties.
  • Views are cheap to build and always fresh; tables are fast to read but only as fresh as the last run; ephemeral models are inlined CTEs.
  • A good default: staging as views, marts as tables, and move to incremental only when a rebuild is too slow.

Next: Staging, Intermediate and Marts Layers