dbt Concepts
Transformations as version-controlled, tested, documented models whose dependencies are inferred rather than declared — and materialisation as a choice you make on purpose.
Who needs this, what one row is, and why the obvious build breaks
Every lesson starts from the consumer, because designing from the source outward is this domain's characteristic mistake.
What does a transformation framework actually give you that a folder of scheduled SQL scripts does not?
The next engineer, six months from now, who is asked why revenue moved and must answer from the repository alone: which model produced it, what it depends on, what it asserts, and what a previous version of it computed.
The unit is the model — one SELECT statement that produces one relation. One model, one file, one name, one grain. The discipline of one model per output is what makes the dependency graph derivable at all (The Transformation DAG).
Keep transformations as numbered SQL files in a repository — 01_stage.sql, 02_facts.sql, 03_marts.sql — and run them in filename order from a scheduled job. It is transparent, it needs no framework, and the ordering is right there in the names.
The number in the filename is the dependency graph, maintained by hand. Inserting a model between 02 and 03 means renaming files, and the first time someone forgets, a model runs before the thing it reads.
- The number in the filename is the dependency graph, maintained by hand. Inserting a model between
02and03means renaming files, and the first time someone forgets, a model runs before the thing it reads. - A model reads a table that another model writes, but nothing records that. The only way to know what feeds what is to read every file and grep for table names — which is exactly what nobody does during an incident (Data Lineage).
- Changing a column in
01_stage.sqlbreaks something downstream. Which something? There is no way to ask, so the change is made cautiously, slowly, or not at all (Impact Analysis). - One model needs rebuilding. The script runs all of them, because the runner has no notion of a subgraph, so a one-model fix costs a full rebuild (Compute Waste).
- Someone adds a
CREATE TABLEto one file and aCREATE VIEWto another, and now half the platform is materialised and half is not, decided by whoever wrote each file rather than by what it costs to query. - There are no tests, because there is nowhere to put them. Assertions live in a separate monitoring system that runs on a different schedule and does not stop a bad build from publishing (Data Tests).
What is actually happening
- The core move is small and load-bearing: a model refers to its upstream models by reference rather than by table name. In dbt that is
ref('stg_orders'), which compiles to the physical relation. Because references are function calls rather than strings, the tool can parse them and build the dependency graph without being told it (DAGs in Data Pipelines). - That one property produces most of the rest. A derived graph gives execution order, parallelism, selective rebuild of a subgraph, impact analysis in the reverse direction, and lineage that is generated rather than maintained — all from the same edges (Topological Execution).
- Materialisation is separated from logic. The same
SELECTbecomes a view, a table, or an incremental table by configuration, so the correctness of the model and the physics of storing it are independent decisions. That separation is the reason you can change one without re-reviewing the other. - Tests are assertions attached to models and run as part of the build, not as a separate monitoring job. That placement matters more than the tests themselves: an assertion that runs after publish is a monitor, an assertion that runs before publish is a gate (Data Quality).
- Because models are files, everything that applies to software applies to transformations — review, version history, branches, environments, rollback. The interesting claim of this whole category is that a data model is software, and had been treated as configuration for a decade (Data Engineering and DevOps).
- Documentation is generated from the same graph plus per-column descriptions in the model's configuration, so it is a build artifact rather than a wiki page that was accurate once (Dataset Documentation).
A model is a SELECT that names its inputs
ref() templating shown is dbt's. SQLMesh infers edges from the SQL itself and Dataform uses its own resolver; the derivable-graph property is shared, the syntax is not, and none of it is standard SQL.Everything in this category follows from one decision: a model does not write the name of the table it reads. It calls a function that resolves to that table, and because the call is parseable, the tool knows the edge exists without anyone maintaining a list of edges.
That is the entire mechanism. It sounds like a syntactic convenience and it is the difference between a folder of scripts and a graph. Order of execution, parallel scheduling, selective rebuild, impact analysis and lineage are all reads of the same edge set, derived from the code rather than described alongside it.
The corollary is that a hard-coded table name is not a style violation — it is a missing edge. The model will still build, in whatever order the rest of the graph happens to put it, and it will read whatever that table contained at the time. This is the one rule in the framework that is worth enforcing mechanically.
1-- One model, one output relation, one declared grain.2-- Every upstream is named through ref(), so the edge is derivable.3 4WITH orders AS (5 SELECT * FROM {{ ref('int_orders_enriched') }}6),7 8customers AS (9 SELECT * FROM {{ ref('stg_customers') }}10)11 12SELECT13 o.order_id, -- the grain: one row per order14 o.customer_id,15 c.customer_country,16 o.order_ts,17 o.net_amount_eur,18 o.status19FROM orders o20JOIN customers c21 ON c.customer_id = o.customer_idThe JOIN to customers is order-grain-preserving only if stg_customers is unique on customer_id. That claim belongs in the staging model's tests, not in this file's comments — which is exactly the separation the layering is for.
Materialisation is a decision, not a default
The same SELECT can be a view recomputed on every read, a table rebuilt on every build, or an incremental table that only processes new rows. The logic does not change; what changes is where the compute is spent and what "current" means.
The mistake is to pick one and apply it everywhere. A project where everything is a view pays for expensive logic on every dashboard load. A project where everything is a table rebuilds all of history every night to update one day. Both are defensible as a starting position and neither survives contact with a real read pattern.
The question that settles it is the read-to-build ratio and the fraction of the output that actually changes. High reads and low change means table. Low reads and cheap logic means view. Large history with a small changing tail means incremental — and incremental is where the interesting failures live, because it is the only option that carries state between runs.
How often is it read, how much does its logic cost, and what fraction of its output changes per build?
when Cheap logic — renames, casts, a filter — read occasionally, and always wanted at the freshness of its inputs.
cost The logic runs on every read. A view over a view over a view compiles into one large query whose cost nobody attributed to any model (Lazy Evaluation).
when Read repeatedly, joined widely, or expensive to compute. Anything a dashboard hits.
cost Rebuild cost proportional to all of history on every build, plus storage for the copy. Freshness is bounded by the build schedule.
when History is large, the changing tail is small, and a full rebuild no longer fits the window.
cost State between runs, a merge key that must genuinely be unique, and a permanent question about late-arriving rows for periods the incremental filter no longer scans (Late-Arriving Data).
when A logical step that exists for readability and has no consumer of its own.
cost It disappears from the warehouse, so it cannot be queried, profiled or tested directly — and a bug inside it is debugged by reading the compiled query of whatever inlined it.
when The engine can maintain it incrementally and the query pattern matches what the engine supports.
cost Refresh semantics, supported query shapes and staleness behaviour are engine features that differ substantially, so the model becomes non-portable (Comparing Analytical Warehouses).
Which materialisations exist, which incremental merge strategies are supported, and how warehouse-native materialised views refresh are all product features that change between releases. Verify current documentation for your warehouse and framework version before relying on any specific strategy.
The build is parse, run, test, document
A build is not "run the SQL". It is a sequence with distinct guarantees, and knowing which stage a failure came from is most of the debugging. A parse failure is a broken reference. A run failure is broken SQL or a missing upstream. A test failure is data that does not match a claim — the only stage that is about the data at all.
The ordering that matters most is that tests run before consumers see the result, at least in the sense that a failing build stops the models downstream of it. That is the difference between an assertion and a monitor: a monitor tells you the bad data is live, a gate stops it from being live.
The stage most often skipped is documentation, and skipping it is rational right up to the moment someone else needs to use a model. Generated docs give the graph and the types for free; the part that requires a human is the sentence saying what one row means, and that sentence is the single most valuable line in the project.
- 1Parse
Reads every model, resolves every reference, and constructs the dependency graph.
guarantees The graph matches the code exactly, and a cycle or an unresolvable reference fails here rather than at run time.
fails by Silently omitting an edge when a model hard-codes a table name instead of using a reference — the parse succeeds and the graph is wrong.
- 2Order
Produces an execution order consistent with the graph, and identifies which models can run concurrently.
guarantees Every model runs after everything it references. Nothing about wall-clock scheduling or upstream ingestion (Topological Execution).
fails by Being correct about the graph it knows and blind to dependencies outside the project — the ingestion job that has not finished yet.
- 3Run
Executes each model against the warehouse according to its materialisation.
guarantees Each model's SQL executed successfully and its target relation exists.
fails by Succeeding with zero rows. An empty upstream produces an empty model and a green run (The Pipeline Succeeded. The Data Is Wrong.).
- 4Test
Runs the assertions attached to models and sources.
guarantees The assertions that were written hold on the data that was built.
fails by Passing completely on a model with no assertions, which is indistinguishable in the summary from a model that passed forty.
- 5Document
Emits the graph, column-level metadata and descriptions as a browsable artifact.
guarantees The structure shown is generated from the project and is therefore current.
fails by Presenting structure as understanding — a complete lineage graph with no meaning attached to any column (Dataset Documentation).
- 6Publish
Makes the built relations visible to consumers.
guarantees Whatever atomicity the warehouse write provided, per relation.
fails by Not being atomic *across* models, so a half-finished build leaves consumers reading a mix of new and stale relations (Atomic Publish).
Only one of these six stages is about whether the data is right, and it is the one that passes trivially when nobody wrote assertions.
How to build it
Most important first.
- Keep one model per output relation and let the graph be derived. A model that writes two tables breaks every property in the list above, and the convenience it buys is never worth it.
- Use references everywhere. A single hard-coded table name is a missing edge, which means a missing lineage link, a wrong execution order and a subgraph rebuild that silently omits something.
- Choose materialisation from query pattern and cost, not from habit: view for cheap logic read rarely, table for anything read repeatedly or joined widely, incremental only when a full rebuild genuinely does not fit (Full Refresh vs Incremental).
- Attach the grain claim as a test on every model — uniqueness and not-null on the key that defines its grain. This is the highest-value test in the whole framework and it is one line (Grain: What Does One Row Represent?).
- Write column descriptions as you write the model. Documentation written later is documentation written from the code, which reproduces the code's assumptions rather than checking them.
- Treat the transformation repository like application code: pull requests, CI that builds against a sample or a separate schema, and a deploy that is a merge rather than a manual run (A Test Strategy Chosen by What Each Layer Can Prove).
What this actually promises
Naming the guarantee you do not have is worth more than naming the one you do — everything downstream inherits the weakest promise in the chain.
- The framework guarantees execution order consistent with the dependency graph. Every model runs after everything it references, on every run, without anyone maintaining a schedule.
- It guarantees that the graph matches the code, because the graph is parsed from the code. This is a stronger guarantee than any hand-maintained lineage and it is the main thing being bought.
- It guarantees that the assertions you wrote ran. It guarantees nothing about whether they were the right assertions, and a model with no tests builds and publishes exactly as happily as one with forty (The Pipeline Succeeded. The Data Is Wrong.).
- It does not guarantee atomic publish across models. A build that fails partway leaves earlier models updated and later ones stale, so consumers reading across the boundary see a mixed state unless the platform adds atomicity separately (Atomic Publish).
- It does not guarantee idempotency. An incremental model with a badly chosen unique key will happily accumulate duplicates on re-run; the framework runs what you wrote (Idempotent Data Pipelines).
Can I trust it?
A green pipeline is evidence that code ran. These four fields are the evidence that the data is right.
- The four assertions that belong on nearly every model: uniqueness on the grain key, not-null on the grain key and on any column an aggregate depends on, accepted values on every enumerated column a filter touches, and referential integrity to each dimension joined.
- Add a freshness assertion on every source, so a build that succeeds against a source that stopped updating fails rather than publishing a confident stale answer (Freshness Checks).
- What all of this misses is unchanged: the tests assert structure. A model whose business logic is wrong passes every one of them, builds green, publishes, and is documented — which makes it *more* convincing, not less (Two Dashboards, Two Numbers).
- Materialisation is the freshness dial. A view is always as fresh as its inputs and pays for that on every read; a table is as fresh as its last build and is cheap to read. The choice moves the cost between build time and query time without changing the logic.
- Incremental models trade freshness for build cost in a subtler way: they are fresh for new data and stale for anything that changed in a period they no longer look at, which is where late-arriving data goes to be forgotten (Late-Arriving Data).
- Because the whole graph builds in one run, per-model freshness is visible — you can say which model last succeeded rather than reporting one number for the platform (Freshness Monitoring).
- A column rename in a staging model breaks every downstream model that references it — loudly, at build time, in CI, before anything publishes. That is the whole argument for the layer (Model Layering).
- Adding a column is safe in the framework's sense and can still be unsafe in the data's sense: an incremental model built before the column existed will have nulls for all history until it is fully refreshed, and nothing about that is an error (Schema Evolution).
- Changing a materialisation from view to table changes when the logic runs and therefore what "current" means for anything it reads that is mutable. It is a behavioural change dressed as a configuration change.
- Rebuilding a subgraph is the framework's recovery primitive: select the model that was wrong and everything downstream of it, and rebuild exactly that. This is only possible because the graph is derived (Topological Execution).
- A full refresh of an incremental model is the escape hatch for any incremental bug, and it is the operation whose cost you should know before you need it. If a full refresh does not fit in any window, the model has no recovery path and that is a design problem (Full Refresh vs Incremental).
- Because models are versioned, recovering a *definition* is a revert. That is a genuinely different position from a warehouse full of hand-edited views, where the previous definition is gone.
What can go wrong
- A hard-coded table name instead of a reference: the edge is missing, the model runs in the wrong order, and it succeeds against yesterday's data.
- An incremental model with a unique key that is not unique, accumulating duplicates on every run while the build stays green.
- A build that fails halfway, leaving upstream models refreshed and downstream ones stale, so a cross-model join reads two different points in time.
- Tests that all pass because they were written to describe the data rather than to constrain it — the most common way a tested project is not a trustworthy one.
- A model materialised as a view whose logic is expensive, read by twenty dashboards, so the cost of the logic is paid once per read forever.
- "dbt is an orchestrator." It resolves dependencies within a transformation project and executes them in order. It does not schedule, does not manage cross-system dependencies, and does not know that an ingestion job must finish first — that is the orchestrator's job (Scheduler vs Orchestrator).
- "The tests prove the data is correct." They prove the assertions you wrote hold. A project with green tests and no assertion about business meaning is a project with green tests (Data Tests).
- "Incremental is the efficient default." Incremental introduces state, a merge key, and a whole class of late-data bugs. Use it when a full rebuild genuinely does not fit, and know the cost of the full refresh you will eventually need (Incremental Processing).
- "Documentation generated from the project is documentation." Generated docs give structure — names, types, lineage. They cannot give meaning, and the column description that says "the order date" is worse than none, because it looks like an answer (Dataset Documentation).
Operating it
- Per-model build duration and rows produced, run over run. A model whose output count jumps without an input count jumping is a fan-out (Volume Anomalies).
- Test pass and fail counts per build, and specifically the count of models with zero tests — that number is the honest measure of how much of the graph is unguarded.
- The generated lineage graph itself, used during incidents in both directions: what feeds this, and what else does this feed (Impact Analysis).
- At twenty models the graph is a convenience. At two hundred it is the only thing making the project navigable, and at that point conventions about naming and layering stop being style and start being infrastructure (Model Layering).
- Build times grow with the graph, not with data alone. Parallelism helps up to the width of the graph and no further — a long chain of dependent models cannot be made faster by adding workers (Amdahl's Law).
- Contributor count is what actually forces process. One author needs no review; ten authors sharing a staging layer need ownership, review and tests, or the layer becomes the least trustworthy part of the platform (Data Ownership).
- Materialisation is the main cost lever. A view moves cost to every read; a table moves it to every build. Which is cheaper depends entirely on the read-to-build ratio, and that ratio is knowable (Scan Cost).
- Selective rebuild of a subgraph is the second lever, and it is the one that stops a hundred-model project from rebuilding everything to fix one thing (Compute Waste).
- Tests cost a query each. That is real and usually small next to the models themselves, and it is the wrong place to economise (Data Quality).
- A framework buys graph, tests, docs and selective rebuild, and costs a dependency, a compile step, and a project structure everyone must learn. For a handful of transformations that is overhead; past a couple of dozen it is the cheaper option by a wide margin.
- Deriving the graph from references means the graph is only as good as the discipline. One hard-coded table name and the guarantee is quietly gone for that edge, with nothing warning you.
- SQL-first transformation is readable, reviewable and universally understood, and it is a poor fit for logic that is genuinely procedural. Forcing everything into SQL produces models that are correct and unreadable.
Model graph explorer
Change an input and watch which number moves — and which one does not. Everything here comes from a model in this repository, not from a measurement.
| fct_orders | |
|---|---|
| Layer | Mart — A stated grain and a stable set of columns. This is the layer a consumer is allowed to depend on, which is exactly why changing it is expensive. |
| One row is | One order, with measures and dimension keys. |
| Materialised as | incremental |
| Depends on | |
| Depended on by |
Where this applies
Almost nothing here is universal. These labels say what each claim is specific to, and where a different engine, format, warehouse or scale would differ.
- TOOL-SPECIFICThe reference-and-derive-the-graph idea is dbt's, and it has been copied widely — SQLMesh, Dataform and several warehouse-native features implement the same primitive with different names and different opinions about state and versioning. What transfers is the primitive; what does not is any particular configuration syntax.
- GENERALVersion control, tests-as-a-gate, derived lineage and materialisation-as-a-choice are properties any transformation layer can have, including a hand-rolled one. The framework makes them cheap rather than possible.
- WAREHOUSE-SPECIFICWhich materialisations are available and what they cost differs by warehouse — incremental merge strategies, table clustering and materialised-view refresh semantics are all engine features, so the same model configuration performs very differently across two warehouses.
Where the depth lives
This domain teaches how data moves and how you know it arrived intact. It hands the rest off by name.
- — DevOps / Production Engineering owns the CI that builds this project on a pull request, the environment separation between a development schema and production, and the rollback that a revert implies. Treating models as software is the claim; that domain owns the machinery.