Staging, intermediate, and marts pattern in dbt - Mini Project: Build & Apply

Staging, Intermediate, and Marts Pattern in dbt

📖 Scenario: You work as a data analyst at a retail company. You receive raw sales data from multiple sources. To make this data useful for business decisions, you need to organize it in layers: staging, intermediate, and marts.Staging cleans and standardizes raw data. Intermediate transforms and combines staging data. Marts prepare final tables for reporting.

🎯 Goal: Build a simple dbt project with three models: a staging model to clean raw sales data, an intermediate model to calculate monthly sales totals, and a marts model to prepare the final sales report.

📋 What You'll Learn

Create a staging model called stg_sales that selects and cleans raw sales data

Create an intermediate model called int_monthly_sales that aggregates sales by month

Create a marts model called mart_sales_report that selects final columns for reporting

Use dbt model files with correct SQL SELECT statements

Use appropriate naming conventions for each layer

💡 Why This Matters

🌍 Real World

Data teams use this pattern to build reliable, maintainable data pipelines that transform raw data into business-ready reports.

💼 Career

Understanding this pattern is essential for data analysts, engineers, and scientists working with dbt or modern data warehouses.

1

Create the staging model stg_sales.sql

Create a dbt model file named stg_sales.sql. Write a SQL SELECT statement that selects order_id, order_date, customer_id, and amount from the raw table raw_sales. Rename amount to sale_amount. This model cleans and standardizes the raw data.

dbt

-- Your code here to create stg_sales.sql
-- Select order_id, order_date, customer_id, and amount as sale_amount from raw_sales

Need a hint?

Use SELECT to pick columns and AS to rename amount to sale_amount.

2

Create the intermediate model int_monthly_sales.sql

Create a dbt model file named int_monthly_sales.sql. Write a SQL SELECT statement that selects the year and month from order_date as order_year and order_month, and sums sale_amount as total_sales. Use the staging model stg_sales as the source table. Group by year and month.

dbt

select
  -- Extract year and month from order_date
  -- Sum sale_amount as total_sales
from {{ ref('stg_sales') }}
-- Group by year and month
-- Your code here

Need a hint?

Use extract to get year and month, sum to aggregate, and {{ ref('stg_sales') }} to reference the staging model.

3

Create the marts model mart_sales_report.sql

Create a dbt model file named mart_sales_report.sql. Write a SQL SELECT statement that selects order_year, order_month, and total_sales from the intermediate model int_monthly_sales. This model prepares the final sales report.

dbt

select
  order_year,
  order_month,
  total_sales
from {{ ref('int_monthly_sales') }}
-- Your code here

Need a hint?

Simply select the columns from the intermediate model using {{ ref('int_monthly_sales') }}.

4

Run and display the final sales report

Write a SQL SELECT statement to query all columns from the marts model mart_sales_report. This will display the final sales report with year, month, and total sales.

dbt

select * from {{ ref('mart_sales_report') }}
-- Your code here

Need a hint?

Use select * from the marts model to see the final report.