Skip to content
Tutorials Center
My learning

Topics

Lesson 27 of 41 ยท Incremental Models and Materializations

Materializations: View, Table and Ephemeral

Understand how dbt turns a SELECT into a view, table or ephemeral CTE, how to configure it per model or folder, and how to choose between them.

A dbt model is just a SELECT statement. A materialization decides what dbt does with it in the warehouse: create a view, build a table, inline it as a CTE, or insert only new rows. Choosing the right one is the main performance and cost lever in a dbt project.

This lesson covers the three simple materializations. Incremental models get their own lessons.

dbt cannot run on this site, so the dbt run output is read-only. The generated SQL (create view, create table) is plain SQL, and the editors run it on SQLite so you can see the difference in behavior.

Setting the materialization

Set it inside a model with a config block, or for a folder in dbt_project.yml:

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

select * from {{ ref('stg_orders') }}
YAML
# dbt_project.yml
models:
  my_project:
    staging:
      +materialized: view
    marts:
      +materialized: table

The most specific setting wins: a config() in the file beats the folder setting, which beats the project-wide default (view).

view

dbt runs create or replace view analytics.stg_orders as (select ...). Nothing is stored; each time someone queries the view, the underlying SQL runs again.

  • Pros: instant to build, no storage, always fresh.
  • Cons: slow for heavy logic, because the cost is paid on every query; stacks of views get slow.
  • Use for: light staging models (renames, casts) that are queried rarely or only by other models.

table

dbt runs create table ... as select ... (replacing the old table, usually through a temporary table and a swap). The result is physical data.

  • Pros: fast to query, cost paid once per run.
  • Cons: slower to build, can be stale between runs, rebuilds the whole table each time.
  • Use for: marts and anything BI tools or analysts query often.

The difference in practice, using plain SQL on the shop data:

SQL View versus table ยท database: shop

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

The view recomputes and sees the new item; the table still holds the old number until the next dbt run rebuilds it. Freshness versus speed is the trade-off you are choosing.

ephemeral

An ephemeral model creates nothing in the warehouse. dbt copies its SQL into every downstream model as a CTE named __dbt__cte__<model>.

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

select order_id, customer_id from {{ ref('stg_orders') }}
where status <> 'cancelled'

If fct_orders refs this model, the compiled SQL starts with with __dbt__cte__active_orders as (select ... ).

  • Pros: no clutter, no storage, good for small reusable logic.
  • Cons: you cannot query it directly or test it separately in the warehouse (tests on ephemeral models are limited), harder to debug, and the same CTE is repeated in every consumer.
  • Use for: tiny intermediate steps used by one or two models. Do not stack many levels.

The compiled result is effectively this:

SQL Ephemeral = CTE in the consumer ยท database: shop

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

Choosing

flowchart TD
  A["New model"] --> B{"Queried by people or BI tools?"}
  B -->|"Yes"| C{"Built in a few minutes and data fits?"}
  C -->|"Yes"| T["table"]
  C -->|"No: huge, append-mostly"| I["incremental"]
  B -->|"No, only by other models"| D{"Reused in many places?"}
  D -->|"Yes"| V["view (or table if heavy)"]
  D -->|"No, small step"| E["ephemeral"]

A starting heuristic: views for staging, tables for marts, incremental when tables get too big to rebuild.

A common default: staging models as views, intermediate models as ephemeral or views, marts as tables. Start simple and move a model up only when it is slow or expensive.

Other materializations

  • materialized_view: the warehouse maintains the result itself (supported on several warehouses).
  • incremental: build once, then add only new or changed rows.
  • Warehouse-specific options like transient tables (Snowflake) or partition_by and cluster_by (BigQuery) go into the same config block.
  • You can write your own materialization macro, but it is rarely needed.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What does an ephemeral model create in the warehouse?

    dbt copies the SQL into each downstream model as a CTE named dbt__cte<model>.
  2. 2. Which materialization recomputes results on every query?

    A view stores only the query, so the work happens when someone selects from it.
  3. 3. A config() inside the model file says materialized='table', but dbt_project.yml says view for that folder. Which wins?

    More specific configuration overrides broader configuration.
  4. 4. Which is a typical choice for marts queried by dashboards?

    Tables pay the compute cost once per run, so dashboards stay fast.

Summary

  • Materialization decides how a model's SELECT is stored: view, table, ephemeral (CTE), or incremental.
  • Views are always fresh but slow to query; tables are fast but only as fresh as the last run.
  • Ephemeral models leave no object behind and are best for tiny steps.
  • Configure per model or per folder; the most specific setting wins.

Next: Incremental Models: the Basics