dbt-skill

name	dbt-skill
description	Use when working with dbt (data build tool) - creating models, writing tests, CI/CD pipelines, materializations, sources, staging/intermediate/marts layers, Snowflake/BigQuery warehouse configuration, incremental strategies, Jinja macros, data quality, semantic layer, or making analytics engineering decisions
license	Apache-2.0
metadata	{"author":"Daniel Song","version":"1.0.0"}

dbt Skill for Claude

Comprehensive dbt guidance: project structure, modeling, testing, CI/CD, production patterns. Targets Snowflake and BigQuery. Beginner-friendly with progressive scaling.

When to Use

Activate when: creating/modifying dbt models, choosing materializations, structuring layers, setting up tests, implementing CI/CD, configuring sources/freshness, writing Jinja macros, reviewing dbt projects, making analytics engineering decisions.

Skip when: basic SQL syntax, warehouse admin, raw pipeline config (Fivetran/Airbyte), BI tool config.

Core Principles

1. DRY via ref()/source() -- never hardcode table names
2. Single Source of Truth -- each concept defined once; staging = entry point, marts = consumer interface
3. Idempotent Transformations -- dbt run twice produces identical results
4. Test Everything -- every model has at minimum PK uniqueness and not-null tests
5. Progressive Complexity -- start with views/tables, add complexity when volume demands it

Project Structure

dbt_project/
├── dbt_project.yml
├── packages.yml
├── profiles.yml                 # local only, not committed
├── models/
│   ├── staging/                 # 1:1 with source tables
│   │   └── <source>/
│   │       ├── _<source>__models.yml
│   │       ├── _<source>__sources.yml
│   │       └── stg_<source>__<entity>.sql
│   ├── intermediate/            # business logic, joins, pivots
│   │   └── <domain>/
│   └── marts/                   # business-facing tables
│       └── <domain>/
│           ├── _<domain>__models.yml
│           ├── fct_<entity>.sql
│           └── dim_<entity>.sql
├── macros/
├── tests/
│   └── generic/
├── seeds/
├── snapshots/
└── analyses/

Layer Decision Matrix

Layer	Materialization	Purpose	Naming	Tests
Staging	view	Clean/rename raw data, 1:1 with source	stg_<source>__<entity>	not_null, unique on PK
Intermediate	ephemeral	Business logic, joins, pivots	int_<entity>_<verb>ed	Tested via downstream
Marts	table/incremental	Business-facing facts and dimensions	fct_<entity>, dim_<entity>	Full coverage

Materialization Decision Matrix

Situation	Materialization	Why
Staging models	view	Always fresh, minimal storage
Intermediate logic	ephemeral	Zero cost, inlined as CTE
Marts < 100M rows	table	Simple, fast reads
Marts > 100M rows	incremental	Process only new/changed data
SCD Type 2	snapshot	Track historical changes

ref() and source() Rules

1. source() only in staging -- staging is the sole gateway to raw data
2. ref() everywhere else
3. Never skip layers -- marts must not ref() staging directly
4. Never hardcode schema names

SQL Style

• Leading commas, lowercase keywords, CTEs over subqueries
• Explicit columns in marts (no select *), final CTE named final
• 4-space indentation, one column per line

Source Configuration

Define sources in _<source>__sources.yml with loaded_at_field and freshness thresholds. Configure warn_after and error_after per table.

Concept	Snowflake	BigQuery
Top-level container	Database	Project
Schema grouping	Schema	Dataset

Warehouse Quick Reference

Config	Snowflake	BigQuery
Profile type	snowflake	bigquery
Auth	User/password or key-pair	OAuth or service account
Schema gen	database.schema.model	project.dataset.model
Incremental default	merge	merge
Partitioning	Automatic micro-partitions	partition_by required for large tables
Clustering	cluster_by (automatic)	cluster_by (manual)
Cost model	Credits (compute time)	Bytes scanned / Slots

Common Commands

Command	Purpose
dbt build	Run + test in DAG order (recommended)
dbt build --select +model	Build model and all ancestors
dbt build --select model+	Build model and all descendants
dbt build --select tag:finance	All models tagged finance
dbt build --select state:modified+	Modified + descendants (Slim CI)
dbt source freshness	Check source freshness
dbt deps	Install packages
dbt docs generate && dbt docs serve	Documentation site

Gotchas

• ref() only in staging-and-above, source() only in staging — using source() in marts bypasses the staging contract and breaks lineage
• Incremental models without unique_key silently duplicate rows on re-runs — always set unique_key for merge strategy
• ephemeral models can't be tested directly or selected with dbt test --select — test via downstream consumers
• dbt run doesn't run tests — always use dbt build to get run + test in DAG order
• --full-refresh on a large incremental model can blow warehouse credits — scope with --select first
• Jinja whitespace control: {%- -%} vs {% %} — trailing whitespace breaks compile output silently
• packages.yml version ranges (>=1.0,<2.0) can pull breaking changes — pin exact versions in production

-- WRONG: source() in marts (skips staging layer)
SELECT * FROM {{ source('stripe', 'payments') }}
-- RIGHT: ref staging model
SELECT * FROM {{ ref('stg_stripe__payments') }}

-- WRONG: incremental without unique_key (duplicates on re-run)
{{ config(materialized='incremental') }}
-- RIGHT: always specify unique_key
{{ config(materialized='incremental', unique_key='payment_id') }}

Reference Files

Load on demand when detailed guidance is needed:

Reference	Topics
Testing & Quality	Schema/generic/singular/unit tests, dbt-expectations, layer strategy
CI/CD & Deployment	Slim CI, GitHub Actions, dbt Cloud, environments, blue/green, SQLFluff
Jinja, Macros & Packages	Jinja fundamentals, custom macros, packages, debugging
Incremental & Performance	Microbatch, merge, delete+insert, insert_overwrite, warehouse tuning
Data Quality & Observability	Source freshness, Elementary, anomaly detection, alerting, incidents
Semantic Layer & Governance	MetricFlow, contracts, versions, access controls, dbt Mesh

License

Apache License 2.0. See LICENSE file for full terms.

Copyright 2026 Daniel Song

---

Converted and distributed by TomeVault — claim your Tome and manage your conversions.