dbt Models skill for Codex

Done well means: every model has one clear grain stated in its YAML, raw tables are touched only through source() in staging models, incremental logic is correct on both the first run and the hundredth, and the change was built and tested against a dev target before anyone suggests production.

Before changing anything

1. Read AGENTS.md, dbt_project.yml, packages.yml and the adapter in use (profiles.yml target type: snowflake, bigquery, postgres, databricks, duckdb…). Incremental strategies and SQL dialect depend on the adapter.
2. dbt --version. This skill targets dbt Core 1.8 to 1.10 and the same YAML in the Fusion engine. Older projects may use tests: instead of data_tests:.
3. Check the target: dbt debug and confirm which schema dev writes to. Never run dbt run/build against a production target without explicit user confirmation.
4. Learn the conventions already in use (dbt ls --resource-type model | head, folder names, prefixes). Follow them over the defaults below.

Layering

Layer	Prefix	Materialization	Rules
Staging	stg_<source>__<entity>	view	One per source table. Rename, cast, light cleaning. Only place source() appears
Intermediate	int_<entity>_<verb>	ephemeral or view	Joins, pivots, re-graining. Not exposed to BI
Marts	fct_<process>, dim_<entity>	table or incremental	Business grain, documented, contract-enforced if consumed downstream

A model reads from the layer below or the same layer, never from a later one. If two marts share logic, extract an intermediate model; do not copy SQL.

Staging model shape

-- models/staging/shop/stg_shop__orders.sql
with source as (
    select * from {{ source('shop', 'orders') }}
),
renamed as (
    select
        id::varchar                         as order_id,
        customer_id::varchar                as customer_id,
        lower(trim(status))                 as status,
        amount_cents / 100.0                as amount,
        created_at::timestamp               as created_at,
        updated_at::timestamp               as updated_at
    from source
    where _fivetran_deleted is not true
)
select * from renamed

# models/staging/shop/_shop__sources.yml
sources:
  - name: shop
    schema: raw_shop
    tables:
      - name: orders
        loaded_at_field: _loaded_at

Choosing a materialization

Situation	Choose
Cheap transform, small data, always fresh	view
Queried often by BI, rebuild under a few minutes	table
Large append-mostly fact, full rebuild too slow or costly	incremental
Helper CTE reused by a couple of models	ephemeral (debugging is harder; prefer view when in doubt)
Time-series fact with reliable event time, needs easy backfills	incremental with microbatch

Start with table. Move to incremental only when build time or cost demands it; incremental adds a class of correctness bugs.

Incremental models

{{ config(
    materialized='incremental',
    unique_key='order_id',
    incremental_strategy='merge',
    on_schema_change='append_new_columns'
) }}

select order_id, customer_id, status, amount, created_at, updated_at
from {{ ref('stg_shop__orders') }}

{% if is_incremental() %}
  -- 3-day lookback catches late-arriving updates; merge on unique_key dedupes them
  where updated_at > (select dateadd(day, -3, max(updated_at)) from {{ this }})
{% endif %}

Strategy	Use when	Watch out
merge	Rows update in place; you have a reliable unique_key	Duplicate keys in the new batch make the merge fail or duplicate rows
delete+insert	Adapter merge is slow, or replacing whole slices	Deletes by key first; still needs a unique key
insert_overwrite	BigQuery/Spark partition replacement	Replaces whole partitions; filter must cover full partitions
append	Immutable events, duplicates acceptable or removed later	Re-running a window duplicates rows
microbatch (1.9+)	Event-time data, backfills by date range	Needs event_time on the model and upstream refs

{{ config(
    materialized='incremental',
    incremental_strategy='microbatch',
    event_time='created_at',
    batch_size='day',
    begin='2024-01-01',
    lookback=2
) }}
select * from {{ ref('stg_events') }}   -- dbt filters each batch by event_time automatically

Backfill a range: dbt run -s fct_events --event-time-start 2025-09-01 --event-time-end 2025-09-08.

Incremental hard rules:

• The unique_key must actually be unique in each batch. Dedupe in the model with qualify row_number() over (partition by order_id order by updated_at desc) = 1.
• Use a lookback window; > max(updated_at) alone loses late rows.
• Test both paths: dbt build -s model --full-refresh and then a normal dbt build -s model.
• on_schema_change defaults to ignore: new columns silently never appear. Pick append_new_columns or fail deliberately.

Macros

-- macros/cents_to_currency.sql
{% macro cents_to_currency(column, scale=2) -%}
    round({{ column }} / 100.0, {{ scale }})
{%- endmacro %}

• Write a macro once logic repeats in three or more models, not before.
• Guard queries that run at parse time: {% if execute %}{% set rows = run_query(sql) %}{% endif %}.
• Cross-database logic: use adapter.dispatch and check dbt_utils first (dbt_utils.star, dbt_utils.generate_surrogate_key, dbt_utils.date_spine).
• Overriding generate_schema_name changes where every model lands; review it carefully.
• dbt deps downloads packages and needs network; in a sandbox ask for approval.

Day-to-day commands

dbt parse                                    # fast syntax and graph check
dbt compile -s fct_orders                    # inspect rendered SQL in target/compiled
dbt show -s fct_orders --limit 20            # preview results without materializing
dbt build -s +fct_orders                     # run and test the model and its parents
dbt build -s fct_orders --empty              # schema-only dry run (1.8+), zero rows
dbt ls -s state:modified+ --state prod-artifacts/

Slim CI: keep production manifest.json as an artifact, then dbt build -s state:modified+ --defer --state prod-artifacts/ builds only changed models and reads unchanged parents from production.

Gotchas

• Hard-coding analytics.raw.orders instead of source() or ref() breaks lineage, environments and slim CI.
• select * in marts lets upstream column additions leak into BI; list columns explicitly.
• Jinja renders before SQL runs; {{ this }} in a non-incremental first run refers to a table that does not exist yet, which is why it sits inside is_incremental().
• Changing an incremental model’s logic does not rewrite history. Say so and propose a --full-refresh (costly on big tables: confirm with the user first).
• Ephemeral models never exist in the warehouse, so nobody can query them while debugging, and they inflate compiled SQL; switch to a view when investigating.

Verification

• dbt build -s <model>+ passes in dev, including downstream tests.
• Compare against the old version: row counts and key metric totals using dbt show --inline "select count(*), sum(amount) from {{ ref('fct_orders') }}" in dev vs prod.
• For incremental changes: full-refresh result equals full-refresh-then-incremental result on the same data (no duplicates on unique_key).
• Data tests for the new grain exist; hand off detail to $dbt-testing.

PR description template

**Models:** fct_orders (table -> incremental merge, 3-day lookback), new int_orders_deduped
**Grain:** one row per order_id
**Built in dev:** dbt build -s +fct_orders+ (42 models, 118 tests passed)
**Parity:** dev vs prod row count 18,442,019 = 18,442,019; revenue diff 0.00
**Deploy note:** requires one --full-refresh of fct_orders after merge (approx 6 min on XL)