Architecting a Scalable dbt Project

Contents

• Why a disciplined project layout prevents entropy
• Designing layers: sources, staging, intermediate, and marts
• dbt naming conventions, configs, and macro hygiene
• Performance patterns: incremental models, snapshots, and clustering
• Operational checklist: Onboarding, governance, and documentation

Why a disciplined project layout prevents entropy

Broken dashboards and late-night incident pager calls are rarely caused by a single bad SQL file — they’re caused by a chaotic repository where the same field is normalized three different ways. A disciplined layout turns that chaos into contracts: one canonical staging model per source, a predictable path for transformations, and clear ownership for each artifact. dbt Labs codified this three-layer approach (staging → intermediate → marts) because it reduces duplicate logic and makes lineage navigable for both humans and automated tooling. (docs.getdbt.com)

Important: Treat your project structure as a living contract. When you rename, move, or refactor, update schema.yml docs, tests, and the dbt_project.yml config in the same PR so the change is atomic and reviewable.

Designing layers: sources, staging, intermediate, and marts

Design the model layers to answer the single question: “If a field breaks, where do I fix it?” Then make that the only place you ever touch that logic.

• Sources (declare with source()): model external systems and mark freshness and metadata. Keep read-only and isolated from transforms.
• Staging — the atoms: stg_<source>__<table> — one-to-one with source tables. Rename, cast, apply canonical keys, and add not_null / unique tests at the column level.
• Intermediate — domain building blocks: compose staging models into reusable units (ephemeral or view materializations). Solve business logic once; reference via ref() everywhere else.
• Marts — the business contract: fct_ (facts) and dim_ (dimensions) materialized as table or incremental for performance. This layer is what reports and BI consume.

Quick reference table:

Layer	Prefix example	Typical materialization	Purpose
Sources	N/A (source() declarations)	n/a	Raw system data + freshness checks
Staging	stg_<source>__<table>	view	Rename, retype, canonical PK
Intermediate	int_<domain>_<thing>	view / ephemeral	Reusable business logic
Marts	fct_... / dim_...	table / incremental	Business-facing datasets

This layer pattern is a direct recommendation from dbt Labs and reduces developer cognitive load when tracing lineage and permissioning. (docs.getdbt.com)

Example — simple staging model that renames and casts (remove repetition; do this once):

-- models/staging/salesforce/stg_salesforce_contacts.sql
{{ config(materialized='view') }}

select
  id as contact_id,
  lower(email) as email,
  created_at::timestamp as created_at,
  updated_at::timestamp as updated_at
from {{ source('salesforce', 'contacts') }}

dbt naming conventions, configs, and macro hygiene

Consistency is a team multiplier. Use precise prefixes, conservative lengths, and a single casing convention (snake_case) so that names are discoverable and safe across warehouses.

• Naming quick rules:

  • stg_<source>__<table> for staging (double underscore separates system and table).
  • int_<domain>_<purpose> for intermediate constructs.
  • fct_<process> for facts, dim_<entity> for dimensions.
  • Keep names < 50 characters and prefer nouns for dims, verbs/verbs-nouns for facts.

• Config precedence and placement:

  • Use dbt_project.yml for directory-level defaults, properties.yml for model metadata and tests, and {{ config(...) }} for model-specific overrides — dbt applies these hierarchically. Directory-level +materialized is a useful guardrail. (docs.getdbt.com)

• Macro hygiene:

  • Name macros by intent: get_effective_schema(), upsert_merge_strategy(), format_currency().
  • Keep macros small and deterministic; avoid macros that trigger side effects or rely on run_query() for production control flow.
  • Put cross-cutting utility macros in a macros/helpers/ path and surface stable interfaces for the team.

Example dbt_project.yml excerpt for conservative defaults:

name: analytics
version: '1.0'
config-version: 2

models:
  analytics:
    staging:
      +materialized: view
    intermediate:
      +materialized: view
    marts:
      +materialized: table
      +schema: analytics

Adopting a linter like SQLFluff with the dbt templater catches style and obvious logic problems early in PRs; there are ready-made GitHub Actions templates for this integration. (github.com)

Performance patterns: incremental models, snapshots, and clustering

Performance decisions belong to repeatable patterns, not ad-hoc tweaks.

• Incremental models
  • Use materialized='incremental' for very large or expensive-to-transform tables; rely on is_incremental() for the incremental branch and full-refresh for the bootstrap path. Test unique_key semantics with unique and not_null tests. dbt’s incremental materialization reduces run time by transforming only the rows you specify. (docs.getdbt.com)

Example incremental skeleton:

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

select
  order_id,
  customer_id,
  order_date,
  amount
from {{ ref('stg_orders') }}

{% if is_incremental() %}
  where order_date > (select max(order_date) from {{ this }})
{% endif %}

• Snapshots (SCD Type 2)
  • Prefer the timestamp strategy when you have a reliable updated_at column; fall back to check when you don’t. Ensure unique_key is enforced upstream; add a uniqueness test on the source to avoid silent corruption. Store snapshots in a dedicated snapshots schema and plan retention. (docs.getdbt.com)

Example snapshot:

-- snapshots/orders_snapshot.sql
{% snapshot orders_snapshot %}
  {{
    config(
      target_schema='snapshots',
      unique_key='order_id',
      strategy='timestamp',
      updated_at='updated_at'
    )
  }}
  select * from {{ source('payments','orders') }}
{% endsnapshot %}

