dbt Model Development

Critical Rules

1. ALWAYS run

   dbt build

   after creating/modifying models - compile is NOT enough
2. ALWAYS verify output after build using

   dbt show

   - don't assume success
3. If build fails 3+ times, stop and reassess your entire approach

Workflow

1. Understand the Task Requirements

• What columns are needed? List them explicitly.
• What is the grain of the table (one row per what)?
• What calculations or aggregations are required?

2. Discover Project Conventions

cat dbt_project.yml
find models/ -name "*.sql" | head -20

3. Find Similar Models

# Find models with similar purpose
find models/ -name "*agg*.sql" -o -name "*fct_*.sql" | head -5

4. Check Upstream Data

# Preview upstream data if needed
dbt show --select <upstream_model> --limit 10

5. Write the Model

6. Compile (Syntax Check)

dbt compile --select <model_name>

7. BUILD - MANDATORY

dbt build --select <model_name>

1. Read the error carefully
2. Fix the specific issue
3. Run build again
4. If fails 3+ times, step back and reassess approach

8. Verify Output (CRITICAL)

# Check the table was created and preview data
dbt show --select <model_name> --limit 10

• Column names match requirements exactly
• Row count is reasonable
• Data values look correct
• No unexpected NULLs

9. Verify Calculations Against Sample Data

# Pick a specific row and verify calculation by hand
dbt show --inline "
  select *
  from {{ ref('model_name') }}
  where <primary_key> = '<known_value>'
" --limit 1

# Cross-check aggregations
dbt show --inline "
  select count(*), sum(<column>)
  from {{ ref('model_name') }}
"

total_revenue = quantity * price

1. Pick one row from output
2. Look up the source quantity and price
3. Manually calculate: does it match?

10. Re-review Against Requirements

• Did you implement what was asked, not what you assumed?
• Are column names exactly as specified?
• Is the calculation logic correct per the requirements?
• Does the grain (one row per what?) match what was requested?

Anti-Patterns

• Declaring done after compile without running build
• Not verifying output data after build
• Getting stuck in compile/build error loops
• Assuming table exists just because model file exists
• Writing SQL without checking existing model patterns first