Staging, intermediate, and marts pattern in dbt - Step-by-Step Execution

Concept Flow - Staging, intermediate, and marts pattern

Raw Data Sources

↓

Staging Models

↓

Intermediate Models

↓

Data Marts

↓

Business Users & Reports

Data flows from raw sources to staging, then to intermediate models, and finally to data marts for business use.

Execution Sample

dbt

select * from raw.customers;
-- staging cleans and standardizes

select customer_id, upper(name) as name from staging.customers;
-- intermediate enriches

select customer_id, name from intermediate.customers;
-- marts for reporting

This code shows data moving through staging, intermediate, and marts layers with transformations at each step.

Execution Table

Step	Layer	Action	Input Data	Output Data
1	Raw	Load raw customer data	Raw CSV with inconsistent names	Raw table with all columns
2	Staging	Clean and standardize names	Raw table	Names uppercased, trimmed
3	Intermediate	Add business logic	Staging table	Filtered customers, enriched columns
4	Marts	Prepare for reporting	Intermediate table	Aggregated customer metrics
5	End	Data ready for business use	Data marts	Reports and dashboards

💡 Data is fully transformed and ready for business consumption in marts.

Variable Tracker

Variable	Raw	Staging	Intermediate	Marts
customer_name	mixed case, spaces	UPPERCASE, trimmed	UPPERCASE, filtered	UPPERCASE, aggregated

Key Moments - 3 Insights

Why do we need a staging layer instead of transforming raw data directly to marts?

Staging cleans and standardizes raw data first, making later transformations simpler and more reliable, as shown in execution_table step 2.

What is the role of intermediate models between staging and marts?

Intermediate models add business logic and enrich data, preparing it for final aggregation in marts, as seen in execution_table step 3.

Can marts use raw data directly?

No, marts rely on cleaned and enriched data from intermediate models to ensure accuracy and performance, shown by the flow in concept_flow.

Visual Quiz - 3 Questions

Look at the execution_table, what transformation happens at the staging layer?

A Add business logic and filters

B Clean and standardize names

C Aggregate customer metrics

D Load raw CSV data

💡 Hint

Check execution_table row 2 under 'Action' for staging layer.

At which step does the data become ready for business use?

A Step 5

B Step 3

C Step 2

D Step 4

💡 Hint

Look at execution_table row 5 for when data is ready for business.

If we skip the intermediate layer, what is likely to happen?

A Data marts will aggregate correctly

B Raw data will be cleaned properly

C Business logic and enrichment will be missing

D Staging will add business logic

💡 Hint

Refer to key_moments about the role of intermediate models.

Concept Snapshot

Staging, intermediate, and marts pattern:
- Staging cleans raw data
- Intermediate adds business logic
- Marts prepare data for reports
- Each layer builds on the previous
- Ensures clean, enriched, and usable data

Full Transcript

This visual execution shows how data moves through the staging, intermediate, and marts pattern in dbt. Raw data is first loaded with inconsistencies. The staging layer cleans and standardizes this data, for example by uppercasing names. Then intermediate models add business logic and enrich the data. Finally, data marts aggregate and prepare the data for business users and reports. This layered approach ensures data is clean, reliable, and ready for analysis. The execution table traces each step, showing inputs and outputs. Variable tracking highlights how data changes at each stage. Key moments clarify why each layer is important. The quiz tests understanding of these transformations and their order.

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