Skip to content
LakeBench
ProblemsCommunityPricing
Sign inStart practicing
Back
  1. Home
  2. Interview prep
  3. Ephemeral models trade-offs

dbt · Workflow, CI & Performance

Ephemeral models trade-offs

Mediumdbt-50
ephemeralmaterializationsperformancedebugging

Question

When should you use ephemeral materialization, and when does it cause pain?

Solution

Ephemeral models are virtual models that dbt never creates as physical tables or views in the warehouse. When another model references an ephemeral model using ref, dbt inlines the ephemeral model SQL as a Common Table Expression directly into the downstream query. They work well for lightweight reusable logic, but cause performance and debugging headaches when applied to heavy transformations.

How ephemeral models work

When you configure materialized ephemeral, dbt compiles the model into a Jinja variable rather than executing a CREATE VIEW or CREATE TABLE DDL statement.

Consider an ephemeral model int_cleaned_users:

{{ config(materialized='ephemeral') }}
select user_id, lower(email) as email from {{ ref('stg_users') }}

When a downstream model dim_customers references this model, dbt compiles the query into an inlined CTE:

with __dbt__cte__int_cleaned_users as (
    select user_id, lower(email) as email from analytics.stg_users
)
select * from __dbt__cte__int_cleaned_users

The database executes the CTE inline during query processing.

When ephemeral models help

Ephemeral models provide clear advantages in specific scenarios:

  • Clean warehouse schemas keep development and production databases uncluttered by avoiding hundreds of intermediate micro-views.
  • Lightweight code reuse allows teams to share simple string manipulation, date formatting, or basic filtering across multiple models without creating database objects.
  • Reduced DDL overhead avoids warehouse transaction overhead for tiny intermediate steps.

These benefits apply best to simple transformations.

Where ephemeral models cause problems

Despite their convenience, ephemeral models introduce significant operational challenges:

  • Debugging friction occurs because you cannot query the model directly in your database console. If downstream data looks incorrect, you must manually extract and run the compiled CTE SQL.
  • Duplicated computation occurs when multiple downstream models reference the same ephemeral model. If four mart models call an ephemeral model that performs an expensive aggregation, the warehouse runs that aggregation four separate times.
  • Testing limitations prevent easy standalone data testing. dbt must wrap the ephemeral CTE inside each test query, lengthening CI test execution.

Avoid ephemeral materialization for heavy joins or window functions; use views or physical tables instead.

PreviousNext