The Three-Layer Modeling Pattern
A well-organized dbt project separates transformation logic into three conceptual layers: staging, intermediate, and marts. Staging models sit closest to raw source data and do light, 1:1 cleanup — renaming columns, casting types, and standardizing values — without joining across sources. Intermediate models sit in the middle, combining and reshaping multiple staging models into reusable business concepts. Marts sit at the top, delivering final, business-facing fact and dimension tables that analysts and BI tools query directly. Data flows strictly one direction: raw source to staging to intermediate to marts, never backward.
Cricket analogy: This is like a domestic cricket pipeline where raw talent is first cleaned up at academy level (staging), shaped into state-team players through intermediate coaching (intermediate), and finally delivered as national-team-ready players (marts) — each stage only builds on the one before it.
Staging Models: The Cleaning Layer
A staging model corresponds to exactly one source table and does not join to other staging models. Its job is narrow: reference the raw table with the source() function, rename cryptic or inconsistent column names into clear, consistent ones, cast data types, and apply light standardization like lowercasing status strings. Staging models are conventionally named stg_<source>__<table> and materialized as views, since they're cheap to compute and act as a thin, queryable interface over raw data rather than a stored copy of it.
Cricket analogy: This is like a scorer converting raw ball-by-ball notes into a standardized scorecard format — renaming shorthand like 'c&b' into 'caught and bowled' — without yet combining it with any other match's data.
-- models/staging/stripe/stg_stripe__payments.sql
with source as (
select * from {{ source('stripe', 'payments') }}
),
renamed as (
select
id as payment_id,
orderid as order_id,
paymentmethod as payment_method,
lower(status) as status,
amount / 100.0 as amount_usd,
created as created_at
from source
)
select * from renamedIntermediate and Marts: Building Business Logic
Intermediate models, conventionally prefixed int_ and living in models/intermediate/, take one or more staging models and apply business logic that's too complex or reusable to belong in a single mart — deduplication, unioning multiple sources, or pre-aggregating a metric used by several downstream marts. They're often materialized as views or ephemeral models since they're implementation details, not final deliverables, and typically aren't exposed directly to BI tools. Marts, prefixed fct_ (fact tables of events or transactions) or dim_ (dimension tables of entities like customers or products), are the final layer — materialized as table or incremental for performance, organized into subdirectories by business area such as marketing or finance, and are what analysts actually query.
Cricket analogy: This is like a team analyst computing a reusable 'player form index' from raw batting and bowling staging data (intermediate), which then feeds both the selection committee's dashboard and the broadcaster's graphics package (marts) without recomputing the index twice.
A common naming convention: stg_<source>__<table> for staging, int_<entity>__<verb> for intermediate (e.g. int_orders__deduplicated), and fct_<event> or dim_<entity> for marts. Consistent naming makes the DAG readable at a glance in dbt docs generate and signals materialization intent without opening each file.
Never let a marts model reference a raw source() directly, and never let a staging model ref() another staging model from a different source without going through intermediate first. Skipping layers creates a tangled DAG that's hard to test, debug, and reason about — the layering is a discipline, not just a folder structure.
- Staging models are 1:1 with source tables, do light cleaning only, and are conventionally materialized as views.
- Intermediate models combine staging models and hold reusable business logic; they're implementation details, not final deliverables.
- Marts are the final business-facing layer — fct_ for events/transactions, dim_ for entities — usually materialized as table or incremental.
- Data should flow strictly staging to intermediate to marts; never skip layers or reference source() from a mart.
- Naming conventions (stg_, int_, fct_/dim_) make the DAG self-documenting in dbt docs.
- Marts are organized by business area (finance, marketing) so analysts can find business-facing models intuitively.
- This layering separates 'what the source data looks like' from 'what the business needs', making refactors safer.
Practice what you learned
1. What is the primary responsibility of a staging model?
2. Which materialization is most conventional for staging models, and why?
3. What distinguishes a fct_ model from a dim_ model in the marts layer?
4. Why is a marts model discouraged from calling source() directly?
5. What is the main purpose of the intermediate layer?
Was this page helpful?
You May Also Like
Incremental Models In-Depth
Incremental models process only new or changed rows on subsequent runs instead of rebuilding the full table, using is_incremental() and configurable strategies to control merge behavior.
Seeds
dbt seeds load small, static CSV files version-controlled in your repo directly into your warehouse as tables, ideal for reference and mapping data that doesn't come from a production source.
Snapshots
dbt snapshots implement Type 2 slowly changing dimensions, capturing row-level changes over time so you can query what a record looked like at any past point.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
ProgrammingPowerShell Study Notes
Programming · 30 topics