dbt Ephemeral Materialization Deep Dive: When to Use CTE-Style Intermediate Nodes

Ephemeral is one of dbt's materializations and does not persist the model to the database. Because the model is inlined as a CTE into the SQL of the referencing model, it fits well for lightweight intermediate transformations or temporary reshaping.

This article covers how ephemeral works, how it differs from other materializations, design heuristics, implementation and debugging techniques, and the points that frequently appear on the certification exam. The content sticks to stable concepts that align with the official dbt documentation.

Ephemeral basics: CTE-style intermediate nodes

Ephemeral inlines the referenced model as a CTE (or equivalent subquery) into the SQL of the model that ref()s it. The ephemeral model itself is never created as a table or view in the database. BI tools and other systems cannot query it directly — it only runs as part of the final referencing model.

It works best for lightweight reshaping, filtering, or column additions in intermediate steps that are not broadly reused across many models. For heavy aggregations or wide tables referenced by many downstream models, view/table/incremental are better for maintainability and performance.

• Intermediate logic is not persisted — it is inlined into the referencing model
• BI cannot query it directly (no persistent relation is created)
• Best suited for small, single-consumer componentization
• For heavy logic or many consumers, choose view/table

Ephemeral dependency and inlining visualization

-- Conceptual view of what runs on the referencing side
WITH orders_ephemeral AS (...),
     payments_ephemeral AS (...)
SELECT ...
FROM orders_ephemeral
LEFT JOIN payments_ephemeral ...

Minimal example (using ephemeral CTE-style)

-- models/stg/orders_ephemeral.sql
{{ config(materialized='ephemeral') }}
select
  o.order_id,
  o.customer_id,
  date_trunc('day', o.order_ts) as order_date
from {{ ref('raw_orders') }} as o;

-- models/stg/payments_ephemeral.sql
{{ config(materialized='ephemeral') }}
select
  p.order_id,
  sum(p.amount) as paid_amount
from {{ ref('raw_payments') }} as p
group by 1;

-- models/marts/fct_orders.sql
{{ config(materialized='table') }}
select
  o.order_id,
  o.customer_id,
  p.paid_amount
from {{ ref('orders_ephemeral') }} as o
left join {{ ref('payments_ephemeral') }} as p
  on o.order_id = p.order_id;

Compilation and dependencies: what runs when

dbt resolves the DAG, inlines referenced ephemeral models into the SQL of the referencing model, and then runs it. The ephemeral model itself never issues a standalone query against the database. As a result, even if you select an ephemeral model alone with dbt run, no persistent object is produced and there is essentially no query to execute.

Features applied at relation creation time — pre/post-hooks, grants, indexes, clustering — do not apply to ephemeral. Tests are an exception: ephemeral is inlined inside the test query, so tests can still run.

• dbt run -s final_model automatically embeds dependent ephemerals
• Running ephemeral alone produces nothing (there is no inlining target)
• hooks / grants do not apply — use view/table if you need them
• incremental settings and unique_key are irrelevant for ephemeral

Inspecting compilation and selective runs

# Compile the final model with its dependencies
$ dbt compile -s marts.fct_orders

# Check which nodes are in scope
$ dbt ls -s marts.fct_orders --resource-type model

# Reference: conceptual compiled SQL
with ep_orders as (
  select ... -- orders_ephemeral inlined
), ep_payments as (
  select ... -- payments_ephemeral inlined
)
select ... from ep_orders left join ep_payments on ...

Performance and cost design guidelines

Because ephemeral does not persist intermediate results, there is no storage or permission-management overhead. On the other hand, the referencing query can balloon in size, and on engines with limits on query length or CTE nesting it can fail or miss optimizations. Splitting heavy joins, window functions, or expensive aggregations across many ephemerals does not help — they all end up combined into a single query.

As a rule of thumb: choose ephemeral for small reshaping consumed by a single final model, view for shared transformations reused across multiple consumers, and table/incremental for heavy logic you do not want to recompute. Consider switching to view when debugging clarity matters or when you are approaching the engine's query-length limit.

• Small transformation, single consumer → ephemeral
• Reuse / debuggability priority → view
• Avoid re-running heavy computation → table / incremental
• Mind query-length and CTE nesting limits; split or persist if it grows

Tier-level defaults (project-wide configuration)

# dbt_project.yml (example)
models:
  my_project:
    staging:
      +materialized: ephemeral
    marts:
      +materialized: table

Best practices and anti-patterns

Make ephemeral's purpose clear through naming and location. Doing lightweight reshaping under stg and finalizing things in marts keeps the inlining boundary obvious. Anything you want to reuse from multiple places should be promoted to view/table, and only logic that stays scoped to a single consumer should remain ephemeral.

The anti-pattern is splitting heavy logic across many ephemerals and ending up generating a single, enormous query. Deep chains of five or more layers or heavy use of complex window functions also make optimization difficult. When you need debugging, granting permissions, or applying hooks, pick another materialization instead of ephemeral.

• Layer by purpose (stg: ephemeral, marts: table/view)
• Promote logic with multiple consumers to view/table
• Avoid deep chains and heavy windows — split or persist when needed
• Do not use ephemeral when hooks/grants are required

An easy-to-switch pattern (variable-controlled)

-- dbt_project.yml (example)
vars:
  stg_materialization: 'ephemeral'

-- models/stg/_base.sql (top of each stg model, etc.)
{{ config(materialized=var('stg_materialization', 'ephemeral')) }}

-- For debugging: change stg_materialization to 'view' and rebuild

Implementation and debugging: inspect compiled artifacts

The surest way to verify ephemeral's effect is to inspect the compiled SQL. The referencing model's SQL produced under target/compiled will contain the ephemeral model's SELECT expanded as a CTE. This lets you see exactly how big the production query will be.

When you want to inspect intermediate results directly during debugging, temporarily switch to view. Tests still run while in ephemeral, but for large row counts or complex logic, persisting the model and verifying incrementally is safer.

• Use dbt compile and inspect target/compiled
• Toggle a switch variable to flip between ephemeral and view quickly
• For heavy rows or complex logic, persist temporarily to verify

Conceptual compiled output and commands

# Compile only
$ dbt compile -s marts.fct_orders

# Build (model and tests together)
$ dbt build -s marts.fct_orders

-- Conceptual compiled SQL
with orders_ephemeral as (
  select ...
), payments_ephemeral as (
  select ...
)
select ... from orders_ephemeral o
left join payments_ephemeral p on o.order_id = p.order_id

Exam prep summary (Analytics Engineer)

The exam frequently asks about the definition of ephemeral (non-persistent, inlined as a CTE) and how to choose between materializations. In particular, remember that ephemeral does not run on its own, that hooks/grants do not apply, and that it is not suited for heavy logic or wide reuse.

You may also be asked that selecting the referencing model automatically includes its ephemerals, and that ephemeral is still executable inside test queries because it is inlined there.

• Keywords: non-persistent, CTE, inlined, cannot run standalone
• Selection: lightweight / single-consumer → ephemeral, reuse → view, heavy → table/incremental
• hooks/grants do not apply; tests do run because they are inlined

Key CLI and configuration to remember

$ dbt run -s marts.fct_orders
# Referenced ephemerals are inlined automatically

-- Example config at the top of a model
{{ config(materialized='ephemeral') }}

Check Your Understanding

Frequently Asked Questions