Staging, Intermediate, Marts

Concepts covered: Model layers and grain

Each layer has 1 job

A project with 1 source table and 2 marts can read the source from each mart and stay readable. A project with 40 source tables and 60 marts cannot: every mart repeats the same renames and casts, and when the billing system renames a column, every mart that touches it breaks on the same morning. Layering splits the work into 3 kinds of model. Each kind is allowed one job, so any change lands in 1 file.

Take 3 marts that read billing.subscriptions directly. The first casts started_at to a timestamp, the second does the same under a different alias, and the third forgets the cast and compares a string to a date. That is 3 copies of the same 4 lines, and a rename in billing touches all 3. With a staging model between them, the cast is written once and the rename is absorbed once.

Staging: one model per source table

A staging model reads 1 source table through source(), renames its columns to the names the project uses, casts types and stops; joins, aggregations and business rules all happen in later layers. The file name is stg_ followed by the source name and the table name, with a doubled separator between the two, so stg_billing__subscriptions can only mean one thing. Everything downstream selects subscription_id and never id, which is why the billing team can rename its column and the project changes in 1 file.

select
    id as subscription_id,
    user_id,
    lower(plan_code) as plan_code,
    status,
    cast(started_at as timestamp) as started_at,
    cast(updated_at as timestamp) as updated_at
from {{ source('billing', 'subscriptions') }}

Staging models are built as views. A view stores nothing, so 40 of them cost nothing to keep, and selecting from one always returns the source as it stands at that moment. The renaming itself is cheap work; its value is that it happens in one place.

Intermediate: where the grain changes

select
    user_id,
    cast(played_at as date) as play_date,
    count(*) as plays,
    sum(ms_played) / 60000.0 as minutes_played
from {{ ref('stg_app__plays') }}
group by
    user_id,
    cast(played_at as date)

An intermediate model holds a join or an aggregation that would make a mart hard to read if it sat inline. It reads staging models, or other intermediate models, through ref(), and never calls source(). This one turns a table of individual plays into 1 row per user per day. The name carries the layer, the staging model it reads, and what a row stands for. Intermediate models are steps toward a mart; analysts query the marts.

Marts: what the business asks for

select
    d.user_id,
    d.play_date,
    s.plan_code,
    d.plays,
    d.minutes_played
from {{ ref('int_plays__daily_by_user') }} as d
left join {{ ref('stg_billing__subscriptions') }} as s
    on d.user_id = s.user_id

A mart is the table an analyst opens. It reads intermediate and staging models and never a source, so every column it shows has passed through a staging rename. The fct_ prefix marks a fact table of events or measures and dim_ a dimension that describes things. Marts are built as tables, because a dashboard that ran the join above on every page load would recompute the aggregation each time, and a table computes it once per build.

models:
  streaming:
    staging:
      +materialized: view
    intermediate:
      +materialized: view
    marts:
      +materialized: table

The project file sets the materialization for each folder, so a model saved under models/marts becomes a table without a config line of its own, and a model under models/staging becomes a view. A model can still override its folder with a config() call at the top of the file.

About this lesson

This section is part of the Models: Intermediate lesson on DataDriven, the home of data engineering. Lessons cover SQL, Python, Spark, data modeling and pipeline architecture, and each section pairs its explanation with worked examples and practice you run as you go.