dbt lab: project layout that scales

The first dbt project feels fine with six models in one folder. Six months later you have orders_final_v3.sql, a mart that selects from another mart that selects from a staging model named after a person, and a Slack thread titled “which orders is the real orders.” Layout is not aesthetics. Layout is how a team finds grain, ownership, and the next safe change. This part is a lab for project shape that still makes sense when the third analyst joins.

This is Part 1 of dbt project lab. We set folders, naming, and layering before we lean hard on tests and builds. Part 2 writes the first models and tests end to end. If you need the concept layer first, read AMS’s conceptual dbt overview in the pipelines series context and keep SQL, Data quality, and Data pipelines nearby. Full map: Learn.

What you’ll learn

What a scalable dbt layout optimizes for (change, not cleverness)
Staging, intermediate, and marts in lab language with clear entry rules
Naming conventions that survive copy-paste and onboarding
Where YAML, seeds, and macros belong without becoming a junk drawer
A worked mini project tree for orders and customers
Common layout mistakes and a practice plan to refactor safely

What “scales” means here

Scales does not mean “supports a Fortune 500 on day one.” It means:

A new teammate can guess where a model lives from its job
You can change a source column without spelunking twenty files named “final”
Marts stay thin and business-shaped; raw quirks stay near the source
Tests and docs attach next to the models they protect
Refactors are boring instead of heroic

If your layout requires tribal knowledge (“oh, we always put finance things in intermediate for historical reasons”), it does not scale past the people who remember the story.

Rule of thumb: Each model should have one job and one grain sentence. If you need “and also” more than once, split the model or rename it until the job is honest.

The three layers (and what each is allowed to do)

Staging: source-shaped, lightly cleaned

Staging models sit closest to sources. One staging model per source table is a strong default. Jobs allowed:

Rename columns to team standards
Cast types
Trim strings, parse dates, standardize nulls
Add basic derived flags that are still source-local (for example is_deleted from a source status)

Jobs not allowed in staging if you want to stay sane: multi-source joins that invent a new business entity, heavy KPI math, “fix the dashboard while we are here.” Staging should feel almost boring. That boredom is the point. When the source schema changes, you want one obvious file to touch.

Intermediate: private building blocks

Intermediate models reshape data for reuse: fan out or fan in joins, bridge tables, progressive enrichment that is not yet a productized mart. Consumers outside the project should rarely query intermediate tables. Name them so people do not put them on dashboards by accident.

Use intermediate when two marts would otherwise duplicate the same complex join. Do not use intermediate as a parking lot for half-finished ideas. If a model has no downstream ref, ask whether it should exist.

Marts: business-shaped, consumer-ready

Marts are what analysts, BI tools, and reverse ETL should touch. Grain is a business entity or a clear fact: customers, orders, order lines, monthly subscription snapshot. Naming can lean business (fct_orders, dim_customers) rather than source system names.

Marts may join staging and intermediate models. They should not re-implement source renames that belong in staging. If you find yourself casting the same timestamp in three marts, push that down.

dbt project layers diagram showing sources flowing into staging, then intermediate, then marts with BI consumers on top

A folder tree you can copy

Exact trees vary by team. This lab tree is a proven starting shape:

models/
  staging/
    jaffle_shop/
      _jaffle_shop__sources.yml
      stg_jaffle_shop__customers.sql
      stg_jaffle_shop__orders.sql
      stg_jaffle_shop__payments.sql
  intermediate/
    int_orders_pivoted_to_payments.sql
  marts/
    core/
      dim_customers.sql
      fct_orders.sql
      _core__models.yml
  utilities/
    all_dates.sql

Notes on the pattern:

Source system subfolders under staging keep messy boundaries clear when you add a second CRM later.
Double underscore in names (stg_jaffle_shop__orders) is a common convention: layer prefix, source, table.
YAML beside the models documents and tests the group without one giant project-wide YAML novel.
Mart subfolders by domain (core, finance, marketing) beat one flat marts dump after year one.

Naming conventions that reduce meetings

Object	Pattern	Example
Source	system + table in sources.yml	`jaffle_shop.orders`
Staging model	`stg_<source>__<table>`	`stg_jaffle_shop__orders`
Intermediate	`int_<verb_or_entity>_...`	`int_orders_payments_joined`
Fact mart	`fct_<entity>`	`fct_orders`
Dimension mart	`dim_<entity>`	`dim_customers`
Primary key column	`<entity>_id` or agreed surrogate	`order_id`

Avoid final, v2, new, use_this, and people’s names in model names. Versions belong in git history. If you must sunset a mart, plan a rename with a deprecation window, not a permanent _old twin that everyone still uses.

Sources.yml is part of the layout

Declare sources explicitly. Freshness checks and source-level tests hang off that declaration. A minimal shape:

