dbt Models skill for Claude Code

A dbt project is in good shape when every model has a declared grain that a test enforces, raw tables are touched only through source() in staging models, marts are built from ref() with explicit column lists, incremental models are idempotent with a late-data lookback, and CI builds only what changed.

Ground rules

• One staging model per source table: rename, cast, light cleaning. No joins, no business logic.
• Never hardcode schema.table names; use source() and ref() so lineage and environments work.
• State the grain (“one row per order line”) in the model description and test it.
• No select * in marts; explicit columns make contracts and reviews possible.
• --full-refresh on large production incremental models rebuilds history and can be expensive or lossy (if sources have aged out); require user confirmation.
• Run dbt build (run + test in DAG order), not dbt run alone.

Workflow

- [ ] 1. Inspect: dbt version, adapter, project layout, existing conventions
- [ ] 2. Declare sources with freshness
- [ ] 3. Staging models (1:1 with sources)
- [ ] 4. Intermediate models for reusable joins/logic
- [ ] 5. Marts (fct_/dim_) with grain, contract, tests, docs
- [ ] 6. Choose materializations; incremental where justified
- [ ] 7. Build the changed subgraph; verify row counts and tests

1. Inspect

dbt --version
dbt debug                                  # connection and profile
dbt ls --resource-type model | head -50
dbt ls -s +fct_orders                      # upstream lineage
cat dbt_project.yml packages.yml

Layout and naming

models/
├── staging/
│   └── stripe/
│       ├── _stripe__sources.yml
│       ├── _stripe__models.yml
│       ├── stg_stripe__charges.sql
│       └── stg_stripe__customers.sql
├── intermediate/
│   └── int_orders__joined_payments.sql
└── marts/
    ├── finance/fct_orders.sql
    └── core/dim_customers.sql

Layer	Prefix	Materialization	Contains
Staging	stg_<source>__<entity>	view	Renames, casts, dedupe of source quirks
Intermediate	int_<entity>__<verb>	ephemeral or view (table if heavy)	Joins, reshaping reused by marts
Marts	fct_<process>, dim_<entity>	table or incremental	Business-ready, documented, tested

2. Sources

# models/staging/stripe/_stripe__sources.yml
version: 2
sources:
  - name: stripe
    database: raw
    schema: stripe
    loaded_at_field: _loaded_at
    freshness:
      warn_after: { count: 6, period: hour }
      error_after: { count: 24, period: hour }
    tables:
      - name: charges
      - name: customers

Recent dbt versions also accept freshness and loaded_at_field under config:; follow whatever the project’s dbt version documents. Check with dbt source freshness.

3. Staging model

-- models/staging/stripe/stg_stripe__charges.sql
with source as (
    select * from {{ source('stripe', 'charges') }}
),
renamed as (
    select
        id                                  as charge_id,
        customer                            as customer_id,
        amount / 100.0                      as amount_usd,
        lower(status)                       as status,
        cast(created as timestamp)          as created_at,
        _loaded_at
    from source
    where livemode = true                   -- drop Stripe test-mode rows
)
select * from renamed

5. Mart with grain, contract, tests

-- models/marts/finance/fct_orders.sql
with orders as (select * from {{ ref('stg_shop__orders') }}),
payments as (select * from {{ ref('int_orders__joined_payments') }})
select
    orders.order_id,
    orders.customer_id,
    orders.ordered_at,
    payments.amount_usd,
    payments.payment_method
from orders
left join payments using (order_id)

# models/marts/finance/_finance__models.yml
version: 2
models:
  - name: fct_orders
    description: One row per order. Revenue in USD at time of payment.
    config:
      contract: { enforced: true }
    columns:
      - name: order_id
        data_type: varchar
        data_tests: [unique, not_null]
      - name: customer_id
        data_type: varchar
        data_tests:
          - not_null
          - relationships:
              to: ref('dim_customers')
              field: customer_id
      - name: ordered_at
        data_type: timestamp
      - name: amount_usd
        data_type: numeric
      - name: payment_method
        data_type: varchar
        data_tests:
          - accepted_values:
              values: [card, bank_transfer, wallet]

