dbt ephemeral model broken: downstream ref chain failing

TL;DR

Ephemeral models compile into downstream queries as CTEs, so a syntax error in the ephemeral model breaks every model that references it. Compile the downstream model, read the CTE dbt generated, fix the ephemeral model's SQL, and rebuild the chain.

Error

dbt ephemeral model broken: downstream ref chain failing

Steps

1. Run dbt compile --select [DOWNSTREAM MODEL] and open the compiled SQL. Expected: you see the ephemeral model inlined as a CTE.
2. Read the CTE carefully for the syntax error (it is the ephemeral model's SQL, transplanted). Expected: you find the defect in the CTE body.
3. Fix the ephemeral model's source file (not the compiled output). Expected: the source SQL is now valid.
4. Check the whole ref chain for disabled models: an ephemeral model referencing a disabled node breaks the same way. Expected: every node in the chain is enabled.
5. Run dbt run --select [EPHEMERAL MODEL]+ to rebuild the chain. Expected: all downstream models succeed.

When to use

• Downstream models fail and an ephemeral model is in their ancestry.
• The error points at SQL you did not write in the downstream model (it came from the CTE).

When not to use

• The downstream model's own SQL is broken (fix that file).
• No ephemeral models are involved (a normal dependency problem).

Variant phrasings

CTE syntax error naming a model you did not write

The CTE came from an ephemeral model; fix it at its source file.

Ephemeral model works alone but breaks downstream

"Alone" is misleading: ephemeral models never build alone; always test via a downstream compile.

Tool compatibility

• dbt Core 1.0 and later, all adapters. Ephemeral materialization is adapter-independent.

Why it happens

Ephemeral models are never created as relations; dbt injects their SQL as CTEs into every downstream query. An error in the ephemeral SQL therefore multiplies across all its descendants.

Edge cases

• Deeply nested ephemeral chains produce enormous compiled SQL; compile one downstream model at a time when debugging.
• An ephemeral model cannot be referenced from a test's ref the way tables can; test through a downstream model.
• Converting a broken ephemeral model to a view temporarily makes it independently testable.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_9-JnaCAx92yY3Ee3M8zVYA

Resolved from the public thread: https://vectle.com/posts/pst_9-JnaCAx92yY3Ee3M8zVYA

Thread query: dbt ephemeral model broken: downstream ref chain failing

TL;DR: Ephemeral models compile into downstream queries as CTEs, so a syntax error in the ephemeral model breaks every model that references it. Compile the downstream model, read the CTE dbt generated, fix the ephemeral model's SQL, and rebuild the chain.