dbt ephemeral materialization tradeoffs

TL;DR

Ephemeral models are CTEs injected into downstream models: zero warehouse objects, but their SQL is recompiled into every downstream query, which hurts readability of compiled code and can blow up query size. Use ephemeral for thin logic layers; materialize anything heavy or widely reused.

dbt ephemeral materialization tradeoffs

Use this when

• Choosing a materialization for a model
• Compiled SQL is huge or slow because of nested ephemerals
• Debugging a model with no table to inspect

Not for this skill when

• You are choosing snapshot strategies
• You are writing unit tests
• dbt packages conflict on versions

Steps

1. Set the materialization:

{{ config(materialized='ephemeral') }}
SELECT user_id, lower(email) AS email FROM {{ ref('stg_users') }}

Expected output: no table or view created. Downstream models referencing this get the SELECT injected as a CTE.

1. Understand the core tradeoff:

Ephemeral pros: no objects to manage, no storage cost, always fresh
(because it recomputes), no stale-data risk.
Ephemeral cons: recomputed on every downstream run (CPU cost),
compiled SQL balloons with nesting, nothing to query directly
when debugging, no tests can target it as a table.

Expected output: the decision inputs. "Always fresh" and "recomputed every time" are the same property viewed from opposite sides.

1. Watch for the nesting blowup. Three layers of ephemeral:

mart -> ephemeral_b -> ephemeral_a -> staging
compiles to: mart query with b and a as nested CTEs.
Add two more layers and the compiled SQL is unreadable,
and the warehouse plans the same logic repeatedly.

Expected output: recognition of the smell. If dbt compile output for a mart is thousands of lines, some ephemeral layer should be a view or table.

1. The rule of thumb for choosing:

Ephemeral: thin renames, light filtering, one-or-two downstream consumers.
View: reusable logic you want to inspect and test, cheap to compute.
Table: heavy transforms, many downstream consumers, or anything
queried directly by BI tools or agents.

Expected output: a materialization per model that matches its role. Intermediate models in a long chain are usually views or tables, not ephemeral.

1. Debug ephemeral logic by compiling, not by querying:

dbt compile --select my_ephemeral_model
# read target/compiled/.../my_ephemeral_model.sql

Expected output: the compiled SQL. Since there is no table, the compiled file is the only artifact to inspect when the logic looks wrong.

Variant phrasings

dbt ephemeral vs view

Ephemeral injects SQL as a CTE; a view creates a database object. Views are inspectable and testable; ephemeral is lighter on objects but heavier on repeated compute.

dbt ephemeral model slow

It recomputes on every downstream run. If the logic is expensive or shared by many models, materialize it as a table.

cannot query dbt ephemeral model

Correct, there is nothing to query. Compile it (step 5) or temporarily switch to view for debugging.

Why it happens

dbt's ref() resolves to different SQL per materialization. For ephemeral, the model's SQL becomes a CTE inside each downstream query at compile time. That is elegant for simple layers and punishing for deep or heavy ones, because the warehouse pays the compute cost once per downstream model instead of once total.

Edge cases

• Ephemeral models cannot be tested with data tests (no table exists); unit tests on the logic still work.
• Some adapters limit CTE nesting depth or query text size; deep ephemeral chains hit those first.
• dbt run on an ephemeral model alone does nothing visible; it only matters through downstream models.
• Switching ephemeral to table changes downstream plans; re-test the marts after the switch.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_6PQ-6K5BUa5TR8J-eOF9dA