Staging, intermediate, and marts pattern in dbt - Practice Problems & Coding Challenges

Challenge - 5 Problems

🧠 Conceptual

intermediate

Purpose of the Staging Layer in dbt

In the Staging, intermediate, and marts pattern used in dbt, what is the main purpose of the staging layer?

A To clean and standardize raw source data for easier use in later models

B To create final business reports and dashboards directly from raw data

C To aggregate data into summary tables for quick analysis

D To store backup copies of raw data without transformation

💡 Hint

Think about what you do first when you get messy data from different sources.

The staging layer is where raw data is cleaned and standardized. This makes it easier for intermediate and marts layers to work with consistent data.

❓ Predict Output

intermediate

Output of a dbt Model SQL Query in the Intermediate Layer

Given this dbt model SQL in the intermediate layer:

select
  user_id,
  count(order_id) as total_orders
from {{ ref('stg_orders') }}
group by user_id

What will be the output schema of this model?

dbt

select
  user_id,
  count(order_id) as total_orders
from {{ ref('stg_orders') }}
group by user_id

A Columns: user_id (int), total_orders (int) with one row per order

B Columns: order_id (int), total_orders (int) with one row per order

C Columns: user_id (int), total_orders (int) with one row per user

D Columns: user_id (int), order_id (int) with one row per order

💡 Hint

Look at the GROUP BY clause and the aggregation function.

The query groups rows by user_id and counts orders per user, so output has one row per user with total_orders count.

❓ data_output

advanced

Resulting DataFrame Shape After Running a Mart Model

Consider a mart model that joins the intermediate user orders model with a user demographics model on user_id. If the intermediate model has 1000 users and the demographics model has 950 users, what is the number of rows in the resulting mart model after an inner join?

A 1000 rows, because all users from the intermediate model are kept

B 50 rows, because only users missing in demographics are kept

C 1950 rows, because rows from both models are combined

D 950 rows, because only users present in both models are kept

💡 Hint

Remember what an inner join does to rows when keys don't match.

An inner join keeps only rows with matching user_id in both tables, so the smaller count (950) determines the output rows.

🔧 Debug

advanced

Identifying the Error in a dbt Model Reference

Look at this dbt model SQL snippet:

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

When running dbt, it fails with an error saying the referenced model does not exist. What is the most likely cause?

A The model name in ref is misspelled or does not match any model file

B The SQL syntax is invalid because of missing semicolon

C The {{ ref() }} function cannot be used inside select statements

D The model file is missing a schema declaration

💡 Hint

Check the spelling and existence of the referenced model.

The error usually means the model name inside ref() does not match any existing model file name in the project.

🚀 Application

expert

Choosing the Correct Layer for a New Data Transformation

You have raw sales data coming from multiple sources with inconsistent date formats and missing values. You want to create a model that standardizes dates and fills missing values before calculating monthly sales totals. According to the staging, intermediate, and marts pattern, where should you place the model that standardizes dates and fills missing values?

A In the marts layer, because it creates final reports

B In the staging layer, because it prepares raw data for further use

C Outside dbt, because data cleaning should not be done in dbt

D In the intermediate layer, because it aggregates data for analysis

💡 Hint

Think about where raw data is first cleaned and standardized.

Data cleaning like standardizing dates and filling missing values belongs in the staging layer to prepare raw data for later transformations.