Project Structure and Layering

Layering Is a Maintenance Strategy, Not a Folder Convention

An earlier module introduced staging, intermediate, and marts as a beginner-level convention: staging cleans, intermediate combines, marts finalize. That description is correct as far as it goes, but it undersells what the layering is actually for. Layering is not primarily about tidy folders — it is a deliberate strategy for containing the blast radius of change in a project that will eventually have hundreds of models, dozens of contributors, and source systems that change their schemas without warning.

The question this module is really answering is: when something upstream breaks — a column gets renamed in a source system, a business rule changes, a new join condition is discovered — how many files does a person have to touch, and how confident can they be that they found all of them? A well-layered project has a precise, small, predictable answer to that question for almost any kind of change. A poorly layered project has an answer that starts with "grep the entire models directory and hope."

This module assumes you're comfortable with the mechanics of a model — ref(),source(), materializations, the config() block — covered earlier in this track. What follows goes deep on the one topic those modules only sketched: exactly what belongs in each layer, why the boundaries are drawn where they are, how to name things so a project stays self-describing at 20 models and at 300, and a full worked example tying every rule to a realistic, multi-source project.

One Staging Model per Source Table — Nothing More, Nothing Less

The staging layer's job is narrower than it might first appear, and the narrowness is the entire point. A staging model exists to do exactly one thing: take one raw source table and produce a clean, renamed, correctly-typed version of it — with zero joins to any other table, and zero business logic of any kind. If you can describe what a staging model does without using the word "and," it's a staging model. The moment "and" creeps in — "renames columns and joins to customers" — the model has drifted out of the staging layer's job description.

with source as (

    select * from {{ source('stripe', 'payments') }}

),

renamed as (

    select
        id                                  as payment_id,
        order_id,
        amount_cents                        as amount_cents,
        lower(status)                       as payment_status,
        cast(created as timestamp)          as created_at,
        cast(updated as timestamp)          as updated_at

    from source
    where not _fivetran_deleted

)

select * from renamed

Notice everything this model deliberately does not do. It does not join to anorders table to check whether order_id is valid — that's arelationships test, not staging-layer logic. It does not compute whether the payment succeeded in a business sense beyond normalizing the raw status string — that belongs downstream, where "succeeded" might mean something more nuanced than one status value. The only judgment calls made here are structural: rename, cast, drop rows that shouldn't exist in a clean dataset at all.

Staging models are almost always views, and that is deliberate

Staging models default to view materialization for a reason connected directly to their scope: they are thin, so recomputing them on every downstream query is cheap, and keeping them as views means they never go stale relative to the raw source — exactly what a cleanup layer that many other models build on should guarantee. A staging model materialized as a table introduces a lag between when raw data lands and when the cleaned version reflects it, which is rarely what you want this close to the source.

Where Joins and Business Logic Actually Live

If staging is defined by what it must not do, intermediate is defined by what it exists to do: combine two or more staging models and apply the business logic that turns clean, independent tables into a single, coherent, business-meaningful shape. This is where a join betweenstg_stripe__payments and stg_app__orders happens. This is where aCASE expression deciding whether a payment counts as "successful" for revenue-reporting purposes lives. This is where a fan-out risk from a one-to-many join gets deliberately handled, rather than silently inherited by whatever mart references the model later.

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

with payments as (

    select * from {{ ref('stg_stripe__payments') }}

),

orders as (

    select * from {{ ref('stg_app__orders') }}

),

joined as (

    select
        payments.payment_id,
        payments.order_id,
        payments.amount_cents,
        payments.payment_status,
        orders.customer_id,
        orders.order_placed_at,
        -- business logic: what counts as a "successful" payment for
        -- revenue purposes is narrower than the raw Stripe status --
        -- a refunded-then-recaptured payment is still "succeeded" at
        -- the Stripe API level but should not double-count as revenue
        case
            when payments.payment_status = 'succeeded'
                 and payments.amount_cents > 0
            then true
            else false
        end as counts_as_revenue

    from payments
    left join orders
        on payments.order_id = orders.order_id

)

select * from joined

Two details in that file are worth naming explicitly. First,materialized='ephemeral' is a common, though not universal, choice for intermediate models — they are implementation details, stepping stones toward a mart, not something an analyst or a BI tool is ever meant to query directly. Making them ephemeral keeps the warehouse's schema browser free of clutter that nobody outside the dbt project itself should ever need to see. Second, the counts_as_revenue column is exactly the kind of business logic that has no business living in either stg_stripe__payments (which knows nothing about how this company defines revenue) or a downstream mart (which would then have to re-derive or duplicate this logic in every mart that needs it).

