How dbt works, and why orchestrators shouldn't split it into tasks | Windmill

Skip to main content

First post in a five-part series on data pipelines. This one is the primer: what dbt is architecturally, the five ways people run it in production, and why the arrangement that looks most sophisticated is the slowest and the most fragile.

Briefly, for anyone who has not used it: dbt is the transformation layer over a data warehouse. You write select statements as files, one per table you want to build, and each of those files is called a model. dbt wraps each model in the DDL that materializes it as a table or view, works out what order to run them in from the references between them, and sends the SQL to the warehouse to execute. A project is typically a few hundred models.

The counter-intuitive conclusion first: dbt should almost always run as one command, rather than split into one orchestrator task per model.

Splitting it up is tempting for two reasons, and both are worth wanting. You want to see which model failed without opening a log, and you want to retry that one model rather than the whole project. The argument of this post is that you get both more cheaply by keeping the record dbt already writes of what it ran, and reading it, than by turning two hundred models into two hundred tasks spread across workers.

That is not a contrarian take any more. It is where Dagster started, and it is where astronomer-cosmos, the library that popularised one Airflow task per dbt model, ended up after measuring its own default at roughly six times the cost of a single invocation. What follows is why the appealing design loses, and what the two systems that converged on it still got wrong on the way.

What dbt actually is​

Three things sharing a CLI.

A templating layer. A dbt model is a file containing a select statement with Jinja in it. {{ ref('stg_orders') }} is a macro call that returns the name of another model's relation. A whole model is just this:

-- models/marts/orders_daily.sql

{{ config(materialized='table') }}

select

date_trunc('day', ordered_at) as day,

count(*) as orders,

sum(amount) as revenue

from {{ ref('stg_orders') }}

where status = 'paid'

group by 1

dbt renders the template, wraps the result in DDL, and sends the string to your warehouse. With the BigQuery adapter and materialized='table', what actually arrives is:

create or replace table `analytics`.`orders_daily` as (

select

date_trunc('day', ordered_at) as day,

count(*) as orders,

sum(amount) as revenue

from `analytics`.`stg_orders`

where status = 'paid'

group by 1

);

The two things that changed are the two things dbt does: ref('stg_orders') became a real relation name, and the select got wrapped in the DDL its materialization implies. In dbt 1.x nothing in that path parses SQL, which is what post four in this series will take apart.

A dependency graph, derived for free. Because you had to write ref() to name another model, dbt can resolve every one of those calls at parse time and get a DAG out of it, with no annotation and no registry. This is the single best design decision in the tool, and it is why every integration in existence reads manifest.json.

A materialization compiler. materialized='table' compiles to create table as select. view to create view. incremental to a temp table plus a merge, or a delete+insert, or an insert overwrite, depending on the strategy and the adapter. snapshot to SCD2 bookkeeping. Each of these is written per warehouse dialect, in Jinja and SQL, in an adapter package.

One materialization matters later: ephemeral models are not built at all. dbt interpolates them into their dependents as a CTE prefixed __dbt__cte__. There is no object in the warehouse and no unit of work. A node in the manifest is not necessarily a thing that runs.

The artifacts land in target/. Two matter: manifest.json, the parsed project and its graph, and run_results.json, the record of what a given invocation actually did, node by node, with status and timing. run_results.json is the one that matters later.

And the architectural fact that everything else follows from: dbt does not process data. It generates SQL, sends it, and waits. dbt's own documentation is unusually clear on this: setting threads: 8 means dbt will work on up to 8 models at once "without violating dependencies", bounded in practice by the available paths through the graph, and a thread is "an open connection to your data warehouse, not the number of parallel threads on your local machine's CPU".

dbt is already an orchestrator​

As a job description, that is an orchestrator. dbt has:

a DAG, derived from the code rather than declared

a topological scheduler over it

a bounded work queue, --threads, whose unit is a warehouse connection

failure semantics: a failed node's descendants are skipped, the rest of the graph continues

a structured run log, run_results.json

and a resume command, dbt retry, which reads that log and rebuilds only...