Transformation as Code with dbt

An analyst opens a pull request that changes six lines of SQL in the revenue model. The review is quick, the tests pass, and it merges. The next morning the model rebuilds and every figure in the quarterly deck is different — not wrong, just different, because the definition changed and the model recomputed all of history with the new rule. The example is invented, and it names what makes this work unlike ordinary software delivery: deploying application code usually changes what happens next; deploying transformation code can also change what already happened.

This article is about the structure that makes that manageable — where loading ends and transformation begins, how dependencies come from the code itself, what gets tested and documented, how a code version relates to a data version, and who owns which part. The dbt examples depend on the selected materialization and adapter; verify behavior for the version you run.

Loading and transformation are different jobs

The first structural decision is to stop treating “the pipeline” as one thing. Extracting and loading gets data into the warehouse as faithfully as possible. Transformation turns it into the tables people query. Keeping the boundary sharp has practical consequences:

• Different failure modes. A loading failure usually shows up as data missing or duplicated; a transformation failure usually shows up as data that is there but means something other than what people think — though a wrong join or filter in a transformation produces missing and duplicated rows too, so the symptom points at a first suspect, not a verdict. They need different alerts and different people.
• Different change rates. Loading changes when a source system changes. Transformations change whenever the business redefines something, which is often more frequent.
• Different reruns. Re-running a transformation does not touch the source system, which is the real advantage; whether it is cheap and safe depends on the inputs still being retained, on how the output is applied — an append rerun duplicates — and on the result being verified before it is published. Re-extracting from a source costs the source something every time.

Transformation frameworks make the boundary explicit rather than implied. In dbt, the loaded tables are declared as sources — the documentation describes them as a way to name and describe the data loaded into the warehouse by extract-and-load tools — and models reference them through a function rather than by hardcoding a table name. The declaration is what turns “some table in raw” into a named dependency with an owner, a description, and expectations you can check.

Dependencies that come from the code

The second structural idea is that the execution order should be derived, not maintained. When a model refers to another model through a reference function, the tool knows the edge exists, and the build order follows from the code. Nobody updates a list of steps; nobody discovers at 03:00 that a new model was inserted in the wrong position.

Two consequences are worth stating plainly. First, this graph is the lineage that impact analysis starts from — with a limit worth knowing. It records which models read which models, so it answers “which datasets depend on this table.” It does not answer “which of them read this column,” because a ref is an edge between models and says nothing about the fields used inside the query. Narrowing a column change from twelve candidate models to the two that actually select it needs column-level lineage: SQL parsing, execution plans, or an explicit field mapping, collected as a separate capability. Second, the graph contains only what is expressed in it. A dashboard that queries a model directly, an export somebody scheduled, a notebook reading the table — these are consumers the graph does not know about, and they are where “we didn’t know anyone used that” comes from.

Where each model lives, and why it matters

A model is a query. Whether its result is stored, and how, is a separate decision — dbt calls these materializations, “strategies for persisting dbt models in a warehouse.” The choice is one line of configuration and it changes cost, latency, and what a rerun means.

Strategy	What happens on each run	The documented advice
View	Rebuilt as a view; no data stored, evaluates the current input when queried	Start here; change when you notice performance problems
Table	Rebuilt as a table from scratch	For BI queries and slow transformations many models depend on
Incremental	Processes only the rows selected for this run — by the filter the model writes or, under dbt’s microbatch strategy, by the configured event-time column, batch size, and lookback — then inserts, merges, or replaces partitions according to the strategy. Either way the selection is a rule you set, not a detected change set	For event-style data, when a full run becomes too slow — not a starting point
Ephemeral	Not built at all; interpolated into dependents as a CTE	Light logic used by one or two downstream models
Materialized view	Managed by the database, often auto-refreshing	When the database can own the refresh logic

Views retain a query definition that is evaluated when read; they reflect the input available then, so a stale loaded source still produces stale results. The source freshness check below tests that separate condition. Table materialization recomputes the result when run. Incremental models reuse prior target state and append, merge, or replace the selected scope. A full recomputation can repair earlier calculations when the needed inputs and reference history remain and the corrected logic is verified. Incremental processing can repair selected history, but errors outside that selection may remain. Its cost and reconciliation burden depend on the strategy, data volume, and retained state.

Three kinds of check, doing three jobs

