Staging, intermediate, and marts pattern in dbt

Introduction

This pattern helps organize data work in steps. It makes data easier to clean, transform, and use for reports.

When you get raw data from many sources and want to clean it first.

When you need to combine and transform data before final use.

When you want to build clear, reusable data layers for reports or dashboards.

When you want to keep your data work organized and easy to update.

When you want to separate simple cleaning from complex business logic.

Syntax

dbt

models/
  staging/
    source_table.sql
  intermediate/
    transformed_table.sql
  marts/
    final_report_table.sql

Staging: Raw data cleaning and simple fixes.

Intermediate: Data transformations and combining tables.

Examples

Staging model just selects raw data for cleaning later.

dbt

-- models/staging/customers.sql
select * from raw.customers

Intermediate model joins staging tables to combine data.

dbt

-- models/intermediate/customer_orders.sql
select c.id, c.name, o.order_date
from {{ ref('customers') }} c
join {{ ref('orders') }} o on c.id = o.customer_id

Marts model aggregates data for final reports.

dbt

-- models/marts/sales_report.sql
select customer_id, count(*) as total_orders
from {{ ref('customer_orders') }}
group by customer_id

Sample Program

This example shows three layers: staging cleans raw tables, intermediate joins them, and marts summarize data for reports.

dbt

-- models/staging/customers.sql
select id, name, email from raw.customers

-- models/staging/orders.sql
select id, customer_id, order_date from raw.orders

-- models/intermediate/customer_orders.sql
select c.id as customer_id, c.name, o.order_date
from {{ ref('customers') }} c
join {{ ref('orders') }} o on c.id = o.customer_id

-- models/marts/customer_order_counts.sql
select customer_id, count(*) as total_orders
from {{ ref('customer_orders') }}
group by customer_id

Important Notes

Use {{ ref('model_name') }} to link models and keep dependencies clear.

Keep staging models simple to make debugging easier.

Intermediate models handle business logic and complex joins.

Summary

The pattern splits data work into clear steps: staging, intermediate, and marts.

This helps keep data clean, organized, and easy to maintain.

Use dbt refs to connect models and build a reliable data pipeline.