How do I structure a large dbt project for maintainability and scalability?

For a large dbt project, optimize for three things: clear ownership, a predictable DAG, and a small stable consumer-facing surface. dbt Labs’ recommended pattern is a layered flow of staging → intermediate → marts, which is a good foundation for scaling.

1. Use layers with strict responsibilities

A practical structure is:

models/
├── staging/
│   ├── salesforce/
│   │   ├── _salesforce__sources.yml
│   │   ├── stg_salesforce__accounts.sql
│   │   ├── stg_salesforce__opportunities.sql
│   │   └── stg_salesforce.yml
│   ├── stripe/
│   │   ├── _stripe__sources.yml
│   │   ├── stg_stripe__customers.sql
│   │   └── stg_stripe__payments.sql
│   └── app/
│
├── intermediate/
│   ├── finance/
│   │   ├── int_orders_enriched.sql
│   │   └── int_payments_reconciled.sql
│   └── marketing/
│       └── int_customer_attribution.sql
│
└── marts/
    ├── finance/
    │   ├── dim_customers.sql
    │   └── fct_orders.sql
    ├── marketing/
    │   ├── dim_campaigns.sql
    │   └── fct_attribution.sql
    └── product/
        └── fct_product_usage.sql

Staging

Purpose: make raw sources consistent.

Do things like:

• rename columns
• cast data types
• standardize timestamps
• lightly clean values
• unpack simple JSON
• establish/test primary keys

Avoid putting business logic and cross-source joins here. dbt's guidance emphasizes staging as the reusable "atoms" of the project.

A good naming convention is:

stg_<source>__<entity>

For example:

stg_salesforce__accounts
stg_salesforce__opportunities
stg_stripe__payments

Intermediate

Purpose: encapsulate reusable transformation logic.

This is where you put things such as:

int_orders_enriched
int_customer_lifetime_value
int_subscription_events
int_orders_with_payments

If three marts need the same complicated join or business transformation, do it once in intermediate rather than three times in the marts.

This is one of the biggest maintainability wins in a large project: business logic has a single home. dbt also recommends modularizing repeated logic rather than allowing DAGs to become tangled.

Marts

Purpose: expose stable, business-friendly datasets to consumers.

Usually organize these by business domain, not by source system:

marts/
├── finance/
├── marketing/
├── sales/
├── product/
└── customer/

Typical models:

dim_customer
dim_product
fct_order
fct_subscription
fct_web_session

The mart layer is where business logic and consumer-oriented modeling belong; fact/dimension naming is a common dbt convention.

---

2. Make the DAG flow in one direction

Ideally:

RAW
 │
 ├── Salesforce
 ├── Stripe
 └── Application DB
       │
       ▼
   STAGING
       │
       ▼
 INTERMEDIATE
       │
       ▼
    MARTS
       │
       ├── BI
       ├── Data Science
       └── Analytics

Try to prevent things like:

mart → staging
mart → intermediate → another mart
staging → mart

The important principle is that higher-level models shouldn't become dependencies of lower-level models.

That makes lineage understandable and makes refactoring substantially safer.

---

3. Organize staging by source, marts by domain

This distinction is extremely useful at scale.

Staging follows your data producers:

staging/
├── salesforce/
├── stripe/
├── netsuite/
└── application/

Marts follow your business consumers:

marts/
├── finance/
├── sales/
├── marketing/
├── product/
└── customer/

That means when someone asks:

"Where does Stripe customer data get cleaned?" you know where to look.

And when someone asks:

"Where is the canonical customer model?" you also know where to look.

This source-vs-domain separation is a useful way to prevent a large project from becoming a giant flat directory.

---

4. Treat marts as your public API

One of the most important architectural decisions is to make the mart layer the consumer-facing contract.

For example:

stg_salesforce__accounts
       ↓
int_customer_accounts
       ↓
dim_customer

Downstream analysts should generally query:

dim_customer

rather than:

stg_salesforce__accounts

This gives you freedom to replace Salesforce, restructure intermediate models, or change implementation details without breaking every dashboard.

Think of:

marts/

as the API of your analytics platform.

---

5. Put configuration at the folder level

Don't repeat configuration in hundreds of individual models.

For example:

models:
  my_project:

    staging:
      +materialized: view
      +schema: staging

    intermediate:
      +schema: intermediate

    marts:
      +materialized: table
      +schema: marts

      finance:
        +tags:
          - finance

      marketing:
        +tags:
          - marketing

Then override individual models only when there's a good reason.

This gives you sensible defaults while keeping exceptions explicit.

For large projects, folder-level configuration also makes ownership, schemas, materialization and execution strategy much easier to reason about.

---

6. Be deliberate about materialization

Don't automatically make everything a table.

A reasonable starting point is:

But performance should drive exceptions.

For example:

stg_orders
    ↓ view

int_orders_enriched
    ↓ table

fct_orders
    ↓ incremental

If an intermediate model is computationally expensive and referenced by many downstream models, materializing it can be worthwhile rather than recomputing it repeatedly.

---

7. Keep each model focused

A useful rule:

One model should have one understandable purpose. Bad:

fct_customer.sql

with 1,500 lines that:

• joins 15 tables
• calculates attribution
• calculates revenue
• calculates churn
• calculates marketing costs
• deduplicates events
• handles currency conversion
• generates customer segments

Better:

stg_orders
stg_payments
stg_customers

int_orders_enriched
int_customer_revenue
int_customer_activity

dim_customer
fct_orders
fct_customer_activity

More models aren't inherently bad. Unnecessary coupling is bad.

---

8. Establish naming conventions early

For example:

stg_<source>__<entity>

int_<business_concept>

dim_<entity>
fct_<event>

rpt_<specific_report>   # optional

Examples:

stg_stripe__charges
stg_app__users

int_customer_orders
int_subscription_status

dim_customer
dim_product

fct_order
fct_subscription

Don't obsess over the exact prefixes. Consistency matters more than the particular convention.

---

9. Keep tests and documentation close to the models

For example:

marts/
└── finance/
    ├── fct_orders.sql
    └── fct_orders.yml

Your YAML should describe:

• what the model represents
• its grain
• important columns
• ownership
• key constraints/tests
• business definitions

At minimum, test important identifiers:

columns:
  - name: order_id
    data_tests:
      - not_null
      - unique

For a large project, don't treat tests as an afterthought. They're part of the model's contract.

Keeping YAML alongside the relevant directory/model also makes navigation and maintenance easier.

---

10. Define grain explicitly

This is particularly important for large analytics teams.

For every fact model, you should be able to answer:

What does one row represent? For example:

fct_orders
Grain: one row per completed order

versus:

fct_order_items
Grain: one row per order line item

A huge percentage of analytics bugs come from accidentally joining models with different grains.

I'd put the grain directly in the model documentation.

---

11. Separate reusable logic from macros

Use a model when the transformation represents a meaningful data concept.

Use a macro when you're abstracting repeated SQL behavior.

For example, this belongs in a model:

int_customer_lifetime_value

while something like:

generate_surrogate_key(...)

is a good macro.

Don't turn every complicated SQL expression into a macro. Excessive macros can make a dbt project harder to understand because the actual SQL becomes hidden behind layers of Jinja.

---

12. Scale CI around the DAG

Once you have hundreds or thousands of models, running everything for every pull request becomes painful.

Use dbt's DAG selectors to test the affected portion of the graph.

Conceptually:

changed model
     ↓
parents / children
     ↓
targeted CI

You can also use tags to partition domains:

tag:finance
tag:marketing
tag:product

The goal is that a developer working on marketing doesn't have to rebuild the entire warehouse just to validate a small change.

---

13. Establish ownership

At large scale, every domain should have clear ownership.

For example:

Finance
  ├── dim_customer
  ├── fct_invoice
  └── fct_payment

Marketing
  ├── dim_campaign
  └── fct_attribution

Product
  ├── fct_product_event
  └── fct_subscription

Then define:

• owner/team
• SLA
• expected freshness
• downstream consumers
• criticality

This becomes increasingly important as the number of models grows.

