Dbt Transformation Patterns

When to use this skill

Use Dbt Transformation Patterns when an AI agent needs a reusable SKILL.md workflow for this job: 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.

When not to use it

Skip Dbt Transformation Patterns when the task is outside the coding category, or when a more specific skill in this directory already covers the same workflow with clearer triggers.

How to install

1. Personal install: create ~/.claude/skills/dbt-transformation-patterns/SKILL.md (and any bundled scripts) so Claude Code, Claude Desktop, and compatible agents can load it in every project.
2. Project install: commit the same folder at .claude/skills/dbt-transformation-patterns/ so teammates get the skill with the repo.
3. Restart the agent session after copying files so it re-scans the skills directory, then ask for the task in words that match the skill description.

Full install guide for Claude, Cursor, and Codex

What this skill does

# 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

```yaml # 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

Intended uses

• 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