Skip to main content

Ephemeral Materialization

An ephemeral materialization is compiled into downstream SQL as a common table expression (CTE). dbt does not create a table or view for the ephemeral model in the data warehouse.

This makes ephemeral models useful for small, reusable pieces of transformation logic that only need to exist while a downstream model is running.

How It Works​

Suppose an intermediate model prepares completed orders:

models/intermediate/int_completed_orders.sql​

{{ config(materialized='ephemeral') }}

SELECT
ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
AMOUNT
FROM {{ ref('stg_orders') }}
WHERE STATUS = 'completed'

The model is still part of the dbt dependency graph, but dbt does not create an INT_COMPLETED_ORDERS table or view.

A downstream model can reference it normally:

models/marts/fct_customer_orders.sql​

{{ config(materialized='table') }}

SELECT
CUSTOMER_ID,
COUNT(*) AS TOTAL_COMPLETED_ORDERS,
SUM(AMOUNT) AS TOTAL_AMOUNT
FROM {{ ref('int_completed_orders') }}
GROUP BY CUSTOMER_ID

When dbt compiles the downstream model, it inlines the ephemeral model as a CTE. The resulting SQL is conceptually similar to:

WITH __dbt__cte__int_completed_orders AS (
SELECT
ORDER_ID,
CUSTOMER_ID,
ORDER_DATE,
AMOUNT
FROM ANALYTICS.STAGING.STG_ORDERS
WHERE STATUS = 'completed'
)

SELECT
CUSTOMER_ID,
COUNT(*) AS TOTAL_COMPLETED_ORDERS,
SUM(AMOUNT) AS TOTAL_AMOUNT
FROM __dbt__cte__int_completed_orders
GROUP BY CUSTOMER_ID

The warehouse runs the CTE as part of the downstream query, and only the final table FCT_CUSTOMER_ORDERS is created.

Run and Verify​

You should run the downstream model, not the ephemeral model by itself:

dbt run --select fct_customer_orders

After the command completes, check your warehouse for the final relation:

<target_database>.<target_schema>.FCT_CUSTOMER_ORDERS

You will not find an INT_COMPLETED_ORDERS table or view because the ephemeral model is only compiled into the downstream query.

To inspect the generated SQL locally without executing it, run:

dbt compile --select fct_customer_orders

Then open the compiled downstream SQL under:

target/compiled/<project_name>/models/marts/fct_customer_orders.sql

Look for the __dbt__cte__int_completed_orders CTE near the top of the file. After dbt run, the executed SQL is also available under the corresponding path in:

target/run/<project_name>/models/marts/fct_customer_orders.sql

The exact database and schema depend on the active target in your profiles.yml and your dbt model configuration.

When To Use Ephemeral Models​

  • Reusable logic is small and belongs close to one or more downstream models.
  • The intermediate result does not need to be queried independently.
  • You want to avoid adding temporary tables or views to the warehouse.
  • The logic is simple enough that repeating it as a CTE will not make the final query difficult to understand.

Important Considerations​

  • An ephemeral model cannot be queried directly because it does not create a database relation.
  • Its SQL is re-evaluated whenever a downstream model runs.
  • Large or expensive ephemeral models can make downstream queries slower because their logic is executed inside each dependent query.
  • If a model needs independent testing, frequent reuse, debugging, or warehouse-level performance optimization, a view or table may be a better choice.