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

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.