dbt-transformation-patterns

dbt Transformation Patterns

Production-ready patterns for dbt (data build tool) including model organization, testing strategies, documentation, and incremental processing.

When to Use This Skill

• Building data transformation pipelines with dbt
• Organizing models into staging, intermediate, and marts layers
• Implementing data quality tests
• Creating incremental models for large datasets
• Documenting data models and lineage
• Setting up dbt project structure

Core Concepts

1. Model Layers (Medallion Architecture)

sources/          Raw data definitions
    ↓
staging/          1:1 with source, light cleaning
    ↓
intermediate/     Business logic, joins, aggregations
    ↓
marts/            Final analytics tables

2. Naming Conventions

Quick Start

# dbt_project.yml
name: "analytics"

Detailed patterns and worked examples

Detailed pattern documentation lives in references/details.md. Read that file when the navigation tier above is insufficient.

Best Practices

Do's

• Use staging layer - Clean data once, use everywhere
• Test aggressively - Not null, unique, relationships
• Document everything - Column descriptions, model descriptions
• Use incremental - For tables > 1M rows
• Version control - dbt project in Git

Don'ts

• Don't skip staging - Raw → mart is tech debt
• Don't hardcode dates - Use {{ var('start_date') }}
• Don't repeat logic - Extract to macros
• Don't test in prod - Use dev target
• Don't ignore freshness - Monitor source data