Ephemeral Materialization

Ephemeral Materialization

Unlike other materializations, ephemeral models don't create database objects. Instead, dbt incorporates the model's code into dependent models using Common Table Expressions (CTEs).

This means:

  • No physical tables or views are created

  • Code is integrated into downstream models

  • Logic can be reused across models

Ephemeral models are ideal for lightweight, reusable transformations that don't need to be queried directly.


Working with Ephemeral Models

Let's understand ephemeral models through a simple example:

-- models/intermediate/clean_customer_data.sql
{{
    config(
        materialized='ephemeral'
    )
}}

SELECT
    customer_id,
    TRIM(first_name) as first_name,
    TRIM(last_name) as last_name,
    LOWER(email) as email
FROM {{ ref('raw_customers') }}

When this model is referenced:

  1. No table or view is created

  2. The code becomes a CTE in downstream models

  3. The logic is recomputed each time it's used


When to Use Ephemeral Models

Ephemeral models work best for:

  • Simple transformations (cleaning, filtering)

  • Early-stage models in your DAG

  • Light data preparation steps

  • Code that supports 1-2 downstream models


Configuration

Model-Level Configuration

-- In your model SQL file
{{
    config(
        materialized='ephemeral'
    )
}}

SELECT 
    user_id,
    CASE 
        WHEN age < 18 THEN 'youth'
        WHEN age < 65 THEN 'adult'
        ELSE 'senior'
    END as age_category
FROM {{ ref('raw_users') }}

Performance and Usage Guidelines

Advantages and Limitations

Advantages
Limitations

🧹 Reduces database clutter

🚫 Can't query directly

♻️ Enables code reuse

⚠️ No model contracts support

📦 No storage overhead

🔍 Harder to debug

🔄 Flexible logic updates

🛠️ Limited operational use

When to Change Materialization

Consider a different materialization when you:

  • Need to query the model directly

  • Have complex transformations

  • Support many downstream models

  • Require model contracts

Best Practices

  1. Keep It Simple: Use for lightweight transformations only

  2. Limit Dependencies: Best for 1-2 downstream models

  3. Consider Debugging: Use sparingly to maintain query clarity

Ephemeral models are powerful for code organization but should be used judiciously. Consider views or tables for more complex or widely-used transformations.

Last updated

Was this helpful?