# 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

{% hint style="info" %}
Ephemeral models are ideal for lightweight, reusable transformations that don't need to be queried directly.
{% endhint %}

***

#### Working with Ephemeral Models

Let's understand ephemeral models through a simple example:

```sql
-- 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**

```sql
-- 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**

<table><thead><tr><th width="338">Advantages</th><th>Limitations</th></tr></thead><tbody><tr><td>🧹 Reduces database clutter</td><td>🚫 Can't query directly</td></tr><tr><td>♻️ Enables code reuse</td><td>⚠️ No model contracts support</td></tr><tr><td>📦 No storage overhead</td><td>🔍 Harder to debug</td></tr><tr><td>🔄 Flexible logic updates</td><td>🛠️ Limited operational use</td></tr></tbody></table>

**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

{% hint style="info" %}
Ephemeral models are powerful for code organization but should be used judiciously. Consider views or tables for more complex or widely-used transformations.
{% endhint %}