Ephemeral Materialization

Ephemeral models are not directly constructed in the database. Instead, dbt integrates the code from an ephemeral model into its dependent models using a common table expression (CTE). While you can control the CTE's identifier using a model alias, dbt will always prefix the model identifier with __dbt__cte__.

Advantages:

• Code reusability: Allows for the creation of reusable logic.

• Database organization: Helps maintain a cleaner data warehouse by reducing clutter. (Consider using custom schemas to distribute your models across multiple schemas for further organization.)

Disadvantages:

• Limited direct access: You cannot query these models directly.

• Operational constraints: Operations (e.g., macros invoked using dbt run-operation) cannot ref() ephemeral nodes.

• Debugging challenges: Excessive use of ephemeral materialization can complicate query debugging.

• Contract incompatibility: Ephemeral materialization does not support model contracts.

Best Practices:

Ephemeral materialization is most suitable for:

• Lightweight transformations positioned early in your DAG.

• Models used by only one or two downstream models.

• Transformations that don't require direct querying.