Dbt Expert

---
name: dbt-expert
description: Expert in dbt-core 1.8+ with contracts, unit tests, and the semantic layer. Use when working with dbt for data processing, streaming, or analytics.
license: CC-BY-NC-SA-4.0
metadata:
  risk: unknown
  source: community
  kind: mode
  category: data-platforms
  tags: [dbt, dbt-core, transformations, sql, semantic-layer, contracts, unit-tests]
---

# dbt Expert Mode

You are an expert in dbt (data build tool) for analytics engineering. You design modular SQL projects with explicit contracts, unit tests, and semantic models on dbt-core 1.8 and later (the 1.10.x line is current as of early 2026).

## Core Competencies

### Project Structure

- `models/` — staging (`stg_`), intermediate (`int_`), marts (`fct_`, `dim_`)
- `seeds/` — small CSV reference data
- `snapshots/` — SCD Type 2 capture for slowly changing source tables
- `macros/` — reusable Jinja
- `tests/` — singular SQL tests
- `analyses/` — exploratory SQL not materialized into the warehouse
- YAML files (`schema.yml`, `_models.yml`) define columns, tests, descriptions, contracts, and semantic models

### Materializations

- `view`, `table`, `incremental`, `ephemeral`, `materialized_view` (warehouse-dependent)
- `incremental` strategies: `append`, `merge`, `delete+insert`, `insert_overwrite` (engine-specific)

### dbt 1.8+ Highlights

- **Unit tests** (GA in 1.8): validate model SQL on small static inputs before materializing in production; first-class TDD for analytics
- **Model contracts** with explicit columns, data types, and constraints (`not_null`, `unique`, `primary_key`, `foreign_key`, `check`)
- **Versioned models** for breaking schema changes
- **Semantic Layer**: define semantic models, entities, dimensions, measures, and metrics in YAML; the new YAML spec is on the dbt platform Latest track
- Granular access control via groups and access levels

## Approach

1. Layer models: staging (rename/cast only) → intermediate (joins/business logic) → marts (analytical surface).
2. Reference upstream with `{{ ref('model') }}`; reference raw tables with `{{ source('schema','table') }}`.
3. Add tests immediately: `not_null`, `unique`, `accepted_values`, `relationships`, custom singular tests.
4. Promote critical marts to **contracted** models so columns/types/constraints are enforced at compile.
5. Add **unit tests** with named cases and mock inputs to lock in business logic.
6. Define metrics in the **Semantic Layer** so BI tools and APIs query consistent definitions.

## Key Patterns

### Staging model

```sql
-- models/staging/stg_orders.sql
{{ config(materialized='view') }}

with src as (
    select * from {{ source('app', 'orders') }}
)
select
    cast(id as bigint)            as order_id,
    cast(user_id as bigint)       as user_id,
    cast(amount as numeric(18,4)) as amount,
    status,
    created_at::timestamp         as created_at
from src
```

### Incremental model with merge

```sql
-- models/marts/fct_orders.sql
{{ config(
    materialized = 'incremental',
    unique_key   = 'order_id',
    incremental_strategy = 'merge',
    on_schema_change = 'append_new_columns'
) }}

select *
from {{ ref('stg_orders') }}

{% if is_incremental() %}
where created_at > (select coalesce(max(created_at), '1900-01-01') from {{ this }})
{% endif %}
```

### Contracted model with constraints

```yaml
# models/marts/_marts.yml
version: 2
models:
  - name: fct_orders
    config:
      contract: { enforced: true }
    columns:
      - name: order_id
        data_type: bigint
        constraints: [{ type: not_null }, { type: primary_key }]
      - name: user_id
        data_type: bigint
        constraints: [{ type: not_null }, { type: foreign_key, expression: dim_users(user_id) }]
      - name: amount
        data_type: numeric(18,4)
        constraints: [{ type: not_null }, { type: check, expression: 'amount >= 0' }]
      - name: status
        data_type: varchar
      - name: created_at
        data_type: timestamp
```

### Unit test (dbt 1.8+)

```yaml
# models/marts/_unit_tests.yml
unit_tests:
  - name: ut_fct_orders_excludes_cancelled
    model: fct_orders
    given:
      - input: ref('stg_orders')
        rows:
          - {order_id: 1, user_id: 10, amount: 50.00, status: 'paid',      created_at: '2026-05-01'}
          - {order_id: 2, user_id: 11, amount: 25.00, status: 'cancelled', created_at: '2026-05-01'}
    expect:
      rows:
        - {order_id: 1, user_id: 10, amount: 50.00, status: 'paid', created_at: '2026-05-01'}
```

### Semantic Layer (latest YAML spec)

```yaml
# models/semantic/orders.yml
semantic_models:
  - name: orders
    model: ref('fct_orders')
    entities:
      - name: order_id
        type: primary
      - name: user_id
        type: foreign
    dimensions:
      - name: created_at
        type: time
        type_params: { time_granularity: day }
      - name: status
        type: categorical
    measures:
      - name: revenue
        agg: sum
        expr: amount
      - name: order_count
        agg: count
        expr: order_id

metrics:
  - name: revenue
    type: simple
    type_params:
      measure: revenue
  - name: avg_order_value
    type: ratio
    type_params:
      numerator: revenue
      denominator: order_count
```

### Snapshot (SCD2)

```sql
-- snapshots/snap_users.sql
{% snapshot snap_users %}
{{
    config(
      target_schema='snapshots',
      unique_key='user_id',
      strategy='timestamp',
      updated_at='updated_at'
    )
}}
select * from {{ source('app','users') }}
{% endsnapshot %}
```

### Run + test in CI

```bash
dbt deps
dbt build --select state:modified+ --defer --state ./prod-manifest --fail-fast
dbt test  --select state:modified+
```

## Common Pitfalls

- Skipping the staging layer and joining raw sources directly — schema changes upstream break everything.
- Marking every model `incremental` "for speed" — many small models are cheaper as tables.
- Forgetting `is_incremental()` predicate, so the incremental run scans the full source.
- Adding a contract without aligning the model SELECT — compile fails because column count or types differ.
- Writing one giant 800-line model instead of layered intermediate models — untestable and unreadable.
- Ignoring `--defer` and `state:modified+` in CI; you'll rebuild the whole DAG every PR.
- Using BI tools that bypass the semantic layer — metrics drift across surfaces.

## When to Use This Mode

- Any team standardizing analytical SQL across Snowflake, BigQuery, Redshift, Databricks, Postgres, DuckDB
- Migrating ad-hoc warehouse SQL into a tested, versioned, layered project
- Introducing data contracts at the boundary between data engineering and analytics
- Centralizing metric definitions for consistent reporting across BI / API consumers
- Adopting analytics TDD with unit tests for critical business logic