Materializations (view, table, incremental, ephemeral) in dbt - Step-by-Step Execution

Concept Flow - Materializations (view, table, incremental, ephemeral)

Start dbt model run

↓

Check materialization type

↓

View

↓

Create/View

↓

Model data ready for next step

dbt runs models by choosing a materialization type, then creates or updates data accordingly.

Execution Sample

dbt

model.sql:
-- materialization: incremental
select * from source_table
where updated_at > (select max(updated_at) from {{ this }})

This incremental model adds only new or updated rows since last run.

Execution Table

Step	Materialization Type	Action	Data Output	Notes
1	View	Create or replace SQL view	Virtual table with fresh data	No data stored, always fresh
2	Table	Create or replace physical table	Full data snapshot stored	Data stored on disk, refreshed fully
3	Incremental	Append or update rows in table	Only new/changed rows added	Efficient for large datasets
4	Ephemeral	No table created, used in SQL compilation	Temporary CTEs in queries	No physical table, fast for small logic
5	End	Model materialized	Data ready for downstream use	Process complete

💡 All materializations complete, data ready for next steps

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	After Step 4	Final
materialization_type	None	View	Table	Incremental	Ephemeral	Set per model
data_storage	None	Virtual (view)	Physical table	Updated table	None (CTE)	Depends on materialization
data_freshness	Unknown	Always fresh	Snapshot at run	Partial update	Depends on query	Ready for use

Key Moments - 3 Insights

Why does an incremental model not recreate the whole table every run?

Because incremental materialization appends or updates only new or changed rows, as shown in execution_table step 3, making it efficient for large data.

What happens to data in an ephemeral materialization?

No physical table is created; ephemeral models are compiled as CTEs inside other queries, so data exists only during query execution (execution_table step 4).

How is a view different from a table in dbt materializations?

A view is a virtual table that runs the query fresh each time (step 1), while a table stores data physically and is refreshed fully on each run (step 2).

Visual Quiz - 3 Questions

Look at the execution_table, what action does the incremental materialization perform at step 3?

A Create or replace SQL view

B Append or update rows in table

C Create or replace physical table

D Use temporary CTEs in queries

💡 Hint

Check execution_table row with Step 3 under 'Action' column

According to variable_tracker, what is the data freshness state after Step 2 (Table materialization)?

A Always fresh

B Partial update

C Snapshot at run

D Depends on query

💡 Hint

Look at 'data_freshness' row, column 'After Step 2'

If you want no physical table created and only temporary logic in queries, which materialization should you choose?

A Ephemeral

B Incremental

C View

D Table

💡 Hint

Refer to execution_table step 4 and variable_tracker 'data_storage' after Step 4

Concept Snapshot

dbt materializations control how models store data:
- view: virtual table, always fresh
- table: full physical table, replaced each run
- incremental: update/append rows, efficient for big data
- ephemeral: no table, used as CTEs in queries
Choose based on data size and freshness needs.

Full Transcript

In dbt, materializations decide how your data models are stored and updated. Views create virtual tables that run fresh queries every time. Tables store full snapshots of data and replace them fully on each run. Incremental materializations add or update only new or changed rows, saving time on large datasets. Ephemeral models don't create tables but compile as temporary query parts (CTEs) inside other models. This flow helps dbt efficiently manage data transformations depending on your needs.

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