Data Modeling Layers
The onion that keeps the warehouse sane
Data modeling layers are the onion of a warehouse: sources, staging, intermediate, and marts. Each layer has a job, and mixing responsibilities across layers is how warehouses go to war with themselves.
What you will learn
- Describe the four canonical layers and their responsibilities
- Decide what belongs in staging vs intermediate vs marts
- Refactor a flat messy pipeline into clean layers
What is this?
A layered warehouse assigns each query a home. Sources are raw loads; staging lightly cleans and types; intermediate models express multi-step logic that marts reuse; marts are the final, selectable, business-shaped views. The rule is simple: keep every model doing one job at one layer so changes stay local.
Why do you need it?
- Layers make a model's job obvious and its bugs discoverable.
- Intermediate models remove duplicated logic and halve 'why do two dashboards disagree?'
- A clear seam means a mart change never rewrites the staging layer.
Mental model
A restaurant kitchen
The staging-to-mart pipeline
Prep cooks (staging) wash and chop; the pass (marts) plates the final dish. You would not plate at the prep station or wash dishes at the pass.
How it works
- Load raw tables as sources with minimal renaming.
- Staging: cast types, dedupe, rename, and standardize — no business logic.
- Intermediate: join, aggregate, and express business rules that marts will reuse.
- Marts: final, documented datasets a dashboard or BI tool selects from directly.
Each layer restricts what its models may do.
In production
- A weekly revenue mart is built once on an intermediate orders-plus-payments model.
- A new analyst learns the warehouse from the marts, not the staging tables.
- A schema change upstream only rewrites the one staging model that touched it.
Best practices
- Never query source tables directly from dashboards or analysts' notebooks.
- Keep staging models 1:1 with sources to isolate upstream changes.
- Build intermediate models when the same join or rule would otherwise appear twice.
Common mistakes
- Putting business logic in staging, forcing every downstream model to re-filter.
- Skipping intermediate and stamping the same five-join logic into ten marts.
- Building marts that are just renamed sources — the layering buys nothing.
Debugging tips
- If a mart behaves differently after an unrelated change, its logic probably reached past its layer.
- If the same join appears in three models, promote it to an intermediate model.
Interview checkpoint
Hands-on lab
- Refactor a flat SQL query into source, staging, intermediate, and mart models
- Move a repeated join into an intermediate model
- Verify the layered pipeline returns identical numbers to the original
Layer a Messy Orders Pipeline
45 min · intermediate
Quick quiz
Business logic and repeated joins belong in
Staging models should