dbt Best Practices: Staging, Marts, and Naming That Actually Scales
src_ staging, fct_ and dim_ naming, dbt tests, and orchestrating dbt runs in Airflow correctly.
Get one deep-dive article every Saturday. No spam. Unsubscribe anytime.
dbt has won the data transformation layer. But most dbt projects have the same issues: models that are impossible to navigate, missing tests, and orchestration that breaks when a source changes shape.
The Three-Layer Architecture
Layer 1: Staging (stg_) — One model per source table, minimal logic, rename to snake_case.
-- models/staging/stg_orders.sql
WITH source AS (
SELECT * FROM {{ source('postgres', 'orders') }}
)
SELECT
id::BIGINT AS order_id,
user_id::BIGINT AS customer_id,
status::VARCHAR AS order_status,
total_amount::NUMERIC AS order_amount_usd
FROM source WHERE id IS NOT NULLLayer 2: Intermediate (int_) — Joins and business logic reused across multiple models.
Layer 3: Marts (fct_ and dim_) — Business-ready fact and dimension tables organized by domain.
Get unlimited access to all dbt tutorials & animated diagrams
1,000+ animated tutorials · 100+ AI tools · Mock interviews · Certifications
Testing Strategy
Every model should have not_null and unique on the primary key. Plus relationship tests on foreign keys and accepted_values on status columns.
# models/marts/fct_orders.yml
version: 2
models:
- name: fct_orders
columns:
- name: order_id
tests: [unique, not_null]
- name: customer_id
tests:
- not_null
- relationships:
to: ref('dim_customers')
field: customer_id
- name: order_status
tests:
- accepted_values:
values: ['pending', 'completed', 'cancelled']Get unlimited access to all dbt tutorials & animated diagrams
1,000+ animated tutorials · 100+ AI tools · Mock interviews · Certifications