---

14. Watch for architectural smells

I'd periodically look for these:

🚩 Staging models containing business logic

stg_orders
  → customer segmentation
  → revenue recognition
  → marketing attribution

Move those downstream.

🚩 Multiple marts implementing the same rule

fct_sales        → calculates customer revenue
fct_marketing    → calculates customer revenue
fct_finance      → calculates customer revenue

Consider:

int_customer_revenue
        ↓
   ┌────┼────┐
   ↓    ↓    ↓
sales marketing finance

🚩 Analysts querying staging directly

That usually means the consumer-facing layer isn't doing its job.

🚩 Giant models

If a model is hundreds/thousands of lines and has multiple conceptual steps, look for intermediate models.

🚩 Circular-ish dependencies

If the DAG starts looking like a web rather than a directed pipeline, you're probably losing your layer boundaries.

dbt's own guidance emphasizes using staging/intermediate/mart layers and abstracting repeated logic to keep the DAG modular.

---

A good "large project" blueprint

If I were starting a new project intended to eventually reach hundreds or thousands of models, I'd start roughly here:

analytics/
├── dbt_project.yml
├── packages.yml
│
├── models/
│   │
│   ├── staging/
│   │   ├── salesforce/
│   │   │   ├── _sources.yml
│   │   │   ├── stg_salesforce__accounts.sql
│   │   │   └── stg_salesforce.yml
│   │   ├── stripe/
│   │   └── app/
│   │
│   ├── intermediate/
│   │   ├── customer/
│   │   ├── finance/
│   │   └── marketing/
│   │
│   └── marts/
│       ├── customer/
│       ├── finance/
│       ├── marketing/
│       ├── product/
│       └── sales/
│
├── macros/
├── tests/
├── snapshots/
├── seeds/
└── analyses/

Then enforce a few simple architectural rules:

1. Sources → staging → intermediate → marts.
2. Staging is source-oriented; marts are domain-oriented.
3. Marts are the stable consumer interface.
4. Repeated business logic gets one canonical implementation.
5. Every important model has an explicit grain.
6. Every important key has tests.
7. Configuration is inherited from folders wherever possible.
8. CI runs selectively against the DAG.
9. Ownership is explicit.
10. Optimize the DAG for comprehension, not for minimizing model count.

The last point is probably the most important. A large dbt project is easier to maintain when you have more small, composable models with obvious responsibilities than fewer enormous models containing hidden logic. That's essentially the purpose of dbt's modular modeling philosophy.

If you're already dealing with a 500+ model dbt project, the architecture I'd recommend is somewhat different from what I'd use for a 50-model project—particularly around domain boundaries, model contracts, CI, ownership, incremental models, and DAG performance.

stg_orders
  → customer segmentation
  → revenue recognition
  → marketing attribution

Move those downstream.

🚩 Multiple marts implementing the same rule

fct_sales        → calculates customer revenue
fct_marketing    → calculates customer revenue
fct_finance      → calculates customer revenue

Consider:

int_customer_revenue
        ↓
   ┌────┼────┐
   ↓    ↓    ↓
sales marketing finance

🚩 Analysts querying staging directly

That usually means the consumer-facing layer isn't doing its job.

🚩 Giant models

If a model is hundreds/thousands of lines and has multiple conceptual steps, look for intermediate models.

🚩 Circular-ish dependencies

If the DAG starts looking like a web rather than a directed pipeline, you're probably losing your layer boundaries.

dbt's own guidance emphasizes using staging/intermediate/mart layers and abstracting repeated logic to keep the DAG modular.

---

A good "large project" blueprint

If I were starting a new project intended to eventually reach hundreds or thousands of models, I'd start roughly here:

analytics/
├── dbt_project.yml
├── packages.yml
│
├── models/
│   │
│   ├── staging/
│   │   ├── salesforce/
│   │   │   ├── _sources.yml
│   │   │   ├── stg_salesforce__accounts.sql
│   │   │   └── stg_salesforce.yml
│   │   ├── stripe/
│   │   └── app/
│   │
│   ├── intermediate/
│   │   ├── customer/
│   │   ├── finance/
│   │   └── marketing/
│   │
│   └── marts/
│       ├── customer/
│       ├── finance/
│       ├── marketing/
│       ├── product/
│       └── sales/
│
├── macros/
├── tests/
├── snapshots/
├── seeds/
└── analyses/

Then enforce a few simple architectural rules:

1. Sources → staging → intermediate → marts.
2. Staging is source-oriented; marts are domain-oriented.
3. Marts are the stable consumer interface.
4. Repeated business logic gets one canonical implementation.
5. Every important model has an explicit grain.
6. Every important key has tests.
7. Configuration is inherited from folders wherever possible.
8. CI runs selectively against the DAG.
9. Ownership is explicit.
10. Optimize the DAG for comprehension, not for minimizing model count.

The last point is probably the most important. A large dbt project is easier to maintain when you have more small, composable models with obvious responsibilities than fewer enormous models containing hidden logic. That's essentially the purpose of dbt's modular modeling philosophy.

Once you have hundreds or thousands of models, running everything for every pull request becomes painful.

Use dbt's DAG selectors to test the affected portion of the graph.

Conceptually:

changed model
     ↓
parents / children
     ↓
targeted CI

You can also use tags to partition domains:

tag:finance
tag:marketing
tag:product

The goal is that a developer working on marketing doesn't have to rebuild the entire warehouse just to validate a small change.

---

13. Establish ownership

At large scale, every domain should have clear ownership.

For example:

Finance
  ├── dim_customer
  ├── fct_invoice
  └── fct_payment

Marketing
  ├── dim_campaign
  └── fct_attribution

Product
  ├── fct_product_event
  └── fct_subscription

Then define:

• owner/team
• SLA
• expected freshness
• downstream consumers
• criticality

This becomes increasingly important as the number of models grows.

---

14. Watch for architectural smells

I'd periodically look for these:

🚩 Staging models containing business logic

stg_orders
  → customer segmentation
  → revenue recognition
  → marketing attribution

Move those downstream.

🚩 Multiple marts implementing the same rule

fct_sales        → calculates customer revenue
fct_marketing    → calculates customer revenue
fct_finance      → calculates customer revenue

Consider:

int_customer_revenue
        ↓
   ┌────┼────┐
   ↓    ↓    ↓
sales marketing finance

🚩 Analysts querying staging directly

That usually means the consumer-facing layer isn't doing its job.

🚩 Giant models

If a model is hundreds/thousands of lines and has multiple conceptual steps, look for intermediate models.

🚩 Circular-ish dependencies

If the DAG starts looking like a web rather than a directed pipeline, you're probably losing your layer boundaries.

dbt's own guidance emphasizes using staging/intermediate/mart layers and abstracting repeated logic to keep the DAG modular.

---

A good "large project" blueprint

If I were starting a new project intended to eventually reach hundreds or thousands of models, I'd start roughly here:

Structuring a large dbt (data build tool) project requires moving away from a single, cluttered directory and adopting a modular, tiered architecture . The gold standard for achieving this is the layering approach (often mirroring Medallion architecture: Staging → Intermediate → Marts), combined with a clean organizational schema.

Here is how to structure your project for maximum maintainability and scalability:

