Ephemeral Models

Concepts covered: Materializations

A model dbt never builds

Materialized as a view or a table, a model becomes an object in the warehouse. Materialized as ephemeral, it becomes nothing there. dbt keeps its SQL, and when another model refs it, dbt pastes that SQL into the referencing model as a CTE and sends the combined statement. The ephemeral model exists in the project files and in the lineage, and never in the schema. Here the intermediate model that section 1 asked for settles subscriptions to 1 row per user.

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

select
    user_id,
    max_by(plan_code, started_at) as plan_code
from {{ ref('stg_billing__subscriptions') }}
where status = 'active'
group by
    user_id

The group by settles the grain to 1 row per user whatever the subscriptions table holds, and max_by returns the plan_code of the active row that started most recently. The mart from section 0 now refs this model instead of the staging view.

select
    d.user_id,
    d.play_date,
    c.plan_code,
    d.plays,
    d.minutes_played
from {{ ref('int_plays__daily_by_user') }} as d
left join {{ ref('int_subscriptions__current') }} as c
    on d.user_id = c.user_id

The SQL dbt sends

create table analytics.fct_user_daily_listening as
with __dbt__cte__int_subscriptions__current as (
    select
        user_id,
        max_by(plan_code, started_at) as plan_code
    from analytics.stg_billing__subscriptions
    where status = 'active'
    group by
        user_id
)
select
    d.user_id,
    d.play_date,
    c.plan_code,
    d.plays,
    d.minutes_played
from analytics.int_plays__daily_by_user as d
left join __dbt__cte__int_subscriptions__current as c
    on d.user_id = c.user_id

The ref() to int_subscriptions__current became the CTE name __dbt__cte__int_subscriptions__current, and the whole body of the ephemeral model was pasted in above the mart's select. The ref() to int_plays__daily_by_user, a view, became a relation name as before. dbt sends 1 statement for the mart, and the warehouse runs the group by over subscriptions as part of building the table. When the run finishes, nothing named int_subscriptions__current exists in the schema.

Materialization	What dbt sends for the node	What the warehouse holds after	What a ref() to it becomes
view	create view	a saved query	the view's name
table	create table as	the rows	the table's name
ephemeral	nothing of its own	nothing	a CTE pasted into the caller

What that changes

No object to query: a select from analytics.int_subscriptions__current fails, the relation was never created.
Its SQL runs once per model that refs it: 3 marts that ref it run the group by 3 times.
It stays in the lineage and dbt orders the build around it, but the node itself takes no run time.
An ephemeral model that refs another ephemeral model nests as a second CTE; a chain of 5 makes a 5-CTE statement.
To see its rows, build a model that refs it, or switch its config to view for an afternoon.

Ephemeral or a warehouse object

Ephemeral fits

Logic read by 1 or 2 models
A filter or a small aggregation too light to deserve a warehouse object
A schema you want to show only the tables analysts should open

Make it a view or a table

Logic refd by many models, which would each recompute it
Rows you need to inspect while debugging
Heavy work, where a table stores the result once per build

The schema listing is the first reason. A project with 30 intermediate models as views lists 30 objects beside the 8 marts analysts want, and every analyst learns to skip past them. Ephemeral keeps the project's structure in the files and the warehouse's listing to the results. The cost is repetition: a heavy intermediate model refd by 6 marts recomputes 6 times per build, where a view or a table pays once.

Switching a model between view and ephemeral changes no SQL inside it and no SQL in any model that refs it. The 1 line of config decides whether dbt sends a create view during the run and a relation name in the mart, or nothing during the run and a CTE in the mart. Because every neighbor reaches the node through ref(), none of them depends on how the node is materialized.

About this lesson

This section is part of the Models: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.