Check	Asks	Runs
Unit test on the logic	Given these inputs, does the transformation produce the expected output?	In development and CI, on fixed fixtures
Data test on the output	Does the built table satisfy its rules — keys unique, values in range, references resolvable?	After each build, before publishing
Source freshness check	Is the input recent enough to build on at all?	Before the build, on a schedule

The third one is easy to skip and cheap to add. dbt lets a source declare a loaded_at_field with warn and error thresholds, and a command evaluates them; the documentation frames freshness checks as telling you whether your pipelines are in a healthy state and as an input to service level agreements. Without it, a stale source produces a successful build of yesterday’s numbers — the failure that looks like nothing happening.

The second row says “before publishing,” and that is a structure you build rather than a default you get. A data test runs against a relation the build has already written, so by the time it fails the table holds the bad data; what the failure can do is stop the work downstream of it — if the run is structured so that it does. dbt build runs tests in graph order and skips downstream resources when an error-level test fails, whereas dbt run followed by dbt test has already built everything by the time a test fails, and a warn-level test stops nothing. Outside that command, the orchestration has to read the test result and gate the next step on it. Keeping the previous version in front of consumers needs the build to write somewhere they are not reading — a staging or versioned relation — with the view or alias moved only after the checks pass.

The first two are covered in more detail elsewhere: what makes a definition change detectable, in unit and integration tests, and where assertions belong relative to publishing, in The Job Succeeded and the Numbers Are Wrong. What matters structurally is that all three are code in the same repository as the models, reviewed in the same pull request, so that a change to a model and the checks that constrain it move together.

Documentation describes; contracts constrain

Keeping model and column descriptions next to the code, and generating a browsable catalog from them, solves a real problem: documentation that lives elsewhere drifts unless something keeps it current, and living beside the code makes reviewing both together possible rather than guaranteed. It does not solve a different problem. A description saying “revenue excludes cancelled orders” is not enforced by anything; the day someone changes the filter, the description is simply wrong until a human notices.

What enforces is not the document but the checks built from it. A data contract is the promise — shape, but also meaning, grain, freshness, and what happens on change — and its parts are enforced by different means: a schema check or a tool’s model-contract feature for shape, a test with fixed inputs for meaning, an operating procedure for the rest. The written metric definition is what a person reviews when any of them changes, and it can be part of the contract rather than separate from it. Descriptions are how consumers find out what a column means; the contract and the test are how they find out when it stops meaning that.

Two versions, and why they must be told apart

Here is the difference from application deployment that the opening example pointed at. A table’s contents are a function of two things: the code that produced it and the input data it read. Both move, and confusing them makes incidents unresolvable. The table below is a comparison design rather than a verdict table: it says which comparison to run, and each cell still needs its own examination.

	Same code	New code
Same input	Numbers should be identical — given the same prior target state, run settings, macros, libraries, reference data, and clock; a difference means one of those moved, or the logic is non-deterministic	Ask what kind of change it was: a refactor or a performance fix must reproduce the numbers; a bug fix restores the result the approved definition always called for, and a definition change needs the new meaning approved; either can move figures already published, and then the impact and the agreed notification threshold decide whether it is a restatement consumers must hear about
New input	Usually ordinary daily change — but check what the input change was: a past-period correction, a deletion, or an incomplete delivery is not routine growth	Both at once — the hardest case to explain afterwards, and the one to avoid deploying together

The practices that keep the quadrants distinguishable are unglamorous. Record which code version produced a table, as a column or as run metadata. Deploy definition changes separately from large data changes so that a shift in the numbers has one candidate explanation. Keep the version history of models that others depend on, with deprecation notices rather than silent redefinition — the vocabulary of schema evolution and compatibility applies to meaning as well as shape. And when a definition change — or a bug fix — does move published figures, treat it as a restatement: say what changed, for which periods, and why.

The DataOps Manifesto, published by DataKitchen, makes versioning its structural answer: “Reproducible results are required and therefore we version everything: data, low-level hardware and software configurations, and the code and configuration specific to each tool in the toolchain.” Versioning data itself is the expensive half of that sentence, and most teams approximate it — immutable raw storage, partitioned rebuilds, snapshots of key dimensions — rather than achieving it literally.

Deploying a change to data

The delivery pipeline for transformation code looks like ordinary continuous integration with one extra question at the end: what happens to data that already exists?