Intermediate models are also where reusable business logic gets consolidated

A frequent real-world pattern: two different marts both need "orders joined to their most recent payment status," but one mart is finance-facing (revenue reporting) and the other is operations-facing (fulfillment tracking). Without an intermediate layer, that join and its associated business logic get written twice, and the two copies drift apart over months as each team edits its own mart independently — a classic, hard-to-detect source of two dashboards disagreeing about numbers that should match. With an intermediate model sitting between staging and both marts, the join and its logic exist in exactly one place, and both marts build on the same, single source of truth for that specific piece of business logic.

Marts Are Organized by Business Domain, Not by Source System

The marts layer is the final, business-facing output of a dbt project — the tables a BI tool, an analyst's ad hoc query, or a downstream reverse-ETL sync actually reads. The single most important organizational decision at this layer, and the one beginners most often get backwards, is that marts are grouped by business domain — finance, marketing, product, operations — not by which raw source system fed them. A finance mart might combine data that originated from Stripe, an internal Postgres database, and a marketing platform; from a business user's perspective, none of that source-system provenance matters. What matters is that finance/fct_revenueanswers a finance question completely, regardless of how many raw systems its inputs came from.

models/marts/
├── finance/
│   ├── fct_revenue.sql
│   ├── fct_payments.sql
│   └── dim_invoices.sql
├── marketing/
│   ├── fct_attributed_conversions.sql
│   └── dim_campaigns.sql
└── product/
    ├── fct_feature_usage.sql
    └── dim_users.sql

-- NOT this (organizing marts by source system instead of business domain):
models/marts/
├── stripe/
│   └── fct_payments.sql       -- forces a finance analyst to know
├── postgres_app/                 which SOURCE fed a table, rather
│   └── fct_orders.sql            than which BUSINESS QUESTION it
└── marketing_platform/           answers -- the wrong organizing axis
    └── fct_campaigns.sql

The distinction between fct_ and dim_ models — fact tables and dimension tables — carries over from traditional dimensional modeling, and both live inside the same domain-scoped folders. A fact table records events or transactions with measures that get aggregated (an order's amount, a payment's value, a session's duration). A dimension table records descriptive attributes about an entity that facts reference (a customer's name and signup date, a product's category, a campaign's channel and budget).

{{
  config(
    materialized='table',
    tags=['finance', 'daily']
  )
}}

with payments as (

    select * from {{ ref('int_payments_joined_to_orders') }}
    where counts_as_revenue

),

final as (

    select
        payment_id,
        order_id,
        customer_id,
        amount_cents,
        order_placed_at,
        date_trunc('day', order_placed_at) as revenue_date

    from payments

)

select * from final

Notice this mart reads from int_payments_joined_to_orders — the intermediate model from Part 03 — and does very little beyond that: filter to rows that count as revenue, and shape the final columns a finance dashboard needs, including a convenience revenue_datecolumn for daily rollups. All of the actual join and business-rule complexity already happened one layer up. This is what a well-layered mart looks like: thin, because the hard work was already done by staging and intermediate, and materialized as a table because it's the layer BI tools query constantly and repeatedly.

A Raw Schema Change Should Touch Exactly One File

Here is the concrete scenario that makes every rule in Parts 02 through 04 worth the extra files and discipline: the team that owns the Stripe integration renames a column, or Stripe itself changes a field name in an API version bump, or an internal Postgres app database gets a column renamed during a migration. This happens to every real project, repeatedly, over its lifetime. The question is what has to change in the dbt project in response.

-- BEFORE the source change:
-- raw.stripe.payments.amount_cents  (the column staging reads from)

