dbt
**dbt (Data Build Tool)** is the **SQL-first transformation framework that brings software engineering best practices — version control, testing, documentation, and modular design — to data transformation pipelines** — enabling analytics engineers to define data models as SELECT statements that dbt compiles, executes against the warehouse, and documents automatically, becoming the standard "T" in ELT pipelines.
**What Is dbt?**
- **Definition**: An open-source command-line tool (and cloud service) that lets data teams write SQL SELECT statements as modular "models," which dbt compiles into warehouse-specific SQL, runs in dependency order against the data warehouse, and documents via auto-generated data catalogs.
- **ELT Architecture**: dbt handles the Transform step in ELT (Extract → Load → Transform) — data is first loaded raw into the warehouse by tools like Fivetran or Airbyte, then dbt transforms it into clean, analysis-ready tables using SQL models.
- **Models as SQL Files**: Each dbt model is a .sql file containing a SELECT statement — dbt manages all CREATE TABLE / CREATE VIEW boilerplate, materialization strategies (table vs view vs incremental), and dependency resolution automatically.
- **Software Engineering for SQL**: dbt introduces Git-based version control, automated testing (not_null, unique, referential integrity), CI/CD integration, and modular design patterns to SQL data transformation — previously an undisciplined manual process.
- **dbt Cloud**: The commercial SaaS product providing a hosted IDE, scheduled job execution, CI/CD integration, and the dbt Explorer data catalog — the managed alternative to dbt Core (open-source CLI).
**Why dbt Matters for AI and Data Engineering**
- **Reliable Training Data**: ML models trained on data with quality issues produce poor results — dbt's built-in testing framework validates uniqueness, null values, and referential integrity before data reaches training pipelines.
- **Feature Engineering in SQL**: Complex feature engineering (rolling averages, lag features, categorical encodings) expressed as dbt models — version-controlled, tested, and documented alongside application code.
- **Data Lineage**: dbt automatically generates a dependency graph of all models — trace exactly which source tables feed into any feature table used for ML training, satisfying data governance requirements.
- **Reproducibility**: Git-tagged dbt runs produce identical output from the same source data — pin training data to a specific dbt commit hash for reproducible ML experiments.
- **Analytics Engineering Role**: dbt created the "analytics engineer" discipline — engineers who own the transformation layer between raw data and business intelligence, combining SQL expertise with software engineering practices.
**dbt Core Concepts**
**Models (SQL Transformations)**:
-- models/staging/stg_orders.sql
{{ config(materialized='view') }} -- or 'table', 'incremental'
SELECT
order_id,
customer_id,
order_total,
CAST(created_at AS DATE) AS order_date
FROM {{ source('raw', 'orders') }} -- references raw source table
-- models/marts/customer_features.sql
{{ config(materialized='table') }}
SELECT
c.customer_id,
COUNT(o.order_id) AS order_count,
SUM(o.order_total) AS lifetime_value,
AVG(o.order_total) AS avg_order_value,
MAX(o.order_date) AS last_order_date
FROM {{ ref('stg_customers') }} c -- ref() resolves dependency
LEFT JOIN {{ ref('stg_orders') }} o ON c.customer_id = o.customer_id
GROUP BY 1
**Testing**:
-- models/staging/stg_orders.yml
version: 2
models:
- name: stg_orders
columns:
- name: order_id
tests:
- not_null
- unique
- name: customer_id
tests:
- not_null
- relationships:
to: ref('stg_customers')
field: customer_id
**Incremental Models**:
{{ config(materialized='incremental', unique_key='order_id') }}
SELECT order_id, customer_id, order_total, created_at
FROM {{ source('raw', 'orders') }}
{% if is_incremental() %}
WHERE created_at > (SELECT MAX(created_at) FROM {{ this }})
{% endif %}
**Macros (Reusable SQL Functions)**:
-- macros/cents_to_dollars.sql
{% macro cents_to_dollars(column_name) %}
({{ column_name }} / 100)::NUMERIC(10,2)
{% endmacro %}
-- Usage in model:
SELECT {{ cents_to_dollars('price_cents') }} AS price_dollars FROM orders
**dbt Commands**:
- dbt run: Execute all models against the warehouse
- dbt test: Run all data quality tests
- dbt docs generate && dbt docs serve: Generate and serve data catalog
- dbt build: Run models + tests + snapshots in dependency order
**dbt vs Alternatives**
| Tool | SQL-first | Testing | Docs | Orchestration | Best For |
|------|----------|---------|------|--------------|---------|
| dbt | Yes (only SQL) | Built-in | Auto-generated | External (Airflow) | Analytics engineering |
| Apache Spark | No | Custom | Manual | Airflow/Prefect | Big data transforms |
| Dataform | Yes (SQL+JS) | Built-in | Good | GCP-native | Google Cloud teams |
| Pandas | No (Python) | Custom | Manual | Standalone | Ad-hoc analysis |
dbt is **the SQL transformation standard that brought software engineering discipline to the analytics stack** — by treating SQL SELECT statements as version-controlled, tested, documented code artifacts rather than one-off scripts, dbt enables data teams to build reliable feature pipelines, training datasets, and business intelligence that maintain quality and reproducibility at enterprise scale.