Materializations (view, table, incremental, ephemeral) in dbt - Mini Project: Build & Apply

Materializations (view, table, incremental, ephemeral)

📖 Scenario: You are working on a data analytics project using dbt (data build tool). You want to create different types of models to understand how dbt materializes data in your warehouse.Materializations control how dbt builds your models: as views, tables, incremental tables, or ephemeral (temporary) tables.

🎯 Goal: Build four dbt models using different materializations: view, table, incremental, and ephemeral. Learn how to configure each materialization and see their outputs.

📋 What You'll Learn

Create a dbt model with view materialization

Create a dbt model with table materialization

Create a dbt model with incremental materialization

Create a dbt model with ephemeral materialization

Use simple SQL queries for each model

Print or log the materialization type in the final step

💡 Why This Matters

🌍 Real World

Data analysts and engineers use dbt materializations to control how data models are built and refreshed in data warehouses.

💼 Career

Understanding dbt materializations is essential for roles in data engineering, analytics engineering, and modern data stack development.

1

Create a dbt model with view materialization

Create a dbt model file named my_view.sql with a simple SQL query selecting id and name from a source table called raw.customers. Set the materialization to view using the config function at the top of the file.

dbt

-- Create a dbt model with view materialization
{{ config(materialized='view') }}

select id, name from raw.customers

Need a hint?

Use {{ config(materialized='view') }} at the top of your SQL file to set the materialization.

2

Create a dbt model with table materialization

Create a dbt model file named my_table.sql with a SQL query selecting id and email from raw.customers. Set the materialization to table using the config function at the top.

dbt

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

select id, name from raw.customers

-- Create a dbt model with table materialization
{{ config(materialized='table') }}

select id, email from raw.customers

Need a hint?

Use {{ config(materialized='table') }} at the top of your SQL file to set the materialization.

3

Create a dbt model with incremental materialization

Create a dbt model file named my_incremental.sql with a SQL query selecting id, created_at from raw.orders. Set the materialization to incremental using the config function. Add a where clause to select only rows where created_at is greater than the max created_at in the target table, using the is_incremental() function.

dbt

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

select id, name from raw.customers

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

select id, email from raw.customers

-- Create a dbt model with incremental materialization
{{ config(materialized='incremental') }}

select id, created_at from raw.orders
where true

Need a hint?

Use is_incremental() to filter new rows for incremental models.

4

Create a dbt model with ephemeral materialization and print materializations

Create a dbt model file named my_ephemeral.sql with a SQL query selecting id and status from raw.orders. Set the materialization to ephemeral using the config function. Then, print the materialization types of all four models as a Python dictionary named materializations.

dbt

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

select id, name from raw.customers

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

select id, email from raw.customers

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

select id, created_at from raw.orders
{% if is_incremental() %}
  where created_at > (select max(created_at) from {{ this }})
{% endif %}

-- Create a dbt model with ephemeral materialization
{{ config(materialized='ephemeral') }}

select id, status from raw.orders

# Print the materializations dictionary below
materializations = {
    'my_view': 'view',
    'my_table': 'table',
    'my_incremental': 'incremental',
    'my_ephemeral': 'ephemeral'
}
print(materializations)

Need a hint?

Use {{ config(materialized='ephemeral') }} at the top of your SQL file. Then create a Python dictionary with the model names and their materializations and print it.