dbt Best Practices

Best practices are the habits and conventions that experienced dbt teams use to keep projects maintainable, trustworthy, and fast as they grow. This topic brings together the most important principles from across the entire course into one reference you can apply from your first day on a new dbt project.

Project Structure

Use a Three-Layer Model Architecture

models/
  staging/       one model per source table, light cleanup only
  intermediate/  joins and business logic
  marts/         analytics-ready tables for end consumers

One Source Table, One Staging Model

Every raw source table gets exactly one staging model. All downstream models read from staging, never from raw sources directly. This single point of truth means changes to raw schema names require updating only one file.

Keep Model Names Unique Across the Entire Project

dbt requires globally unique model names. Use clear prefixes to prevent conflicts and communicate purpose:

stg_   staging model       stg_orders, stg_customers
int_   intermediate model  int_orders_with_items
fct_   fact table          fct_orders, fct_revenue
dim_   dimension table     dim_customers, dim_products
rpt_   report model        rpt_weekly_sales

Source Management

Declare Every Raw Table as a Source

Never hardcode schema and database names in SQL. Put them in sources.yml once. All staging models reference sources with source().

Add Freshness Checks to Every Source

tables:
  - name: orders
    loaded_at_field: _fivetran_synced
    freshness:
      warn_after:  {count: 4,  period: hour}
      error_after: {count: 12, period: hour}

Materialisation Choices

Layer           Materialisation    Reason
-----------     ---------------    ------
Staging         view               Always fresh, zero storage cost
Intermediate    ephemeral or view  Rarely queried directly
Marts (small)   table              Fast dashboard queries
Marts (large)   incremental        Efficient for big event tables

Testing

Test Every Primary Key

Apply not_null and unique to every primary key in every model. This one habit catches the majority of data quality issues.

Test Every Foreign Key

Apply the relationships test to every foreign key. Missing referential integrity causes silent join inflation in downstream models.

Run dbt build, Not dbt run

Always use dbt build in production. It runs models and tests in DAG order and stops downstream models if upstream tests fail, preventing bad data from propagating.

# Production
dbt build

# Not this
dbt run   # skips tests entirely

Documentation

Describe Every Mart Model

Write a description for every model in marts/. Include what the table contains, what logic was applied, and any important caveats.

Describe Primary and Foreign Keys

Column descriptions on key columns are the most valuable documentation. They answer the question every new team member asks: "what does this ID refer to?"

Define Exposures for Every Dashboard

Use exposures to document which dashboards and tools consume which models. This makes impact analysis possible before any refactoring.

Version Control

Every Change Goes Through a Pull Request

Never commit directly to main. Open a PR, let CI run, get a review, then merge. This prevents untested code from reaching production.

Write Meaningful Commit Messages

Bad:  "fix stuff"
Good: "fix: add NULL filter to stg_orders to exclude test records"
Good: "feat: add fct_revenue_monthly incremental model"
Good: "refactor: split int_orders into two models for clarity"

Configuration

Set Defaults in dbt_project.yml, Override Per Model

# dbt_project.yml -- defaults for each folder
models:
  my_project:
    staging:
      +materialized: view
    marts:
      +materialized: table

-- Override in a specific model that needs incremental
{{ config(materialized='incremental') }}

Never Hardcode Credentials

Use env_var() for all passwords, account names, and API keys. Store secrets in environment variables or your CI/CD secret store, never in files committed to Git.

Performance

Use Incremental for Large Event Tables

Any table with more than a few million rows that grows daily is a candidate for incremental materialisation. Full rebuilds that once took minutes will take hours as data grows.

Tag Models by Refresh Frequency

+tags: ['hourly']   for real-time metrics
+tags: ['daily']    for standard operational reporting
+tags: ['weekly']   for trend and cohort analyses

Run separate scheduled jobs per tag so hourly models do not block on slow weekly models.

Team Conventions Checklist

[ ] Three-layer folder structure: staging, intermediate, marts
[ ] All raw tables declared in sources.yml with freshness checks
[ ] All model names unique with stg_, int_, fct_, dim_ prefixes
[ ] not_null + unique on every primary key
[ ] relationships test on every foreign key
[ ] Description on every mart model
[ ] Exposure defined for every active dashboard
[ ] dbt build used in all CI and production jobs
[ ] No hardcoded credentials anywhere in the repo
[ ] Large tables use incremental materialisation
[ ] CI runs on every pull request before merge
[ ] Production manifest.json saved after every run for slim CI

The Golden Rule

Treat your dbt project like production software. Version-control everything. Test everything. Document everything that your colleagues will need to understand six months from now. The investment in good practices pays back many times over every time the team avoids a production incident, onboards a new engineer quickly, or confidently refactors a model without breaking downstream consumers.