dbt materializations: view, table, incremental, ephemeral and materialized view

1.Materializations and where to set them

A dbt model is a select statement, and its materialization decides what that select becomes in the warehouse. A view stores only the query. A table stores the rows. The other built-in types change how a table gets filled, or whether anything is created at all. Picking one means trading build cost against query speed and freshness, and you will come back to that choice often as a project grows.

dbt ships five built-in materializations: table, view, incremental, ephemeral and materialized_view. Custom materializations can extend the list. A model with no materialized setting builds as a view, so a new project is all views until you configure something else.

You can set the materialized config in three places:

- under models: in dbt_project.yml, with a + prefix, for a whole folder - in the model's properties YAML, under config: - in a {{ config() }} block at the top of the model's SQL file

Folder settings cascade into subfolders, and the most specific scope wins. That lets you set a default for a directory and override it for one model. The full precedence rules are covered in [project configuration](lesson:1.6). The in-file block is handy when a model also needs adapter-specific performance settings, such as Redshift sort and dist keys:

{{ config(materialized='table', sort='timestamp', dist='user_id') }}

select *
from ...

Sources12

2.Views, tables and incremental models compared

dbt rebuilds a view on every run with create view as. The run is almost free because no data is processed, and the view always shows the latest source data. The cost moves to query time, because every query runs the logic again. That works for renaming and recasting columns. It gets slow when the view does heavy transformation or sits on top of other views.

dbt rebuilds a table on every run with create table as. Queries are fast because the rows are already computed. Compute costs much more than storage, so paying for the transformation once per job usually beats paying for it every time someone opens a dashboard. The catch is staleness. A table built hourly at 10:00 still shows 10:00 data at 10:59, because new source rows only appear after the next build.

An incremental model is still a table. After the first run, dbt transforms only new or changed rows and inserts or updates them in place. The first build is as slow as building a full table, but later builds are much faster. In exchange you take on extra configuration, and the table can drift from the source: changes to historical source rows are not picked up unless you reprocess them.

An incremental model needs three things:

- a filter that selects only new or updated rows - a conditional that applies the filter only on incremental runs - config that tells dbt to build incrementally

{{ this }} refers to the model's existing table in the warehouse, which provides the cutoff for the filter:

select * from orders

where
  updated_at > (select max(updated_at) from {{ this }})

unique_key tells dbt what to do when an incoming row has the same key as a row already in the table: update that row instead of adding a duplicate. Without it, incremental builds suit only append-only data. With it, they also handle records that change over time. Which SQL statement dbt uses to apply the changes is a separate decision, covered in [incremental strategies](lesson:1.11).

{{
    config(
        materialized='incremental',
        unique_key='order_id'
    )
}}

select ...

Sources134

3.Ephemeral models: logic without a warehouse object

An ephemeral model creates nothing in the database. dbt takes its SQL and inserts it as a common table expression into each model that refs it. The CTE name is always the model identifier with a __dbt__cte__ prefix. An alias changes the identifier, but the prefix stays.

Ephemeral models let you reuse logic without cluttering the warehouse, but they come with hard limits:

- You cannot select from one directly, so analysts and BI tools cannot query it. - Macros called with dbt run-operation cannot ref() an ephemeral node. - They do not support model contracts, so a model that needs an enforced shape (see [model contracts](lesson:2.1)) must use another materialization. - Overusing them makes queries harder to debug, because the logic only exists inside the models downstream.

Use ephemeral for very light transformations early in the DAG that feed only one or two downstream models and never need to be queried on their own. For anything outside that, a view keeps the logic available to query and inspect.

Sources1

4.Materialized views: tables the platform refreshes

A materialized_view gives you a table's query speed with a view's freshness. It serves the same use cases as an incremental model, but the database manages the incremental logic and refresh instead of dbt, usually on a schedule you configure. Choose it when an incremental model would do the job and you would rather the platform run the refreshes.

This changes what dbt run is for. For a table, an incremental model or a snapshot, a run is the only thing that updates the data. For a materialized view, a run is needed only when its SQL or configuration may have changed, so it acts like deploying a view. That assumes auto refresh is turned on and configured in the model. Most platforms support auto refresh. Postgres is the exception.

When the configuration does change, on_configuration_change decides what happens. It tells dbt to apply the change to the existing object where possible instead of recreating it. On dbt-postgres, for example, dbt can drop and create indexes without rebuilding the materialized view.

Support varies by platform. Materialized views have fewer configuration options than tables, and some platforms do not support them at all. dbt-snowflake is one of these: it uses Dynamic Tables instead.

Sources1

5.What happens when you change a model's materialization

Every materialization goes through the same drop-through life cycle when dbt builds it. Order matters when you switch a model from one type to another, because dbt checks the type before it considers any configuration changes:

1. If no model exists at the path, create it. 2. If a model exists but is a different type, drop it and create the new one. 3. If --full-refresh is passed, replace the model regardless of configuration changes or on_configuration_change. 4. If there are no configuration changes, perform the type's default action, such as refreshing a materialized view. 5. Otherwise, apply or skip the configuration changes according to on_configuration_change.

On most platforms, switching a model from view to table needs nothing special: the existing view is a different type, so dbt drops it and creates the table. BigQuery is the exception. After changing a model's materialization there, you may need dbt run --full-refresh to make sure dbt fully replaces the old relation.

Python models have fewer options. They support only table and incremental, and cannot be views or ephemeral. Writing them is covered in [Python models](lesson:1.8).

Sources5

6.Choosing materializations by layer

In practice you set materializations per folder in dbt_project.yml and override them in individual models only where needed, which keeps the config DRY. Each layer's materialization follows from who queries it and how fresh it has to be:

- Staging models are rarely queried by end users and must stay in sync with the source as building blocks, so they are views. View is already the default, but setting it explicitly makes the intent clear. - Intermediate models are not queried directly by end users and exist to simplify mart logic, which makes them good candidates for ephemeral. - Marts are queried directly and must be fast, and users can usually work with hourly or daily data, so marts are tables, or incremental once a full rebuild takes too long.

Ephemeral intermediates have a cost when something breaks: there is nothing to query. If you need to inspect intermediate results, build them as views in a custom schema with restricted permissions instead. Layering itself is covered in [clean DAG design](lesson:1.5).

models:
  jaffle_shop:
    staging:
      +materialized: view
    intermediate:
      +materialized:  ? 
    marts:
      +materialized: table

Sources2

Exam traps

Each one states something that sounds right. Open it to see what is actually true.

1. 1.On Snowflake you can configure a model with materialized='materialized_view'.Why is that wrong?

   dbt-snowflake does not support materialized views. It uses Dynamic Tables for the same purpose.

   Covered in Materialized views: tables the platform refreshes

2. 2.An ephemeral model can be queried like a lightweight view in the warehouse.Why is that wrong?

   An ephemeral model is never built in the database. It exists only as a CTE inside its downstream models, so you cannot select from it.

   Covered in Ephemeral models: logic without a warehouse object

3. 3.A table model picks up new source rows as they land, just like a view.Why is that wrong?

   A table holds the data as of its last build. New source rows appear only after the next dbt run.

   Covered in Views, tables and incremental models compared

4. 4.A Python model can be materialized as a view to save build cost.Why is that wrong?

   Python models support only table and incremental. They cannot be views or ephemeral.

   Covered in What happens when you change a model's materialization