data_tests: is the current key (tests: still works). Newer dbt versions prefer generic test arguments nested under arguments:; match the project’s version. Composite grain: dbt_utils.unique_combination_of_columns.

Unit test for tricky logic (dbt 1.8+):

unit_tests:
  - name: refunds_reduce_amount
    model: int_orders__joined_payments
    given:
      - input: ref('stg_stripe__charges')
        rows:
          - { charge_id: c1, order_id: o1, amount_usd: 100, status: succeeded }
          - { charge_id: c2, order_id: o1, amount_usd: -30, status: refunded }
    expect:
      rows:
        - { order_id: o1, amount_usd: 70 }

6. Materialization decision

Situation	Materialization
Staging, cheap transforms	view
Mart queried often, rebuild < a few minutes	table
Large append-mostly fact, rebuild too slow/expensive	incremental
Very large time-series facts, backfills by period (dbt 1.9+)	incremental with microbatch strategy
Small reusable CTE	ephemeral (sparingly; hard to debug)

Incremental with merge and lookback:

{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge',
    on_schema_change='append_new_columns'
) }}
select order_id, customer_id, status, amount_usd, updated_at
from {{ ref('stg_shop__orders') }}
{% if is_incremental() %}
where updated_at >= (select dateadd('hour', -3, max(updated_at)) from {{ this }})
{% endif %}

The 3-hour lookback catches late-arriving and same-timestamp rows; unique_key makes the overlap idempotent. Without unique_key, overlap creates duplicates. Date function syntax varies by adapter (dateadd on Snowflake/Redshift, timestamp_sub on BigQuery); dbt.dateadd() macro is cross-database.

7. Build and verify

dbt build -s +fct_orders                   # model, its parents, and all tests
dbt show -s fct_orders --limit 10
dbt build -s state:modified+ --defer --state ./prod-artifacts   # slim CI
dbt source freshness
dbt docs generate

Slim CI needs the production manifest.json in --state; --defer reads unchanged parents from production instead of rebuilding them.

Sanity checks after a change:

• Row count vs. the previous version (dbt show --inline "select count(*) from {{ ref('fct_orders') }}", compare to prod).
• Grain test passes; no new nulls in key columns.
• Revenue/total reconciles with the source system for one recent day.

Review checklist

• No hardcoded relations; every raw table via source()
• Staging models have no joins
• Each mart documents and tests its grain
• Joins checked for fan-out (row count before/after)
• Incremental models have unique_key and a lookback
• Timestamps normalized to one time zone in staging
• sqlfluff lint (dbt templater) passes if configured

Gotchas

• A left join to a table with multiple rows per key fans out and inflates sums; join on the grain or aggregate first.
• Changing incremental logic does not rewrite old rows; a full refresh or targeted backfill is needed (confirm first).
• is_incremental() is false on the first run and on --full-refresh; test both paths.
• Tests only on marts let bad data pass through staging unnoticed; test primary keys in staging too.
• Snapshot (dbt snapshot) is for SCD type 2 of mutable sources; do not emulate it with incremental merges.
• on_schema_change='fail' (default ignore) is safer for contracts; ignore silently drops new columns.

Output

**Models added/changed:** stg_stripe__charges (new), int_orders__joined_payments, fct_orders (incremental, merge, 3h lookback)
**Grain:** fct_orders one row per order_id (unique + not_null pass)
**Build:** dbt build -s +fct_orders — 14 models, 38 tests, 0 failures
**Reconciliation:** 2026-10-18 revenue 412,331.20 vs Stripe dashboard 412,331.20
**CI:** slim CI with state:modified+ --defer (12 min -> 3 min)