dbt

dbt Analytics Engineering AI Skill Guide

Overview & Engine Architecture

dbt compiles Jinja-SQL models into warehouse DDL/DML, managed via a DAG of ref() / source() edges. Runs materialize views/tables/incremental models; tests and documentation live beside SQL. Agents keep models thin and tested, prefer incremental strategies for large facts, and never hardcode environment-specific database names inside model bodies.

sources.yml -> staging models -> intermediate -> marts
                     |
              dbt compile/run/test
                     |
              warehouse relations + docs

When to use this skill

• Versioned SQL transforms in Snowflake/BigQuery/Redshift/DuckDB/etc.
• Data tests (unique, not_null, relationships, custom)
• Documenting marts for analysts

Operational directives

1. Stage raw sources 1:1 (stg_*) before business logic.
2. Use {{ ref('model') }} and {{ source('src','table') }} - never hardcode prod relations.
3. Add primary-key and relationship tests on mart grains.
4. Prefer incremental with a clear unique_key and bounded predicate for large facts.
5. Run dbt build (run+test) in CI on changed selectors when possible.

Model sketch

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

select
  o.order_id,
  o.customer_id,
  o.amount,
  o.created_at
from {{ ref('stg_orders') }} o
{% if is_incremental() %}
where o.created_at > (select coalesce(max(created_at), '1970-01-01') from {{ this }})
{% endif %}

Commands

dbt deps
dbt run --select marts.fct_orders+
dbt test --select fct_orders
dbt build --select state:modified+   # with defer/state artifacts in CI
dbt docs generate && dbt docs serve

Common failures

Symptom	Cause	Fix
Compilation error	bad Jinja/ref	dbt compile; check node name
Full-refresh surprise	incremental misconfig	review unique_key / predicates
Flaky tests	late-arriving data	warn severity; quarantine models
Slow CI	building entire project	select ancestors/descendants only