dbt Project Structure: Staging, Intermediate, and Marts Done Right

You’re here to ship a clean dbt project structure that scales. Use three layers: staging for cleaned source tables, intermediate for modeled joins/aggregations, and marts for star-schema facts and dims. Organize by clear folders, map them to database schemas via dbt_project.yml, and enforce naming conventions with macros and tests. This dbt project layout shows the exact folder structure, schema mapping, example SQL, and project configuration you can copy today—plus how to handle seeds, snapshots, and one-vs-many repositories. If you need a refresher on fundamentals, see the dbt Tutorial for Beginners or browse the dbt topic hub.

An opinionated structure of a dbt project that scales

Goal: fast onboarding, predictable lineage, and maintainable analytics. Here’s the folder structure I’ve used across teams:

your_dbt_repo/
├── dbt_project.yml
├── packages.yml
├── models/
│   ├── staging/
│   │   ├── stripe/
│   │   │   ├── stg_stripe__charges.sql
│   │   │   └── stg_stripe__customers.sql
│   │   └── app/
│   │       ├── stg_app__events.sql
│   │       └── stg_app__users.sql
│   ├── intermediate/
│   │   ├── int_orders_enriched.sql
│   │   └── int_user_first_touch.sql
│   └── marts/
│       ├── core/
│       │   ├── dim_users.sql
│       │   └── fct_orders.sql
│       └── marketing/
│           ├── dim_campaigns.sql
│           └── fct_attribution.sql
├── seeds/
│   └── country_codes.csv
├── macros/
│   └── surrogate_key.sql
├── snapshots/
│   └── orders_status_snapshot.sql
└── tests/
    └── schema.yml

This project structure keeps domain boundaries explicit and moves complexity upward from stage to mart in small, reviewed steps. As your dbt project grows, these layers prevent model sprawl.

Map folders to database schemas with dbt_project.yml

Each folder should build into its own schema. This reduces clutter and makes intent obvious in your data warehouse. Use the configuration file dbt_project.yml to set this once:

# dbt_project.yml (project configuration)
name: your_project
version: 1.0.0
profile: your_profile
models:
  your_project:
    +materialized: view
    staging:
      +schema: stg
      +tags: ["stage"]
    intermediate:
      +schema: int
      +tags: ["intermediate"]
    marts:
      +schema: mart
      core:
        +materialized: table
        +tags: ["mart", "core"]
seeds:
  +schema: ref

