dbt Materializations: View, Table, Incremental, Ephemeral

Your models work in dev; prod is where materialization choices either save your team hours or light up pager duty. In dbt, materialization defines where and how a model stores results: view, table, incremental, or ephemeral. Pick based on cost at build time vs cost at read time, not preference. Views are cheap to build and expensive to read. Tables are the reverse. Incremental splits work across runs but adds failure modes. Ephemeral keeps the database clean but can make compiled SQL hard to debug. Below is a decision framework, concrete configs, the cost math, and how to switch a live model safely. If you only need the defaults: stage as views, heavy intermediates as tables, big facts as incremental with a solid unique key, and ephemeral only for tiny helpers.

Pick by cost: build-time vs read-time

Every type of materialization trades compute between when you build and when readers query. Start with this rule: if a model is read many times for each build, pay the build once (table or incremental). If it’s read rarely or changes constantly, pay on demand (view).

Materialization	Build-time cost	Read-time cost	Operational risk
view	low	high (recomputes each query)	low
table	high (full rebuild)	low	low
incremental	medium (only new/changed)	low	medium
ephemeral	n/a (inlined)	varies (merged into parents)	debuggability risk

Configure materialization per model in SQL or YAML:

-- models/int_orders.sql
{{ config(materialized='table', cluster_by=['order_date']) }}
select * from {{ ref('stg_orders') }};

# models/schema.yml
models:
  - name: int_orders
    config:
      materialized: table
      tags: ['intermediate']

Cost math: measure bytes scanned and duration for both builds and reads. On Snowflake, query ACCOUNT_USAGE. On BigQuery, check INFORMATION_SCHEMA.JOBS. Sum: (build bytes × build frequency) vs (view bytes per read × reads). If you need a deeper repo-level audit, we’ve documented proven fixes in why your dbt run is slow and offer a targeted dbt repo performance service.

When a view beats a table (and when it doesn’t)

Views win when upstream updates constantly and consumers don’t mind paying per query. Typical examples: light renames in staging, tiny reference sets, or fast filters over clustered base tables. A view over a partitioned source lets the database prune aggressively; you don’t need to store a copy.

Tables win when the SELECT is heavy—joins, window functions, or large re-aggregations—and the result is read many times. Recompute once, store it, and shift cost from every reader to the build.

Measure it. Example (Snowflake):

-- Reads per day and avg bytes scanned for a model's view
select query_text, count(*) reads, avg(bytes_scanned) avg_bytes
from snowflake.account_usage.query_history
where query_text ilike '%from my_schema.my_view%'
  and start_time > dateadd(day, -7, current_timestamp())
  and statement_type = 'SELECT'
group by 1;

If avg bytes per read times daily reads dwarfs a single build, switch to a table. If your warehouse bills mostly on read scans, see practical knobs in Snowflake cost optimization.

Incremental that survives backfills and schema drift

Incremental models reduce build cost by processing only new or changed rows. They also introduce pitfalls on week two: late-arriving data, soft-deletes, upstream schema changes, and backfills. Start with a strong unique key and a deterministic is_incremental() filter. Prefer MERGE over INSERT-only if your adapter supports it.

-- models/fct_orders.sql
{{ config(
    materialized='incremental',
    unique_key='order_id',
    on_schema_change='sync_all_columns',
    incremental_strategy='merge',
    cluster_by=['order_date']
) }}

select /* base */ *
from {{ ref('int_orders') }}
{% if is_incremental() %}
  where updated_at > (select coalesce(max(updated_at), '1900-01-01') from {{ this }})
{% endif %};

Failure modes we see most: missing unique keys cause duplicates; filters key off load time instead of business updated_at; backfills bypass the window and never land. For deeper patterns—change data capture windows, soft-delete reconciliation, and incremental models on partitioned sources—use our field notes in dbt Incremental Models guide. If the dataset is truly append-only, incremental materialization is a great long-term strategy; for mixed updates, define the update logic explicitly.

Ephemeral: fewer objects, harder debugging

Ephemeral models compile into the parent query as a common table expression. The upside: no extra database object, no grants, no schema clutter. The downside: the compiled SQL can explode into dozens of nested CTEs, making failures hard to localize and logs hard to read. Very deep ephemeral chains also hit statement length and optimizer limits on some databases.