1. Build the change in isolation. dbt’s CI documentation describes running jobs before merging, where “only the modified data assets in your pull request (PR) and their downstream dependencies are built and tested in a staging schema,” with each pull request given its own uniquely named schema so that pull requests do not collide — runs for the same pull request share that schema, so a new commit’s run supersedes and cancels the previous one. That describes dbt platform CI; a CI assembled around the command-line tool has to manage the production manifest and the state and defer selection itself.
2. Compare against production state. Where the CI job can defer to a production manifest — supplied by the platform, or managed by your own CI — unchanged upstream models can be read from production instead of rebuilt — which is what makes testing a one-model change take minutes rather than rebuilding a warehouse.
3. Review the diff in the numbers, not only in the code. For a definition change, the useful review artifact is the difference between old and new output on the same input — a handful of rows and a total, attached to the pull request. That needs the input pinned: deferring to production reads live tables, not a frozen snapshot.
4. Decide about history before deploying, not after. Leave past periods as they were computed, or rebuild them under the new definition — both are defensible, and the choice cannot wait, because for some materializations the deployment itself makes it. A table rebuilt in full restates every period it covers the moment it runs; a view changes what past periods return the next time anyone queries it. An incremental model is the one case where history can survive a deployment — and it is not automatic. Whether past rows change depends on what the model’s filter — or a microbatch configuration’s event-time window and lookback — selects, whether a unique_key causes existing rows to be updated, whether the strategy merges, appends, or replaces whole partitions, and whether anyone runs a full refresh. A model with a lookback window deliberately reselects past rows; a model with no filter reselects everything. Read those four settings rather than the word “incremental.” So settle the scope, the transition, the backfill plan and how to roll back, and keep publishing the verified new output separate from switching consumers onto it.
5. Tell the consumers whose numbers moved, before they notice.

One environment caveat is specific to data work. A development environment for application code can run on fake data; a development environment for transformations often cannot, because the bugs live in the real data’s edge cases. That pushes teams toward building against production data in isolated schemas, which then raises the access question: who can read what, in which environment, and is that consistent with how the same data is protected in production?

Where Python belongs

SQL in the warehouse covers most transformation, and the cases it does not are recognizable: calling an external service, parsing awkward formats, applying a model, or logic whose expression in SQL would be unreadable. Reaching for Python there is reasonable. What changes is the responsibility that comes with it.

• You own a runtime. Dependencies, versions, and the environment they run in are now yours to reproduce — which is why pinned dependencies and packaged code matter more here than in a notebook.
• You own the tests. Warehouse SQL is at least constrained by the database; custom code is constrained by whatever you assert about it. The ordinary unit and integration tests apply.
• You own the failure modes the warehouse used to handle: retries against a flaky API, memory limits on a large frame, and partial writes when the process dies halfway.

The practices are the same ones application teams use, applied to data code: version control, review, small changes, tests that run automatically. Software engineering habits for data work covers them, and Python for data engineers covers the packaging side.

What DataOps claims, and what it asks of the organization

DataOps is the name usually given to this whole posture. The DataOps Manifesto states it as a set of principles: that analytics is fundamentally code, that reproducibility requires versioning everything, that quality and performance should be monitored continuously to detect unexpected variation, that end-to-end orchestration of data, tools, code, environments, and the team’s work is a key driver of success, and that cycle time from an idea to a repeatable production process should be minimized. That is a position, argued from experience rather than measured, and worth reading as such.

Read as a checklist of technical practices, it is uncontroversial and easy to adopt partially. The part that is harder, and that no tool installs, is the allocation of responsibility underneath.

Question	Who answers it
Is the source data arriving, complete and on time?	Whoever owns loading — with freshness and completeness checks as evidence
Does this table mean what its definition says?	The model’s owner, with tests and a written definition
Is it safe to change this model?	The owner, using lineage plus query logs to find consumers
Who is called when the nightly build fails?	Named in advance, or discovered during the incident
Who decides that a definition changes?	The business owner of the metric, not the person editing the SQL

The last row is the one that is easiest to leave unanswered, and it is the reason the opening example is a governance failure rather than a tooling failure. The pull request was reviewed for correctness by people qualified to judge the SQL and not qualified to decide what revenue means.

References

• dbt Documentation, Materializations.
• dbt Documentation, Microbatch incremental models.
• dbt Documentation, Add sources to your DAG.
• dbt Documentation, build.
• dbt Documentation, Continuous integration.
• dbt Documentation, Defer.
• DataKitchen, The DataOps Manifesto