version: 2

sources:
  - name: jaffle_shop
    database: raw
    schema: jaffle_shop
    tables:
      - name: orders
        columns:
          - name: id
            tests:
              - not_null
              - unique
      - name: customers
      - name: payments

Staging models should source('jaffle_shop', 'orders') rather than hardcoding database.schema.table strings in every file. Hardcoded locations make environment promotion painful.

Materializations by layer (starter defaults)

Defaults differ by warehouse cost and team taste. A sensible lab starting point:

Staging: views (cheap, always current to upstream tables)
Intermediate: views or ephemeral when thin; tables if reused heavily and expensive
Marts: tables (or incremental tables when volume demands it)

Put layer defaults in dbt_project.yml so individual models stay quiet unless they need an exception. Exceptions should be rare and commented.

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

Worked example: orders lab layout decisions

Business goal: one trusted fct_orders and dim_customers for a small shop. Sources: customers, orders, payments.

Grain sentences first

Model	Grain sentence	Layer
`stg_jaffle_shop__customers`	One row per customer from the shop system	staging
`stg_jaffle_shop__orders`	One row per order header	staging
`stg_jaffle_shop__payments`	One row per payment attempt	staging
`int_payments_pivoted_to_orders`	One row per order with payment method totals	intermediate
`dim_customers`	One row per customer for analytics	marts
`fct_orders`	One row per order with customer and payment rollups	marts

Dependency sketch

Sources feed staging only. Intermediate reads staging. Marts read staging and intermediate. No mart reads a raw source. No staging reads a mart. That acyclic discipline keeps lineage readable in dbt docs and in code review.

What we refuse to do in v1

No “god model” that joins every table for convenience.
No BI-facing view of staging tables.
No finance mart until core grains are stable.
No shared macro that hides a business rule with a cute name until we need it twice.

Filled project tree and grain table for staging intermediate and mart models in a small orders lab

YAML, docs, and tests as layout citizens

Co-locate YAML with the models it describes. A _core__models.yml next to marts is easier than a 2,000-line root file. Document:

Model description with grain sentence first
Column descriptions for keys and metrics consumers will see
Tests for uniqueness and not-null on primary keys (Part 2 goes deeper)

Docs sites do not replace conversation, but they beat archaeology. If your company also uses a catalog, treat dbt docs as the transform-facing truth and keep metric names aligned with Metrics that matter.

Macros, seeds, and snapshots: park them deliberately

macros/ for repeated SQL patterns. Do not macro-ify a one-off.
seeds/ for small reference CSVs (country codes, status maps). Not for million-row facts.
snapshots/ when you need type-2 history and the source does not give it. Snapshots are powerful and easy to misuse; start without them unless slowly changing dimensions are a real requirement.

A cluttered macros/ folder becomes a second language. Prefer readable SQL in models until repetition hurts twice.

Environments and folders (dev versus prod)

Layout inside models/ is not the same as environment strategy, but they interact. Use targets (dev, prod) and developer schemas so experiments do not overwrite production tables. Custom schemas per layer can help governance (for example staging in stg, marts in marts). Pick a pattern, write it in the README, and stick to it long enough for muscle memory.

Common mistakes

One folder for everything. Fast on day one, hostile on day ninety.
Marts that select from other teams’ staging without agreement. Hidden coupling.
Business logic in staging and again in marts. Divergent definitions.
Intermediate models exposed to BI. Temporary join tables become permanent APIs.
Names with versions and adjectives. orders_final_new_correct is a cry for help.
Skipping grain sentences. Two “customer” models with different uniqueness rules.
Giant YAML at repo root. Merge conflicts and fear of editing docs.

How to practice this week

Day 1: Draw your current model graph on paper. Circle anything that jumps layers the wrong way (mart to staging, staging to mart).
Day 2: Write grain sentences for your ten most used models. Fix the ones you cannot finish in one sentence.
Day 3: Propose a target folder tree in a short RFC. Do not move files yet. Collect objections.
Day 4: Move one source’s staging models into the new pattern. Update refs. Run the project subset.
Day 5: Add or fix sources.yml for that system. Document materialization defaults in dbt_project.yml.

Quick recap

Layout scales when people can predict where logic lives.
Staging cleans sources; intermediate builds private blocks; marts serve business grain.
Names encode layer and intent; git encodes history, not filenames.
Sources, YAML, and materialization defaults are part of architecture.
One job, one grain sentence, one obvious folder.

Next: Part 2 implements first models and tests on this layout: staging SQL, refs into a mart, unique and not-null tests, and a small build you can explain in review.

Sources

dbt Docs: How we structure our dbt projects
dbt Docs: Sources
dbt Docs: Materializations
dbt Docs: dbt_project.yml
dbt Docs: How we structure our dbt projects: Staging