dbt Project Best Practices Checklist
As you prepare to deploy your first production dbt project for your organization, review this comprehensive checklist.
Following these best practices will ensure your project remains scalable, maintainable, and easy to onboard new Data Engineers.
1. Directory & File Structure
-
✅ Use the standard dbt folder structure: Separate your models into
staging,intermediate, andmarts. Do not dump 100 SQL files into a single folder. -
✅ Group by Source System in Staging: If you ingest data from Salesforce and Stripe, your staging folder should look like:
-
models/staging/salesforce/ -
models/staging/stripe/ -
✅ Group by Business Area in Marts: Your final presentation models should be grouped by the department that consumes them:
-
models/marts/finance/ -
models/marts/marketing/
2. Naming Conventions
-
✅ Strict Model Prefixes: Every file must have a prefix indicating its role in the DAG.
-
stg_for staging models (e.g.,stg_stripe_payments.sql) -
int_for intermediate models (e.g.,int_customer_lifetime_value.sql) -
fct_for fact tables (e.g.,fct_orders.sql) -
dim_for dimension tables (e.g.,dim_customers.sql) -
✅ Consistent Primary Keys: Primary keys should always end in
_id. A foreign key must have the exact same name as the primary key it references (e.g.,customer_idshould be named the same in bothfct_ordersanddim_customers). -
✅ Boolean Columns: Columns representing a true/false state should start with
is_,has_, ordoes_(e.g.,is_active,has_subscribed).
3. Modeling Principles
-
✅ The 1-to-1 Staging Rule: A staging model must have a 1-to-1 relationship with the raw source table. You may rename columns and cast data types, but you must not perform joins or aggregations in a staging model.
-
✅ Never query Raw Data in a Mart: A
fct_ordim_model must NEVER use{{ source() }}. It must only{{ ref() }}staging or intermediate models. If you violate this, you ruin the modularity of the DAG. -
✅ Avoid Circular Dependencies: If Model A requires Model B, and Model B requires Model A, your project will not compile. Break the cycle by extracting shared logic into an
int_(intermediate) model.
4. Testing & Documentation
-
✅ Primary Key Testing: Every single model must have a
uniqueandnot_nulltest on its primary key. This is non-negotiable. -
✅ Source Freshness: Configure source freshness blocks in
sources.yml. Your pipeline should fail if the raw data hasn't been updated recently. -
✅ Descriptions in YAML, not SQL comments: Do not write paragraph-long explanations inside
-- SQL comments. Put descriptions in theschema.ymlfiles so they automatically render on the dbt Docs website.
5. Deployment & Orchestration
-
✅ Use Slim CI for Pull Requests: Configure GitHub Actions (or dbt Cloud CI) to run
dbt build --select state:modified+. Never rebuild the entire warehouse on every minor code commit. -
✅ Do Not Run
dbt runin Production Orchestration: Always usedbt build. Thebuildcommand automatically runs tests on a model immediately after creating it. If the test fails, it halts downstream execution, preventing bad data from reaching the CEO's dashboard. -
✅ Use Target Environments: Never develop against production data. Use
{{ target.name }}logic in yourprofiles.ymlandsources.ymlto safely route development runs to personal sandboxes (e.g.,DEV_ARJUN).
6. Performance
-
✅ Leverage Incremental Models: If a fact table takes longer than 15 minutes to build, it should probably be converted to an
incrementalmaterialization using anupdated_atfilter. -
✅ Disable Unused Models: If a model is deprecated or no longer needed, do not just delete the file (which loses git history). Set
enabled: falsein theconfig()block.
Congratulations! You have completed the theoretical and best-practice modules of the dbt and Data Engineering Training.