Materializations (view, table, incremental, ephemeral) in dbt

Introduction

Materializations decide how dbt saves your data models. They help you choose if data is saved as a table, a view, or handled differently for faster or flexible use.

When you want to save a model as a permanent table for fast queries.

When you want a lightweight view that always shows fresh data.

When you want to update only new or changed data to save time.

When you want to use a model only inside other models without saving it.

Syntax

dbt

materialized: [view|table|incremental|ephemeral]

Specify materialization in the model's config block or in the dbt_project.yml file.

Each materialization affects performance and storage differently.

Examples

This saves the model as a physical table in the database.

dbt

config(materialized='table')

This creates a view that runs the query fresh each time.

dbt

config(materialized='view')

This updates only new or changed rows, saving time on large datasets.

dbt

config(materialized='incremental')

This does not create a table or view but inlines the SQL in dependent models.

dbt

config(materialized='ephemeral')

Sample Program

This example shows how to set each materialization type in dbt models. The incremental model updates only new data. The ephemeral model is used inside another model without creating a table or view.

dbt

/* Example dbt model using different materializations */

-- models/my_table_model.sql
{{ config(materialized='table') }}

select id, name from source_data

-- models/my_view_model.sql
{{ config(materialized='view') }}

select id, name from source_data

-- models/my_incremental_model.sql
{{ config(materialized='incremental', unique_key='id') }}

select id, name from source_data

{% if is_incremental() %}
  where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

-- models/my_ephemeral_model.sql
{{ config(materialized='ephemeral') }}

select id, name from source_data where active = true

-- models/using_ephemeral.sql
select * from {{ ref('my_ephemeral_model') }}

Important Notes

Incremental models need a unique key to identify new or changed rows.

Ephemeral models are useful for reusable SQL snippets without extra storage.

Views always show the latest data but can be slower than tables for big data.

Summary

Materializations control how dbt saves or uses your data models.

Choose 'table' for permanent storage, 'view' for fresh queries, 'incremental' for efficient updates, and 'ephemeral' for inline SQL.

Each type helps balance speed, storage, and freshness based on your needs.