Result: models/staging/* deploy to schema stg; intermediate to int; marts to mart. You can also configure per-subfolder (e.g., staging/stripe to stg_stripe) when you need finer control. Example:

# dbt_project.yml (per-source staging schemas)
models:
  your_project:
    staging:
      +schema: stg
      stripe:
        +schema: stg_stripe
      app:
        +schema: stg_app

For example, do you have separate staging schemas? If your platform supports many schemas cheaply, yes—stg_stripe and stg_app keep ownership crisp. Otherwise, a single stg with folders per source is fine.

Layer responsibilities and handoffs

Staging: cleanly model raw data, source by source

Staging is per source, per table. Keep consistent naming conventions: stg_{source}__{entity}. Don’t join across sources here—keep each stage clear and testable. If you need an overview of ref() vs source(), see dbt ref() vs source(): Model-to-Table References Explained.

-- models/staging/stripe/stg_stripe__charges.sql
with source as (
  select * from {{ source('stripe', 'charges') }}
),
renamed as (
  select
    id as charge_id,
    customer as customer_id,
    created_at::timestamp as created_at,
    amount_cents/100.0 as amount_usd,
    status
  from source
)
select * from renamed;

Use simple, readable sql. Add lightweight tests in schema.yml for not null and unique keys. For heavy JSON unpack or large tables (e.g., 40M rows), keep transformations minimal and push heavy work to intermediate.

Intermediate: cross-table logic and reuse

Intermediate models assemble staged inputs into reusable building blocks. Example: joining orders to items and payments, computing first_user_touch, or standardizing time zones before facts. Keep these models domain-scoped and focused on specific outputs.

-- models/intermediate/int_orders_enriched.sql
with orders as (
  select * from {{ ref('stg_app__orders') }}
), items as (
  select * from {{ ref('stg_app__order_items') }}
), payments as (
  select * from {{ ref('stg_stripe__charges') }}
), joined as (
  select
    o.order_id,
    o.user_id,
    o.created_at as order_ts,
    sum(i.qty * i.unit_price) as gross_item_revenue,
    sum(case when p.status = 'succeeded' then p.amount_usd else 0 end) as paid_amount
  from orders o
  left join items i on i.order_id = o.order_id
  left join payments p on p.charge_id = o.charge_id
  group by 1,2,3
)
select * from joined;

Marts: dimensional models for analysts

The mart layer presents conformed dimensions and fact tables aligned to analytics use cases. Adopt clear naming conventions: dim_* and fct_*. Define the grain in comments and enforce with tests. If you manage metrics centrally, review the dbt Semantic Layer approach and keep mart models metrics-ready.

-- models/marts/core/fct_orders.sql
{{ config(materialized='incremental', unique_key='order_id') }}

with enriched as (
  select * from {{ ref('int_orders_enriched') }}
)
select
  order_id,
  user_id,
  order_ts,
  gross_item_revenue,
  paid_amount,
  gross_item_revenue - paid_amount as discount_amount
from enriched
{% if is_incremental() %}
  where order_ts > (select coalesce(max(order_ts), '1900-01-01') from {{ this }})
{% endif %};

These are your mart models consumed by BI. Keep documentation and tests tight. For detailed testing patterns (singular, generic, and unit), defer to dbt Tests: Complete Guide.

How we structure dbt schemas in the data warehouse

Options for mapping folders to schema depend on your team and platform. Two pragmatic patterns:

For example, do you have separate staging schemas? Larger orgs often do: stg_app, stg_stripe, stg_salesforce. Smaller teams can keep a single stg schema and split by source at the folder level.

Seeds: do we stage them?

@claire asked: do you follow similar principles for seed data tables and create staging tables for them? Treat seeds as small reference data (often a csv). Land them once into a ref schema and reference directly from staging or intermediate. Create a staging model on top only if you need casts/renames to keep consistency or to align naming conventions. Otherwise, keep it simple.

Snapshots: where and why

Place snapshot definitions in the snapshots/ folder and use a consistent naming pattern. Snapshots track change over time for slowly changing records that your source overwrites. For a complete playbook, see dbt Snapshots. The dbt developer hub provides canonical reference material; use that alongside your team conventions. Reference snapshots in intermediate or marts depending on where the changing attributes become critical.

Macros and packages: standardize and share

Create a macro for common patterns like surrogate keys or date spine generation. Keep it small and reviewed. Example:

-- macros/surrogate_key.sql
{% macro sk(columns) %}
  md5(concat_ws('||', {{ columns | join(", ") }}))
{% endmacro %}

Use in models:

select {{ sk(["user_id", "coalesce(email, '')"]) }} as user_sk, * from {{ ref('stg_app__users') }}

Declare external package usage and any dependencies in packages.yml. Keep versions pinned to prevent unexpected changes downstream.

# packages.yml
packages:
  - package: dbt-labs/dbt_utils
    version: 1.3.0

Naming conventions that prevent drift

• Models: stg_{source}__{entity}, int_{topic}_{detail}, dim_*, fct_*.
• Columns: primary keys end with _id; timestamps end with _at; booleans start with is_ or has_.
• Schemas: layer-first, optionally with domain (e.g., mart_finance).

Write the grain at the top of each model as a comment, and add tests enforcing it. Good naming conventions minimize surprises during a query review or incident.

FAQ: schemas, SQL generation, and repositories

Does dbt create the SQL query to generate it?

dbt compiles your models into SQL and orders them via the dependency graph, but you write the queries. Jinja + macros help templatize logic, and ref()/source() define lineage. For ref/source specifics, see Model-to-Table References.

For example, do you have separate staging schemas?

Often yes for larger orgs: stg_app, stg_stripe, stg_salesforce. Smaller teams can keep a single stg schema and split by source at the folder level.

How do you structure your marts & database schemas?

Use mart per domain: mart_core, mart_marketing, mart_finance. Within each, facts and dims. Keep intermediate shared across domains when logic overlaps; otherwise, domain-specific.

How to configure your dbt repository (one or many)?

One repository (monorepo) is simpler for coordination, code review, shared macros, and central CI. Multiple repositories work when teams have distinct lifecycles, isolated SLAs, or regulated data. If you split, standardize contracts (naming conventions, tags, versioned outputs) and treat cross-repo handoffs as stable interfaces.

I’m curious… you laid out how you structure dbt projects – how do you map these to database schemas?

Map each top-level folder to a schema via dbt_project.yml. Optionally prefix by domain. Example: staging/stripe → stg_stripe; marts/marketing → mart_marketing.

Ready to implement these conventions in your dbt project?

Copy the folder tree, align dbt_project.yml schemas, and migrate models layer-by-layer. Add tests before moving on.

Ready to transform your dbt workflow?

Automate CI, use tags to scope runs, and add unit tests. Keep PRs small: one model, one purpose.

Performance notes for large tables

• Stage as views; push heavy work to intermediate or mart with incremental tables.
• On a 40M-row orders table, compute expensive aggregates in incremental facts and limit backfills by date.
• Partition/cluster where supported; choose a stable unique_key for incremental models.

For query tuning, push filters down, avoid cross joins, and audit join keys. Keep columns minimal in each layer until needed. This pays off in every warehouse engine and keeps costs predictable in your data warehouse.

End-to-end example: putting it together

Walkthrough: a project in data build tool dbt, staging raw data from Stripe and an app database, enriching, then marts.

1. staging/stripe/stg_stripe__charges.sql: cast types, expose clear columns (no joins).
2. staging/app/stg_app__orders.sql: dedupe on natural key; keep all rows.
3. intermediate/int_orders_enriched.sql: join staged inputs; compute paid_amount.
4. marts/core/fct_orders.sql: incremental fact by order_id; documented grain.

Add a snapshot for entities like customers when the source overwrites attributes and you need history. Keep staging models simple; move core logic to intermediate/marts.

Directory tree and config recap

Here’s the minimal directory reminder teams paste into reviews. This is the backbone that helps structure dbt for growth in a dbt project:

models/
├── staging/
├── intermediate/
└── marts/

Governance: tests, docs, and CI

• Add not_null and unique on keys in staging; more semantic tests in marts. See the dbt Tests guide.
• Document models with descriptions and columns at each layer.
• Use tags (stage, intermediate, mart) to scope CI runs.

If you’re new to snapshots, our snapshot playbook covers hands-on patterns alongside vendor docs.

Common pitfalls and best practices

• Pitfall: joining across sources in staging. Fix: keep joins in intermediate.
• Pitfall: mixing marts and intermediate in one schema. Fix: map folders to separate schemas.
• Pitfall: unclear model grains. Fix: document grain and enforce with tests.
• Best practices: keep models short, isolate responsibility, and add ownership metadata.

Cheat sheet: terms and when to use them

• staging: per-source cleaning layer; staging models live here.
• intermediate: shared transformations and joins.
• marts: final BI-facing layer; mart delivers star schema outputs.
• macros: shared logic for repeatable patterns.
• seeds: small static tables, typically csv-based.

Putting policy in writing

Codify “how we structure our dbt projects” in your README: folder mapping, schema targets, tags, and review checklists. This becomes your team contract for growth. When someone asks how to structure your project in data teams with multiple domains, point them to that doc.

Final checklist to roll out

1. Create folders: staging/ by source, intermediate/, marts/ by domain.
2. Set schemas in dbt_project.yml. Include tags: stage, intermediate, mart.
3. Define naming conventions and grains. Add tests before merges.
4. Extract heavy logic to intermediate/marts. Keep staging thin.
5. Add a macro for surrogate keys; pin package versions in packages.yml.
6. Decide repository topology (mono vs multi) and document interfaces.

Why this works

This dbt project approach cleanly separates concerns by layer, keeps dependencies explicit, and reflects ownership in schemas. It’s easy to onboard, review, and change. It also reduces blast radius: a bad change in intermediate won’t silently rewrite your mart without review. You can extend it as your analytics dataset grows without rethinking the entire structure.

Two closing notes: 1) Use tags and selectors to run only what you need (faster CI). 2) Keep an eye on graph sprawl; refactors should simplify the DAG, not entangle it.

Glossary and one-liners

• dbt project: your codebase for modeling transformations in dbt.
• dbt_project.yml: the central configuration for models, tests, and docs.
• project structure: how folders map to schemas, ownership, and reviews.

Ready to implement? Start a small PR in your dbt project: add staging for one source, an intermediate join, and a single fact. Then iterate. Want practice cementing these ideas? Try the free graded exercises at /practice.