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


---

# Agent Instructions: Querying This Documentation

If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.

Perform an HTTP GET request on the current page URL with the `ask` query parameter:

```
GET https://docs.paradime.io/app-help/concepts/dbt-fundamentals/model-materializations/ephemeral-materialization.md?ask=<question>
```

The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.

Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.