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.