Staging, intermediate, and marts pattern in dbt - Interactive Code Practice

Practice - 5 Tasks

1fill in blank

easy

Complete the code to define a staging model that selects all columns from the raw source table.

dbt

select * from [1]

Drag options to blanks, or click blank then click option'

Araw.customers

Bintermediate.customers

Cmarts.customers

Danalytics.customers

Attempts:

3 left

💡 Hint

Common Mistakes

Using intermediate or marts tables in staging models.

Referencing analytics schema instead of raw.

The staging layer reads directly from the raw source tables, so the correct source is raw.customers.

2fill in blank

medium

Complete the code to create an intermediate model that joins staging customers with staging orders.

dbt

select c.customer_id, c.name, o.order_id, o.order_date
from [1] c
join [2] o on c.customer_id = o.customer_id

Drag options to blanks, or click blank then click option'

Aintermediate.customers

Bstaging.orders

Cstaging.customers

Draw.orders

Attempts:

3 left

💡 Hint

Common Mistakes

Using intermediate or raw tables for customers in this join.

Mixing staging and intermediate layers incorrectly.

BLANK_1 is staging.customers (C) and BLANK_2 is staging.orders (B). Intermediate models join staging tables.

3fill in blank

hard

Fix the error in the intermediate model code by choosing the correct table for orders.

dbt

select c.customer_id, c.name, o.order_id, o.order_date
from staging.customers c
join [1] o on c.customer_id = o.customer_id

Drag options to blanks, or click blank then click option'

Araw.orders

Banalytics.orders

Cmarts.orders

Dstaging.orders

Attempts:

3 left

💡 Hint

Common Mistakes

Joining raw.orders directly in intermediate models.

Using marts or analytics tables too early.

The intermediate model should join staging orders, so staging.orders is the correct choice.

4fill in blank

hard

Fill both blanks to create a mart model that aggregates total sales per customer from the intermediate orders table.

dbt

select customer_id, sum([1]) as total_sales
from [2]
group by customer_id

Drag options to blanks, or click blank then click option'

Aorder_amount

Bintermediate.orders

Cstaging.orders

Draw.orders

Attempts:

3 left

💡 Hint

Common Mistakes

Using staging or raw tables directly in marts.

Summing wrong columns like order_id.

The mart model sums order_amount from the intermediate.orders table.

5fill in blank

hard

Fill all three blanks to create a mart model that calculates average order value per customer using intermediate data.

dbt

select [1], avg([2]) as avg_order_value
from [3]
group by [1]

Drag options to blanks, or click blank then click option'

Acustomer_id

Border_amount

Cintermediate.orders

Dstaging.customers

Attempts:

3 left

💡 Hint

Common Mistakes

Using staging tables in marts.

Grouping by wrong columns.

The mart model groups by customer_id, averages order_amount, and reads from intermediate.orders.

Practice

1. What is the main purpose of the staging layer in the dbt pattern Staging, intermediate, and marts?

easy

A. To create final reports and dashboards

B. To aggregate data for business metrics

C. To clean and prepare raw data for further transformations

D. To store backup copies of raw data

Solution

1. Step 1: Understand the role of staging

   The staging layer is where raw data is cleaned and standardized to make it ready for analysis.

2. Step 2: Differentiate from other layers

   Intermediate and marts layers perform transformations and aggregations, while staging focuses on cleaning.

3. Final Answer:

   To clean and prepare raw data for further transformations -> Option C

4. Quick Check:

   Staging = cleaning raw data [OK]

Hint: Staging = clean raw data first [OK]

Common Mistakes:

• Confusing staging with marts layer
• Thinking staging creates final reports
• Assuming staging stores backups

2. Which of the following is the correct way to reference a model in dbt within the intermediate layer?

easy

A. SELECT * FROM stg_customers

B. SELECT * FROM {{ ref('stg_customers') }}

C. SELECT * FROM ref('stg_customers')

D. SELECT * FROM {{ source('stg_customers') }}

Solution

1. Step 1: Recall dbt model referencing syntax

   dbt uses the Jinja function ref() inside double curly braces to reference models.

2. Step 2: Identify correct syntax

   The correct syntax is {{ ref('model_name') }}, so {{ ref('stg_customers') }} is correct.

3. Final Answer:

   SELECT * FROM {{ ref('stg_customers') }} -> Option B

4. Quick Check:

   Use {{ ref('model') }} to reference models [OK]

Hint: Use {{ ref('model_name') }} to reference models [OK]

Common Mistakes:

• Omitting curly braces around ref()
• Using source() instead of ref() for models
• Calling ref() without curly braces

3. Given the following dbt model code in the intermediate layer:

SELECT
  customer_id,
  COUNT(order_id) AS total_orders
FROM {{ ref('stg_orders') }}
GROUP BY customer_id

What will be the output columns of this model?

medium

A. customer_id, total_orders

B. order_id, total_orders

C. customer_id, order_id

D. customer_id only

Solution

1. Step 1: Analyze SELECT statement

   The query selects customer_id and counts order_id as total_orders.

2. Step 2: Understand GROUP BY effect

   Grouping by customer_id means output will have one row per customer with total orders counted.

3. Final Answer:

   customer_id, total_orders -> Option A

4. Quick Check:

   Output columns = customer_id and total_orders [OK]

Hint: GROUP BY columns + aggregated columns appear in output [OK]

Common Mistakes:

• Expecting order_id as output column
• Missing aggregated column in output
• Confusing GROUP BY with WHERE clause

4. You wrote this dbt model in the marts layer:

SELECT
  customer_id,
  SUM(total_orders) AS total_orders
FROM {{ ref('int_customer_orders') }}
GROUP BY customer_id

But the model fails with an error about total_orders. What is the likely cause?

medium

A. Missing GROUP BY clause

B. SUM() cannot be used in marts layer

C. Incorrect use of {{ ref() }} syntax

D. The referenced model int_customer_orders does not have a column named total_orders

Solution

1. Step 1: Check referenced model columns

   The error suggests total_orders column is missing in int_customer_orders.

2. Step 2: Validate aggregation usage

   SUM() is valid in marts layer and GROUP BY is present, so those are not issues.

3. Final Answer:

   The referenced model int_customer_orders does not have a column named total_orders -> Option D

4. Quick Check:

   Column missing in referenced model causes error [OK]

Hint: Check referenced model columns before aggregating [OK]

Common Mistakes:

• Assuming SUM() is invalid in marts
• Forgetting GROUP BY with aggregation
• Misusing ref() syntax

5. You want to build a sales report in the marts layer that shows total sales per region. Your staging layer has raw sales data, and the intermediate layer calculates sales per store. Which is the best approach to build this report using the staging, intermediate, and marts pattern?

hard

A. Reference the intermediate sales per store model in the marts layer and aggregate sales by region

B. Directly query the raw sales data in the marts layer and group by region

C. Aggregate sales by region in the staging layer

D. Create the sales report in the intermediate layer without using marts

Solution

1. Step 1: Follow pattern responsibilities

   Staging cleans raw data, intermediate transforms data per store, marts create final aggregates like sales per region.

2. Step 2: Use intermediate model in marts

   Use the intermediate sales per store model as input to marts, then aggregate by region for the report.

3. Final Answer:

   Reference the intermediate sales per store model in the marts layer and aggregate sales by region -> Option A

4. Quick Check:

   Marts aggregate intermediate data for final reports [OK]

Hint: Build reports in marts using intermediate models [OK]

Common Mistakes:

• Skipping intermediate layer aggregation
• Aggregating raw data directly in marts
• Doing final aggregation in staging