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', }}

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

Best practices

• Contract tests on public marts; keep staging columns renamed and typed.
• YAML docs for columns business users query.
• Separate dev targets with schemas per developer/PR.
• Pin adapter versions with dbt-core.

Limitations

• Orchestration is external (@airflow, @prefect, dbt Cloud jobs).
• Warehouse cost/permissions still owned by the platform team.
• Python models are adapter-specific and not a substitute for clear SQL.

Related skills

• @snowflake / @clickhouse / @duckdb - warehouse dialects
• @great-expectations - deeper data quality beyond dbt tests
• @airflow - schedule dbt jobs