dbt Ephemeral Models

An ephemeral model does not create any database object in your warehouse. Instead, dbt inlines the model's SQL as a Common Table Expression (CTE) inside every model that references it. Ephemeral models let you organize your SQL logic into small, readable pieces without cluttering your warehouse with intermediate tables or views.

A Grocery Store Analogy

View / Table:    "Make guacamole, put it in a bowl, store in fridge, serve from bowl"
Ephemeral:       "Mash the avocados directly into the dish as you assemble it"

The ephemeral model represents a step in your cooking process that happens inline. It never becomes its own stored item. Downstream models consume the logic, not a stored result.

Creating an Ephemeral Model

-- models/intermediate/int_orders_enriched.sql

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

select
    o.order_id,
    o.customer_id,
    o.order_date,
    o.amount_dollars,
    case
        when o.amount_dollars >= 500 then 'high_value'
        when o.amount_dollars >= 100 then 'medium_value'
        else 'low_value'
    end as order_tier,
    datediff(day, o.order_date, current_date) as days_since_order
from {{ ref('stg_orders') }} o

Referencing the Ephemeral Model

-- models/marts/fct_orders.sql

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

select
    e.order_id,
    e.order_tier,
    e.days_since_order,
    c.full_name    as customer_name,
    c.email
from {{ ref('int_orders_enriched') }} e    ← references the ephemeral model
join {{ ref('stg_customers') }}       c
  on e.customer_id = c.customer_id

What the Compiled SQL Looks Like

When dbt compiles fct_orders.sql, it inlines int_orders_enriched as a CTE:

-- target/compiled/fct_orders.sql (simplified)

with int_orders_enriched as (
    -- contents of int_orders_enriched.sql inlined here
    select
        o.order_id,
        o.customer_id,
        o.order_date,
        o.amount_dollars,
        case
            when o.amount_dollars >= 500 then 'high_value'
            when o.amount_dollars >= 100 then 'medium_value'
            else 'low_value'
        end as order_tier,
        datediff(day, o.order_date, current_date) as days_since_order
    from dbt_dev.stg_orders o
)

select
    e.order_id,
    e.order_tier,
    e.days_since_order,
    c.full_name as customer_name,
    c.email
from int_orders_enriched e
join dbt_dev.stg_customers c
    on e.customer_id = c.customer_id

The warehouse sees one combined query. It never creates a separate table or view for int_orders_enriched.

Ephemeral Models in the DAG

Ephemeral models still appear in dbt's DAG and in the documentation site. They show as a distinct node type (dotted outline in dbt Cloud). You can still see their dependencies and which models use them.

[stg_orders] ──────────────────────────────────────────────────────────>
                                                                         [fct_orders]
[int_orders_enriched] (ephemeral, inlined into fct_orders) ────────────>
[stg_customers] ────────────────────────────────────────────────────────>

Chaining Ephemeral Models

One ephemeral model can reference another ephemeral model. dbt chains the CTEs together in the final compiled SQL:

-- int_step1 (ephemeral): filters raw orders
-- int_step2 (ephemeral): enriches filtered orders (references int_step1)
-- fct_final (table): references int_step2

Compiled SQL for fct_final:
  with int_step1 as (...),
       int_step2 as (...references int_step1 as CTE...),
  select * from int_step2 ...

Limitations of Ephemeral Models

Cannot be queried directly

Because no warehouse object is created, you cannot run SELECT * FROM int_orders_enriched in your warehouse to inspect the data. To debug an ephemeral model, temporarily switch it to a view, inspect the output, then switch back.

Cannot be used by multiple unrelated downstream models efficiently

If three independent downstream models all reference the same ephemeral model, the ephemeral SQL is inlined (and re-run) in each of the three queries separately. A view or table would be computed once and reused. For shared logic used by many models, a view is more efficient than ephemeral.

Cannot run tests on intermediate data

Because no table exists, you cannot run a not_null test on an ephemeral model's columns. Tests on ephemeral models run against the inlined CTE, which some configurations do not support.

When to Use Ephemeral

Use ephemeral when:                    Use view instead when:
----------------------------           -----------------------
Logic is only used by one model        Logic is used by multiple models
No need to debug intermediate data     You want to query intermediate data
Cleaning up warehouse object count     Tests are needed on intermediate data
Short, simple transformations          Complex transformations worth inspecting