Ephemeral Materialization in dbt: What It Is and When to Use

Ephemeral materialization in dbt means the model does not create a physical table or view in the database. Instead, its SQL is embedded directly into downstream models during compilation, making it fast and efficient for temporary or intermediate calculations.

How It Works

Imagine you are cooking a meal and need to chop vegetables before mixing them into a salad. Instead of putting chopped vegetables in a separate bowl, you directly add them to the salad bowl as you chop. This is similar to how ephemeral materialization works in dbt.

When you use ephemeral materialization, dbt does not create a separate table or view in your database. Instead, it takes the SQL code of the ephemeral model and inserts it directly into the SQL of the models that depend on it. This means the database runs the combined SQL in one go, without storing intermediate results.

This approach saves time and storage because no extra tables or views are created. It is like doing quick calculations on the fly rather than saving temporary results.

Example

This example shows a simple ephemeral model and how it is used in a downstream model.

sql

/* models/ephemeral_model.sql */
-- This model is ephemeral and filters active users
{{ config(materialized='ephemeral') }}

select * from {{ ref('raw_users') }} where status = 'active'


/* models/final_model.sql */
select user_id, email from {{ ref('ephemeral_model') }} where email is not null

Output

The final SQL run by dbt will be:

select user_id, email from (
  select * from raw_users where status = 'active'
) where email is not null

When to Use

Use ephemeral materialization when you want to avoid creating extra tables or views for intermediate steps. It is ideal for small, reusable SQL snippets that are only needed inside other models.

For example, if you have a complex filter or calculation used by multiple models, making it ephemeral lets you keep your database clean and your transformations fast.

However, ephemeral models are not suitable for very large datasets or when you want to inspect intermediate results because they do not exist as separate tables.

Key Points

• Ephemeral models do not create tables or views in the database.
• Their SQL is embedded directly into downstream models during compilation.
• They improve performance by avoiding extra storage and queries.
• Best for small, reusable SQL snippets or intermediate calculations.
• Not suitable for large datasets or when intermediate results need to be saved.

Key Takeaways

Ephemeral materialization embeds SQL directly into downstream models without creating tables.

It saves storage and speeds up transformations by avoiding intermediate tables or views.

Use it for small, reusable SQL snippets or intermediate calculations.

Avoid ephemeral models for large datasets or when you need to save intermediate results.