dbt — Transformations as Code · Data Engineering Quest

The transformation layer of the modern data stack

dbt (data build tool) — currently 1.9 as of April 2026 — is how the modern data team writes the transformation half of ELT. Instead of orchestrators running Python that runs SQL, dbt is a tool dedicated to managing SQL transformations: as version-controlled code, with tests, docs, lineage, and a deployment story.

What dbt manages

Models — SQL SELECT statements that dbt materializes as tables or views.
Sources — declared raw upstream tables, with freshness checks.
Tests — assertions about model outputs (uniqueness, non-null, accepted values, custom).
Docs — auto-generated lineage graph + documentation site.
Snapshots — the dbt name for SCD Type 2 history tables.
Macros — Jinja-templated reusable SQL.

Why everyone is on dbt

Pre-dbt, the transformation layer was either ad-hoc SQL run by a scheduler, or buried inside an orchestrator's task. Either way, the SQL itself wasn't a first-class artifact — it didn't have tests, didn't have docs, didn't have version control discipline. dbt made the SQL the artifact, and once that happened, every other practice (review, test, lineage) followed naturally.

Code

A dbt model with a ref-based dependency, schema, and tests·sql

-- models/marts/orders/fct_orders.sql
-- Materializes as a table; the ref() call creates a dependency in the graph.
{{ config(materialized='table') }}

WITH src AS (
    SELECT * FROM {{ ref('stg_orders') }}
),
customers AS (
    SELECT * FROM {{ ref('dim_customers') }}
)

SELECT
    s.order_id,
    s.line_item_id,
    c.customer_key,
    s.product_key,
    s.date_key,
    s.quantity,
    s.amount_usd,
    s.discount_usd
FROM   src s
LEFT JOIN customers c
  ON   s.customer_id = c.customer_id
  AND  s.order_date >= c.valid_from
  AND  s.order_date <  c.valid_to

schema.yml — declarative tests and docs sit next to the model·yaml

version: 2

models:
  - name: fct_orders
    description: "One row per order line item. Joins to dim_customers at order time."
    columns:
      - name: order_id
        description: "Source order identifier."
        tests:
          - not_null
      - name: line_item_id
        tests:
          - not_null
      - name: customer_key
        tests:
          - relationships:
              to: ref('dim_customers')
              field: customer_key
      - name: amount_usd
        tests:
          - not_null
          - dbt_utils.expression_is_true:
              expression: "amount_usd >= 0"

Exercise

Install dbt-core for any warehouse you have access to (DuckDB locally is the easiest: pip install dbt-duckdb). Run dbt init, build one staging model and one mart model with a ref between them, add a not_null test on a key column, and run dbt build. The point is to feel the test → materialize → docs loop end-to-end.

dbt — Transformations as Code