dbt-model-scaffolder

name	dbt-model-scaffolder
description	Scaffolds new dbt models (staging, dimension, or fact) following dbt best practices. Use when the user asks to create a dbt model, scaffold a staging model, add a mart model, build a dim_ or fct_ table, generate a schema.yml entry, or add a sources.yml source. Also trigger for: dbt staging, stg_ prefix, dim_ prefix, fct_ prefix, surrogate key, dbt_utils.generate_surrogate_key, schema.yml, sources.yml, dbt ref(), dbt source(), mart model, dimension table, fact table, dbt scaffold, new dbt model. Do NOT use for reviewing existing dbt models, debugging dbt run failures, or generating dbt documentation outside of schema.yml entries.
allowed-tools	Read Write Edit Bash
license	GPL-3.0-only
status	unstable
metadata	{"version":"0.1.0","author":"Leandro Kellermann de Oliveira <[e-mail]>","model":"sonnet"}

dbt Model Scaffolder

This skill scaffolds new dbt models — staging, dimension, or fact — with correct file placement, SQL templates, and schema.yml entries. No values are hardcoded: Claude infers or asks for project-specific details at runtime before writing any file.

---

Pre-flight: Gather Parameters

Before creating any file, determine the following. Ask for anything that cannot be confidently inferred from context (open files, recent commands, or a CLAUDE.md):

1. Layer — staging or mart.
2. Model name — the entity name without prefix or extension (e.g., orders, customers).
3. Mart type (only if layer = mart) — dim_ (dimension) or fct_ (fact). Ask unless the name makes it obvious (e.g., events or transactions imply fact; users or products imply dimension).
4. dbt project root — the directory that contains dbt_project.yml. Infer from context or ask.
5. Source name and raw table (staging only) — the dbt source alias (first arg to source()) and the raw table name (second arg). Ask if not supplied.

Once all parameters are confirmed, proceed through Steps 1–3 in order.

---

Step 1 — Create the SQL File

Staging model

Path: <dbt_root>/models/staging/stg_<name>.sql

/*
Staging model: stg_<name>

Source: <source_name>.<raw_table_name>

What this model does:
  - <fill in>

What this model does NOT do:
  - Join with any other table (joins happen in marts)
*/

with source as (
    select * from {{ source('<source_name>', '<raw_table_name>') }}
),

cleaned as (
    select
        -- TODO: add columns with renames/casts

    from source
)

select * from cleaned

Dimension model

Path: <dbt_root>/models/marts/dim_<name>.sql

/*
Dimension: dim_<name>

Grain: one row per unique <name>.

Source: <which staging model>
*/

with stg as (
    select * from {{ ref('stg_<source>') }}
),

deduped as (
    select distinct
        -- TODO: add columns

    from stg
)

select * from deduped

Fact model

Path: <dbt_root>/models/marts/fct_<name>.sql

/*
Fact table: fct_<name>

Grain: one row per <describe grain>.

Sources: <list staging or dim models used>
*/

with events as (
    select * from {{ ref('stg_<source>') }}
),

-- TODO: add dimension CTEs and joins

final as (
    select
        {{ dbt_utils.generate_surrogate_key(['<key_cols>']) }} as <name>_id,
        -- TODO: add columns

    from events
    -- TODO: add joins
)

select * from final

---

Step 2 — Add schema.yml Entry

Append a new model block to the appropriate schema.yml:

• Staging models → <dbt_root>/models/staging/schema.yml
• Mart models → <dbt_root>/models/marts/schema.yml

If the file does not exist, create it with a version: 2 header first.

- name: <model_name>
  description: >
    <one sentence describing grain and purpose>
  columns:
    - name: <primary_key_column>
      description: Primary key.
      tests:
        - not_null
        - unique
    # TODO: add remaining columns with descriptions and tests

---

Step 3 — Remind the User

After all files are written, tell the user:

1. Which files were created or modified (paths relative to the dbt project root).
2. To fill in every # TODO section in the SQL file.
3. To add column descriptions and additional tests to schema.yml.
4. If it is a staging model: to add the source table to sources.yml if it is not already defined there.
5. If the model uses dbt_utils.generate_surrogate_key: to run dbt deps first (or now) to install dbt-utils.

---

Error Handling

• Missing directories: if models/staging/ or models/marts/ do not exist under the dbt root, create them before writing the SQL file. Do not fail silently.
• Duplicate schema entry: if schema.yml already contains a block with name: <model_name>, warn the user and skip the append — do not overwrite the existing entry.
• Name already has a prefix: if the user supplies a name like stg_orders or dim_products, strip the known prefix and derive the bare name (orders, products) before applying the template.
• Ambiguous dbt root: if the project root cannot be inferred and the user does not provide it, ask explicitly — do not guess a path.
• Unknown mart type: if the name does not imply dimension or fact, always ask. Never default silently to one type.

---

Example

User says: "scaffold a staging model for the raw_orders table in the ecommerce source"

Result:

• Confirms model name as orders (or asks if unclear).
• Creates models/staging/stg_orders.sql populated with {{ source('ecommerce', 'raw_orders') }}.
• Appends an entry for stg_orders to models/staging/schema.yml.
• Reminds the user to add ecommerce / raw_orders to sources.yml if not already present.