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.