• Clustering and partitioning
  • Don’t cluster by default. Clustering is effective for very large tables and when many queries filter on the same columns; Snowflake recommends clustering only when tables have many micro-partitions and when queries will benefit substantially (usually multi-TB tables). Order cluster keys by the selectivity/cardinality that matches your query patterns. (docs.snowflake.com)
  • BigQuery: combine partitioning (time or integer ranges) with clustering for cost-effective pruning; BigQuery auto-reclusters partitions and stores block-level min/max metadata to enable efficient pruning. Use clustering on columns that appear frequently in filters or joins, and order clustering columns left-to-right by importance. (cloud.google.com)

Contrarian insight: aggressively materializing everything as table to save CPU on repeated queries shifts cost to storage and makes refactoring hard. Start with views/ephemerals, measure, then promote only the hot paths to table or incremental.

Operational checklist: Onboarding, governance, and documentation

Actionable, bite-sized tasks you can implement immediately to scale with low friction.

1. Local onboarding script (developer day 0)

   • Provide a shell script in the repo with:
     • git clone ...
     • pip install -r ci/requirements.txt (pin dbt adapter + sqlfluff)
     • cp profiles.example.yml ~/.dbt/profiles.yml and instructions to set secrets
     • dbt debug and dbt deps
     • dbt seed --select +tag:test (if seeds used)
   • Document expected CI run time and where to find logs — this reduces first-day confusion.

2. PR / CI pipeline (minimal, high ROI)

   • Steps (order matters):
     1. Lint changed SQL with SQLFluff (annotate PR on failure). (github.com)
     2. dbt deps + dbt parse to validate project compilation.
     3. Run dbt build --select state:modified+ or dbt test --select state:modified+ to test only changed nodes.
     4. Run dbt docs generate and upload target/ artifacts if you host docs somewhere central. (docs.getdbt.com)
     5. Run dbt_project_evaluator rules as a final gate (set severity error in CI for critical checks). (docs.getdbt.com)

• Example GitHub Actions outline (trimmed):

name: dbt PR checks
on: [pull_request]

jobs:
  lint-compile-test:
    runs-on: ubuntu-latest
    steps:
      - uses: actions/checkout@v4
      - name: Set up Python
        uses: actions/setup-python@v4
        with: python-version: '3.11'
      - name: Install dependencies
        run: |
          pip install dbt-core dbt-bigquery sqlfluff sqlfluff-templater-dbt
      - name: SQLFluff lint
        run: sqlfluff lint --dialect bigquery --templater dbt
      - name: dbt deps & compile
        run: |
          dbt deps
          dbt parse
      - name: dbt tests (changed)
        run: dbt test --select state:modified+

1. Governance checklist (short)

   • Enforce PR reviews and CI green before merge; require at least one reviewer with domain OWNERS tag.
   • Tag models by domain (tags:) and require domain owner approval for marts changes.
   • Keep secrets and profiles out of the repo; inject them in CI via the provider’s secret store.

2. Documentation and discoverability

   • Require every model folder to include a README.md and a schema.yml documenting models and columns.
   • Use exposures to map dashboards / reports to the models they depend on; expose owner and SLA metadata.
   • Schedule a nightly dbt docs generate job (or use dbt Cloud Catalog) so docs reflect the last successful production run. (docs.getdbt.com)

3. Tests and data quality (practical rules)

   • Every dim_ and fct_ must have: unique test on PK (when appropriate), not_null on primary keys, and at least one accepted_values or business-level assertion.
   • Run end-to-end reconciliation (row counts + sums) after large upstream loads and incorporate these into scheduled alarms.

4. Onboarding metrics for the first 30 days

   • Track: CI run time on PRs, number of flaky tests, and mean time to fix a failing test. Use those metrics to decide which models to materialize differently.

Closing

Make the layout, naming, and tests your team’s guardrails — not a bureaucratic checklist. Apply the layer rules, enforce naming and tests in CI, and treat performance patterns (incremental, snapshots, clustering) as measured trade-offs rather than defaults; you’ll reduce incident volume, speed up reviews, and convert ad-hoc analytics into reliable, debuggable services.

Sources

How we structure our dbt projects - dbt Labs’ recommended three-layer project structure and rationale used for layering and organizational guidance. (docs.getdbt.com)

Configure incremental models - dbt documentation describing incremental materialization, is_incremental(), and incremental design patterns. (docs.getdbt.com)

Add snapshots to your DAG - dbt documentation on snapshot strategies (timestamp vs check), unique_key, and snapshot best practices. (docs.getdbt.com)

Clustering Keys & Clustered Tables (Snowflake) - Snowflake guidance on when to use clustering keys, ordering, and cost/benefit considerations. (docs.snowflake.com)

Querying clustered tables (BigQuery) - BigQuery docs explaining clustering behavior, ordering, and partition/clustering interactions. (cloud.google.com)

sqlfluff-github-actions (SQLFluff GitHub repo) - Examples and templates for running SQLFluff in GitHub Actions and annotating PRs. (github.com)

Get started with Continuous Integration tests (dbt Guides) - dbt’s guide to CI patterns, PR-based testing, and the dbt Project Evaluator recommendation. (docs.getdbt.com)

Build and view your docs with dbt - Commands and behavior for dbt docs generate, dbt docs serve, and the Catalog experience. (docs.getdbt.com)