Contents
Figure 2: dbt's lineage graph visualizes your transformation dependency chain — every ref() call connects models automatically
The ETL pipeline article's genuinely important finding applies directly here: most new data projects in 2026 follow ELT, not ETL — load raw data first, transform it inside the warehouse afterward. dbt is the tool built specifically for that second half of the equation. It doesn't extract anything, and it doesn't orchestrate anything. It does exactly one thing — the "T" — and does it as version-controlled, testable SQL rather than a pile of stored procedures nobody wants to touch.
dbt now ships as a Rust-based v2 engine by default, a genuinely significant rewrite from the Python-based version most existing tutorials still describe — bringing native SQL comprehension across multiple warehouse dialects, richer editor tooling, and meaningfully faster compilation. This is the transformation-layer piece that slots directly between the Airflow orchestration article and the raw data your ETL pipeline already loads.
By the end of this guide, you'll understand what dbt actually replaces, build a working transformation model, and know specifically what changes when your destination is a training dataset rather than a BI dashboard. IMO, the fact that "data transformation should be managed like code" is dbt's entire founding philosophy is worth sitting with — it's a genuinely different mental model from writing throwaway SQL in a notebook :)
What dbt Actually Replaces
dbt is an open-source framework letting data teams define, test, and document transformations directly inside their data warehouse, using SQL as the primary language rather than stored procedures or scattered Python scripts.
dbt does not extract or load data. It sits between your raw data — already loaded by Fivetran, Airbyte, or the custom Python ELT pipeline from earlier in this series — and your analytics or ML-ready layer, handling exclusively the transformation step.
It's not an orchestrator either. Recall the Airflow article: dbt doesn't handle global scheduling or cross-tool dependency management — it's commonly triggered by Airflow, not a replacement for it.
Instead of hand-writing complex stored procedures, you write modular SQL "models" that dbt compiles and runs directly against your warehouse's own compute — Snowflake, BigQuery, Databricks, or similar — meaning your transformations run exactly where your data already lives, with zero data movement required.
Why This Approach Genuinely Matters
Simplicity — SQL is the language most data professionals already know, which dramatically lowers the barrier to entry compared to a bespoke Python transformation framework.
Modularity — each model is self-contained, making individual pieces of a complex pipeline genuinely easy to maintain and debug independently rather than untangling one monolithic script.
Version control — dbt projects integrate with Git natively, so every transformation change is tracked, reviewable, and reversible, exactly the software-engineering discipline the MLOps articles argued classical data science teams too often skip.
Testing and documentation as first-class citizens — not afterthoughts bolted on, but genuine dbt commands (dbt test, dbt docs generate) baked into the standard workflow.
Installing dbt and Your First Project
pip install dbt-core dbt-postgres # or dbt-snowflake, dbt-bigquery, etc.
dbt init my_ml_pipeline
cd my_ml_pipelinePick the adapter matching your actual warehouse — dbt's adapter architecture means the core commands stay identical regardless of whether you're running against Postgres, Snowflake, or BigQuery underneath; only the connection configuration changes.
Your First Model
A dbt "model" is genuinely just a .sql file containing a SELECT statement — dbt handles turning that into an actual materialized table or view in your warehouse.
-- models/staging/stg_customer_orders.sql
SELECT
order_id,
customer_id,
order_date,
amount,
CASE
WHEN amount > 200 THEN 'large'
WHEN amount > 50 THEN 'medium'
ELSE 'small'
END AS order_size
FROM {{ source('raw', 'orders') }}
WHERE amount IS NOT NULLNotice {{ source('raw', 'orders') }} instead of a hardcoded table name — this is dbt's templating (Jinja) layer, letting you reference sources declaratively rather than baking a specific schema path into every query. Change your source configuration once, and every model referencing it updates automatically.
dbt run --select stg_customer_ordersThis compiles the Jinja-templated SQL into a real query and executes it against your warehouse, materializing the result as a view or table depending on your project configuration.
Building the Dependency Graph: ref() Is the Whole Trick
The genuinely important pattern that makes dbt projects scale is referencing other models by name, not by hardcoded table paths.
-- models/marts/customer_features.sql
SELECT
customer_id,
COUNT(order_id) AS total_orders,
SUM(amount) AS lifetime_value,
AVG(amount) AS avg_order_value,
MAX(order_date) AS last_order_date
FROM {{ ref('stg_customer_orders') }}
GROUP BY customer_idThat {{ ref('stg_customer_orders') }} call is doing genuinely important work beyond just referencing a table — dbt parses every model's ref() calls to build a full dependency graph automatically, then runs models in the correct order without you ever manually specifying "run this before that." This is conceptually the same DAG idea from the Airflow article, just built implicitly from your SQL's actual data lineage rather than explicit >> operators.
dbt runRunning without --select executes your entire project in dependency order — staging models first, then anything downstream that references them.
Testing: Data Quality as Part of the Pipeline, Not an Afterthought
# models/marts/schema.yml
models:
- name: customer_features
columns:
- name: customer_id
tests:
- unique
- not_null
- name: lifetime_value
tests:
- not_nulldbt testThis genuinely catches exactly the kind of silent data quality problem the ETL pipeline article warned about — a duplicate customer ID or an unexpected null slipping through undetected. Running dbt test as part of your CI/CD pipeline (GitHub Actions, GitLab CI) means a broken transformation fails loudly before it ever reaches a model training job downstream, rather than silently corrupting your training data.
The Specific Case for ML: Solving Train-Serve Skew
This is genuinely the reason this tutorial exists as a distinct article rather than a generic dbt intro, and it's worth understanding precisely.
ML systems need transformations in two genuinely different contexts: during training, operating on large historical batches where some computational expense is tolerable, and during inference, needing low latency on individual records or small batches. Inconsistencies between these two — the same feature computed slightly differently in training versus production — cause train-serve skew, where a model performs beautifully in development and then quietly underperforms in production because it's encountering differently transformed data than it learned from.
dbt addresses this directly by representing the transformation logic as version-controlled code that applies identically whether you're preparing historical training data or transforming real-time inference inputs. The same customer_features model definition above, run against a training-time snapshot or a fresher inference-time slice, applies mathematically identical logic — there's no separate "training version" and "production version" of the feature engineering to accidentally drift apart.
This is genuinely the single most important architectural reason to use dbt specifically for ML feature engineering rather than maintaining separate Python feature logic for training and a duplicated version for serving.
dbt's Layered Model Structure: Bronze, Silver, Gold
A pattern worth adopting from the start rather than retrofitting later — organizing models into progressively refined layers.
dbt run --select models/bronze # raw, lightly cleaned
dbt run --select models/silver # joined, deduplicated, typed
dbt run --select models/gold # feature-engineered, ML-readyBronze — close to raw source data, minimal transformation, mostly type-casting and basic cleaning.
Silver — joined across sources, deduplicated, business logic applied.
Gold — genuinely ML-ready feature tables, the layer your training pipeline actually reads from.
This layered structure directly mirrors the ETL pipeline article's small-named-functions principle, just expressed as separate SQL models instead of Python functions — each layer isolates a distinct transformation concern, making the whole pipeline easier to debug and reason about incrementally.
Connecting dbt Into the Rest of This Series' Pipeline
Recall the full stack this series has built toward: extraction and loading (the ETL article's Python pipeline or a tool like Fivetran/Airbyte), transformation (dbt, this article), and orchestration (Airflow, the previous article) coordinating when each step runs.
# Inside an Airflow DAG, from the previous article
@task
def run_dbt_transformations():
import subprocess
subprocess.run(["dbt", "run", "--select", "models/gold"], check=True)Airflow triggers dbt; dbt doesn't trigger itself, and it doesn't extract or load anything. This division of labor is genuinely deliberate — each tool does the one thing it's actually built for, rather than any single tool trying to be the entire pipeline. The dbt-fal community package even extends this further, letting you run Python (for actual model training) directly alongside dbt's SQL transformations within the same project.
Where dbt Genuinely Falls Short for ML
Worth being honest about the boundaries here, since they matter for deciding what stays in dbt versus what moves elsewhere.
SQL-only by design — complex ML-specific transformations (embeddings, custom feature encodings beyond what SQL expresses cleanly) need a richer language. Support for Python models exists but remains limited, mostly concentrated in Databricks-specific environments rather than being universally available across every warehouse adapter.
No orchestration or ingestion of its own — dbt requires pairing with tools like the ones covered in the ETL and Airflow articles for the rest of the pipeline; it deliberately does not try to be an all-in-one platform.
Query cost is a real, direct concern — since every transformation runs as warehouse compute, a poorly optimized model can genuinely inflate your Snowflake or BigQuery bill in a way a local Python script never would.
Complexity at scale needs real governance — large projects with many interdependent models can develop cascading dependencies and performance degradation without deliberate discipline around model organization and review.
Common Mistakes People Make
Hardcoding table names instead of using ref() and source(). This breaks dbt's automatic dependency resolution and defeats the entire point of letting the tool manage execution order for you.
Skipping tests until "later." dbt test is cheap to add early and catches exactly the silent data quality issues that otherwise surface as a confusing model performance regression downstream.
Trying to do ML-specific feature engineering entirely in dbt when it needs richer logic than SQL comfortably expresses. Recognize the boundary — dbt for the tabular, SQL-expressible transformations; Python (in your training pipeline or via dbt-fal) for anything genuinely more complex.
Ignoring query cost until a warehouse bill spikes. Poorly structured models running redundant full-table scans repeatedly add up fast on consumption-priced warehouses.
Maintaining separate training and inference transformation logic instead of a single dbt model both paths share. This is precisely how train-serve skew creeps in — the whole point of using dbt for ML features is one shared definition, not two that can silently drift apart.
Recommended Books
- dbt (data build tool) Best Practices — the definitive community reference for structuring dbt projects at scale, covering testing strategies, documentation patterns, and team governance that actually works in production.
- Fundamentals of Data Engineering by Joe Reis & Matt Housley — covers the full data stack including the transformation layer dbt occupies, giving essential context for where this tool fits in the broader architecture.
- Designing Machine Learning Systems by Chip Huyen — the definitive guide to getting ML models from notebook to production, including feature store design and the train-serve skew problem dbt specifically addresses.
- The Data Warehouse Toolkit by Ralph Kimball — the foundational text on dimensional modeling that directly informs dbt's mart layer patterns and how to structure feature tables for ML consumption.
Wrapping This Up
dbt handles exactly one job in the modern ELT stack — transformation — and does it as version-controlled, tested, documented SQL that runs directly inside your warehouse's own compute, with ref() and source() building an automatic dependency graph so you never manually order your models. For ML specifically, its real value is solving train-serve skew: one shared transformation definition, used identically whether you're preparing a historical training set or transforming a fresh inference request.
Remember that dbt deliberately doesn't extract, load, or orchestrate anything — it's the middle layer this series' Python ETL pipeline and Airflow orchestration wrap around — and that SQL-only design means genuinely complex feature engineering still needs Python alongside it. FYI, this closes out the extract-transform-orchestrate trilogy running through the last three articles in this series: your Python script gets the data in, dbt shapes it consistently for both training and inference, and Airflow decides when each step actually runs :)
Now go take the daily_sales_summary table from the ETL pipeline article's Python script and rebuild that same aggregation as a dbt model instead. Watching the identical transformation logic move from a pandas groupby into a version-controlled, tested SQL model is genuinely the clearest way to feel what dbt actually adds over a raw script.