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.