How to choose between view, table and ephemeral in dbt

Every dbt model is a select. The materialization decides what dbt does with it: a view that runs the query each time someone reads it, a table that stores the result, or nothing at all, with the query pasted into the models that use it. Choosing well keeps runs short and dashboards fast.

Copy and paste

One default per folder, in dbt_project.yml:

models:
  shop:
    staging:
      +materialized: view
    intermediate:
      +materialized: ephemeral
    marts:
      +materialized: table

An exception in a single model:

{{ config(materialized='table') }}

The four materializations

	`view`	`table`	`ephemeral`	`incremental`
In the warehouse	a view	a table	nothing	a table
Each dbt run	redefines the view, in seconds	rebuilds the whole table	nothing to build	adds or updates new rows
Each query on it	runs the full query	reads stored data	not queryable	reads stored data
Good for	staging, light logic	marts read by BI tools, heavy joins	small reusable steps	large, growing fact tables

Ephemeral models

An ephemeral model never reaches the warehouse. dbt turns it into a CTE at the top of every model that uses it:

with __dbt__cte__int_orders_shipped as (
select * from "shop"."main"."stg_orders" where status = 'shipped'
) select sum(amount) as shipped_revenue from __dbt__cte__int_orders_shipped

That keeps the schema tidy, but you cannot query the step on its own when something looks wrong, and a step used by five models is computed five times. Keep ephemeral for small pieces used once or twice.

A simple default

Staging: view. One view per source table, with renaming and casting. Cheap to build, always current.
Intermediate steps: ephemeral or view. Ephemeral when they are small; view when you want to be able to query them.
Marts: table. They are read by dashboards and analysts, often many times a day. Reading stored data is faster than recomputing a chain of views.
Large facts: incremental, once a full rebuild takes too long; see how to create an incremental model.

A chain of views on views is the usual cause of slow dashboards: every query recomputes the whole chain. Turning the last view into a table fixes it.

On Snowflake

Tables are transient by default. dbt-snowflake creates transient tables unless you set transient: false. They have no Fail-safe and at most one day of Time Travel, which saves storage. Set +transient: false for tables you need to be able to recover.
Dynamic tables. materialized='dynamic_table' creates a Snowflake dynamic table that refreshes itself on a target lag, without a dbt run.

Tested with dbt Core 1.12 (DuckDB adapter); the Snowflake details come from dbt-snowflake 1.12.