dbt-transformation-patterns

SKILL.md

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

Layer
Prefix
Example

Staging
stg_
stg_stripe__payments

Intermediate
int_
int_payments_pivoted

Marts
dim_, fct_
dim_customers, fct_orders

Quick Start

# dbt_project.yml
name: "analytics"
version: "1.0.0"
profile: "analytics"

model-paths: ["models"]
analysis-paths: ["analyses"]
test-paths: ["tests"]
seed-paths: ["seeds"]
macro-paths: ["macros"]

vars:
  start_date: "2020-01-01"

models:
  analytics:
    staging:
      +materialized: view
      +schema: staging
    intermediate:
      +materialized: ephemeral
    marts:
      +materialized: table
      +schema: analytics

# Project structure
models/
├── staging/
│   ├── stripe/
│   │   ├── _stripe__sources.yml
│   │   ├── _stripe__models.yml
│   │   ├── stg_stripe__customers.sql
│   │   └── stg_stripe__payments.sql
│   └── shopify/
│       ├── _shopify__sources.yml
│       └── stg_shopify__orders.sql
├── intermediate/
│   └── finance/
│       └── int_payments_pivoted.sql
└── marts/
    ├── core/
    │   ├── _core__models.yml
    │   ├── dim_customers.sql
    │   └── fct_orders.sql
    └── finance/
        └── fct_revenue.sql

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