A dbt project that starts with 47 models, no naming conventions, and a single schema.yml holding three tests is the normal beginning. Scaling that to 530+ models across four business domains, deploying six times a day with a CI pipeline that catches schema-breaking changes before they reach Snowflake, is where the official best-practices guides stop being enough. The patterns below are the ones that survive past roughly 200 models, when everything that looked clean starts creaking. (Context: dbt Core 1.8 on Snowflake, orchestrated by Airflow, monorepo serving multiple analytics teams - most of this is warehouse-agnostic, with Snowflake-specific bits called out.)

Project structure that doesn't collapse

The directory structure is the single most consequential early decision. Get it wrong and you spend months untangling spaghetti references; get it right and onboarding takes a day. The structure that holds up groups staging by source system and intermediate/marts by business domain:

models/
├── staging/          # grouped by SOURCE system
│   ├── stripe/        _stripe__sources.yml, stg_stripe__payments.sql, …
│   └── salesforce/    _salesforce__sources.yml, stg_salesforce__accounts.sql, …
├── intermediate/     # grouped by DOMAIN
│   └── finance/       int_payments__pivoted_by_method.sql, …
├── marts/            # grouped by DOMAIN
│   ├── finance/       fct_revenue.sql, dim_customers.sql, …
│   └── sales/         fct_deals.sql, dim_accounts.sql, …
└── utilities/         util_date_spine.sql, util_currency_rates.sql

A Stripe payment is a Stripe payment regardless of which team consumes it, so staging follows the source; once cleaned, humans think in domains, so intermediate and marts follow domains. Underscore-prefixed YAML files (_stripe__sources.yml) sort to the top of each directory. Folder structure alone isn't enough - dbt_project.yml should enforce materialization and schema policy by directory:

models:
  my_project:
    staging:      {+materialized: view,      +schema: staging,      +tags: ["staging"]}
    intermediate: {+materialized: ephemeral,  +schema: intermediate, +tags: ["intermediate"]}
    marts:
      +materialized: table
      +schema: analytics
      finance: {+schema: finance}
      sales:   {+schema: sales}