-- AFTER the source change:
-- raw.stripe.payments.amount        (renamed upstream, outside dbt's control)

-- Without a staging layer -- every model that read directly from
-- {{ source('stripe', 'payments') }} and referenced amount_cents
-- breaks simultaneously: the intermediate join, every mart that
-- touched payment amounts, possibly a dozen files across the project.

-- With a staging layer -- exactly ONE file needs a one-line change:

-- models/staging/stripe/stg_stripe__payments.sql
select
    id                                  as payment_id,
    order_id,
    amount                              as amount_cents,   -- <-- only this line changes
    lower(status)                       as payment_status,
    cast(created as timestamp)          as created_at
from {{ source('stripe', 'payments') }}
where not _fivetran_deleted

-- Every downstream model -- int_payments_joined_to_orders, fct_revenue,
-- fct_payments -- still references stg_stripe__payments.amount_cents,
-- completely unaware that the raw column's name ever changed at all.

This is the entire economic case for the layering discipline in one example. The staging layer's sole job — one model per raw source, alias every column to a stable, dbt-side name — is precisely what makes it possible to absorb an upstream rename, a type change, or even a wholesale migration to a new source system, by touching one file instead of auditing the entire project for every place a raw column name might have leaked downstream.

A Model's Name Should Tell You Its Layer and Its Job, Without Opening the File

Because a model's filename is its object name with no separate naming step, the naming convention a team adopts is not cosmetic — it is the single biggest lever for keeping a project navigable as it grows past the size where any one person remembers what every model does. The convention that has become close to a de facto industry standard follows a simple, layer-encoded pattern.

The double underscore in stg_stripe__payments is deliberate, not a typo — it visually separates "which source system" from "which table within that source," which matters the moment a project has more than one source with an overlapping table name. stg_stripe__paymentsand stg_app__payments (an internal payments-adjacent table in the main application database, say) are unambiguous at a glance, whereas stg_payments andstg_payments_2 tell you nothing about where either one actually comes from.

-- Two different source systems, each with a "subscriptions" table:

stg_stripe__subscriptions.sql    -- from Stripe's subscriptions API
stg_recurly__subscriptions.sql   -- from a legacy Recurly billing system
                                  -- still being migrated off of

-- Without the source prefix, both would collide on the same name,
-- or force an arbitrary disambiguator (stg_subscriptions_v2) that
-- carries no information about which source it actually is.

Intermediate models deliberately have no fixed suffix the way marts do, because what an intermediate model is for varies too much to compress into two or three prefix categories — it might be a join, a deduplication step, a pivot, a business-rule application. The convention instead leans on a clear, descriptive name: int_payments_joined_to_orders,int_customer_orders_deduplicated, int_events_pivoted_by_type — each name states the specific transformation happening in that one file, since a reader can't infer it from a fixed prefix the way they can with stg_ or fct_.

Folders Should Mirror the Layering, Down to the Source and Domain Level

A folder structure that mirrors the layering conventions from Parts 02 through 04 is what makes the naming convention actually navigable rather than just theoretically parseable. Staging folders are typically subdivided by source system, since a new source system usually means a batch of new staging models arriving together. Marts folders are subdivided by business domain, per Part 04's reasoning. Intermediate folders are commonly subdivided by the domain they primarily serve, since most intermediate logic exists in service of one specific downstream mart area even if it's technically reusable elsewhere.

models/
├── staging/
│   ├── stripe/
│   │   ├── stg_stripe__payments.sql
│   │   ├── stg_stripe__subscriptions.sql
│   │   └── stg_stripe__sources.yml
│   ├── app_postgres/
│   │   ├── stg_app__orders.sql
│   │   ├── stg_app__customers.sql
│   │   └── stg_app__sources.yml
│   └── marketing_platform/
│       ├── stg_marketing__campaigns.sql
│       ├── stg_marketing__ad_spend.sql
│       └── stg_marketing__sources.yml
├── intermediate/
│   ├── finance/
│   │   └── int_payments_joined_to_orders.sql
│   └── marketing/
│       └── int_campaigns_joined_to_conversions.sql
└── marts/
    ├── finance/
    │   ├── fct_revenue.sql
    │   ├── fct_payments.sql
    │   ├── dim_invoices.sql
    │   └── finance_models.yml
    └── marketing/
        ├── fct_attributed_conversions.sql
        ├── dim_campaigns.sql
        └── marketing_models.yml

Each source folder under staging/ typically carries its own YAML file declaring that source's tables (per the source() mechanics covered earlier in this track), keeping a source's declaration physically next to the staging models that consume it rather than in one enormous, project-wide sources file that becomes unwieldy to navigate.

models:
  my_project:
    staging:
      +materialized: view
    intermediate:
      +materialized: ephemeral
    marts:
      +materialized: table

What Actually Breaks Down as a Project Grows, and How Layering Prevents It

A project with twenty models can survive almost any amount of organizational sloppiness — a single contributor can hold the whole thing in their head, and a wrong turn is easy to spot and fix. A project with two hundred models cannot rely on any one person's memory, and the failure modes that show up at that scale are specific and predictable.

A second, less obvious scaling pressure is on the DAG itself: at 200+ models, the dependency graph between staging, intermediate, and marts becomes something a person can no longer trace by eye. This is exactly why the layering conventions matter more, not less, as a project grows — a predictable three-layer flow (source → staging → intermediate → marts, strictly in that direction, never backwards) is what keeps dbt docs generate's dependency graph interpretable even at hundreds of nodes, because every edge in that graph is expected to point in one direction.

A useful heuristic for a growing team: whenever the same join or business rule is written for the second time across two different marts, that is the trigger to extract it into a shared intermediate model immediately, rather than waiting for a third or fourth duplicate to accumulate. Catching duplication at two copies, not four, is what keeps the intermediate layer doing its actual job — a small number of well-named, single-purpose models — instead of becoming a second staging layer with unclear boundaries of its own.

A Realistic Multi-Source Project, End to End

Bringing every rule in this module together: a project ingesting from three real source systems — Stripe for payments, a Postgres application database for orders and customers, and a marketing platform for campaign and spend data — flowing through staging, into intermediate, and landing in a finance mart and a marketing mart.

models/
├── staging/
│   ├── stripe/
│   │   ├── _stripe__sources.yml
│   │   └── stg_stripe__payments.sql
│   ├── app_postgres/
│   │   ├── _app__sources.yml
│   │   ├── stg_app__orders.sql
│   │   └── stg_app__customers.sql
│   └── marketing_platform/
│       ├── _marketing__sources.yml
│       ├── stg_marketing__campaigns.sql
│       └── stg_marketing__ad_spend.sql
├── intermediate/
│   ├── finance/
│   │   └── int_payments_joined_to_orders.sql
│   └── marketing/
│       └── int_orders_attributed_to_campaigns.sql
└── marts/
    ├── finance/
    │   ├── fct_revenue.sql
    │   └── dim_customers.sql
    └── marketing/
        └── fct_campaign_roi.sql

Follow one full chain from raw source to final mart. stg_app__orders andstg_app__customers each clean exactly one Postgres source table.stg_stripe__payments cleans the one Stripe source table. None of the three know anything about each other yet — that's intentional, per Part 02.

with source as (
    select * from {{ source('app_postgres', 'orders') }}
),
renamed as (
    select
        id                             as order_id,
        customer_id,
        campaign_id,
        cast(placed_at as timestamp)   as order_placed_at,
        lower(status)                  as order_status
    from source
)
select * from renamed

The finance-facing intermediate model, int_payments_joined_to_orders, is exactly the model built in Part 03 — it joins Stripe payments to Postgres orders and applies the "counts_as_revenue" business rule. A second, marketing-facing intermediate model performs a different join entirely, connecting orders to the campaign that is credited with driving them:

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

with orders as (
    select * from {{ ref('stg_app__orders') }}
),

campaigns as (
    select * from {{ ref('stg_marketing__campaigns') }}
),

attributed as (
    select
        orders.order_id,
        orders.customer_id,
        orders.order_placed_at,
        campaigns.campaign_id,
        campaigns.campaign_name,
        campaigns.channel
    from orders
    left join campaigns
        on orders.campaign_id = campaigns.campaign_id
)

select * from attributed

Notice this second intermediate model reuses stg_app__orders — the same staging model that feeds the finance-side intermediate model. This is exactly the payoff of a thin, reusable staging layer: one clean staging model serves two entirely different downstream domains, each with its own business logic, without either domain needing to re-clean the raw orders table itself.

{{ config(materialized='table', tags=['marketing']) }}

with attributed_orders as (
    select * from {{ ref('int_orders_attributed_to_campaigns') }}
),

ad_spend as (
    select * from {{ ref('stg_marketing__ad_spend') }}
),

revenue as (
    select * from {{ ref('int_payments_joined_to_orders') }}
    where counts_as_revenue
),

per_campaign_revenue as (
    select
        attributed_orders.campaign_id,
        sum(revenue.amount_cents) as attributed_revenue_cents
    from attributed_orders
    join revenue
        on attributed_orders.order_id = revenue.order_id
    group by 1
),

final as (
    select
        ad_spend.campaign_id,
        ad_spend.campaign_name,
        ad_spend.spend_cents,
        coalesce(per_campaign_revenue.attributed_revenue_cents, 0) as attributed_revenue_cents,
        coalesce(per_campaign_revenue.attributed_revenue_cents, 0) - ad_spend.spend_cents as roi_cents
    from ad_spend
    left join per_campaign_revenue
        on ad_spend.campaign_id = per_campaign_revenue.campaign_id
)

select * from final

This final mart is a striking illustration of the whole module's thesis: it reads fromtwo different intermediate models, each encoding a different piece of business logic (revenue-counting rules, campaign attribution rules), themselves built from staging models spanning three unrelated raw source systems — and none of that layered history is visible in the mart's own SQL, which reads as a clean, short summary of "campaign spend versus attributed revenue." That readability is not an accident. It is the direct, designed consequence of every rule in Parts 02 through 04 being followed correctly one layer at a time.

One schema.yml per Folder, Not One Giant File for the Whole Project

Everything in Parts 06 through 08 addressed how .sql model files are named and organized. A parallel question, easy to overlook until a project has grown past a few dozen models, is how the YAML files declaring sources, tests, and descriptions should themselves be organized. The same layering logic applies here too: YAML should live physically close to the models it documents, split by folder rather than centralized into one enormous project-wide file.

A single schema.yml at the root of models/ containing every model's tests and descriptions across the entire project technically works — dbt does not require YAML to be split at all — but it becomes a genuine liability past a certain size. Every contributor editing any model's tests touches the same file, multiplying merge conflicts, and finding a specific model's test configuration means scrolling or searching through a file that has nothing to do with the folder structure the models themselves live in.

models/
├── staging/
│   ├── stripe/
│   │   ├── _stripe__sources.yml       -- source() declarations for Stripe
│   │   ├── stg_stripe__payments.sql
│   │   └── stg_stripe__payments.yml   -- tests/descriptions for this one model
│   └── app_postgres/
│       ├── _app__sources.yml
│       ├── stg_app__orders.sql
│       └── stg_app__orders.yml
└── marts/
    └── finance/
        ├── fct_revenue.sql
        ├── fct_payments.sql
        └── _finance__models.yml       -- tests/descriptions for the whole domain

Two conventions are worth calling out in that layout. First, a leading underscore on files like_stripe__sources.yml is a common, purely cosmetic convention that sorts configuration-only files above the .sql files they describe in most file browsers and IDEs, making them easy to spot at a glance. Second, notice the granularity difference between staging and marts: staging YAML is often one file per model (stg_stripe__payments.ymlsitting directly next to stg_stripe__payments.sql), while a mart-level domain folder more commonly consolidates several models' tests into one shared file (_finance__models.yml covering both fct_revenue andfct_payments), since mart-level models within one domain are usually reviewed and edited together anyway.

A concrete example: the per-model YAML for one staging model

To make this tangible, here is what stg_stripe__payments.yml actually contains, sitting directly beside stg_stripe__payments.sql in the same folder from Part 09's worked example. Everything a reader needs to understand this one model — its columns, its tests, its purpose — lives in exactly two files, next to each other, rather than being split across a model-specific SQL file and a project-wide YAML file that could be anywhere.

version: 2

models:
  - name: stg_stripe__payments
    description: >
      One row per Stripe payment attempt, cleaned and renamed from the
      raw Stripe payments source. No business logic -- see
      int_payments_joined_to_orders for revenue-counting rules.
    columns:
      - name: payment_id
        description: Primary key -- Stripe's payment id, renamed from the raw "id" column.
        tests:
          - unique
          - not_null
      - name: order_id
        description: Foreign key to the order this payment is associated with.
        tests:
          - not_null
      - name: payment_status
        description: Lowercased, normalized Stripe payment status.
        tests:
          - accepted_values:
              values: ['succeeded', 'pending', 'failed', 'refunded']

A new contributor looking for anything about this model — what it does, what's tested, what business logic to expect downstream — never needs to search the project. They open thestripe/ folder and find both files sitting together, which is the entire practical payoff of organizing YAML this way rather than centralizing it.

The underlying test in both directions is the same one this whole module keeps returning to: does splitting (or not splitting) reduce how many unrelated things a single file forces a contributor to touch or scroll past? One YAML file per staging model passes that test, since staging models are numerous and independent. One YAML file per mart model, in a domain where the marts are few and closely related, usually fails it — the split adds file-hopping overhead without actually isolating anything meaningfully unrelated.

Five Misconceptions About dbt Project Structure

Three Companies, Three Layering Lessons

5 Interview Questions — With Complete Answers

The Structural Mistakes That Get Expensive Later

Structural Problems You Will Hit — And Exactly Why They Happen