majiayu000 dbt Model Spec Skill
A dbt model is only trustworthy if its grain is unambiguous, its sources are declared, and it's tested. This skill specs a model the way a good analytics engineer would — naming the grain first, mapping lineage, defining each column, choosing the right materialization, and writing the dbt tests that keep it correct — so the model is reviewable before a line of SQL ships.
Required Inputs
Ask for these only if they aren't already provided:
- What the model represents and its grain (one row per ___ — the single most important decision).
- Layer — staging, intermediate, or mart (dimension/fact). Conventions differ per layer.
- Sources / upstream refs — the raw tables or models it builds on.
- The business logic — joins, filters, aggregations, and any business rules.
Output Format
dbt Model: [model_name]
1. Purpose & grain — what it is, and one row per [grain] stated explicitly. Layer (staging/intermediate/mart).
2. Lineage — source('…') / ref('…') upstreams → this model → likely downstream consumers.
3. Transformation logic — the joins, filters, aggregations, window functions, and business rules, in order. Flag fan-out risks (joins that break the grain).
4. Columns — a table: name · type · description · (key/measure/dimension). The schema contract.
| column | type | description |
|---|
5. Tests (dbt) — unique + not_null on the grain key, relationships for FKs, accepted_values for enums, and any custom/dbt_utils tests the logic needs. Tests are the model's guarantees — don't skip them.
6. Materialization — view / table / incremental / ephemeral, with the reasoning (incremental needs a unique_key + an is_incremental() filter).
7. Skeleton — a starting model.sql (CTE-structured: imports → logic → final select) and the schema.yml with tests, ready to fill in.
Quality Checks
- The grain is stated as "one row per ___" and the key is tested unique + not_null
- Sources/refs use
source()/ref(), not hard-coded table names - Every column has a type and description (the schema contract)
- Tests cover the grain key, FKs (relationships), and enum columns
- Materialization is justified; incremental models declare a unique_key and is_incremental() logic
- Fan-out joins that could break the grain are flagged
Anti-Patterns
- Do not leave the grain ambiguous — an untested, unclear grain is how duplicate rows and wrong metrics happen
- Do not hard-code upstream table names — use ref()/source() so lineage and environments work
- Do not ship a model with no tests — untested models silently rot; the grain key at minimum must be tested
- Do not default everything to a table — pick the materialization the use justifies (views for light, incremental for large append-only)
- Do not bury business logic without comments — the next analyst must understand the rules
Based On
dbt / analytics-engineering best practice — explicit grain, ref/source lineage, layered modelling (staging→intermediate→mart), schema tests.