Staging models are views (thin wrappers over sources), intermediate models default to ephemeral (they exist to simplify mart logic), and marts are tables (that's what BI tools hit). Override intermediate to table case-by-case when debugging needs it or a model is referenced by 5+ downstream nodes.

Naming conventions that survive 500 models

At 50 models, inconsistent naming is annoying; at 500 it's dangerous - someone queries payments when they meant fct_payments and the CFO's dashboard shows unfiltered test transactions. The naming contract:

Layer	Pattern	Example
Staging	stg_{source}__{entity}	stg_stripe__payments
Intermediate	int_{entity}__{verb}	int_payments__pivoted_by_method
Facts	fct_{event/action}	fct_revenue
Dimensions	dim_{entity}	dim_customers
Utilities	util_{purpose}	util_date_spine

The double underscore isn't cosmetic - everything before __ is the source system, everything after is the entity, which saves real cognitive effort scanning a lineage graph of hundreds of nodes. Columns get the same rigor: every primary key is {entity}_id, every timestamp ends in _at, every boolean starts with is_ or has_, every date ends in _date - enforced by a CI macro that flags violations.

A three-layer testing strategy

unique and not_null on primary keys is table stakes. Real protection at scale needs three layers. Schema tests are the foundation - including range and constancy checks, not just null/unique:

models:
  - name: fct_revenue
    columns:
      - name: revenue_id
        tests: [unique, not_null]
      - name: customer_id
        tests:
          - not_null
          - relationships: {to: ref('dim_customers'), field: customer_id}
      - name: revenue_amount_usd
        tests:
          - dbt_utils.accepted_range: {min_value: 0, max_value: 10000000}
      - name: revenue_month
        tests: [not_null, dbt_utils.not_constant]

The accepted_range test exists because a currency-conversion bug once turned a $47 transaction into $47,000,000 in the finance mart; not_constant on the date catches a subtle failure where an incremental model silently stops processing and every row gets the same timestamp. Custom data tests check business logic across tables - for instance, asserting that recognized revenue never exceeds payments received (with a 1% tolerance for timing), which has caught both a duplicate-payment ingestion issue and a timezone mismatch that double-counted midnight transactions:

-- tests/assert_revenue_matches_payments.sql
select r.customer_id, r.total_revenue, p.total_payments
from (select customer_id, sum(revenue_amount_usd) total_revenue
      from {{ ref('fct_revenue') }} group by 1) r
join (select customer_id, sum(payment_amount_usd) total_payments
      from {{ ref('fct_payments') }} where payment_status='succeeded' group by 1) p
  on r.customer_id = p.customer_id
where r.total_revenue > p.total_payments * 1.01   -- 1% tolerance for timing

Freshness and volume tests are the early-warning system. Source freshness (warn_after/error_after on loaded_at_field, run hourly via Airflow with Slack alerts) tells you when Fivetran silently breaks a connector at 2 AM, before standup. A simple volume macro comparing today's row count to a rolling 7-day average and alerting on >30% deviation catches things schema tests never will.

Incremental models that don't bite

Incremental models are where dbt becomes a real data-engineering tool - and where most production incidents originate. The pattern worth trusting:

{{ config(materialized='incremental', unique_key='event_id',
          incremental_strategy='merge', cluster_by=['event_date','event_type'],
          on_schema_change='append_new_columns') }}
select event_id, user_id, event_type, occurred_at,
       date(occurred_at) as event_date, _loaded_at
from {{ ref('stg_internal_app__events') }}
{% if is_incremental() %}
where _loaded_at > (select dateadd(hour, -3, max(_loaded_at)) from {{ this }})
{% endif %}

Three details matter. The 3-hour lookback instead of a strict greater-than: late-arriving data is a fact of life, and logic that assumes perfect ordering eventually loses rows - the overlap costs a little extra merge work and guarantees correctness. Use _loaded_at (warehouse arrival time) as the cursor, not a business timestamp like occurred_at, which can be backdated or replayed. And on_schema_change='append_new_columns' stops the model silently dropping new fields when the source evolves - the default ignore causes more data-quality issues than any other dbt default. Not everything should be incremental: keep models under ~10M rows and under 2 minutes' build time as full-refresh tables, since the cognitive overhead of incremental logic only pays off once full refreshes get expensive.

CI/CD that actually gates deploys

Running dbt build in CI is easy; making it prevent bad merges takes a few non-obvious settings. The key is state:modified+ with --defer, which builds only the models changed in the PR plus their downstream dependents, reading unmodified upstream models from the production manifest:

- name: Run modified models
  run: |
    dbt build --select state:modified+ --defer --state ./prod-manifest/ \
      --target ci --fail-fast
  env:
    DBT_CI_SCHEMA: "ci_pr_${{ github.event.pull_request.number }}"
- name: Check source freshness
  run: dbt source freshness --target ci
- name: Cleanup CI schema
  if: always()
  run: dbt run-operation drop_schema --args '{schema_name: "ci_pr_${{ github.event.pull_request.number }}"}'

A PR touching one staging model builds maybe 15 downstream models instead of all 530 - cutting CI from 45 minutes to under 8. Each PR gets its own Snowflake schema (ci_pr_142), cleaned up afterward, so PRs run CI concurrently without interference. A dbt docs generate step that posts a docs link on the PR lets reviewers click through lineage for changed models without cloning the branch - a small touch that noticeably improved review quality.

Performance patterns

A naive dbt build at 530 models can take 90 minutes; disciplined patterns bring the production run to ~22. On Snowflake, clustering keys on large fact tables make the biggest single difference - but cluster on the columns downstream queries filter on, never on the unique key (which makes a terrible cluster key). Model-level warehouse sizing matters more than it looks: one revenue model consuming 40% of CI credits on a medium warehouse (18 minutes) dropped to 3 minutes on an XL - and cost less, because the warehouse suspended faster.

marts:
  +snowflake_warehouse: TRANSFORM_WH_MEDIUM
  finance:
    fct_revenue: {+snowflake_warehouse: TRANSFORM_WH_XLARGE}

A conditional-materialization macro toggles by target - simple tables in dev for fast iteration, incremental in prod and CI - which eliminates the common "works in dev, breaks in prod" bug where developers never test the incremental path:

{% macro get_incremental_or_table() %}
  {{ return('table' if target.name == 'dev' else 'incremental') }}
{% endmacro %}

Anti-patterns

• Source references in mart models. A mart hitting source('stripe','payments') directly loses staging's renaming, casting, and filtering, and couples every consumer to the raw schema. Reference ref('stg_stripe__payments') instead.
• Business logic in staging. Staging should be boring - rename, cast, maybe dedupe. A currency conversion or an arbitrary where created > '2023-01-01' in staging silently affects every downstream consumer, and in six months nobody remembers why pre-2023 payments are missing.
• The 500-line model. Past ~150 lines, a model almost certainly contains logic that belongs in an intermediate model. Long models are hard to test, debug, and reuse - extract the CTEs.
• Disabling tests to fix CI. Setting a failing test to warn or deleting it lets the underlying data-quality issue snowball for weeks. Fix the data, fix the test, or file a ticket with an expected resolution date.
• Incremental models with no full-refresh strategy. Every incremental model drifts - late arrivals, retroactive corrections, deletions. You need a lookback window and a scheduled full refresh (weekly works) to reconcile. If you can't afford the refresh, the underlying query is too expensive.
• Reinventing dbt packages. dbt-utils, dbt-expectations, and dbt-date are battle-tested across thousands of projects - your hand-rolled generate_surrogate_key probably has the edge-case bug theirs fixed two years ago.

Documentation and macros, with restraint

At 500 models the docs site is how new people understand what data exists. Enforce it: CI fails if any model lacks a description (30 seconds per model, hours of saved Slack), and use doc blocks for anything needing more than a sentence - especially a grain statement, which prevents half the "why are my numbers wrong" conversations (join fct_revenue to dim_customers without understanding the grain and you fan out revenue by product). Macros are powerful and also the fastest route to an unreadable project - extract one only when identical logic appears in three or more models. A three-line cents_to_dollars macro earns its keep; an 80-line generic SCD-dimension macro does not - when it generated duplicate rows at 3 AM, five levels of Jinja indirection made root cause nearly impossible, and explicit, slightly-repetitive SQL cost less total time than debugging the abstraction.

What to do from day one

1. Enforce naming conventions before the 10th model - renaming models with downstream dependents is painful. Lint them in CI from the start.
2. Put descriptions on every model and column immediately - documentation debt compounds faster than any other kind in analytics.
3. Use staging / intermediate / mart structure from the first model - the "we'll refactor later" moment only arrives when something breaks in production.
4. Set up CI with state comparison before 20 models - state:modified+ keeps CI fast and teaches the team to think in impact analysis.
5. Schedule weekly full refreshes for incremental models from the start - nearly free while tables are small, and it establishes the pattern before it's critical.
6. Write at least one data test per mart that encodes a business rule you know must hold - these become the regression safety net.
7. Keep macros simple, or don't write them. Three lines of repeated SQL across five models beats a clever macro only its author can maintain.

Scaling a dbt project isn't about clever Jinja - it's about discipline, conventions, and defaults so easy to follow that doing the wrong thing takes more effort than doing the right thing. Every practice here exists because a team learned the hard way what happens without it.