In this lesson, you’ll learn:

  • What materialization is and why it matters in dbt.
  • The different types of built-in materialization strategies (view, table, incremental, ephemeral, and materialized view).
  • How to configure materialization in dbt using dbt_project.yml, model files, and properties files.
  • Best practices for selecting the right materialization strategy based on your data volume, update frequency, and performance needs.

By the end of this lesson, you’ll have a solid understanding of how dbt materialization works and how to apply it effectively in your projects. Make sure you complete the previous lesson Models in dbt before starting this one to maximize your understanding and learning experience.

Model Materialization Strategies

Materialization defines how dbt stores the results of your models in the data warehouse after running them. dbt provides five built-in materialization types, each with different benefits and trade-offs.

  • Definition: dbt recreates the model as a view on each run using a CREATE VIEW AS statement.
  • Pros
    • No additional storage is required.
    • Views always reflect the latest records from the source data.
  • Cons
    • Slow query performance for complex transformations or when stacking multiple views.
  • Definition: dbt rebuilds the model as a table on each run using a CREATE TABLE AS statement.
  • Pros
    •  Provide fast query performance.
  • Cons
    • Rebuilding tables can be time-consuming, especially for complex transformations.
    • New records in the source data do not update automatically in the table.
  • Definition: Enables dbt to insert or update records in a table only since the last time the model was executed.
  • Pros
    • Faster build times by processing only new records instead of the entire dataset.
  • Cons
    • Requires additional configuration and is considered an advanced dbt feature.

 

  • Definition: Models are not stored in the database. Instead, dbt inlines their code into dependent models using a Common Table Expression (CTE).
  • Pros
    • Supports reusable logic while keeping the data warehouse clean by reducing clutter.
  • Cons
    • Cannot be queried directly or referenced in dbt run-operation macros.
    • Overuse can make debugging queries difficult.
    • Does not support model contracts.
  • Definition: Enables the creation and management of materialized views in the target database. These views combine the benefits of tables and views, making them useful for incremental-style use cases.
  • Pros
    • Faster query performance like tables, while maintaining data freshness like views.
    • Automatically refreshes on a schedule (depending on the database), unlike incremental models that require manual dbt runs.
  • Cons
    • Limited configuration options, as support varies by database platform.
    • Not universally supported across all databases.

Configure Materialization​

 By default, dbt materializes models as views. However, you can customize this behavior in three ways:

1. Configuration in dbt_project.yml

You can define materialization settings in the dbt_project.yml file. The example below specifies that:

  • Models undermodels/orders/ will be materialized as views.
  • Models undermodels/consumers/ will be materialized as tables.
dbt_project.yml
Copy 
models:
  demo_project:
    orders:
      +materialized: view
    consumers:
      +materialized: table

2. Configuration in the Model File

Materialization can also be set directly within a model’s .sql file using the config() macro. The example below materializes the completed_orders model as a table:

Copy 
{{ config(materialized='table') }} 
SELECT 
    order_id,
    customer_id,
    order_date,
    total_amount
FROM raw.orders
WHERE 
    status = 'completed'

3. Configuration in the properties.yml File

Materialization settings can also be defined in the model’s properties.yml file. The example below materializes not_completed_orders as a view:

properties.yml
Copy 
version: 2

models:
  - name: not_completed_orders
    config:
      materialized: view

Conclusion

Understanding model materialization strategies is key to optimizing query performance and managing storage efficiently.

  • Views work well for frequently updated data but may slow down complex queries.
  • Tables improve performance but require full rebuilds.
  • Incremental models balance performance and freshness by processing only new data.
  • Ephemeral models keep the warehouse clean but cannot be queried directly.
  • Materialized views offer a hybrid approach but have database-specific limitations.

Choosing the right materialization strategy depends on your data volume, update frequency, and performance requirements.

PreviousNext