Materializations (view, table, incremental, ephemeral) in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

❓ Predict Output

intermediate

Output of a view materialization in dbt

Given the following dbt model configured as a view, what will be the result when querying the model?

-- models/my_view.sql
{{ config(materialized='view') }}

select
  user_id,
  count(*) as total_orders
from {{ ref('orders') }}
group by user_id

Assume the orders table has 3 users with 2, 3, and 5 orders respectively.

A No table or view is created; the model runs only during compilation and returns no data.

B A physical table is created with the aggregated data, so querying my_view returns 0 rows until manually refreshed.

C An incremental table is created that only updates new orders, so querying my_view returns partial data until full refresh.

D A view is created that runs the query fresh each time, so querying my_view returns 3 rows with counts 2, 3, and 5.

💡 Hint

Think about what a view materialization does in dbt.

A view materialization creates a database view that runs the query fresh every time you query it. So the data is always current and reflects the underlying table.

❓ data_output

intermediate

Rows in a table materialization after multiple runs

Consider a dbt model configured as a table materialization:

-- models/my_table.sql
{{ config(materialized='table') }}

select * from {{ ref('customers') }}

If the customers table has 100 rows, and you run dbt run twice, how many rows will my_table contain after the second run?

A 100 rows, because the table is dropped and recreated each run.

B 200 rows, because the data is appended each run.

C 0 rows, because the table is not created automatically.

D 100 rows, but only if you run dbt seed first.

💡 Hint

Think about how dbt handles table materializations on multiple runs.

dbt drops and recreates tables on each run for table materializations, so the table always contains the latest data, not appended data.

🔧 Debug

advanced

Why does incremental model not update all rows?

You have this incremental model:

-- models/incremental_orders.sql
{{ config(materialized='incremental', unique_key='order_id') }}

select * from {{ ref('orders') }}
{% if is_incremental() %}
  where order_date > (select max(order_date) from {{ this }})
{% endif %}

After running dbt multiple times, you notice some orders with earlier dates are missing in incremental_orders. Why?

A Because the incremental filter only adds rows with order_date greater than max in the table, older rows are never added or updated.

B Because the model is configured as a view, incremental logic is ignored.

C Because the unique_key is wrong, dbt cannot merge rows correctly.

D Because dbt does not support incremental models with date filters.

💡 Hint

Look at the filter condition inside is_incremental().

The incremental filter only adds rows with order_date greater than the max in the existing table. So older rows are never added or updated, causing missing data.

🧠 Conceptual

advanced

Purpose of ephemeral materialization in dbt

What is the main purpose of using ephemeral materialization in dbt models?

A To create temporary tables in the database that persist only during the session.

B To inline the model SQL into downstream models without creating any physical table or view.

C To create views that refresh automatically on each query.

D To create incremental tables that update only new data.

💡 Hint

Think about how ephemeral models behave in dbt compilation.

Ephemeral models do not create any physical object in the database. Instead, their SQL is inlined into downstream models during compilation, improving performance and avoiding extra tables.

🚀 Application

expert

Choosing materialization for a large slowly changing dataset

You have a large dataset of customer transactions that updates daily with new rows and occasional corrections to existing rows. You want to build a dbt model that efficiently updates only new and changed rows without rebuilding the entire dataset each run. Which materialization should you choose and why?

A Use <code>table</code> materialization because it rebuilds the entire dataset ensuring correctness.

B Use <code>view</code> materialization because it always shows the latest data without storage cost.

C Use <code>incremental</code> materialization with a unique key and logic to update changed rows efficiently.

D Use <code>ephemeral</code> materialization to inline the logic and avoid physical tables.

💡 Hint

Consider the trade-offs between full rebuilds and incremental updates for large datasets.

Incremental materialization allows updating only new and changed rows using a unique key, which is efficient for large datasets with daily updates and corrections.

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