dbt-transformation-patterns

★ 37,559

by wshobson · part of wshobson/agents

Master dbt (data build tool) for analytics engineering with model organization, testing, documentation, and incremental strategies. Use when building data transformations, creating data models, or implementing analytics engineering best practices.

🧩 One of 7 skills in the wshobson/agents package — works on its own, and pairs well with its siblings.

This is the playbook your agent receives when the skill activates — you don't need to read it to use the skill, but it's here to audit before installing.

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`

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

Copy & paste — that's it

npx skills add https://github.com/wshobson/agents --skill dbt-transformation-patterns

Run this in your project — your agent picks the skill up automatically.

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