Every modern data warehouse you'll work in has three (sometimes four) layers. Skipping any of them is technical debt the team will pay for later.
The three layers
+-------------+
| Marts | ← what dashboards and BI tools query
+-------------+
↑
+-------------+
| Staging | ← cleaned, renamed, lightly transformed
+-------------+
↑
+-------------+
| Source | ← raw landed data from OLTP, APIs, files
+-------------+
Each layer has a clear contract:
Source (a.k.a. raw)
What goes here: untransformed data exactly as it landed from the source — same column names, same types, same nulls. No business logic.
Why: a faithful copy of source state. If the warehouse breaks downstream, you can rebuild from source without re-pulling from systems. Source is your safety net.
What NOT to do here: don't filter, don't dedup, don't rename. Just copy.
Staging
What goes here: source data with light cleaning — consistent column naming, type fixes, simple deduplication, removal of obvious test/internal rows.
Why: separate "the world is messy" from "our model". Future ETL of the same source can re-use staging; multiple marts can build on it.
One staging model per source table. The naming convention is usually stg_<source>_<table> (e.g., stg_shopify_orders, stg_stripe_charges).
Marts
What goes here: business-defined dimensional models. Facts, dimensions, conformed metrics. One row per business concept ("one row per order line", "one row per user per day").
Why: marts are what analysts and dashboards query. Marts hide the source-table complexity behind business-friendly names (fct_orders, dim_customers).
A mart can join multiple staging tables (e.g., fct_orders joins stg_shopify_orders with stg_users and stg_products).
What goes in which layer — examples
| Operation | Layer |
|---|---|
SELECT * FROM raw.orders (Fivetran-loaded) | Source |
Rename orderId → order_id, cast total_str → total_dec | Staging |
Filter out test orders where email LIKE '%@example.com' | Staging |
Compute revenue = quantity * unit_price - discount | Mart |
Join orders to customers to products | Mart |
| Slowly Changing Dimension (Type 2) snapshot | Mart |
Why three layers (not one)
One-layer warehouses (everything in marts):
- Hard to test — business logic mixed with parsing/cleaning.
- Hard to debug — when a number's wrong, is the source bad, the cleaning bad, or the metric bad?
- Hard to evolve — changing a source-system column breaks 20 downstream models simultaneously.
Three-layer warehouses:
- Each layer has a clear responsibility — easy to assign ownership.
- A source schema change ripples only through staging; marts can be insulated.
- Easy to validate — test each layer's contract independently.
The optional fourth layer: intermediate
For complex marts, an int_ layer between staging and marts holds reusable transformations: deduped events, sessionized data, joined-but-not-aggregated tables. The convention: int_<concept>.
In dbt projects, this is conventionally:
models/staging/— staging modelsmodels/intermediate/— intermediate modelsmodels/marts/<domain>/— domain-organized marts (marts/finance/,marts/marketing/)
Materialization strategy
- Staging — usually views or incremental tables. Cheap, easy to refresh.
- Intermediate — usually ephemeral (CTEs) or tables, depending on cost.
- Marts — materialized tables or incremental models. They're queried often; the warehouse engine benefits from pre-computed rows.
dbt's default: views in staging, tables in marts. Override per-model when costs dictate.
What this lets you do
- A new analytics engineer can read the staging layer to understand "what data do we have" without learning every source system.
- A broken pipeline can be re-run from staging without re-extracting from the source system (which is often rate-limited).
- A schema change in one source breaks one staging model — not 50 dashboards.
Common mistakes
- No staging layer — business logic and parsing mixed in marts. Painful to maintain.
- Business logic in source — overwrite raw data with "cleaned" data. You lose the safety net.
- Treating staging as marts — analysts query
stg_*directly. Now you can't change staging without breaking dashboards. - Too many intermediate models — every CTE becomes its own model. Models lose their purpose.
Takeaway
Three layers. Each has one job. Source is the safety net. Staging is the clean-but-not-yet-modeled layer. Marts are what the business sees. Skipping any of them is technical debt.