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 + an filter).