Materializations strategy in dbt - Step-by-Step Execution

Concept Flow - Materializations strategy

Start: Define model

↓

Choose materialization type

↓

Table

↓

Create physical table

↓

View

↓

Create virtual view

↓

Incremental

↓

Append or update data

↓

Ephemeral

↓

Inline CTE in queries

↓

Run dbt model

↓

Materialization applied

↓

Data ready for use

This flow shows how dbt chooses a materialization strategy for a model and applies it to create tables, views, or inline queries.

Execution Sample

dbt

model.sql:
-- materialized as table
select * from source_table

-- materialized as view
{{ config(materialized='view') }}
select * from source_table

-- materialized as incremental
{{ config(materialized='incremental') }}
select * from source_table

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

This code shows four dbt models with different materialization strategies: table, view, incremental, and ephemeral.

Execution Table

Step	Model Type	Action	Result
1	table	dbt runs model.sql	Creates physical table with full data
2	view	dbt runs model.sql with view config	Creates virtual view referencing source_table
3	incremental	dbt runs model.sql with incremental config	Appends or updates only new/changed data in table
4	ephemeral	dbt runs model.sql with ephemeral config	Inlines SQL as CTE in dependent models, no table/view created
5	all	dbt finishes run	Models materialized as per config, data ready for queries

💡 All models processed with their materialization strategy; dbt run completes successfully

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
model_type	undefined	table	view	incremental	ephemeral	all processed
physical_object	none	table created	view created	table updated incrementally	no object created	all materialized
data_state	source data	full data copied	virtual view points to source	partial data updated	data inlined in queries	ready for use

Key Moments - 3 Insights

Why does the ephemeral materialization not create a table or view?

Because ephemeral models are inlined as CTEs inside dependent models, no physical object is created. See execution_table row 4.

How does incremental materialization differ from table materialization?

Incremental only adds or updates new data instead of recreating the whole table, making it faster. See execution_table rows 1 and 3.

What happens if you don't specify a materialization in dbt?

By default, dbt materializes models as tables, creating a physical table with full data. See execution_table row 1.

Visual Quiz - 3 Questions

Look at the execution_table, what does dbt do at Step 2 for a view model?

A Creates a physical table with data

B Appends new data to an existing table

C Creates a virtual view referencing source_table

D Inlines SQL as a CTE in dependent models

💡 Hint

Check execution_table row 2 under 'Result' column

At which step does dbt update only new or changed data instead of recreating the whole table?

A Step 3

B Step 2

C Step 1

D Step 4

💡 Hint

Look at execution_table row 3 describing incremental materialization

If you want no physical table or view created, which materialization should you choose?

A view

B ephemeral

C incremental

D table

💡 Hint

Refer to execution_table row 4 about ephemeral materialization

Concept Snapshot

dbt Materializations:
- table: creates physical table with full data
- view: creates virtual view
- incremental: updates/appends data
- ephemeral: inlines SQL, no table/view
Choose based on performance and use case
Run dbt to apply chosen materialization

Full Transcript

This visual execution shows how dbt applies different materialization strategies to models. First, you define your model SQL and choose a materialization type: table, view, incremental, or ephemeral. When dbt runs, it creates a physical table for 'table' models, a virtual view for 'view' models, updates only new or changed data for 'incremental' models, and inlines SQL as CTEs for 'ephemeral' models without creating physical objects. Variables like model_type and physical_object change step-by-step as dbt processes each model. Key points include understanding that ephemeral models do not create tables or views, incremental models update data efficiently, and the default is table materialization if none is specified. The quizzes test your understanding of these steps and outcomes. This helps you choose the right materialization for your data workflow.

Practice

1. What does the table materialization do in dbt?

easy

A. Runs the model only when data changes incrementally.

B. Creates a temporary view that disappears after the session ends.

C. Creates a physical table in the database that stores data permanently.

D. Does not create any database object but runs SQL inline.

Solution

1. Step 1: Understand table materialization

   The table materialization creates a real table in the database that stores the data physically.