• Staging Layer (models/staging/ ): The foundation. This is where you connect directly to your raw source data. Rules here: 1-to-1 with source tables, rename columns to a consistent standard (e.g., snake_case, standard timestamps), cast data types, and do light cleaning. No business logic or heavy joins belong here.
• Intermediate Layer (models/intermediate/ ): The workspace for business logic. This is optional for simple transformations, but crucial for large projects. Here you perform heavy joins, unions, and aggregations across multiple staging models. Models in this layer are usually building blocks (ephemeral or view) and are rarely exposed directly to end-users or BI tools.
• Marts Layer (models/marts/ ): The business-facing layer. This is split into business domains (e.g., finance/, marketing/, core/ ). Models here are final, highly polished, and optimized for BI consumption. Tables or incremental models live here, representing clear business entities like dim_customers or fct_orders.
• Semantic Layer / Metrics (models/semantic/ or metrics/ ): Where you define centralized metrics, dimensions, and entities using dbt Semantic Layer definitions so that definitions remain consistent across all reporting tools.
• Analyses (analyses/ ): For ad-hoc SQL queries or exploratory scripts that you want to track in version control but don't want dbt to compile/materialize into your warehouse.
• Macros (macros/ ): Reusable SQL snippets, custom generic tests, or utility functions that keep your code DRY (Don't Repeat Yourself).
• Snapshots (snapshots/ ): For capturing slowly changing dimensions (SCD Type 2) from source tables over time.
• Tests (tests/ ): Custom singular SQL tests or configuration files for generic tests (like not_null, unique , or relationships).

Best Practices for Scalability

• Naming Conventions: Enforce strict prefixes for your models to make lineage instantly recognizable at a glance (e.g., stg_ for staging, int_ for intermediate, dim_ for dimensions, fct_ for facts).
• Use sources.yml Wisely: Always define your raw sources in sources.yml files inside your staging folders. This allows you to use the {{ source() }} function, track freshness, and attach source-level tests.
• Leverage config and groups : Use your dbt_project.yml to apply default materializations (e.g., views for staging/intermediate, tables/incremental for marts) and directory-level tags or access controls.
• Keep Graphs Clean: Avoid circular dependencies and long, tangled DAGs (Directed Acyclic Graphs) by strictly adhering to the Staging → Intermediate → Marts flow. Staging models should only point to sources, and marts should only point to intermediate or staging models.

If you want, tell me:

• What data warehouse are you using (Snowflake, BigQuery, Databricks, etc.)?
• Are you migrating an existing messy project or starting from scratch?

I can provide a concrete directory tree template or specific materialization strategies tailored to your stack.

For a large dbt project, optimize for three things: clear ownership, predictable dependencies, and small units of logic. The classic staging → intermediate → marts pattern is still a strong foundation, and dbt Labs continues to recommend it.

1. Use layers with strict responsibilities

A good high-level structure is:

models/
├── staging/
│   ├── salesforce/
│   │   ├── _salesforce__sources.yml
│   │   ├── _salesforce__models.yml
│   │   ├── stg_salesforce__accounts.sql
│   │   └── stg_salesforce__opportunities.sql
│   ├── stripe/
│   │   ├── _stripe__sources.yml
│   │   ├── _stripe__models.yml
│   │   ├── stg_stripe__customers.sql
│   │   └── stg_stripe__payments.sql
│   └── app/
│
├── intermediate/
│   ├── customers/
│   │   ├── int_customers__orders.sql
│   │   └── int_customers__subscriptions.sql
│   └── orders/
│       ├── int_orders__enriched.sql
│       └── int_orders__payments.sql
│
└── marts/
    ├── finance/
    │   ├── dim_accounts.sql
    │   ├── fct_revenue.sql
    │   └── _finance__models.yml
    ├── marketing/
    │   ├── dim_campaigns.sql
    │   └── fct_attribution.sql
    └── product/
        ├── dim_products.sql
        └── fct_product_usage.sql

The important distinction is:

• Staging: make raw data consistent. Rename columns, cast types, standardize timestamps, light cleanup. Generally one model per source table.
• Intermediate: reusable transformations, joins, deduplication, complicated business logic, and entity construction.
• Marts: business-facing data products. Facts, dimensions, and models that BI/analytics users actually consume.

This separation prevents business logic from being duplicated across dozens of downstream models.

2. Organize staging by source, marts by business domain

This is one of the most useful scaling decisions.

staging/
├── salesforce/
├── stripe/
├── postgres/
└── zendesk/

marts/
├── finance/
├── marketing/
├── sales/
├── product/
└── customer/

Why?

A source-oriented staging layer answers:

"Where does Salesforce opportunity data get cleaned?" A domain-oriented mart layer answers:

"Where is the canonical revenue model?" That distinction becomes extremely valuable when you have hundreds of models and multiple teams.

dbt's project-evaluator guidance similarly recommends source-specific staging directories and domain-oriented model organization.

3. Establish naming conventions early

Make model names communicate their role:

stg_stripe__customers
stg_salesforce__accounts

int_orders__enriched
int_orders__with_payments

fct_orders
fct_revenue
dim_customers
dim_products

rpt_executive_revenue

I particularly like the double underscore in staging:

stg_<source>__<entity>

because it separates the source from the business entity.

For marts, use:

fct_<business_process>
dim_<business_entity>

For example:

fct_orders
fct_payments
fct_subscriptions

dim_customer
dim_product
dim_sales_rep

These conventions make the DAG and warehouse much easier to navigate.

4. Treat each model as a contract

A model should have one obvious purpose.

Bad:

int_customer_order_revenue_and_marketing_attribution.sql

This is probably doing too much.

Prefer:

int_customers__orders.sql
int_orders__attribution.sql
fct_customer_revenue.sql

A useful test is:

Can I describe what this model does in one sentence? If not, split it.

Don't take this too far, though. You don't want a DAG containing 400 tiny models whose only purpose is renaming two columns.

5. Make grain explicit

This is probably the most important maintainability rule for analytics models.

Every fact model should have an explicit grain.

For example:

-- Grain: one row per order
select
    order_id,
    customer_id,
    order_created_at,
    order_amount
from ...

Then test it:

models:
  - name: fct_orders
    description: "One row per customer order."
    columns:
      - name: order_id
        tests:
          - unique
          - not_null

The same principle applies to intermediate models.

If a model's grain isn't obvious, downstream users will eventually join it incorrectly.

6. Keep business logic out of staging

Avoid:

stg_salesforce__opportunities
    ↓
    17 joins
    ↓
    revenue recognition logic
    ↓
    sales commission calculation

Instead:

stg_salesforce__opportunities
             ↓
    int_opportunities__enriched
             ↓
       fct_revenue

Staging should form a relatively stable interface between volatile source systems and your analytics models. dbt specifically recommends staging as a modular layer that prevents repeated source-cleaning logic downstream.

7. Use dbt_project.yml for defaults

Don't configure every model independently.

For example:

models:
  my_project:

    staging:
      +materialized: view
      +schema: staging

    intermediate:
      +materialized: view
      +schema: intermediate

    marts:
      +materialized: table
      +schema: marts

      finance:
        +tags:
          - finance

      marketing:
        +tags:
          - marketing

Then override only where necessary.

For a huge fact table:

{{ config(
    materialized='incremental',
    unique_key='order_id'
) }}

The principle is:

Convention by default, exceptions explicitly configured.

8. Don't materialize everything the same way

A sensible starting point is:

But performance should determine exceptions.

For example, an intermediate model referenced by 30 downstream models might be worth materializing even if intermediates normally aren't. Conversely, a tiny mart may not need a physical table.

9. Separate reusable business logic from reporting logic

Suppose Finance and Marketing both need customer lifetime value.

Don't create:

finance_customer_ltv.sql
marketing_customer_ltv.sql

with duplicated SQL.

Instead:

int_customers__revenue.sql
        ↓
fct_customer_lifetime_value
        ↓
finance reporting
marketing reporting

The DAG should encourage reuse, not duplication. dbt's guidance on modular DAGs explicitly recommends abstracting logic that's used across multiple models.

10. Be disciplined about macros

Macros are powerful, but don't turn your project into a Jinja programming language.

Good macro candidates:

generate_surrogate_key()
safe_divide()
convert_timezone()
standardize_column_name()

Bad candidates are business processes that become impossible to understand without jumping through five macro files.

A good rule:

If the logic represents data transformation, prefer a model. If it represents reusable SQL generation, consider a macro.

11. Put documentation and tests next to the models

For example:

marts/finance/
├── fct_revenue.sql
├── dim_accounts.sql
└── _finance__models.yml

Document:

• What the model represents
• Its grain
• Ownership
• Important columns
• Business definitions
• Known limitations

And test the important invariants:

columns:
  - name: account_id
    tests:
      - not_null

  - name: revenue
    tests:
      - not_null

At the mart layer, add more meaningful business tests—not just mechanical not_null checks.

12. Design CI around the DAG

Large dbt projects become painful when every pull request runs everything.

Use selectors/tags and state-aware CI so a PR primarily builds:

modified models
    +
their upstream dependencies
    +
their downstream consumers

rather than the entire warehouse.

For example, tag domains:

+tags: ["finance"]

and have separate jobs for appropriate domains or environments.

Also consider using a project-evaluation package to continuously detect structural problems such as naming inconsistencies and misplaced tests.

13. Establish ownership at the domain level

At scale, the architecture should roughly mirror team ownership:

marts/
├── finance/       → Finance Analytics
├── marketing/     → Marketing Analytics
├── sales/         → Sales Analytics
└── product/       → Product Analytics

Then define clear ownership for shared models:

shared/
    customer/
    calendar/
    geography/

This reduces the classic large-project problem where everyone feels responsible for everything—and therefore nobody really owns anything.

14. Keep the DAG intentional

A healthy DAG tends to look roughly like:

              raw sources
             /     |     \
            /      |      \
        staging  staging  staging
           \       |       /
            \      |      /
             intermediate
                  |
             marts/domain
              /    |    \
            BI   metrics  ML

Be suspicious of DAGs that look like:

A → B → C → D → E
 \       ↘
  F → G → H → I
       ↘   ↗
        J

with dozens of cross-domain dependencies.

Cross-domain dependencies aren't inherently bad, but excessive ones create coupling and make seemingly harmless changes propagate everywhere.

A practical "large project" blueprint

If I were starting a dbt project expected to grow to 500–2,000+ models, I'd start with:

dbt_project/
├── models/
│   ├── staging/
│   │   ├── app/
│   │   ├── salesforce/
│   │   ├── stripe/
│   │   └── ...
│   │
│   ├── intermediate/
│   │   ├── customer/
│   │   ├── order/
│   │   ├── subscription/
│   │   └── ...
│   │
│   └── marts/
│       ├── finance/
│       ├── marketing/
│       ├── sales/
│       ├── product/
│       └── ...
│
├── macros/
├── snapshots/
├── seeds/
├── tests/
├── analyses/
├── dbt_project.yml
├── packages.yml
└── README.md

Then enforce roughly these rules:

1. One source table → one staging model.
2. Staging contains cleaning, not business logic.
3. Intermediate models solve reusable transformation problems.
4. Marts are business-facing and domain-owned.
5. Every important model has an explicit grain.
6. Facts and dimensions use consistent naming.
7. Tests and documentation live close to the models.
8. Defaults live in dbt_project.yml; exceptions are explicit.
9. Macros are for reusable SQL generation, not hiding business logic.
10. CI builds only the relevant part of the DAG.
11. Domains have clear ownership.
12. Cross-domain dependencies are deliberate.

The biggest mistake is trying to make the folder structure clever. Simple conventions that everyone follows beat a sophisticated architecture that nobody understands. The staging → intermediate → marts pattern works well precisely because it gives a large team a shared mental model without imposing too much complexity.

Structuring a large dbt (data build tool) project requires moving away from the default single-folder setup and adopting a modular, multi-layered architecture . The industry standard—championed by dbt Labs and elite data engineering teams—is the Layered Architecture (often split into staging, intermediate, and marts).

Here is how to structure your project for maximum maintainability, scalability, and developer sanity.

1. The Standard Folder Architecture

Organize your models/ directory by functional layers. This creates a clear lineage and data flow: from raw data ingestion to final business metrics.

text``` models/ ├── staging/ # 1. Clean, rename, and cast raw source data │ ├── _stg_sources.yml # Source definitions and freshness checks │ ├── _stg_models.yml # Documentation and tests for staging │ ├── stripe/ │ │ ├── stg_stripe__customers.sql │ │ └── stg_stripe__payments.sql │ └── salesforce/ │ ├── stg_salesforce__accounts.sql │ └── stg_salesforce__opportunities.sql │ ├── intermediate/ # 2. Joins, business logic, and heavy transformations │ ├── finance/ │ │ ├── int_finance__customer_revenue.sql │ │ └── int_finance__daily_aggregations.sql │ └── _int_finance.yml │ └── marts/ # 3. Final, business-ready dimensional models (star schema) ├── core/ │ ├── dim_customers.sql │ ├── fct_orders.sql │ └── _core_mart.yml └── marketing/ ├── dim_campaigns.sql └── _marketing_mart.yml

Use code with caution.

### 2. Breakdown of the Layers

- **Staging (`models/staging/`)** 
	- **Rule:** 1-to-1 with source tables.
	- **What to do here:** Rename columns to a consistent convention, cast data types, handle light parsing (like JSON extraction), and filter out test/deleted records if necessary. Do **not** do heavy joins or aggregations here.
	- **Naming:** `stg_<source_system>__.sql` 
- **Intermediate (`models/intermediate/`)** 
	- **Rule:** Optional, but crucial for large projects. This is where you pre-join models, clean up granular business logic, and build reusable building blocks.
	- **What to do here:** Combine staging models (e.g., join `stg_stripe__payments` with `stg_salesforce__accounts` ), handle deduplication, and build complex CTE logic that would clutter your final tables.
	- **Naming:** `int___<what_it_does>.sql` 
- **Marts (`models/marts/`)** 
	- **Rule:** The source of truth for business consumers, BI tools, and reverse-ETL.
	- **What to do here:** Model your data into a dimensional structure (facts and dimensions). Clean business-facing metrics live here.
	- **Naming:** `dim_.sql` (dimensions) or `fct_<event>.sql` (facts).

### 3. Key Scalability Best Practices

- **Use `sources.yaml` religiously:** Define all source tables in YAML files within your staging folders. This allows you to track freshness (`dbt source freshness` ), add schema-level tests, and use the `{{ source() }}` function safely.
- **Embrace `ref()` everywhere:** Never hardcode table or schema names. Using `{{ ref('stg_model') }}` lets dbt automatically build the DAG (Directed Acyclic Graph) and manage dependencies.
- **Limit cross-domain pollution:** Keep domains separate (e.g., `finance` shouldn't deeply interlock with `marketing` intermediate tables unless passing through a well-defined core model) to reduce circular dependencies and compile time.
- **Split up YAML files:** Instead of one massive `schema.yml` file per project, place a `<model_group>.yml` file inside each respective subfolder next to the SQL files it describes.

### 4. Recommended Resources & Tools

To dive deeper into official blueprints and advanced patterns, check out these guides:

- Read the official dbt Best Practices Guide for comprehensive architectural patterns.
- Explore the community-favorite Jaffle Shop Example Repository on GitHub to see a smaller-scale reference implementation of this exact folder structure.

If you'd like to narrow this down, let me know:

- What **data warehouse** (Snowflake, BigQuery, Databricks, Postgres) are you using?
- How many **sources and team members** are working on the project?

I can give you more specific advice on **access control (grants)**, **semantic layer integration** , or **CI/CD optimization**.

Structuring a large dbt (data build tool) project requires moving away from flat directories and adopting a layered architecture (often called modular or layered modeling). This keeps code DRY (Don't Repeat Yourself), makes lineage clear, and prevents your DAG (Directed Acyclic Graph) from turning into spaghetti.

Here is a blueprint for structuring a scalable, enterprise-grade dbt project inspired by official dbt Labs Best Practices.

1. The Core Layered Directory Structure

Organize your models/ directory into distinct subdirectories based on data maturity and transformation depth:

text``` models/ ├── 01_sources/ # Source declarations & freshness checks │ ├── _sources.yml │ └── src_erp.yml ├── 02_staging/ # Clean, type-cast, and rename raw data (1:1 with sources) │ ├── erp/ │ │ ├── stg_erp__customers.sql │ │ ├── stg_erp__orders.sql │ │ └── _stg_erp_models.yml │ └── crm/ │ ├── stg_crm__leads.sql │ └── _stg_crm_models.yml ├── 03_intermediate/ # Business logic, joins, filtering, and grain reduction (reusable) │ ├── finance/ │ │ ├── int_finance__customer_revenue.sql │ │ └── _int_finance_models.yml │ └── sales/ │ └── int_sales__order_pipeline.sql ├── 04_marts/ # Final business-facing dimensional models (Facts & Dimensions) │ ├── finance/ │ │ ├── dim_customers.sql │ │ ├── fct_orders.sql │ │ └── _finance_marts.yml │ └── marketing/ │ └── fct_campaign_performance.sql └── 05_semantic/ # Metrics layer & semantic models (optional, for dbt Semantic Layer) └── metrics.yml

Use code with caution.

2. Breakdown of the Layers

- **Sources (`01_sources`):** Define your raw tables here using `sources.yml` . Never select directly from `source()` in intermediate or mart layers. Always pass through staging first.
- **Staging (`02_staging`):** One model per source table.
	- *Rules:* Rename columns to a standard convention (e.g., `created_at` instead of `crt_ts` ), cast data types, handle light parsing (like extracting JSON fields), and maintain a 1:1 relationship with source tables. Use views here.
- **Intermediate (`03_intermediate`):** The heavy lifting happens here. Join staging models together, pivot data, aggregate, or apply complex business logic that might be useful in multiple downstream marts.
	- *Rules:* These are modular building blocks. They are usually ephemeral or view models, not exposed directly to business users or BI tools.
- **Marts (`04_marts`):** The final output. Structured around dimensional modeling (Kimball methodology)—using `dim_` for entities/dimensions and `fct_` for events/facts.
	- *Rules:* This is what BI tools (Looker, Tableau, Preset) connect to. Make sure these are materialized as tables or incremental models.

3. Key Scalability Best Practices

- **Enforce Naming Conventions:** Prefix your models (`stg_`, `int_`, `dim_`, `fct_` ) so anyone can look at a table name and immediately know its layer, grain, and purpose in the DAG.
- **Use Grouping and Semantic Tags:** Leverage `tags` or `groups` (via `dbt-mesh` / enterprise groups) in your `dbt_project.yml` or schema files to assign ownership to different teams when scaling past a single analytics department.
- **Limit `select *` and Heavy Joins in Marts:** Marts should read from clean intermediate or staging models, avoiding messy multi-source joins on the fly.
- **Leverage `ref()` Religiously:** Never hardcode table or schema names. Use `{{ ref('stg_model') }}` to let dbt auto-calculate the dependency graph.
- **Modularize with Custom Generic Tests:** Put custom data tests in a `tests/generic/` folder and apply them cleanly in your YAML files to maintain data quality contracts across large teams.

If you'd like to narrow this down, tell me:

- What is the **size of your data team** or number of source systems?
- Are you planning to use **dbt Mesh / Cross-project references** for multi-repo setups?
- What **data warehouse** (Snowflake, BigQuery, Databricks, Redshift) are you targeting?

A large dbt project stays maintainable when the structure reflects data flow, ownership, and business domains rather than just the order tables were created. A common scalable pattern is:

Sources → Staging → Intermediate → Marts → BI/ML/Applications

A practical layout:

my_dbt_project/
├── dbt_project.yml
├── packages.yml
├── models/
│   ├── staging/
│   │   ├── stripe/
│   │   │   ├── stg_stripe__customers.sql
│   │   │   ├── stg_stripe__payments.sql
│   │   │   └── _stripe__models.yml
│   │   ├── salesforce/
│   │   │   ├── stg_salesforce__accounts.sql
│   │   │   └── _salesforce__models.yml
│   │
│   ├── intermediate/
│   │   ├── customers/
│   │   │   └── int_customers__lifecycle.sql
│   │   └── orders/
│   │       └── int_orders__enriched.sql
│   │
│   └── marts/
│       ├── finance/
│       │   ├── fct_revenue.sql
│       │   └── dim_customers.sql
│       ├── marketing/
│       │   └── fct_campaign_performance.sql
│       └── product/
│           └── fct_events.sql
│
├── macros/
├── snapshots/
├── seeds/
└── tests/

This three-layer approach is widely used because it separates source cleanup, reusable business logic, and consumer-facing datasets.

1. Keep staging boring

Purpose: Create a stable interface over raw data.

Rules:

• One staging model per source table.
• Rename columns.
• Cast types.
• Clean obvious issues.
• Extract JSON fields.
• Add basic tests.

Avoid:

• Business rules.
• Cross-source joins.
• Complex aggregations.

Example:

-- stg_stripe__payments.sql

select
    id as payment_id,
    customer as customer_id,
    cast(amount as decimal) / 100 as amount_usd,
    created_at::timestamp as payment_at,
    status
from {{ source('stripe', 'payments') }}

A staging layer reduces duplication because source changes are handled once instead of across many downstream models.

---

2. Use intermediate models as your "engineering layer"

Intermediate models are where complexity belongs.

Good candidates:

• Joining customers + orders + payments.
• Creating reusable business concepts.
• Preparing data for multiple marts.
• Complex window functions.

Example:

intermediate/
├── orders/
│   ├── int_orders__with_payments.sql
│   └── int_orders__customer_metrics.sql

A useful rule:

If two marts need the same transformation, it probably belongs in intermediate. Avoid giant marts like:

fct_customer_revenue.sql  (900 lines)

Instead:

stg_customers
      ↓
int_customer_orders
      ↓
int_customer_revenue
      ↓
fct_customer_revenue

---

3. Organize marts by business domain

Large teams usually scale better when marts follow business ownership:

marts/
├── finance/
├── marketing/
├── sales/
├── product/
└── operations/

Typical naming:

Facts and dimensions make ownership and downstream usage clearer.

---

4. Make ownership obvious

At scale, every model should answer:

• Who owns it?
• Who consumes it?
• What does it represent?
• What is its grain?

Example YAML:

models:
  - name: fct_orders
    description: "One row per completed customer order."

    meta:
      owner: finance_team

    columns:
      - name: order_id
        tests:
          - unique
          - not_null

A new engineer should understand a model without opening the SQL.

---

5. Use schemas and tags intentionally

Example dbt_project.yml:

models:
  my_project:

    staging:
      +schema: staging
      +materialized: view

    intermediate:
      +schema: intermediate

    marts:
      +schema: marts
      +materialized: table

Then you can run:

dbt build --select tag:finance

or:

dbt build --select marts.finance

---

6. Keep YAML close to models

Avoid one giant:

models.yml

with 500 models.

Prefer:

staging/
  stripe/
    stg_stripe__models.yml

marts/
  finance/
    finance_models.yml

Benefits:

• Smaller pull requests.
• Fewer merge conflicts.
• Easier ownership.

---

7. Establish conventions early

Examples:

Columns

Good:

customer_id
created_at
is_active
has_subscription

Avoid:

cust
date_created
active_flag

Model names

Good:

stg_salesforce__accounts
int_orders__with_refunds
fct_daily_revenue

Bad:

final_table
new_orders2
customer_model_latest

---

8. Handle very large projects with domain boundaries

For hundreds or thousands of models:

models/
├── staging/
├── intermediate/
├── marts/
│   ├── finance/
│   ├── product/
│   └── growth/

If a single dbt project becomes too large, split by domain only when coordination becomes a real bottleneck. Splitting too early creates dependency and deployment complexity.

---

9. Add guardrails

A mature dbt project usually has:

• CI builds on pull requests.
• Freshness checks on sources.
• Key tests (unique, not_null, relationships).
• Documentation generation.
• Ownership metadata.
• Model contracts for important datasets.

---

A good rule of thumb

The biggest mistake in large dbt projects is allowing every model to become a "special case." Strong layers, naming rules, ownership, and testing keep the DAG understandable as it grows.

cust
date_created
active_flag

Model names

Good:

stg_salesforce__accounts
int_orders__with_refunds
fct_daily_revenue

Bad:

final_table
new_orders2
customer_model_latest

---

8. Handle very large projects with domain boundaries

For hundreds or thousands of models:

models/
├── staging/
├── intermediate/
├── marts/
│   ├── finance/
│   ├── product/
│   └── growth/

If a single dbt project becomes too large, split by domain only when coordination becomes a real bottleneck. Splitting too early creates dependency and deployment complexity.

---

9. Add guardrails

A mature dbt project usually has:

A large dbt project stays maintainable when the structure communicates ownership, data flow, and intended usage. The most common scalable pattern is:

sources → staging → intermediate → marts → consumers

A good rule: each layer should have a clear purpose, and models should become more business-specific as they move downstream.

1. Use a layered model structure

A typical large project:

models/
├── staging/
│   ├── stripe/
│   │   ├── stg_stripe__customers.sql
│   │   ├── stg_stripe__payments.sql
│   │   └── _stripe__models.yml
│   ├── salesforce/
│   │   ├── stg_salesforce__accounts.sql
│   │   └── _salesforce__models.yml
│   └── app/
│       └── stg_app__events.sql
│
├── intermediate/
│   ├── finance/
│   │   └── int_orders__payment_status.sql
│   ├── marketing/
│   │   └── int_users__campaign_attribution.sql
│   └── product/
│       └── int_users__sessions.sql
│
├── marts/
│   ├── finance/
│   │   ├── fct_revenue.sql
│   │   └── dim_customers.sql
│   ├── marketing/
│   │   ├── fct_campaign_performance.sql
│   │   └── dim_channels.sql
│   └── product/
│       ├── fct_sessions.sql
│       └── dim_users.sql

This separates:

• source ownership in staging
• reusable business logic in intermediate
• analytics products in marts

This staging/intermediate/marts pattern is a common dbt recommendation for projects that need to scale.

---

2. Define responsibilities for each layer

Staging (stg_)

Purpose:

• Rename columns
• Cast types
• Clean obvious source issues
• Standardize field names

Example:

-- stg_stripe__payments.sql

select
    id as payment_id,
    customer_id,
    created_at::timestamp as payment_created_at,
    amount / 100.0 as amount_usd
from {{ source('stripe', 'payments') }}

Avoid:

• joins
• business rules
• metrics

A staging model should usually represent one source table.

---

Intermediate (int_)

Purpose:

• Combine staging models
• Apply reusable business logic
• Prepare entities for marts

Example:

int_orders__with_payments
int_customers__lifetime_value
int_sessions__attribution

Good intermediate models answer questions like:

"What is a clean order entity?" Not:

"What table does the CEO dashboard need?"

---

Marts (fct_, dim_)

Purpose:

• Serve analysts, BI, ML, exports
• Represent business concepts

Typical patterns:

dim_customers
dim_products
dim_dates

fct_orders
fct_payments
fct_events

Facts:

• events
• transactions
• measurable things

Dimensions:

• entities
• descriptive attributes

---

3. Organize by domain, not just by layer

Small projects:

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

Large projects:

models/
├── staging/
│   ├── finance/
│   ├── product/
│   └── marketing/
│
├── intermediate/
│   ├── finance/
│   ├── product/
│   └── marketing/
│
└── marts/
    ├── finance/
    ├── product/
    └── marketing/

Domain ownership prevents the "500 models in one folder" problem.

---

4. Establish naming conventions early

Example convention:

The double underscore convention helps separate the source/domain from the entity.

---

5. Keep YAML documentation close to models

Avoid one giant:

models/schema.yml

Instead:

staging/
└── stripe/
    ├── stg_stripe__payments.sql
    └── _stripe__models.yml

Example:

models:
  - name: stg_stripe__payments
    description: Cleaned Stripe payment records

    columns:
      - name: payment_id
        tests:
          - unique
          - not_null

Benefits:

• fewer merge conflicts
• easier ownership
• easier reviews

---

6. Use dbt_project.yml for conventions

Example:

models:
  analytics:

    staging:
      +materialized: view

    intermediate:
      +materialized: view

    marts:
      +materialized: table

Then override selectively:

marts:
  finance:
    +schema: finance
    +materialized: incremental

---

7. Treat tests as contracts

Minimum expectations:

Staging

• primary keys are unique
• required fields are not null
• sources have freshness checks

Intermediate

• relationship integrity
• expected row counts
• accepted values

Marts

• business rules
• metric correctness
• grain validation

Example:

columns:
  - name: order_id
    tests:
      - unique
      - not_null

---

8. Avoid common scaling traps

❌ Giant marts

Bad:

fct_customer_dashboard.sql
-- 900 lines
-- joins 20 tables
-- calculates every metric

Better:

int_customer_activity.sql
int_customer_revenue.sql
fct_customer_summary.sql

---

❌ Business logic in staging

Bad:

case
  when revenue > 10000 then 'enterprise'
end as customer_segment

That belongs downstream because it is a business definition.

---

❌ Too many tiny models

Don't split every CTE into a model.

A good intermediate model should exist because:

• multiple things reuse it, or
• it makes a complex transformation understandable.

---

9. Add governance for a large team

Useful additions:

macros/
├── generate_surrogate_key.sql
├── date_spine.sql

snapshots/
├── customers_snapshot.sql

seeds/
├── country_codes.csv

tests/
├── assert_revenue_not_negative.sql

Also consider:

• model owners in YAML
• tags (finance, daily, critical)
• exposures for dashboards
• CI runs on modified models only

---

10. When to split into multiple dbt projects

Keep one project while:

• teams share lots of logic
• deployments are manageable
• lineage is easier together

Consider multiple projects when:

• domains have independent ownership
• DAG size becomes difficult to operate
• deployment cycles conflict

Do this because of organizational boundaries, not just because the model count is high.

---

A practical "large but sane" target architecture is:

Raw Sources
    ↓
staging (source-aligned)
    ↓
intermediate (business logic)
    ↓
marts (domain-owned analytics products)
    ↓
BI / ML / Applications

If you are designing a new project today, this structure will usually scale comfortably into the hundreds of models.

---

❌ Too many tiny models

Don't split every CTE into a model.

A good intermediate model should exist because:

• multiple things reuse it, or
• it makes a complex transformation understandable.

---

9. Add governance for a large team

Useful additions:

macros/
├── generate_surrogate_key.sql
├── date_spine.sql

snapshots/
├── customers_snapshot.sql

seeds/
├── country_codes.csv

tests/
├── assert_revenue_not_negative.sql

Also consider:

• model owners in YAML
• tags (finance, daily, critical)
• exposures for dashboards
• CI runs on modified models only

---

10. When to split into multiple dbt projects

Keep one project while:

• teams share lots of logic
• deployments are manageable
• lineage is easier together

Consider multiple projects when:

• domains have independent ownership
• DAG size becomes difficult to operate
• deployment cycles conflict

Do this because of organizational boundaries, not just because the model count is high.

---

A practical "large but sane" target architecture is:

Structuring a large dbt (data build tool) project requires shifting from a monolithic "dump-everything-here" mindset to a modular, domain-driven architecture. A well-structured project ensures clean lineage, fast compilation, reusable logic, and clear ownership as your team and data models grow.

Here is a battle-tested framework for structuring large-scale dbt projects based on modern best practices.

1. Adopt the Layered Architecture (The Folder Structure)

Divide your models/ directory into distinct, functional layers. Data flows strictly forward through these layers—from raw ingestion to final business consumption.

• staging/ (The Extraction & Cleanup Layer)
  • Purpose: The 1:1 reflection of your source data. Do basic renaming, type casting, and light cleaning here (e.g., snake_case columns, parsing timestamps). No complex business logic or multi-table joins.
  • Organization: Group by source system/schema (e.g., models/staging/stripe/, models/staging/salesforce/).
• intermediate/ (The Business Logic & Joining Layer)
  • Purpose: Reusable, single-purpose building blocks. This is where you handle heavy lifting like un-nesting JSON, stitching together entities (e.g., joining users and accounts), and calculating granular, reusable metrics.
  • Rule: Models here should be ephemeral (config(materialized='ephemeral') ) unless they are heavy bottlenecks that need materializing for downstream performance.
• marts/ (The Presentation & Consumption Layer)
  • Purpose: Clean, business-ready dimensional models (facts and dimensions) or wide reporting tables tailored for BI tools and end-users.
  • Organization: Group by business domain or department (e.g., models/marts/finance/, models/marts/marketing/, models/marts/core/).

2. Implement the "Three-Schema" Pattern

Don't land everything in a single database schema. Separate your environments and layers into logical schemas to enforce access control and keep things clean:

• raw / source: Loaded directly by your ELT tool (Fivetran, Airbyte, etc.). Read-only for dbt.
• dbt_staging: Where staging views/tables are materialized.
• dbt_intermediate / dbt_marts (Production): Where final analytical models live. Grant BI tools access only to the marts schema.

3. Scale with Advanced Features

As the project balloons past 100+ models, manual configuration becomes impossible. Leverage these native dbt features:

• dbt_project.yml Defaults: Apply configurations globally or per-folder rather than inside individual SQL files. For example, set all models in models/marts/ to materialized: table and assign specific tags or metas automatically based on folder paths.
• Groups and Access Control (Enterprise/v1.5+): Use groups and access: public/protected in your properties files to hide intermediate internal models from other business domains, preventing cross-domain spaghetti dependencies.
• Semantic Layer / Metrics: Define your core metrics (e.g., monthly_recurring_revenue ) centrally in YAML rather than rewriting SQL aggregates across different BI tools or reports.

4. Golden Rules for Maintainability

• One Source of Truth for Staging: Never reference a source() macro anywhere outside of the staging/ layer. Downstream models must always select from the corresponding staging model.
• Use ref() Religiously: Never hardcode schema or table names. Using ref() ensures dbt builds the DAG (Directed Acyclic Graph) accurately.
• Write Unit Tests & Data Tests: Use generic tests (not_null, unique, relationships ) and dbt's native unit testing feature (v1.8+) to test complex SQL logic against mock inputs before pushing to production.

If you'd like, I can:

• Provide a sample folder layout tree and dbt_project.yml configuration
• Share strategies for managing cross-project dependencies using dbt Mesh
• Explain how to set up naming conventions and coding standards for large teams

Structuring a large dbt (data build tool) project requires shifting from a monolithic "dump-everything-here" mindset to a clean, modular software-engineering approach. The gold standard for achieving this scalability is the layers-based architecture popularized by fishtown analytics (dbt Labs) and refined by the community.

Here is a blueprint to structure your project for maximum maintainability and scale:

1. The Multi-Layered Architecture

Divide your models into clear, sequential schemas or directories. Data flows strictly forward from one layer to the next.

• Staging Layer (staging/ or stg/):
  • Purpose: Your first-line cleanup. One model per source table.
  • Rules: Do not join across sources here. Rename columns to your internal conventions, recast data types (e.g., string to timestamp), and handle light null-safety or filtering. Keep it 1:1 with source data.
• Intermediate Layer (intermediate/ or int/ - Optional):
  • Purpose: The heavy lifting. This is where you handle complex business logic, re-usable joins, unions, and grain-shifting before building final entities.
  • Rules: Models here are usually built on top of staging models. They should be modular building blocks, not final reporting tables.
• Marts Layer (marts/ or fct_ / dim/):
  • Purpose: Business-ready output. Structured as dimensional models (facts and dimensions) or wide reporting tables tailored for BI tools.
  • Rules: Named clearly (fct_orders, dim_customers ). This is the only layer downstream users and BI tools (like Looker, Tableau, or Preset) should query.

2. Recommended Directory Layout

Organize your folders either by domain/source in staging and by business entity in marts. A clean layout looks like this:

text``` my_dbt_project/ ├── analyses/ ├── macros/ ├── models/ │ ├── intermediate/ │ │ ├── finance/ │ │ │ (_int_finance_models.yml, int_payments_combined.sql) │ │ └── marketing/ │ └── marts/ │ ├── core/ │ │ (dim_customers.sql, fct_orders.sql, core.yml) │ └── finance/ │ (fct_revenue.sql, finance.yml) ├── seeds/ ├── snapshots/ ├── tests/ └── dbt_project.yml

Use code with caution.

3. Key Scaling Best Practices

- *Use Source Declarations (`sources.yml`):* Never hardcode raw table names in your staging models using `from source_db.schema.table` . Always define them in a `sources.yml` file and call them using the `{{ source('source_name', 'table_name') }}` function. This ensures lineage tracking works from day one.
- *Leverage Groups and Contracts (Enterprise):* For massive multi-team dbt projects, use **dbt mesh / contracts** to enforce data types and primary key constraints on public models, preventing upstream changes from breaking downstream BI dashboards.
- *DRY Up Code with Packages:* Don't reinvent the wheel. Use community-loved packages like dbt_utils for cross-database general macros and dbt_expectations for advanced data testing.
- *Enforce Naming Conventions:* Standardize prefixes so anyone on the team knows what a model is just by looking at its filename:
	- `stg_` for staging
	- `int_` for intermediate
	- `fct_` for fact tables (events, transactions)
	- `dim_` for dimension tables (entities, people, places)

Would you like me to dive deeper into **how to configure `dbt_project.yml`** for materializations, or do you want an example of a **staging-to-mart model workflow** for a specific domain like e-commerce or SaaS?

For a large dbt project, optimize for three things: clear ownership, predictable dependencies, and small units of change. A good structure should let someone unfamiliar with the project answer “where does this logic belong?” without asking another engineer.

The classic staging → intermediate → marts pattern is still a strong foundation, and dbt Labs continues to recommend it for separating source preparation, reusable transformations, and business-facing models.

1. Organize the repository by responsibility

A scalable starting point:

dbt_project/
├── dbt_project.yml
├── packages.yml
├── macros/
├── models/
│   ├── staging/
│   │   ├── salesforce/
│   │   │   ├── _salesforce__sources.yml
│   │   │   ├── stg_salesforce__accounts.sql
│   │   │   ├── stg_salesforce__contacts.sql
│   │   │   └── salesforce.yml
│   │   ├── stripe/
│   │   │   ├── _stripe__sources.yml
│   │   │   ├── stg_stripe__customers.sql
│   │   │   └── stg_stripe__payments.sql
│   │   └── app/
│   │
│   ├── intermediate/
│   │   ├── customers/
│   │   │   ├── int_customers__enriched.sql
│   │   │   └── int_customers__orders.sql
│   │   └── orders/
│   │       └── int_orders__enriched.sql
│   │
│   └── marts/
│       ├── core/
│       │   ├── dim_customers.sql
│       │   ├── dim_products.sql
│       │   └── fct_orders.sql
│       ├── finance/
│       ├── marketing/
│       └── product/
│
├── seeds/
├── snapshots/
├── tests/
└── analyses/

The important distinction is that staging is organized around source systems, while marts are organized around business domains. This makes ownership and discovery much easier as the number of sources and models grows.

2. Give each layer a strict contract

Staging: source-conformed

Think:

“Make this source usable and consistent.” A staging model should generally:

• Rename columns.
• Cast data types.
• Standardize timestamps.
• Normalize obvious values.
• Handle source-specific quirks.
• Perform basic deduplication when appropriate.

Avoid putting business logic here.

select
    id                 as customer_id,
    email              as customer_email,
    created_at::timestamp as created_at,
    status
from {{ source('stripe', 'customers') }}

The key benefit is that if Stripe changes created_at, you fix it once, rather than fixing dozens of downstream models. That's one of the main reasons dbt recommends a staging layer.

Use a predictable naming convention such as:

stg_<source>__<entity>

For example:

stg_salesforce__accounts
stg_salesforce__contacts
stg_stripe__customers
stg_stripe__payments

Intermediate: reusable transformation logic

This is where complicated work goes:

stg_customers
       │
stg_orders ──→ int_customers__orders
       │              │
stg_payments ────────┘
                      │
                      ↓
                dim_customers

Use intermediate models for:

• Multi-table joins.
• Complex transformations.
• Reusable business logic.
• Window calculations.
• Entity construction.
• Expensive transformations shared by multiple downstream models.

Name them around what they accomplish, e.g.:

int_orders__enriched
int_customers__orders
int_subscription__status

Don't let your marts become 800-line SQL queries simply because there's nowhere else to put the complexity.

dbt Labs explicitly recommends modularizing shared logic into its own models to keep the DAG and transformations manageable.

Marts: business-facing data products

Marts should answer:

“What data should analysts and applications actually consume?” Organize these by domain:

marts/
├── finance/
├── marketing/
├── product/
├── sales/
└── core/

And use recognizable model names:

dim_customers
dim_products
fct_orders
fct_payments

The dim_ / fct_ convention makes the intended grain and model type immediately recognizable.

3. Treat the DAG as an architecture, not just a dependency graph

A healthy large project tends to look roughly like:

                RAW SOURCES
                     │
          ┌──────────┴──────────┐
          ↓                     ↓
      STAGING                 STAGING
     Salesforce                Stripe
          │                     │
          └──────────┬──────────┘
                     ↓
                INTERMEDIATE
                     │
          ┌──────────┼──────────┐
          ↓          ↓          ↓
       FINANCE    PRODUCT    MARKETING
        MART       MART        MART

Avoid patterns like:

fct_orders
   ├── raw_orders
   ├── raw_customers
   ├── raw_payments
   ├── raw_products
   ├── another_mart
   └── some_random_report

That creates hidden coupling and makes changes increasingly dangerous.

A useful rule is:

Dependencies should generally flow downward through the layers, never sideways or backward. For example, a staging model shouldn't depend on a marketing mart.

4. Separate "shared" from "domain-specific"

This becomes particularly important around 100+ models.

Suppose both Finance and Marketing need customer lifetime value.

Don't create:

finance/int_customer_ltv.sql
marketing/int_customer_ltv.sql

with subtly different implementations.

Instead, establish a shared canonical model:

intermediate/
└── customers/
    └── int_customers__lifetime_value.sql

Then:

finance ──────┐
              ├──> int_customers__lifetime_value
marketing ────┘

This prevents business definitions from diverging.

But don't over-generalize everything into a giant "common" layer. Shared logic should actually be shared.

5. Make dbt_project.yml enforce the architecture

Don't rely solely on engineers remembering conventions.

For example:

models:
  my_project:

    staging:
      +materialized: view
      +schema: staging

    intermediate:
      +materialized: view
      +schema: intermediate

    marts:
      +materialized: table
      +schema: marts

Then override individual expensive models when necessary:

models:
  my_project:
    intermediate:
      +materialized: view

      orders:
        +materialized: table

    marts:
      +materialized: table

      finance:
        +schema: finance

Materialization should be driven by workload and reuse, not ideology. Staging views are common; heavily reused or expensive intermediate models may deserve tables/incremental materialization; marts are often persisted for consumers.

6. Keep tests and documentation next to the models

Don't create a giant:

models.yml

containing 500 models.

Instead:

staging/
└── stripe/
    ├── stg_stripe__customers.sql
    ├── stg_stripe__payments.sql
    └── stripe.yml

marts/
└── finance/
    ├── fct_payments.sql
    ├── dim_customers.sql
    └── finance.yml

That keeps ownership localized and makes PRs much easier to review. The dbt Project Evaluator similarly recommends keeping tests/documentation close to the models they describe.

For important models, document at least:

• What the model represents.
• Its grain.
• Ownership.
• Important business definitions.
• Primary/business keys.
• Known limitations.

For example:

models:
  - name: fct_orders
    description: >
      One row per customer order.

    columns:
      - name: order_id
        description: "Unique identifier for an order."
        tests:
          - unique
          - not_null

7. Treat model grain as a first-class concept

One of the biggest sources of dbt pain is ambiguous grain.

Every fact model should effectively answer:

What does one row represent? For example:

fct_orders
    grain = one row per order

fct_order_items
    grain = one row per order line item

fct_daily_customer_activity
    grain = one row per customer per day

Put that information in the model description or SQL comment and test the key.

A model called customer_metrics that could mean one row per customer, customer-day, customer-product, or customer-month is a maintenance problem waiting to happen.

8. Use macros sparingly

Macros are excellent for repeated technical patterns:

macros/
├── generate_surrogate_key.sql
├── safe_cast.sql
├── date_spine.sql
└── cents_to_dollars.sql

But don't put business logic into enormous macros just to avoid writing SQL.

Prefer:

int_customer_ltv.sql

over:

{{ calculate_everything_about_customers() }}

Models are much easier to discover, lineage-track, test, and debug.

9. Design CI around domains and selectors

At scale, you don't want every PR rebuilding the entire warehouse.

Use tags, selectors, state comparison, and DAG-aware CI so a change to:

models/marts/marketing/

doesn't unnecessarily rebuild unrelated Finance models.

For example, conceptually:

PR changes
    ↓
affected models
    ↓
their downstream dependencies
    ↓
tests

This becomes increasingly important as projects move from dozens to hundreds or thousands of models.

10. Establish ownership

Every important domain should have an owner:

Finance       → Finance Analytics
Marketing     → Marketing Analytics
Product       → Product Analytics
Core customer → Data Platform

You can encode ownership through metadata/tags and reinforce it with CODEOWNERS or your team's review system.

The goal is that when someone sees:

fct_revenue

they know:

• Who owns it?
• What is its grain?
• Who consumes it?
• What upstream sources does it depend on?
• What tests protect it?
• How frequently does it update?

11. Avoid premature micro-projects

I generally wouldn't split a large dbt codebase into many separate projects just because it has become large.

A single well-organized project often gives you:

• One dependency graph.
• Shared macros.
• Consistent conventions.
• Easier refactoring.
• Centralized CI.
• Easier lineage.

Split projects when you have genuine boundaries such as independent teams, security boundaries, release cycles, or operational requirements—not merely because the model count is high.

A practical architecture for a 500+ model project

If I were starting a large project today, I'd aim for something like:

models/
│
├── staging/
│   ├── salesforce/
│   ├── stripe/
│   ├── postgres/
│   ├── app/
│   └── ad_platforms/
│
├── intermediate/
│   ├── customers/
│   ├── orders/
│   ├── subscriptions/
│   └── marketing/
│
└── marts/
    ├── core/
    ├── finance/
    ├── marketing/
    ├── product/
    ├── sales/
    └── operations/

With these rules:

The most important principle is not the exact folder names. It's establishing a small number of architectural rules and making them hard to violate.

The rules I'd put in the README

1. Raw sources are referenced through source().
2. Staging models represent individual source entities.
3. Staging contains no business logic.
4. Complex/reusable transformations belong in intermediate.
5. Marts are organized by business domain.
6. Every mart has an explicit grain.
7. fct_ = fact; dim_ = dimension; int_ = intermediate; stg_ = staging.
8. Tests and documentation live close to their models.
9. Shared business logic has one canonical implementation.
10. CI should build only the affected DAG wherever practical.

That combination—layered architecture + domain ownership + explicit grain + modular DAG + automated standards—is what makes a dbt project scale. The directory structure itself is only the visible part of the architecture.

If you're dealing with a project that's already large and messy, the best strategy is usually incremental refactoring rather than a rewrite: establish the conventions, migrate one domain at a time, and use the DAG to identify duplicated or overly coupled logic.

Dependencies should generally flow downward through the layers, never sideways or backward. For example, a staging model shouldn't depend on a marketing mart.

4. Separate "shared" from "domain-specific"

This becomes particularly important around 100+ models.

Suppose both Finance and Marketing need customer lifetime value.

Don't create:

finance/int_customer_ltv.sql
marketing/int_customer_ltv.sql

with subtly different implementations.

Instead, establish a shared canonical model:

intermediate/
└── customers/
    └── int_customers__lifetime_value.sql

Then:

finance ──────┐
              ├──> int_customers__lifetime_value
marketing ────┘

This prevents business definitions from diverging.

But don't over-generalize everything into a giant "common" layer. Shared logic should actually be shared.

5. Make dbt_project.yml enforce the architecture

Don't rely solely on engineers remembering conventions.

For example: