dbt Project Structure: A Layout That Survives Two Years and Forty Models

Two years in, every dbt project tells you the truth about how it was built. I have opened projects where I could find the revenue definition in ninety seconds, and projects where four models had “final” in the name and the actual answer was in the fifth.

The difference was never talent. It was whether the layout encoded a rule that the compiler could enforce, or whether it encoded a preference that only survived while the person who wrote it was still on the team.

Forty models is the size where this starts to hurt. Under twenty you can hold the whole graph in your head. Over a hundred you have already either fixed it or given up. Forty is the window where a weekend of renaming buys you two years of not guessing.

This is the layout I use, the rules that make it hold, and the parts that rot anyway.

Three layers, and the rule that makes them real

Staging, intermediate, marts. That part is not controversial. What makes it work is a single dependency rule: a model may only ref its own layer or the layer below it.

flowchart TD
  src["Sources (raw)"] --> stg["staging: 1:1, renamed, typed"]
  stg --> int["intermediate: joins and logic"]
  stg --> marts["marts: grain-defining tables"]
  int --> marts
  marts --> bi["BI, reverse ETL, ML"]
  marts -.->|"never"| stg

The dotted line is the whole design. Everything else is naming.

Without that rule you do not have layers, you have three folders. I have watched a project where a staging model referenced a mart to “reuse the customer segmentation.” Six months later the DAG had a cycle in spirit if not in syntax: the segmentation depended on orders, orders depended on staged orders, and staged orders depended on the segmentation. Every full refresh needed a documented manual run order. That is a project with a runbook instead of a graph.

Enforce it. dbt-project-evaluator has a check for this, or you can write a twenty-line test over manifest.json that fails CI when a staging model’s parents include anything outside source(). Either works. A wiki page does not.

Staging: one model per source table, and nothing clever

A staging model does four things: select from exactly one source, rename columns to your conventions, cast types, and apply light coercion like trimming or lowercasing emails. That is the whole job description.

-- models/staging/shopify/stg_shopify__orders.sql
with source as (
    select * from {{ source('shopify', 'orders') }}
),

renamed as (
    select
        id                                as order_id,
        customer_id                       as customer_id,
        lower(trim(email))                as email,
        -- Shopify sends money as strings. Cast here, once, for everyone.
        cast(total_price as numeric(18, 2)) as order_total,
        cast(created_at as timestamp)     as ordered_at,
        cast(updated_at as timestamp)     as source_updated_at,
        _fivetran_deleted                 as is_deleted
    from source
)

select * from renamed
where not coalesce(is_deleted, false)

Materialize these as views by default. They are cheap, they always reflect the source, and they cost nothing to rebuild. The exception is a source table large enough that every downstream model pays for re-scanning it — then make that one staging model a table and write down why in the model’s description.

The rule I hold hardest: no joins in staging. The instant stg_shopify__orders joins to stg_shopify__customers, staging stops being a mechanical translation of the source and becomes business logic wearing a staging prefix. Then nobody trusts that staging is a faithful rename, and everyone starts reading the SQL instead of the name. That is the whole cost.

Double underscore between source and entity — stg_shopify__orders, not stg_shopify_orders — is not aesthetics. It gives you an unambiguous split point when you script anything over model names, and it survives sources whose names contain underscores.

Intermediate: the layer people skip and then need

Intermediate models exist for one reason: a piece of logic is used by more than one mart, or it is complex enough that inlining it makes the mart unreadable. If neither is true, do not create one.

-- models/intermediate/finance/int_orders__joined_to_payments.sql
-- Grain: one row per order. Payments are aggregated up to the order.
with orders as (
    select * from {{ ref('stg_shopify__orders') }}
),

payments as (
    select
        order_id,
        sum(case when status = 'success' then amount else 0 end) as amount_captured,
        sum(case when status = 'refunded' then amount else 0 end) as amount_refunded,
        max(case when status = 'success' then paid_at end)       as last_paid_at,
        count(*)                                                 as payment_attempts
    from {{ ref('stg_stripe__payments') }}
    group by 1
)

select
    orders.order_id,
    orders.customer_id,
    orders.ordered_at,
    orders.order_total,
    coalesce(payments.amount_captured, 0) as amount_captured,
    coalesce(payments.amount_refunded, 0) as amount_refunded,
    payments.last_paid_at,
    coalesce(payments.payment_attempts, 0) as payment_attempts
from orders
left join payments using (order_id)

Notice the aggregate happens before the join. That is the fan-out discipline from SQL joins and fan-out applied structurally: intermediate models are where you collapse a many-to-one relationship down to the grain the mart needs, so the mart itself is a wide select with no surprise row multiplication.

Intermediate models are private. Nothing outside their own subject area should reference them. Two ways to enforce that: name the subfolder after the mart family that owns it, and set +access: private on the folder in dbt_project.yml if you are on a version that supports model access. A dashboard querying int_orders__joined_to_payments directly is a load-bearing dependency on a table you thought you could refactor.

Marts: named for grain, organized by consumer

A mart declares a grain and defends it. fct_orders is one row per order. dim_customers is one row per customer. If you cannot say the grain in one sentence, the model is not finished.

# models/marts/finance/_finance__models.yml
models:
  - name: fct_orders
    description: |
      One row per order, at the time the order was placed.
      Payments aggregated to order grain. Excludes test orders.
    config:
      materialized: incremental
      unique_key: order_id
      on_schema_change: append_new_columns
    columns:
      - name: order_id
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
              config:
                severity: warn

Name for grain, not for the source system. fct_orders survives a migration from Shopify to a custom checkout. shopify_orders_final does not, and the day you migrate you will have fct_orders_v2 living next to it for eighteen months.

I organize mart subfolders by consuming team — finance, marketing, product — with a core folder for the conformed dimensions everyone shares. The alternative, organizing by entity, is tidier on paper and worse in practice, because ownership is what you actually need to look up when something breaks at 3 a.m.

The tests on the key are the grain contract, exactly the split described in my dbt testing strategy: unique and not_null at error severity on the primary key of every mart, and near-nothing else at error severity.

The files that are not models

models/
  staging/
    shopify/
      _shopify__sources.yml     # source definitions + freshness
      _shopify__models.yml      # docs + tests for stg_ models
      stg_shopify__orders.sql
  intermediate/
    finance/
      _int_finance__models.yml
      int_orders__joined_to_payments.sql
  marts/
    core/
      dim_customers.sql
    finance/
      fct_orders.sql
      _finance__models.yml
macros/
snapshots/
tests/

One YAML per folder, prefixed with an underscore so it sorts to the top. Not one YAML per model — that produces four hundred files and a merge conflict every time two people touch the same source. Not one schema.yml for the whole project either; that file becomes three thousand lines and nobody can review a diff in it.

The _ prefix is doing real work. In an editor’s file tree, config sorts above content, and you read the contract before the implementation.

Configure in dbt_project.yml, override in the model

Folder-level defaults belong in dbt_project.yml. Per-model exceptions belong in the model. If you find yourself writing the same {{ config() }} block in nine files, that is a folder default you have not written yet.

# dbt_project.yml
models:
  jaffle_analytics:
    +on_schema_change: append_new_columns
    staging:
      +materialized: view
      +schema: staging
    intermediate:
      +materialized: ephemeral   # or view; see note below
      +schema: intermediate
    marts:
      +materialized: table
      +schema: analytics
      finance:
        +tags: ['finance', 'sox']

A word on ephemeral for intermediate models. It keeps your warehouse clean, but it inlines the SQL as a CTE into every consumer, so a five-level ephemeral chain compiles into a query you cannot debug and the optimizer may not handle well. My default now is view for intermediate, and ephemeral only for genuinely trivial models. The debuggability is worth the schema clutter — you can select * from a view when a number is wrong at 8 a.m.

The +schema settings give you analytics_staging, analytics_intermediate, analytics in production. Grant BI tools access to the mart schema only. Physical access control is a better layer boundary than any naming convention, and it is the same principle as writing down producer obligations in a data contract.

Where teams get this wrong

Joins in staging. The first one always looks harmless. It is the crack that turns a translation layer into a logic layer, and after that nobody can tell where a business rule lives.

A mart that refs another mart’s internals. Marts referencing conformed dimensions in core is fine and expected. A finance mart referencing a marketing mart’s intermediate model is a hidden cross-team dependency that will break during someone else’s refactor.

Models named _final, _v2, _new. Every one of these is an unfinished migration. Pick the name that describes the grain, replace the old model, and delete it. A rename is one PR. Two models that disagree is a permanent tax.

The god model. Twenty-three refs, four hundred lines, one CTE per department. It is not a model, it is a missing intermediate layer. When I see more than a dozen upstream refs, I start splitting.

Folder structure copied without the dependency rule. Three folders named correctly and no enforcement is the most common failure I see. The layout is a promise; only CI can make it true.

Configuring materializations model by model. Nine identical config blocks means the default is wrong. Fix dbt_project.yml and delete the blocks.

FAQ

How many models is too many for one project?

Model count matters less than the ratio of marts to intermediate models. If a project has three hundred models and eight marts, most of it is scaffolding nobody consumes. I would rather see one project with six hundred models and a clean dependency rule than four projects gluing themselves together across dbt_project.yml packages.

Should intermediate models be ephemeral or views?

Views, in most cases. Ephemeral keeps the warehouse tidy but produces compiled SQL that is hard to read and impossible to query directly when you are debugging a wrong number. Reserve ephemeral for small models with one consumer.

Do I need one staging model per source table even if I never use most columns?

Yes. Selecting only the columns you need today means every new requirement is a two-model change instead of a one-model change. The cost of a view over a table you already pay to store is effectively zero, and it keeps the 1:1 promise intact.

Where do snapshots live?

In their own top-level snapshots/ directory, taken against staging models rather than raw sources, so the type casting and the deleted flag are already applied. That interacts with strategy choice in ways worth reading about separately in dbt snapshots for SCD Type 2.

How do I restructure a project that is already a mess without freezing feature work?

One subject area at a time, using the existing model name as an alias so downstream consumers do not break. Ship the new fct_orders, point the old name at it with a view, migrate consumers over a sprint, then drop the view. Slim CI makes each of those PRs cheap to validate because only the touched subgraph gets built.

Should each team have its own dbt project?

Not until you have a real ownership boundary and a real interface between them. Splitting a project splits the DAG, and the seam becomes a source-freshness handshake you have to operate. One project with clear folders and CODEOWNERS solves this for most teams under a thousand models.

What this means for your pipelines

A project layout is a set of constraints you are willing to have a machine enforce. Staging is 1:1 with sources and never joins. Intermediate is private to its subject area. Marts declare a grain and defend it with two tests. Everything else — folder names, YAML placement, the underscore convention — is ergonomics on top of those three rules.

The payoff shows up when someone new asks where revenue is defined. In a project with real layers, the answer is a path, and they can read the model top to bottom and believe it. In a project without them, the answer is a conversation, and conversations do not scale past the tenure of the person having them.

If your project is already tangled, do not plan a rewrite. Pick the one rule with the most violations — usually joins in staging or marts referencing other marts’ internals — write the CI check, mark the existing violations as accepted exceptions, and fix them one PR at a time. The check stops the bleeding on day one, which matters more than the cleanup finishing on any particular date.