Use ephemeral for small helpers reused in a couple of places, then promote to a view or table when debugging or read cost hurts. A simple switch in dev can help:

-- models/_helpers/stg_dates.sql
{% if target.name == 'dev' %}
  {{ config(materialized='view') }}
{% else %}
  {{ config(materialized='ephemeral') }}
{% endif %}

select date_key, fiscal_week from {{ ref('raw_dates') }};

When a failure occurs in a large compiled statement, open target/compiled to read the flattened SQL. If your transformation includes hierarchical and recursive queries in SQL, avoid nesting them ephemerally—materialize at least once for clarity.

Change materialization safely in production

Question we get: during a rebuild, can users access old data? With dbt’s table materialization, the build creates a new table, then swaps names; readers see the old version until the swap, minimizing downtime. On Snowflake, COPY GRANTS preserves permissions; set copy_grants: true. For views, CREATE OR REPLACE VIEW is atomic on most engines.

1. Add the new config and ensure grants are defined (YAML or hooks).
2. Build in a lower environment; validate row counts and schema diff.
3. In prod, run dbt run -s my_model. dbt will create my_model__dbt_tmp, drop old, then rename.
4. For critical models, gate with CI and contracts; see dbt CI/CD that catches issues.

-- Optional manual swap pattern
create table my_schema.my_model__new as select * from my_schema.my_model_view;
-- validate ...
alter table my_schema.my_model swap with my_schema.my_model__new; -- atomic

Risks: adapters differ in atomicity; some engines can’t swap across schemas; grants can reset if copy_grants is not available. Plan for rollbacks by retaining the prior relation for a short window.

Layer-by-layer defaults most teams should start from

These defaults bias for simplicity first, then scale. Adjust as your read/write ratio becomes clear.

Layer	Default	Notes
staging	view	Light renames, casting, source contracts. Fast to update.
intermediate	table	Heavy joins/windowing. Use cluster_by or partitioning.
marts — dims	table	Recomputed daily; small enough to rebuild fully.
marts — facts	incremental	Append/merge with a strong unique key.
helpers	ephemeral	Only if small; promote if debugging hurts.

For Snowflake, consider transient for intermediates; on BigQuery, define partition_by and cluster_by. Redshift may benefit from sort/dist keys and, occasionally, a database index on operational marts. Keep database schema grants at the schema level where possible to avoid churn. If you need end-to-end setup—from warehouse to dashboards—our Business Health Reporting service covers the full path.

Materialized views, custom materializations, and streaming

Materialized view: some adapters support it directly; others require a custom macro. Database constraints apply (limited SQL, no non-deterministic functions, automatic refresh rules). If your engine’s MV aligns with your transformation, it can be a low-maintenance cache for frequently filtered aggregations.

-- Example custom materialization macro (simplified)
{% materialization materialized_view, default='view' %}
  {%- set target_relation = this -%}
  {{ run_query("create or replace materialized view " ~ target_relation ~
               " copy grants as select * from " ~ ref('int_orders')) }}
  {{ return({'relations': [target_relation]}) }}
{% endmaterialization %}

Custom behavior lives in Jinja macros; the adapter plumbing is in Python under the hood. This is one of several types of materializations you can extend. To pick the right type of materialization, consider refresh semantics and grants—an MV is still a managed database object.

Streaming: dbt is a batch-first data build tool. For streaming, land events with your ingestion tool, then run micro-batch incremental models every few minutes via Airflow or dbt Cloud. Late data and upserts make incremental materializations essential. For ETL clarity, keep extract/load separate and let dbt handle the data transformation models.

FAQ speed-run: materializations in dbt define how a dbt model is stored; yes, you can configure them per model; and ephemeral models are compiled CTEs, not stored relations. For a broader warehouse view, browse our dbt articles and warehousing notes.

---

Next step: pick one noisy model, measure its build vs read cost, and adjust its materialization. If you want a second set of eyes—or to migrate to dbt Cloud cleanly—start with our dbt Cloud migration help or contact Vertex.