dbt Best Practices
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.
# 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.
Practice what you learned
1. What is the primary responsibility of a staging model in dbt?
2. Where should reusable business logic used by multiple marts live?
3. Which dbt test should be applied to nearly every foreign key column?
4. Why should marts never query source() directly?
5. What is the recommended CI practice for dbt pull requests?
Was this page helpful?
You May Also Like
dbt vs Traditional ETL
How dbt's ELT, SQL-in-Git approach differs from traditional GUI-based ETL tools like Informatica or SSIS in compute cost, collaboration, and testing.
Building a Star Schema with dbt
How to design fact and dimension tables in dbt using surrogate keys, explicit grain, incremental materialization, and referential-integrity tests.
dbt Quick Reference
A lookup sheet of the CLI commands, YAML config keys, and Jinja functions used every day in a dbt project.
Related Reading
Related Study Notes in Programming
Browse all study notesApache Spark Study Notes
Programming · 30 topics
ProgrammingApache Flink Study Notes
Programming · 30 topics
ProgrammingHadoop Study Notes
Programming · 30 topics
ProgrammingSnowflake Study Notes
Programming · 30 topics
ProgrammingApache Airflow Study Notes
Programming · 30 topics
ProgrammingPowerShell Study Notes
Programming · 30 topics