2. Step 2: Compare with other materializations

   Unlike views or ephemeral models, tables persist data and improve query speed for large datasets.

3. Final Answer:

   Creates a physical table in the database that stores data permanently. -> Option C

4. Quick Check:

   table materialization = physical table [OK]

Hint: Tables store data physically; views do not [OK]

Common Mistakes:

• Confusing tables with views
• Thinking ephemeral models create tables
• Assuming incremental means full table creation

2. Which of the following is the correct syntax to set a model as incremental in dbt?

easy

A. materialized: 'incremental'

B. materialization: 'incremental'

C. materialized = incremental

D. materialize: incremental

Solution

1. Step 1: Recall dbt model config syntax

   In dbt, model materialization is set using materialized: 'type' inside the model config block.

2. Step 2: Identify correct syntax for incremental

   The correct key is materialized with a colon and the value in quotes, like 'incremental'.

3. Final Answer:

   materialized: 'incremental' -> Option A

4. Quick Check:

   Use materialized: 'incremental' for incremental models [OK]

Hint: Use 'materialized' key with colon and quotes [OK]

Common Mistakes:

• Using 'materialization' instead of 'materialized'
• Missing quotes around 'incremental'
• Using equals sign instead of colon

3. Given this dbt model config and SQL, what will be the result when run?

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

Model config:

materialized: 'incremental'

medium

A. The model will fully refresh the table every run.

B. The model will fail due to syntax error.

C. The model will create a view instead of a table.

D. The model will add only new rows with updated timestamps.

Solution

1. Step 1: Understand the incremental logic in SQL

   The SQL uses is_incremental() to run different queries: full select on first run, then only new rows after.

2. Step 2: Match with incremental materialization behavior

   With materialized: 'incremental', dbt appends new rows where updated_at is greater than the max in the existing table.

3. Final Answer:

   The model will add only new rows with updated timestamps. -> Option D

4. Quick Check:

   Incremental model adds new rows only [OK]

Hint: Incremental models append new data, not full refresh [OK]

Common Mistakes:

• Thinking incremental always refreshes full table
• Confusing view creation with incremental
• Ignoring the is_incremental() condition

4. You wrote this dbt model config:

materialized: incremental

What is the error and how to fix it?

medium

A. Wrong key name; fix: materialization: 'incremental'

B. Missing quotes around 'incremental'; fix: materialized: 'incremental'

C. Should use equals sign; fix: materialized = 'incremental'

D. No error; config is correct

Solution

1. Step 1: Identify syntax error in config

   The value incremental must be a string, so it needs quotes.

2. Step 2: Correct the syntax

   Change to materialized: 'incremental' to fix the error.

3. Final Answer:

   Missing quotes around 'incremental'; fix: materialized: 'incremental' -> Option B

4. Quick Check:

   Materialization values must be strings in quotes [OK]

Hint: Always quote materialization types [OK]

Common Mistakes:

• Forgetting quotes around materialization type
• Using wrong key 'materialization'
• Using equals sign instead of colon

5. You have a large dataset that updates daily with new and changed rows. You want fast query performance and to avoid rebuilding the entire table each time. Which materialization strategy should you choose in dbt?

hard

A. Use incremental materialization to add and update rows efficiently.

B. Use table materialization and rebuild daily.

C. Use view materialization for real-time data.

D. Use ephemeral materialization to avoid creating tables.

Solution

1. Step 1: Analyze dataset size and update pattern

   The dataset is large and updates daily with new and changed rows, so full rebuilds are costly.

2. Step 2: Match materialization to needs

   incremental materialization updates only new or changed rows, improving performance and avoiding full rebuilds.

3. Final Answer:

   Use incremental materialization to add and update rows efficiently. -> Option A

4. Quick Check:

   Incremental = efficient updates for large, changing data [OK]

Hint: Incremental suits large, frequently updated datasets [OK]

Common Mistakes:

• Choosing view which rebuilds every query
• Using table and rebuilding fully each time
• Using ephemeral which does not create tables