Skip to content
Tutorials Center
My learning

Topics

Lesson 9 of 41 ยท Projects and Models

Staging, Intermediate and Marts Layers

Organize a dbt project into staging, intermediate and marts layers, with naming rules, what belongs in each layer, and a complete runnable example on shop data.

Once a project passes a dozen models, you need a plan for where logic lives. The most widely used plan is three layers: staging (clean the raw data), intermediate (combine and prepare) and marts (the business-facing tables). Each layer has one job, and models may only depend on the layers before them. This lesson explains the layers and builds a small but complete example you can run.

Three layers

Staging One model per raw table, cleaned
Intermediate Joins and steps toward a mart
Marts Facts and dimensions for users

Raw data flows left to right and never skips back.

1 / 2

Staging: one model per raw table

Staging models are the only place raw tables are read. Each one wraps one source table and does only light, mechanical work:

  • rename columns to consistent names (id becomes order_id);
  • cast types (strings to dates, text to numbers);
  • standardize values (lowercase emails, trim spaces);
  • compute simple per-row columns (quantity * unit_price).

They do not join tables or aggregate. They are normally views, because they are cheap and should always reflect raw data. Example staging models:

SQL
-- models/staging/shop/stg_shop__orders.sql
select
    id as order_id,
    customer_id,
    ordered_at,
    status,
    shipping
from {{ source('shop', 'orders') }}

Why bother, if it is "just a rename"? Because everything downstream now uses one clean, stable interface. If the raw column id is renamed tomorrow, you fix one staging model, not forty.

Intermediate: building blocks

Intermediate models take staging models and prepare them for the marts: joins, pivots, deduplication, windowed logic. They exist so a mart does not become a 400-line query. They are usually ephemeral or views and are not exposed to end users. If you can name a step ("orders joined with their items"), it probably deserves its own intermediate model.

Marts: what people query

Marts are the finished product, shaped for business use. The common pattern is facts and dimensions:

  • Fact (fct_): events or transactions, one row per thing that happened (an order, a payment). Mostly numbers and keys.
  • Dimension (dim_): descriptive entities (customers, products). One row per entity, many attributes.

Marts are usually tables (or incremental for very large ones), carefully tested and documented, because dashboards read them.

A complete example

Here is the whole chain, executed in order as dbt would: three staging views, one intermediate model, and two marts.

SQL Staging to marts on shop data ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Each step is small and readable, and each layer has a clear contract: staging has one row per raw record, fct_orders has one row per order, and dim_customers has one row per customer. To investigate a number, you walk back one layer at a time:

SQL Debugging by walking back a layer ยท database: shop

Press Run (or Ctrl + Enter) to see the result here.

Folder layout

TEXT
models/
โ”œโ”€โ”€ staging/
โ”‚   โ””โ”€โ”€ shop/
โ”‚       โ”œโ”€โ”€ _shop__sources.yml
โ”‚       โ”œโ”€โ”€ _shop__models.yml
โ”‚       โ”œโ”€โ”€ stg_shop__customers.sql
โ”‚       โ”œโ”€โ”€ stg_shop__orders.sql
โ”‚       โ””โ”€โ”€ stg_shop__order_items.sql
โ”œโ”€โ”€ intermediate/
โ”‚   โ””โ”€โ”€ int_orders_with_totals.sql
โ””โ”€โ”€ marts/
    โ””โ”€โ”€ core/
        โ”œโ”€โ”€ _core__models.yml
        โ”œโ”€โ”€ dim_customers.sql
        โ””โ”€โ”€ fct_orders.sql

Staging folders are grouped by source system; marts folders are grouped by business area (core, finance, marketing). A leading underscore on YAML files makes them sort to the top.

Layer rules worth enforcing

  1. Only staging models read from source().
  2. Staging models never join to other staging models.
  3. Marts do not read directly from sources, only from staging, intermediate or other marts.
  4. Nothing refs a mart from staging or intermediate (no arrows backwards).

You can check these automatically. This stand-in (not dbt) lints a model list against the prefix rules:

Python Stand-in: a layer-rule linter

Press Run (or Ctrl + Enter) to see the result here.

Program input ยท what you'd type while the program runs

Type the answers before you press Run, one per line. Each time the program reads a line (with input()), it gets the next line from this box.

Teams run checks like this with the dbt-project-evaluator package, which you will meet in the best-practices chapter.

Check your understanding

Quick check ยท 4 questions

of 4 answered You scored / 4 ยท perfect!

  1. 1. What belongs in a staging model?

    Staging models are one-to-one wrappers over raw tables that standardize names and types.
  2. 2. Which materialization is the usual default for staging models?

    Staging models are cheap, and views keep them always in step with the raw data.
  3. 3. What is the difference between a fact and a dimension?

    A fact records something that happened (an order), while a dimension describes a thing (a customer).
  4. 4. Which dependency breaks the layering rules?

    Dependencies must flow forward. A staging model depending on a mart creates a backward arrow, and often a cycle.

Summary

  • Staging cleans one raw table each; intermediate prepares building blocks; marts expose facts and dimensions.
  • Prefixes (stg_, int_, fct_, dim_) make the layer obvious from the name.
  • Only staging reads sources; dependencies flow forward through the layers.
  • Group staging folders by source system and marts by business area.

Next: Model Configs and Properties