dbt Best Practices

Analogy categoryAnalogies

A well-run dbt project is judged less by clever SQL and more by whether a new analytics engineer can open the repo and understand the flow of data within an hour. Best practices in dbt center on four pillars: consistent project structure, layered modeling, disciplined testing and documentation, and a repeatable deployment workflow through version control and CI. Skipping any one of these turns a dbt project into the same tangled mess of ad-hoc SQL scripts it was meant to replace.

🏏

Cricket analogy: Just as a franchise like Mumbai Indians builds a squad with clear roles for openers, finishers, and death bowlers rather than eleven random players, a dbt project needs clear roles for staging, intermediate, and mart models so nobody is guessing who does what.

Project Structure and Naming Conventions

dbt Labs recommends organizing models into subdirectories under models/ that mirror data sources and business domains, such as models/staging/stripe/ and models/marts/finance/. File names should be prefixed consistently: stg_ for staging models, int_ for intermediate models, and fct_ or dim_ for fact and dimension marts. This convention alone lets an engineer infer a model's grain and purpose from its filename without opening the file, which matters enormously once a project grows past 50 or 100 models.

🏏

Cricket analogy: Just as scorecards label players by role, such as 'C Rahul (wk)' or 'J Bumrah (bowler)', prefixing model names as stg_orders or fct_payments tells you the role of a model at a glance without reading the SQL.

Modeling Layers: Staging, Intermediate, and Marts

The staging layer should do the minimum necessary work: renaming columns, casting types, and light cleaning, with a strict one-staging-model-per-source-table rule and no joins. Intermediate models handle reusable business logic such as deduplication or complex joins that multiple downstream marts need, and mart models expose the final, business-facing fact and dimension tables that BI tools query directly. Enforcing this separation means a bug in a join only needs to be fixed in one intermediate model rather than copy-pasted across five marts.

🏏

Cricket analogy: A bowling attack that separates the new-ball swing bowler, the middle-overs spinner, and the death-overs specialist gets better results than one bowler trying to do everything, just as separating staging, intermediate, and marts avoids one model trying to do everything.

Testing, Documentation, and Deployment Discipline

Every model should carry at minimum a not_null and unique test on its primary key, plus relationships tests wherever a foreign key exists, defined declaratively in schema.yml files alongside a description for every column that a business user might query. On the deployment side, best practice is to run dbt in CI on every pull request against a temporary schema, use dbt Cloud or Airflow-orchestrated dbt runs in production, and separate dev, staging, and prod targets in profiles.yml so nobody accidentally overwrites production tables while testing a change.

🏏

Cricket analogy: The DRS system checks every close decision against ball-tracking and snickometer evidence before it's finalized, the same way a not_null and unique test checks every primary key before a model is trusted downstream.

yaml

# models/staging/stripe/_stripe__models.yml
version: 2

models:
  - name: stg_stripe__payments
    description: One row per Stripe payment, cleaned and renamed from the raw source.
    columns:
      - name: payment_id
        description: Primary key of the payments table.
        tests:
          - unique
          - not_null
      - name: order_id
        tests:
          - not_null
          - relationships:
              to: ref('stg_orders')
              field: order_id

Never let a mart model query a raw source table directly with source(). Every mart should build on top of staging/intermediate refs only — bypassing the staging layer breaks lineage tracking and means a schema change upstream silently corrupts a final report instead of failing a test loudly.

Use consistent prefixes — stg_, int_, fct_, dim_ — so a model's role is obvious from its filename.
Staging models should only clean and rename columns; never put business logic or joins there.
Intermediate models hold reusable business logic shared by multiple downstream marts.
Every primary key needs unique and not_null tests; every foreign key needs a relationships test.
Document every column a business user might query in schema.yml, not just the tricky ones.
Run dbt in CI against a temporary schema on every pull request before merging.
Keep dev, staging, and prod as separate targets in profiles.yml to avoid overwriting production.