> ## Documentation Index
> Fetch the complete documentation index at: https://docs.paradime.io/llms.txt
> Use this file to discover all available pages before exploring further.

# Ephemeral Materialization

> Describes dbt's ephemeral materialization, which integrates models as CTEs in dependent models, discussing advantages, disadvantages, and recommended use cases.

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

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

***

#### Working with Ephemeral Models

Let's understand ephemeral models through a simple example:

```sql theme={"system"}
-- 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 theme={"system"}
-- 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

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


## Related topics

- [Model Materializations](/guides/dbt-fundamentals/model-materializations/index.md)
- [Table Materialization](/guides/dbt-fundamentals/model-materializations/table-materialization.md)
- [View​ Materialization](/guides/dbt-fundamentals/model-materializations/view-materialization.md)
- [dbt™ fundamentals](/guides/dbt-fundamentals/index.md)
- [dbt™ Model Checks](/integrations/pre-commit/dbt-tm-checkpoint-hooks/dbt-tm-model-checks.md)