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.