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

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

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

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)

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.

Practice

1. Which dbt materialization creates a permanent table in the database that stores data physically?

easy

A. table

B. view

C. incremental

D. ephemeral

Solution

1. Step 1: Understand the purpose of 'table' materialization

   The 'table' materialization creates a physical table in the database that stores data permanently.

2. Step 2: Compare with other materializations

   'view' creates a virtual table, 'incremental' updates existing tables efficiently, and 'ephemeral' runs inline SQL without creating tables.

3. Final Answer:

   table -> Option A

4. Quick Check:

   Permanent storage = table [OK]

Hint: Permanent data storage means 'table' materialization [OK]

Common Mistakes:

• Confusing 'view' with 'table' as both represent data
• Thinking 'incremental' creates a full new table every time
• Assuming 'ephemeral' creates physical tables

2. Which of the following is the correct syntax to specify an incremental materialization in a dbt model's config block?

easy

A. config(materialization = 'incremental')

B. config(materialized = 'incremental')

C. materialized('incremental')

D. set materialized = incremental

Solution

1. Step 1: Recall dbt config syntax for materialization

   dbt uses config() with the keyword 'materialized' to set materialization type.

2. Step 2: Identify the correct keyword and format

   The correct syntax is config(materialized = 'incremental'). Other options use wrong keywords or syntax.

3. Final Answer:

   config(materialized = 'incremental') -> Option B

4. Quick Check:

   Correct keyword is 'materialized' inside config() [OK]

Hint: Use config(materialized = 'type') syntax for materializations [OK]

Common Mistakes:

• Using 'materialization' instead of 'materialized'
• Trying to call materialized as a function
• Using SQL-like SET syntax instead of config()

3. Given this dbt model config and SQL snippet:

-- model.sql
{{ config(materialized='incremental', unique_key='id') }}
select id, value from source_table
{% if is_incremental() %}
  where updated_at > (select max(updated_at) from {{ this }})
{% endif %}

What happens when you run this model multiple times?

medium

A. The model rebuilds the entire table every time

B. The model creates a view that always shows fresh data

C. The model appends only new or updated rows based on 'updated_at'

D. The model runs inline SQL without creating a table

Solution

1. Step 1: Understand incremental materialization with unique_key

   The model uses incremental materialization with a unique key 'id' to update data efficiently.

2. Step 2: Analyze the is_incremental() condition

   When running incrementally, it filters rows where 'updated_at' is newer than the max in the existing table, appending only new or updated rows.

3. Final Answer:

   The model appends only new or updated rows based on 'updated_at' -> Option C

4. Quick Check:

   Incremental + filter = append updates [OK]

Hint: Incremental with is_incremental() filters new data only [OK]

Common Mistakes:

• Thinking incremental rebuilds full table every run
• Confusing view materialization with incremental
• Ignoring the is_incremental() condition

4. You wrote this dbt model:

{{ config(materialized='ephemeral') }}
select * from source_table

But when you run dbt, you get an error saying the model is not found. What is the likely cause?

medium

A. Ephemeral models do not create tables or views, so they cannot be run directly

B. The config syntax for ephemeral is incorrect

C. Ephemeral models require a unique_key to run

D. You must specify incremental materialization for ephemeral models

Solution

1. Step 1: Recall what ephemeral materialization does

   Ephemeral models do not create tables or views; their SQL is inlined into dependent models.

2. Step 2: Understand why the error occurs

   Since ephemeral models don't create database objects, running them directly causes a 'model not found' error.

3. Final Answer:

   Ephemeral models do not create tables or views, so they cannot be run directly -> Option A

4. Quick Check:

   Ephemeral = inline SQL, no table/view created [OK]

Hint: Ephemeral models can't be run alone; they inline SQL [OK]

Common Mistakes:

• Trying to run ephemeral models directly
• Assuming ephemeral needs unique_key
• Confusing ephemeral with incremental

5. You want to build a dbt model that:
- Stores data permanently
- Updates only new rows efficiently
- Avoids rebuilding the entire dataset each run

Which materialization should you choose and why?

hard

A. Use 'table' materialization because it stores data permanently and rebuilds fully each run

B. Use 'ephemeral' materialization because it runs inline SQL without storage

C. Use 'view' materialization because it always shows fresh data without storage

D. Use 'incremental' materialization because it stores data permanently and updates only new rows

Solution

1. Step 1: Identify permanent storage requirement

   Both 'table' and 'incremental' materializations store data permanently.

2. Step 2: Consider update efficiency

   'Table' rebuilds fully each run, while 'incremental' updates only new or changed rows efficiently.

3. Step 3: Match requirements

   Since you want to avoid full rebuilds and update only new rows, 'incremental' fits best.

4. Final Answer:

   Use 'incremental' materialization because it stores data permanently and updates only new rows -> Option D

5. Quick Check:

   Permanent + efficient updates = incremental [OK]

Hint: Incremental = permanent storage + efficient updates [OK]

Common Mistakes:

• Choosing 'table' and expecting incremental updates
• Picking 'view' which does not store data permanently
• Confusing ephemeral with storage options