Data Pipeline CI/CD and Environment Promotion
Application code is deployed to servers that hold no history. A data pipeline is deployed to a warehouse that holds years of it. That one difference changes most of what continuous integration and delivery have to do. A bad web release can be undone by deploying the previous build, because the build was the only thing that changed. A bad pipeline release may already have rewritten tables, advanced watermarks, and fed dashboards by the time anyone notices, and redeploying the previous code does not restore any of it. The discipline of data pipeline CI/CD is therefore less about automating the deploy command and more about deciding, before the change is released, how much damage a wrong change can do, how it will be detected, and how the data will be repaired.
This article covers the path a pipeline change takes from a developer’s branch to production: the fixtures that let tests run without production data, the contract tests that protect the boundary between a producer and its consumers, the isolated environments that let a candidate build run without touching production, the promotion steps that move a verified change forward, and the rollback and migration techniques for the cases where something gets through. It assumes the operating ideas from Airflow Operations: Retries, Backfills, and Safe Recovery: a run owns a data interval, a repeated write must be safe, and recovery must reconcile the orchestrator’s records with the destination’s. It also uses the model, test, and incremental-correctness concepts from Transformation as Code, because dbt supplies the concrete examples. The ideas themselves apply to any transformation tool.
The factual basis is the dbt documentation read in early October 2026 (dbt-core 1.12.5 was current) and the Airflow 3.3.2 documentation, together with local runs of dbt-core 1.12.5 and the dbt-duckdb adapter 1.11.0 on small invented tables in DuckDB. The runs show how the tool behaves in a small controlled case; they do not measure performance, and a different warehouse adapter can differ in constraint enforcement and incremental strategies. Where a statement is reasoning from documented behavior and was not reproduced, it says so.
What a pipeline change can break
Before choosing checks, classify the change, because different changes damage different things and need different guards. The table lists the common kinds, what each can affect, and the cheapest check that would catch the damage before release.
| Kind of change | What it can damage | Cheapest effective check | What the check cannot see |
|---|---|---|---|
| Transformation logic (a filter, a join, a calculation) | Values in the model and everything downstream; for incremental models, only the rows selected after the change | Unit tests on fixture rows around boundaries; a comparison with the previous version on a sample | Real data shapes the fixture did not include |
| Output schema (rename, type change, dropped column) | Every consumer that reads the changed relation | Contract preflight and a consumer-compatibility check against the candidate schema | Consumers that are not registered anywhere |
| Meaning of an existing column (unit, time zone, definition) | Consumers, silently; no structural check fails | Data tests on accepted ranges and units; a written contract; consumer sign-off | A change that stays within the accepted range |
| Orchestration (schedule, retries, dependencies, DAG identifier) | Which intervals run, and how often; history in the orchestrator | Import and graph-structure checks; a dry run of the intervals the schedule would create | Behavior under real load and real failures |
| Configuration and credentials | Where the pipeline reads and writes, which can include production from a non-production run | Environment-specific configuration review; permissions that make cross-environment writes impossible | A misconfigured permission that is too broad |
| Data volume or source behavior (not a code change) | Runtime, cost, and correctness assumptions such as late data | Monitoring and reconciliation after release | Anything before it happens |
The last row is included because CI/CD cannot be the only line of defense. A pipeline can break with no change to its code, and the checks that catch such failures belong to operations. The purpose of the rest of this article is the first five rows, which a team can test before release.
Data and code fixtures
What a fixture is for
A fixture supplies the controlled starting state for a test. The glossary entry on test fixtures lists what that can include: input rows, an explicit schema, session settings, temporary files, and setup or cleanup of dependencies. For a pipeline, fixtures come in two layers. Code fixtures are small objects constructed in the test itself: a handful of rows given to one transformation, a configuration object, a DAG file loaded without running it. Data fixtures are stored datasets of larger size: a seed table, a generated synthetic table, a masked sample of production, or a cloned copy of production state. The two layers answer different questions, and conflating them produces either tests too slow to run on every commit or tests too small to mean anything.
Fixtures for logic: choose rows that tell implementations apart
A good logic fixture is small and contains the rows that distinguish a correct implementation from a plausible wrong one. The generic advice in the fixture entry is to pick inputs that separate zero from missing, a duplicate from a unique row, and an instant on a time boundary from one outside it. The principle is the same in unit tests generally: a fixture is input, not the expected answer, and the expected result must come from the rule, not from running the code under test. The check that a fixture is doing its job is to break the implementation in a plausible way and confirm that the test fails. In the previous article a one-character change from < to <= on a day boundary turned an expected total of 15 over 2 rows into 22 over 3 rows, and the unit test reported the difference.
Incremental models need a fixture with a second input. The state of a table after previous runs is part of the input to the next run, so a faithful unit test supplies both the new source rows and the existing table. dbt allows this by overriding the is_incremental macro and providing the existing table as the input named this. The documentation notes two constraints: the incremental model must already exist in the database before the unit test runs (it suggests building an empty version with the --empty flag to save compute), and the expected output of such a test is what the materialization will merge or insert, not the final table. The framework’s own insert or merge step is not covered; the documentation says there is currently no way to unit test it.
The test below targets the drift from the previous article. The model selects rows strictly newer than the table’s maximum updated_at. The fixture says that the existing table holds payment 1 with amount 40 and payment 3, and that the source now shows payment 1 corrected to 45 (same date) and a new payment 4. The test expects the incremental run to select the correction as well as the new row.
unit_tests:
- name: inc_selects_correction_to_an_already_loaded_row
model: fct_payments_inc
overrides:
macros:
is_incremental: true
given:
- input: ref('stg_payments')
rows:
- {payment_id: 1, order_id: 100, amount: 45, updated_at: 2024-10-01}
- {payment_id: 3, order_id: 102, amount: 10, updated_at: 2024-10-02}
- {payment_id: 4, order_id: 103, amount: 30, updated_at: 2024-10-03}
- input: this
rows:
- {payment_id: 1, order_id: 100, amount: 40, updated_at: 2024-10-01}
- {payment_id: 3, order_id: 102, amount: 10, updated_at: 2024-10-02}
expect:
rows:
- {payment_id: 1, order_id: 100, amount: 45, updated_at: 2024-10-01}
- {payment_id: 4, order_id: 103, amount: 30, updated_at: 2024-10-03}
- {payment_id: 3, order_id: 102, amount: 10, updated_at: 2024-10-02}The expectation includes payment 3 on purpose: the intended design re-selects a three-day lookback window and relies on the unique key to replace unchanged rows harmlessly. Against the strict filter, updated_at > (select max(updated_at) from {{ this }}), the test failed and printed a diff in which the two expected rows were marked as missing from the actual output (the date formatting in the tool’s printout is simplified here):
actual differs from expected:
@@ ,payment_id,order_id,amount,updated_at
---,1 ,100 ,45 ,2024-10-01
---,3 ,102 ,10 ,2024-10-02
,4 ,103 ,30 ,2024-10-03Read the diff as the fixture speaking: the model selects only payment 4, so the correction to payment 1 would never reach the table. With the filter changed to a three-day window, updated_at >= (select max(updated_at) - interval 3 day from {{ this }}), the same test passed. This test caught in milliseconds, with six rows of invented data, the defect that the comparison test in the earlier article needed a mutated source and a full rebuild to catch. It is also narrower than that comparison. It verifies the selection rule against the cases the author imagined, and the author imagined this one only because the drift had already been understood. It says nothing about whether a lookback of three days is long enough for real corrections, which is a question about the source’s behavior and not about the code.
Fixtures for data: provenance, privacy, and size
Stored datasets for integration-style tests carry risks that small inline rows do not. A masked or sampled copy of production is realistic, and its realism has a cost: it may contain personal information that the test environment is not permitted to hold, and a sample that does not preserve rare cases tests the common path only. The safe default for test data is invented or properly licensed data, never real personal data or secrets. For a pipeline team that needs production-like shapes, the safer approach is to generate synthetic data from a stated specification, with the rare and hostile cases added deliberately: nulls in key columns, duplicate keys with different payloads, a record exactly on an interval boundary, a late-arriving correction, an empty interval. Each such case is a regression test for a failure someone has already seen or can reason about. The fixture then documents the pipeline’s known hazards, which is worth more than its volume.
Size is a trade-off with execution cost. A fixture of a few rows runs in a unit test in a fraction of a second. A fixture large enough to show a performance problem needs a warehouse and takes minutes, which belongs in a scheduled job, not in the gate every commit passes. The same split applies to the orchestrator: a code fixture can load the DAG file and assert its task ids, dependencies, retry settings, and the intervals its timetable assigns, as the example in Apache Airflow Fundamentals did, without starting a scheduler. Testing under a scheduler with real workers is a data fixture problem of a different kind and needs an isolated environment, discussed below.
Contract tests
A contract names what the other side may rely on
A contract test checks the boundary between a producer and a consumer. The producer is the pipeline stage that publishes a dataset; the consumers are the dashboards, models, and services that read it. The question is whether the candidate version of the producer still provides what the consumers rely on. A data contract states that reliance: not only the column names and types but also the grain (one row per order), the units, the time basis, the freshness, and the treatment of updates and deletions. A schema lists columns; a contract lists promises, and a promise can be broken without changing the schema at all.
Three kinds of contract test cover three layers of promise, and each layer is enforceable by different machinery.
| Layer | What is promised | How it is tested | Example failure it catches |
|---|---|---|---|
| Structure | Column names, types, nullability, key constraints | A preflight comparison of the candidate’s output schema with the declared one; consumer queries compiled against the candidate schema | A renamed or retyped column |
| Semantics | Grain, units, accepted values, referential integrity, freshness | Data tests (unique, accepted values, relationships) and custom assertions on the candidate build | Duplicate keys; an unexpected status value; amounts in the wrong unit when they fall outside the accepted range |
| Behavior | What specific consumer queries return | Run the consumers’ registered queries or models against the candidate and compare results with the current production results | A changed aggregate that every structural check accepts |
Structural contracts in dbt, with a real failure
dbt supports structural contracts on models. Setting contract: {enforced: true} and listing every column with a name and data type makes dbt perform a preflight check that the query returns columns whose names and types match, independent of order, and it includes the names, types, and any supported constraints in the DDL. The documentation lists supported materializations (table, view, and incremental with on_schema_change set to append_new_columns or fail), notes that constraint support and enforcement vary by platform, and states that contracts do not apply to ephemeral models, materialized views, snapshots, seeds, or sources.
models:
- name: fct_payments_full
config:
materialized: table
contract: {enforced: true}
columns:
- {name: payment_id, data_type: integer, constraints: [{type: not_null}]}
- {name: order_id, data_type: integer}
- {name: amount, data_type: integer}
- {name: updated_at, data_type: date}The model built successfully against this contract. Two edits to the SQL then broke it. First, amount was renamed to amount_usd. Second, amount was cast to double. Both runs failed before building, with the same compilation error:
Compilation Error in model fct_payments_full (models/fct_payments_full.sql)
This model has an enforced contract that failed.
Please ensure the name, data_type, and number of columns in your contract
match the columns in your model's definition.After the failed builds the existing table in the warehouse still had the original columns, amount as an integer included, so consumers kept reading the previous version. That is the useful property of a preflight: a breaking change fails the build in CI, or in production if it slips through, without publishing a half-changed relation. The result is specific to the adapter and version used here; the message names the failure class but not the offending column, so a reader must compare the SQL with the contract to find it.
The limits matter as much as the catch. The check is structural. A change from dollars to cents in an integer column passes it, as the glossary entry on schema evolution and compatibility points out; an assumed example makes the point concrete. Suppose the contract says amount integer, and a refactor changes the unit from major to minor currency units, so a \$12.50 order moves from 12 (rounded) to 1250. The contract passes. Every revenue figure that sums the column is now off by two orders of magnitude, and no structural check will say so. Only a semantic test (an accepted range or a comparison with an independent total) or a behavioral comparison with the previous version can. This is why a contract should name the unit and a test should enforce what can be enforced.
Consumer-side checks and versioned breaking changes
A producer cannot test consumers it does not know about. The behavioral layer therefore needs a registry of consumers, which in practice is incomplete. Lineage and query logs find some consumers within their coverage and retention, and the entry on schema evolution notes that exports, infrequent queries, and shared identities can leave consumers invisible to both. The registry, the accountable owners, and a notice period are the organizational half of the contract, and a pipeline can only enforce the half it can see.
When a breaking change is intentional, the contract should be versioned and not edited in place. dbt’s model versions let a producer publish a new version of a model alongside the old, with the old version marked with a deprecation_date (introduced in dbt 1.6, according to the documentation) so that consumers have a window to move. The documentation also says that adding a column is a non-breaking change that does not need a new version, and it cautions that governance features like contracts and versions add structure that can make rollbacks harder and maintenance heavier if adopted before models stabilize. That is a real trade-off: contracts protect consumers and slow producers, and the right place to enforce them is the models whose consumers are numerous or outside the team.
Isolated environments
What has to be isolated
An environment is more than a database. A candidate build interacts with the compute that runs it, the storage it writes, the credentials it uses, the source data it reads, and the orchestrator that triggers it. Isolation means that a mistake in any of those, made by a branch under test, cannot affect production. The most common failure of isolation is not a technical exploit but a shared setting: a non-production DAG that uses the production connection, a staging job whose schema name falls back to the production schema, or a developer’s local profile that points at the shared warehouse.
| Resource | Isolation mechanism | Typical failure when missing |
|---|---|---|
| Warehouse schemas | A separate schema or database per developer, per pull request, and per environment; names derived from the target | Two branches write the same table and overwrite each other |
| Credentials and permissions | Per-environment service accounts; permissions that make non-production principals unable to write production objects | A test run with a production credential writes production data |
| Source data | A staging source, a sample, or a masked copy; sources selected by configuration | A test reads full production tables and costs or leaks too much |
| Orchestrator state | Separate deployments or at least separate DAG identifiers, schedules, and connections; non-production schedules paused | A branch’s DAG runs on the production schedule or shares run history |
| Code version | The version a scheduler and workers run is pinned (a versioned DAG bundle, or a deployed artifact) | Workers and the scheduler see different versions of a DAG during a rollout |
Environments in dbt: targets, schema names, and per-pull-request schemas
dbt expresses environments as targets in a profile. The connection-profile documentation describes defining several targets (development, staging, production) in one profile, selecting one with --target, and keeping credentials out of the file by reading environment variables. The schema a model is built into comes from a macro, generate_schema_name. By default it uses the target’s schema and, when a model configures a custom schema, joins the two as <target schema>_<custom schema>. The documentation warns against dropping the target schema from that macro, because doing so makes developers who define their own custom schemas overwrite each other’s models; and it describes a common customization that uses custom schemas in production and ignores them everywhere else, so that development and CI builds land in the target schema alone.
For continuous integration, the dbt platform documentation describes the pattern directly: a CI job builds and tests the modified models and their downstream dependencies in a temporary schema unique to the pull request, with a name following dbt_cloud_pr_<job_id>_<pr_id>; it deletes the schema when the pull request is closed or merged; it runs checks for different pull requests concurrently because each writes to its own schema; and it cancels stale in-flight runs when new commits are pushed to the same pull request. The same design can be built with open-source dbt and a general CI system by deriving the schema name from the branch or pull-request number. The idea is to make the schema a function of the change, so that concurrent changes cannot collide and cleanup is a rule instead of a chore.
The documentation on deployment environments adds a staging environment whose stated purpose is to let developers use production tooling while limiting access to production data, and it shows sources selected by environment variable so that the code and the graph stay the same across environments while only the database differs. That last point is the reason to keep environment differences in configuration. The code promoted from staging to production should be the code that was tested, and the differences between the environments should be a short, explicit list. The glossary entry on environment promotion makes the same point and lists what a staging pass cannot establish: data volume and shape, infrastructure in front of the artifact, third parties that are stubbed in test, differing flag states, and concurrency. That list is the specification of what must be verified some other way.
Isolation and state: the incremental problem
A fresh isolated schema has a property that trips up CI for incremental models, shown in the previous article’s runs. On the first build the target table does not exist, so is_incremental() is false and the whole filter is skipped. The build is green and the comparison test passes, and none of the incremental logic has run. The documentation points to dbt clone as one remedy: it copies the production state of selected models into the target schema, as a zero-copy clone on warehouses that support it, or otherwise as pointer views, so that an incremental model in CI has previous state to work from. The documentation says deferral, which resolves references to unbuilt upstream models against the production manifest, is often a cheaper and simpler alternative, while clone creates real objects, which is needed when something outside dbt (such as a BI tool) must read the result. The state comparison caveats in the documentation are worth reading before relying on either: tags and metadata changes do not count as modifications, a changed macro marks every dependent model, a changed variable may go undetected, and a test that spans one modified and one unmodified model reads across environments when references are deferred.
Cost is the other pressure. An isolated build of a large project can be as expensive as a production run. The --empty flag documented for dbt build limits references and sources to zero rows, so the models execute their SQL against the warehouse without reading input data; it validates dependencies and that the models build, not that their results are right. Used first, it gives a cheap structural gate before the more expensive data-bearing build. The documentation notes one caution: dbt may skip processing a ref or source under this flag as an optimization, and a model that needs a relation processed can say so with the .render() method.
Isolating the orchestrator
Everything above concerns the transformation layer. The orchestrator needs the same care. The Airflow architecture documentation says a Dag bundle is the location from which the Dag processor parses files and from which workers read the right version, that the default local-folder bundle does not support versioning so that the processor and workers can briefly see different versions of a Dag, and that versioned bundle backends such as Git let the scheduler pin a specific bundle version when dispatching each task. For a deployment that changes DAG code while runs are in flight, a versioned bundle removes one class of skew. For isolation, a non-production deployment should have its own metadata database, its own connections, and its schedules paused or set to manual trigger, because a branch deployment that shares production’s connections can write production data on its first scheduled run. Which of these a team needs depends on how much it fears a mistake and how much a second deployment costs; both are judgments, not documented defaults.
Safe promotion
Promote the same thing you tested
Promotion moves a candidate through a sequence of environments, each with a gate. The principle in the entry on environment promotion is that the artifact stays identical and only configuration differs, so that the evidence gathered at each stage describes what reaches production. For SQL transformations the artifact is the project at a specific commit, and promotion is running that commit against the next target with that target’s configuration. Two failure patterns follow from breaking the principle. One is rebuilding between stages, for instance a branch that is merged and then re-resolved against a moving dependency version, so that production runs something that was never tested. The other is a production-only code path, such as a deployment script with extra steps that no earlier environment exercises, which makes the riskiest deploy the least rehearsed. Record what was promoted (the commit, the dependency versions, the configuration), not only that a promotion happened.
Gates, and what each one shows
A gate is a stated condition that blocks progress. The entries on continuous integration and on a release gate make two points that apply here: a green check means only that the configured checks passed, and an approval must identify what it covers. The table arranges the gates described so far in the order a change meets them, from cheapest to most expensive, with the evidence each provides.
| Gate | Runs where | Evidence it provides | What it does not show |
|---|---|---|---|
| Parse, compile, and lint | CI, no data | References resolve, no cycles, configuration valid | That any query returns the right rows |
| Unit tests with fixtures | CI, no production data | Logic is correct on the fixture cases, including boundary and incremental selection | Real data shapes; the framework’s merge step |
| Contract preflight | CI, builds structure | Output columns and types match what consumers were promised | Meaning of columns; unregistered consumers |
| Build changed models and their downstream in an isolated schema, with data tests | Isolated environment with sample or masked data | The change builds and its assertions hold on that data | Production volume, real concurrency, the incremental path unless state was cloned |
| Behavioral comparison with the current version | Isolated environment, or a shadow run | How outputs change for the same inputs | Whether a difference is desired; coverage beyond the compared slice |
| Production build with tests in graph order | Production | A failing upstream test skips downstream models, stopping propagation | Anything the tests cannot express |
| Post-release reconciliation | Production, after the run | Counts and totals agree with an independent source | Meaning errors that preserve totals |
The comparison gate deserves a word because it generalizes the shadow testing described in the entry on shadow testing. A candidate pipeline processes the same inputs as production but writes to a separate schema, and the outputs are compared on stated criteria. Two cautions from the entry carry over. The shadow must not repeat an external action, such as an email or a write to a shared system, so side effects are disabled or isolated. And a difference is not an error: the comparison reports where outputs diverge, and a person who knows the intended change decides which divergences are the point of the release. A release gate in the strict sense needs a rule written before the results are seen. As an assumed example, a team could require that no key present in production is absent from the candidate, that row counts differ by less than a stated tolerance except for explained categories, and that every changed value falls in a listed set of intended changes. A candidate with a higher aggregate quality score but one violated rule still fails; changing the rule after seeing the results to admit the candidate defeats the point of a gate.
Deploy is not release
In a warehouse, deploying code and exposing results to consumers can be separate steps, and separating them is the safest form of promotion. A candidate build can be written to a candidate relation, tested, and then exposed by repointing a view or swapping the relation that consumers read. The dbt documentation mentions this pattern in two places. It lists blue/green continuous deployment among the uses of dbt clone on warehouses with zero-copy cloning, and it describes a latest_version_pointer option for versioned models that creates a view named after the model that always points at the current version. The glossary entry on blue-green deployment lists the conditions that make a fast switch meaningful: the previous version must still be able to run against current state, and shared state such as the database is what cannot be switched back. A relation swap has the same condition. It exposes the candidate and retains the previous relation as a rollback target, but only if no one has written to a place the previous relation depends on, and the retained relation costs storage until the rollback window closes. Whether a given adapter supports an atomic swap and zero-copy clones was not verified here; the documentation ties the clone use cases to Snowflake, Databricks, and BigQuery.
Continuous delivery and continuous deployment differ in whether a person approves the production step. The entry on continuous delivery and deployment states the distinction and notes that the abbreviation is used for both. For pipelines the sensible default is a mix. Merging to the main branch can deploy automatically to a staging environment, and production promotion can wait for an explicit approval or a scheduled window. Automating the production step is reasonable when the earlier gates are strong, the blast radius is bounded by contracts and write-then-swap publication, and detection and repair are fast. It is unreasonable when any of those fail.
Promotion and the orchestrator in flight
A pipeline is a running system, so code changes arrive while runs are in progress. The orchestrator documentation gives the facts, and the consequences are inferences. With an unversioned local bundle, tasks that start after the change may run new code while tasks already started in the same run used the old, so a run can mix versions. Pinning a bundle version at dispatch removes that skew for tasks dispatched after the pin, but a run created before the change that is still executing keeps its pinned version. The safe habits follow: avoid deploying while a critical run is in flight when the bundle is not versioned; deploy schema-affecting changes between intervals; and write each task so that a rerun under the new code of an interval started under the old is safe, which is the idempotency requirement of the previous article in another setting.
Rollback and migration
Three meanings of rollback
The entry on rollback defines it as returning a system or component to a previously retained version, useful only if that version can still operate correctly with current data, interfaces, and access requirements. For a pipeline, three separate questions hide in the word. Can the previous code be deployed and run? That is the easy one and usually yes. Can the data the bad release wrote be undone? That depends on what was written and where, and often the answer is no. Can further damage be stopped? That is nearly always possible by pausing publication or the schedule, and it is the first thing to do. A team that says “we can roll back” should specify which of the three it means, because the answers differ by materialization.
| Materialization or object | What a bad release leaves | Code rollback restores | What repairs the data |
|---|---|---|---|
| View | Nothing stored; the view definition was replaced | The previous view at the next build; consumers see the old logic immediately after | Nothing needed beyond the redeploy |
| Table rebuilt each run | A table built with the bad logic | The logic, at the next build | The next full build after the rollback, if the inputs are still available |
| Incremental model | Rows selected and written under the bad logic stay in the table | The logic for rows selected from then on | A bounded rebuild of the affected interval range, or a full refresh |
| Appended or published output | Rows already consumed downstream | Nothing | Replacement or deletion of affected rows, plus notice to consumers if reported figures changed |
| External effects (notifications, API calls) | Effects already sent | Nothing | Compensating actions by the owners of those systems |
A measured incremental rollback
The incremental row of the table is the one that surprises. In a local run, an incremental model with a unique key and a one-day lookback window was deployed with faulty logic that doubled the amount. A payment arrived each day, and three incremental runs followed. After the third, every row that had been selected under the bad logic had amount 20 instead of the correct 10 (the correct values come from a full model over the same source); payment 1, which had been loaded correctly at first, was re-selected by the first bad run and doubled too. The code was then rolled back to the good logic, and the payment for the next day arrived and was loaded. The table afterwards, with the correct amount for comparison:
payment_id updated_at amount_in_table correct_amount matches
1 2024-10-03 20 10 False
4 2024-10-04 20 10 False
5 2024-10-05 10 10 True
6 2024-10-06 10 10 True
7 2024-10-07 10 10 TrueThe first two rows are still wrong after the rollback. The post-rollback run selected rows whose date was within one day of the table’s latest date, which covered payments 5, 6, and 7, and replaced payments 5 and 6 with correct values through the unique key. Payments 1 and 4 fell outside the window and were never selected again. The rollback deployed good code, the run succeeded, the tests on key uniqueness would have passed, and two rows remained wrong indefinitely. The same mechanism that makes an incremental model cheap, selecting only a recent slice, makes its repair partial. The fix is a deliberate repair of the affected range, either a full refresh or a bounded rebuild of the interval range from the release time forward, using the backfill mechanics described in the previous article and the data backfill considerations: define the range, the input and code versions, the concurrency, and the publication order.
This is an invented table of five rows; the numbers show the mechanism and not a measured production effect. The general lesson holds for any incremental or append-style pipeline: after a rollback, identify the range written by the bad release, which is the interval range from the first run under the new code to the rollback, and repair that range, because the code change alone repairs only what the next runs happen to select. A deployment record that lists when each environment received each version gives that range directly. Without such a record the range has to be reconstructed from run history, which is slower and less certain.
Roll forward is usually the real option
When data has been written, going back often means going forward: ship a corrected version and repair the data. The dbt retry command re-executes the last invocation from the point of failure, which suits an interrupted run, and does not undo successful nodes of a bad one. For the platform’s own point-in-time recovery features (table snapshots, time travel), this article makes no claim, since behavior and retention vary by product and none was examined; if one is available it is a third repair path with its own retention limit and should be tested before it is relied on. Governance structures raise the stakes. The dbt documentation observes that model contracts, versions, and access controls can make rollbacks harder (for example removing access), so they should be adopted knowing that the price of stability for consumers is reduced freedom for the producer.
Migration: change the shape without breaking readers
A schema migration is a change that cannot be tested away because consumers and producers move at different times. The standard technique is expand and contract, also called parallel change: add the new form while keeping the old, migrate consumers, and remove the old form only after confirming that no supported consumer depends on it and the rollback window has closed. Each intermediate state must be releasable and must be compatible with the previous code, which is what makes it safe to stop or reverse. A column rename in a warehouse model shows the steps.
| Step | Model output | Consumers | Entry condition | Reversible? |
|---|---|---|---|---|
| Expand | Old column plus new column, same values | Unchanged, reading the old column | Adding a column is non-breaking for named-column readers; check positional exports | Yes: remove the new column |
| Populate | New column filled for historical rows as well as new ones | Unchanged | Backfill designed and tested (new columns are not backfilled automatically) | Yes |
| Verify | Both columns agree for every row | Unchanged | A test comparing the two columns passes in production | Yes |
| Migrate readers | Unchanged | Move one by one to the new column | Consumer inventory and an owner for each | Yes: move back |
| Stop maintaining the old | Old column no longer computed | None read it (confirmed) | A decision not to roll back to a version that reads it | Costly |
| Contract | Old column removed | None depend on it | Notice period complete; removal reviewed | No |
The dbt mechanics deserve care because the defaults are surprising. For an incremental model, the documentation says the on_schema_change default of ignore means a new column does not appear in the target table, and a removed column makes the run fail; the alternatives (fail, append_new_columns, and sync_all_columns) differ in what they add or remove, and none backfills old rows. The previous article’s runs showed both outcomes: with ignore the added amount_cents column was silently absent and with append_new_columns it appeared. The expand step on an incremental model therefore needs append_new_columns or a full refresh, and the populate step needs an explicit update or refresh, since rows outside the selection window would otherwise hold nulls in the new column. When a contract is enforced, the contract must be updated in the same change as the column, and an incremental model’s contract requires on_schema_change to be append_new_columns or fail. For a change that cannot be made additively, dbt model versions let both shapes coexist under different version numbers until the deprecation date passes.
Two failure patterns recur. The first is the contract step that never happens: expansion is satisfying and migration is work, so the old column stays and the table accumulates half-finished parallel changes. Create the contraction task, with an owner and a date, when the expansion starts. The second is dropping the old form while something still writes or reads it, which is why the final step needs evidence that no reader remains and not an inference from a code search. Evidence includes query logs within their retention, consumer sign-off, and a period of renamed-but-retained column access with alerting. That evidence has coverage gaps, as discussed above, so the realistic goal is to reduce the chance of breaking an unknown consumer, not to eliminate it.
Failure modes and limits
| Failure | Mechanism | Detection | Prevention or repair |
|---|---|---|---|
| Green CI, broken production incremental run | Fresh CI schema never executes the incremental branch | Unit test in incremental mode; comparison on cloned state; first production run failing | Test incremental mode with fixtures; clone production state in CI; monitor the first run after a deploy |
| Rename passes review, breaks a consumer | No contract; consumers unknown | Consumer failure, usually after release | Enforced contract, consumer registry, versioned breaking changes |
| Meaning changes with the structure intact | Unit or time-zone change inside an unchanged type | Semantic tests, comparison with the previous version, reconciliation | Name units in the contract; test accepted ranges and totals |
| Staging writes to production | Shared credentials, or schema name falling back to the production schema | Unexpected objects or writes in production | Separate credentials; permissions that forbid the write; schema names derived from the target |
| Rollback leaves wrong rows | Incremental window does not reselect old rows | Reconciliation after rollback; comparison with a full rebuild | Record the release interval range; repair that range by bounded rebuild or full refresh |
| Version skew during a deploy | Unversioned bundle; scheduler and workers see different code | Runs that mix behaviors; import errors on workers | Versioned bundles; deploy between intervals; repeat-safe tasks |
| Promotion of an unrecorded build | No record of what was promoted | Inability to say what is in production | Record commit, dependencies, configuration, and time for each environment |
| Half-finished migration | No owner or date for the contract step | Old columns persisting, with divergent values | Create the contraction task at expansion; test column agreement |
Three limits qualify the whole article. First, every run shown here is a small invented example on one adapter; behavior of constraint enforcement, incremental strategies, clones, and swaps varies by warehouse, and the documentation for the target adapter should be read before a pattern is trusted. Second, the gates reduce the probability of a bad release and bound its damage; they do not eliminate either, and a pipeline that relies on promotion alone, with no reconciliation and no repair plan, will eventually publish a wrong number. Third, a CI/CD design is only as good as the discipline that keeps it true: environments drift, fixtures grow stale, contracts go unversioned, and promotion records go unwritten, so the maintenance of these controls belongs in the team’s regular work, with the same ownership as the pipelines they protect.
References
- dbt Documentation, Unit tests
- dbt Documentation, Model contracts
- dbt Documentation, Model versions
- dbt Documentation, Continuous integration in dbt
- dbt Documentation, Deployment environments
- dbt Documentation, Connection profiles
- dbt Documentation, Custom schemas
- dbt Documentation, clone command
- dbt Documentation, build command
- dbt Documentation, retry command
- dbt Documentation, Defer
- dbt Documentation, State comparison caveats
- dbt Documentation, Incremental models
- Apache Airflow 3.3.2, Architecture Overview
Discover more from Insightful Data Lab
Subscribe to get the latest posts sent to your email.