Materializations strategy in dbt

Introduction

Materializations decide how dbt saves your data models. They help you control when and where data is stored for faster analysis.

When you want to save a table permanently for repeated use.

When you need a temporary table for quick calculations during a run.

When you want to create a view that always shows fresh data without storing it.

When you want to incrementally add new data without rebuilding the whole table.

When you want to test or debug your models without saving results.

Syntax

dbt

materialized: [view|table|incremental|ephemeral]

materialized is a key in your model's config block.

You choose one of the options: view, table, incremental, or ephemeral.

Examples

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

dbt

models/my_model.sql

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

select * from source_table

This creates a view that shows live data without storing it separately.

dbt

models/my_model.sql

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

select * from source_table

This adds only new or changed rows to the existing table, saving time.

dbt

models/my_model.sql

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

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

This does not create a table or view but inlines the SQL into downstream models.

dbt

models/my_model.sql

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

select * from source_table

Sample Program

This model creates a table with only active customers for faster queries later.

dbt

-- models/example_model.sql

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

select id, name from raw.customers where active = true

Important Notes

Choosing the right materialization affects performance and storage costs.

Use incremental for large datasets that update often.

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

Summary

Materializations control how dbt saves or shows your data models.

Common types are table, view, incremental, and ephemeral.

Pick the type based on your data size, update frequency, and performance needs.