In dbt, what does the ephemeral materialization do to a model's compiled SQL?

## What it is `ephemeral` is the fourth built-in dbt materialization, alongside `view`, `table` and `incremental`. It is the odd one out: the other three produce a database object, and ephemeral deliberately does not. ```sql -- models/intermediate/int_active_customers.sql {{ config(materialized='ephemeral') }} select customer_id, region from {{ ref('stg_customers') }} where status = 'active' ``` Running `dbt run` on this model creates nothing. The model still exists in the DAG, still has upstream dependencies, and still shows in the docs — but there is no relation with that name in the warehouse. ## How dbt compiles it When a downstream model does `{{ ref('int_active_customers') }}`, dbt does not resolve that to a schema-qualified table name. It takes the ephemeral model's compiled SQL, wraps it in a CTE, prepends it to the consumer's query, and rewrites the reference to the CTE alias: ```sql with __dbt__cte__int_active_customers as ( select customer_id, region from analytics.stg_customers where status = 'active' ) select region, count(*) from __dbt__cte__int_active_customers group by 1 ``` Chains compose: an ephemeral model that refs another ephemeral model produces two stacked CTEs. Everything is resolved at compile time and sent to the warehouse as a single statement. ## What you gain - **No warehouse clutter.** Interim logic that exists purely to be reused does not become an object analysts have to ignore, or that a BI tool auto-discovers. - **DRY logic in the DAG.** The definition lives in one versioned file with lineage, rather than being copy-pasted into three models. - **No build step.** There is nothing to create, so it adds nothing to run time on its own. ## What you give up - **You cannot query it.** No `select * from int_active_customers` in a SQL client, no BI access, no ad-hoc inspection. This is the most common source of frustration. - **Debugging is indirect.** To see what the model produces you must open the compiled SQL of a downstream consumer in `target/compiled/` and run the CTE by hand. - **Work is repeated per consumer.** If three models ref it, the CTE is inlined three times and executed three times. For heavy logic — a large aggregation, a window over a big scan — a view or table computes it once and is usually cheaper overall, and a table also lets the warehouse store and prune the result. - **Compiled SQL grows.** Deep chains of ephemeral models produce long, hard-to-read statements, and query plans become correspondingly harder to reason about. One practical consequence worth knowing: because there is no relation, an ephemeral model has no `{{ this }}` and cannot be selected as a build target the way a view or table can — running it alone does nothing observable. ## When to use it Good fits are thin, cheap, reusable filters and lookups: an active-customers filter, a currency conversion join, a small mapping used by two marts. The test is whether anyone would ever want to query it directly and whether the logic is cheap enough that re-executing it per consumer is irrelevant. Poor fits are anything expensive, anything reused by many models, and anything an analyst might want to inspect while debugging a number. In those cases a `view` gives you the same DRY reuse plus queryability, at essentially the same build cost. When people describe ephemeral as "a view with no object," that is roughly right in spirit — but a view is computed once per read from one definition, while an ephemeral CTE is pasted into and executed inside each consumer's query. ## Where it sits among the four Think of the four built-ins as answering "what should exist?": a view stores the query, a table stores the rows, an incremental model stores rows and adds new ones over time, and an ephemeral model stores nothing at all and lives only inside its consumers. Ephemeral is the lightest and the least observable, which is exactly the trade you are making.