dbt Models Patterns — A Field Guide

Every dbt project eventually converges on the same three-layer structure, not because dbt enforces it, but because every project that skips it ends up hard to refactor. Staging models normalize raw sources; intermediate models do the joining and reshaping; marts are the final tables analysts actually query.

Staging: one model per source table, no joins

A staging model does renaming, type casting, and light cleanup — never joins across sources. It exists so every downstream model references clean, consistently named columns instead of each writing its own CAST(raw_amt AS DECIMAL) over and over. Materialize staging as views; they are cheap and always reflect the source directly.

{{ config(materialized='view') }}

select
    id as order_id,
    customer_id,
    cast(created_at as timestamp) as ordered_at,
    amount_cents / 100.0 as amount,
    lower(status) as status
from {{ source('shopify', 'orders') }}

Intermediate: where the joins and business logic live

Intermediate models combine multiple staging models and apply business logic that is not yet ready to be a final, user-facing table — deduplication, complex case statements, multi-table joins. They live in their own intermediate/ folder and are usually materialized as views or ephemeral, since nothing outside dbt should query them directly.

select
    o.order_id,
    o.amount,
    o.ordered_at,
    c.customer_id,
    c.signup_date,
    datediff('day', c.signup_date, o.ordered_at) as days_since_signup
from {{ ref('stg_orders') }} o
left join {{ ref('stg_customers') }} c
    on o.customer_id = c.customer_id

Marts: what analysts and dashboards actually query

Marts are the final, documented, tested tables — fct_orders, dim_customers — that BI tools and analysts query directly. This is the layer worth materializing as table or incremental, since it is queried repeatedly and needs to be fast. Fact tables (events, transactions) and dimension tables (entities with attributes) are the standard Kimball naming split, and keeping to it makes a new team member's first week of navigating the project much shorter.

Naming and folder conventions that hold up at scale

Prefix by layer (`stg_`, `int_`, `fct_`, `dim_`) so a filename alone tells you where a model sits in the pipeline. One staging model per source table, named after the source, not the destination. Mirror the folder structure by business domain within marts (`marts/finance/`, `marts/marketing/`) once the project grows past a couple dozen models — a flat `models/` folder past that point becomes its own maintenance problem.

Wrapping up

The staging/intermediate/marts split is not bureaucracy — it is what keeps a rename in a raw source from becoming a company-wide incident. Enforce the boundary that only marts get queried outside dbt, and the rest of the layering falls out naturally as the project grows.