dbt Ephemeral Models

Ephemeral Model Architecture

Materialization Comparison Pipeline

Formal Definitions

Detailed Explanation

What are Ephemeral Models?

Ephemeral models solve a specific problem: intermediate transformations that need to be reused but shouldn't persist as database objects. They combine the reusability of views with the performance of inline SQL.

When to Use Ephemeral Models

Use Case	Reason
Single consumer	Model is referenced by exactly one downstream model
Complex logic	Transformation is too complex to inline
Shared transformations	Same logic used in multiple downstream models
Security	Don't expose intermediate tables to end users

When NOT to Use Ephemeral Models

Scenario	Alternative
Multiple consumers	Use a view
Large datasets	Use a table (CTE may cause optimizer issues)
Long-running queries	Use incremental (CTE re-execution is wasteful)
Debugging needed	Use a view (cannot inspect ephemeral data directly)
Testing required	Use a view (cannot run data tests on ephemeral models)

Key Takeaway: Ephemeral models are injected as CTEs at compile time — no database object is created. Use them for single-consumer intermediate logic; switch to views when more than 3 downstream models reference them.

Code Examples

Basic Ephemeral Model

-- models/intermediate/int_order_items_cleaned.sql
{{
    config(
        materialized='ephemeral'
    )
}}

with source as (
    select * from {{ ref('stg_order_items') }}
),

cleaned as (
    select
        order_id,
        product_id,
        quantity,
        unit_price,
        quantity * unit_price as line_total,
        case
            when quantity < 0 then 0
            else quantity
        end as clean_quantity
    from source
    where order_id is not null
      and product_id is not null
)

select * from cleaned

Downstream Model Using Ephemeral

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

with orders as (
    select * from {{ ref('stg_orders') }}
),

customers as (
    select * from {{ ref('dim_customers') }}
),

-- This references the ephemeral model
-- The CTE is injected here at compile time
order_items as (
    select * from {{ ref('int_order_items_cleaned') }}
),

order_summary as (
    select
        o.order_id,
        o.customer_id,
        c.customer_name,
        c.segment,
        o.order_date,
        sum(oi.line_total) as order_total,
        count(oi.product_id) as item_count
    from orders o
    left join customers c on o.customer_id = c.customer_id
    left join order_items oi on o.order_id = oi.order_id
    group by 1, 2, 3, 4, 5
)

select * from order_summary

Compiled Output (CTE Injection)

-- The above downstream model compiles to:
WITH
order_items AS (
    -- CTE injected from ephemeral model
    SELECT
        order_id,
        product_id,
        quantity,
        unit_price,
        quantity * unit_price AS line_total,
        CASE
            WHEN quantity < 0 THEN 0
            ELSE quantity
        END AS clean_quantity
    FROM stg_order_items
    WHERE order_id IS NOT NULL
      AND product_id IS NOT NULL
)
SELECT
    o.order_id,
    o.customer_id,
    c.customer_name,
    c.segment,
    o.order_date,
    SUM(oi.line_total) AS order_total,
    COUNT(oi.product_id) AS item_count
FROM stg_orders o
LEFT JOIN dim_customers c ON o.customer_id = c.customer_id
LEFT JOIN order_items oi ON o.order_id = oi.order_id
GROUP BY 1, 2, 3, 4, 5

Multiple Ephemeral Models

-- models/intermediate/int_dates_filtered.sql
{{
    config(materialized='ephemeral')
}}

select *
from {{ ref('dim_date') }}
where date_value >= '{{ var("start_date") }}'
  and date_value <= '{{ var("end_date") }}'

-- models/intermediate/int_revenue_calc.sql
{{
    config(materialized='ephemeral')
}}

with orders as (
    select * from {{ ref('stg_orders') }}
),

order_items as (
    select * from {{ ref('int_order_items_cleaned') }}
),

revenue as (
    select
        o.order_id,
        o.order_date,
        sum(oi.line_total) as gross_revenue,
        sum(oi.line_total * 0.1) as estimated_tax,
        sum(oi.line_total * 0.9) as net_revenue
    from orders o
    left join order_items oi on o.order_id = oi.order_id
    group by 1, 2
)

select * from revenue

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

with dates as (
    select * from {{ ref('int_dates_filtered') }}
),

revenue as (
    select * from {{ ref('int_revenue_calc') }}
),

daily_summary as (
    select
        d.date_value,
        coalesce(sum(r.gross_revenue), 0) as total_revenue,
        coalesce(sum(r.estimated_tax), 0) as total_tax,
        coalesce(sum(r.net_revenue), 0) as total_net_revenue,
        count(distinct r.order_id) as order_count
    from dates d
    left join revenue r on d.date_value = r.order_date
    group by 1
)

select * from daily_summary

Comparison: Materializations

Materialization	Database Object	Queryable	Testable	Use Case
View	View	Yes	Yes	Shared transformations
Ephemeral	None (CTE)	No	No	Single-consumer intermediates
Table	Table	Yes	Yes	Final marts
Incremental	Table	Yes	Yes	Large, append-only datasets

Performance Characteristics

Scenario	View	Ephemeral	Table	Incremental
Simple transformation	Good	Best	Good	Good
Complex transformation	Good	Moderate	Best	Best
Multiple consumers	Best	Poor	Good	Good
Single consumer	Good	Best	Moderate	Moderate
Large dataset	Good	Poor	Good	Best
Debugging	Best	Poor	Best	Good

Debugging Ephemeral Models

Option 1: Temporarily Change to View

-- Change materialization to inspect output
{{
    config(
        materialized='view'  -- Changed from 'ephemeral'
    )
}}

-- Run dbt run --select int_order_items_cleaned
-- Then query the view in your database
-- Remember to change back to ephemeral when done

Option 2: Use dbt Compile

# See the compiled SQL with CTE injection
dbt compile --select fct_orders

# Check the target/compiled directory for output
cat target/compiled/project_name/models/marts/fct_orders.sql

Option 3: Add Debug CTE

-- Add a debug CTE to see ephemeral model output
{{
    config(materialized='ephemeral')
}}

with debug as (
    -- Add this to see the output
    select count(*) as row_count from (
        -- Original ephemeral model logic
        select * from {{ ref('stg_orders') }}
    ) subq
)

select * from cleaned
-- Note: This won't work with ephemeral, use view instead

Best Practices

Single consumer - Only use ephemeral when referenced by one downstream model
Keep it simple - Ephemeral models should be small, focused transformations
Document purpose - Clearly describe why a model is ephemeral
Test downstream - Test the downstream model that uses the ephemeral model
Monitor performance - Watch for CTE-related query performance issues
Use for security - Hide intermediate business logic from end users
Avoid for large data - Large ephemeral CTEs can cause optimizer issues
Consider alternatives - Views for shared transformations, tables for large data

dbt Ephemeral Models

dbt Ephemeral Models

Ephemeral Model Architecture

Materialization Comparison Pipeline

Formal Definitions

Detailed Explanation

What are Ephemeral Models?

When to Use Ephemeral Models

When NOT to Use Ephemeral Models

Code Examples

Basic Ephemeral Model

Downstream Model Using Ephemeral

Compiled Output (CTE Injection)

Multiple Ephemeral Models

Comparison: Materializations

Performance Characteristics

Debugging Ephemeral Models

Option 1: Temporarily Change to View

Option 2: Use dbt Compile

Option 3: Add Debug CTE

Best Practices

See Also

Need Expert dbt Help?