Data Model Change and Compatibility
In an illustrative scenario, a team improves a definition. Canceled orders were being counted in revenue, which was wrong, so they are excluded from now on. The schema does not change. No column is added or removed, no type changes, every automated check passes, and the deployment is uneventful. Three weeks later a regional manager asks why their year-on-year growth collapsed in March, and it takes two days to find out that nothing happened in March except that the number started meaning something slightly different.
This is the shape of the problem. The changes that schema checks catch are the ones that alter the schema; the ones that leave no trace in it need a different kind of check, and where none has been set up they go unnoticed until someone questions a number.
Five kinds of change, and how visible each is
“Schema change” is too coarse a category to reason with. Sorting changes by what they affect makes the risk legible, and the last two rows are the ones no schema check reports.
| Change | Example | How a consumer finds out | Caught by tooling? |
|---|---|---|---|
| Column added | A new optional attribute | Usually not at all | Yes, and usually allowed |
| Column removed or renamed | order_total becomes order_amount | The query fails | Yes |
| Type changed | int to decimal; a string becomes nullable | The query fails, or silently rounds or nulls | Usually |
| Key or relationship changed | A dimension gains a second row per member; one-to-one becomes one-to-many | Joins fan out and totals inflate, without an error | Not by schema checks; by a uniqueness test on the key or a join match-count check, where one is set up |
| Meaning changed | Canceled orders excluded; tax now included | A test with fixed inputs fails at once, if one exists; otherwise someone notices a trend break, eventually | Not by schema checks; by a test that fixes inputs and expected output, where one is set up |
The first three are the ones everybody plans for, and automated checks catch much of them — though not all of it fails loudly. A removed or renamed column fails a query that names it, at a known moment, with an error naming the column; an added optional column usually changes nothing for a query that names its columns; a type change can fail, or can round or null values without a message. The bottom two produce no schema difference to check. They are still detectable automatically — a uniqueness test on the dimension key, a check on join match counts, a test with fixed inputs and expected output — but only where such a check has been set up for that kind of change; without one, they surface when someone notices a number, at an unknown moment.
The relationship row deserves more attention than it gets. Nothing in the column list changes when a dimension that used to hold one row per customer starts holding several — but a query joining on the natural key without a version condition now fans out for every member that gained a second row, and what that does to an aggregate depends on the operation and on how unevenly the members are duplicated. COUNT(*) and a SUM of positive amounts grow with every extra match. An average moves only when the duplication is uneven: two facts of 100 and 50 average 75, still 75 if both are matched twice, and 87.5 if only the first is matched three times. MIN, MAX, and COUNT(DISTINCT key) are unchanged by repeats of the same values, as long as the groups and the input values themselves are unchanged. A query that picks one row per key keeps its row count, and even it still has to pick the right version, so preserving the row count is not the same as preserving the meaning. The change is real, the schema is identical, and the failure appears as numbers that are wrong by a plausible-looking factor for part of the data.
What compatibility rules actually promise
The formal machinery for the first three rows comes from message serialization, and it is worth understanding precisely because it is frequently over-trusted. Confluent’s Schema Registry documentation defines compatibility in terms of who can read what, and the operational content is in the upgrade order.
| Setting | Promise | Upgrade first |
|---|---|---|
| Backward | Consumers on the new schema can read data written with the old one — add fields that carry a default, remove fields | Consumers |
| Forward | Consumers on the old schema can read data written with the new one — add fields, remove fields that carry a default | Producers; consumers only once old-schema data no longer reaches them, or they can read it |
| Full | Both directions — add or remove fields that carry a default | Either; they can be upgraded independently |
| None | No checking | Whatever the actual change requires — verified yourself, or avoided with a new topic or a coordinated cut-over |
“Optional” is doing specific work in that table, and it is format-dependent. In Avro a field is made nullable with a union type that includes "null", but nullable is not the same as safe to add. Confluent’s documentation is explicit that a new field needs a default value so that data encoded with the old schema can be read with the new one: without a default, the new reader has no value to use for the field missing from an old record. Protobuf has an optional keyword and different rules again. Before relying on a row of that table, check what your serialization format means by the word.
Two refinements matter in practice. The transitive variants check a new schema against all previous versions rather than only the immediately preceding one, which is the setting you want for data anyone might re-read from an archive — non-transitive compatibility permits a chain of individually safe steps that together cannot read version one. And turning checking off is not neutral. NONE means the registry stops verifying, not that the change became safe: for an incompatible change the documentation’s own options are to upgrade all producers and consumers at the same time or to move to a new topic, while a change that happens to be compatible in both directions can still be rolled out in stages — but nothing now verifies that, so the compatibility test, the parallel topic, or the coordinated cut-over becomes your job rather than the registry’s.
One naming caution, since it causes real confusion: different products use the words backward and forward in opposite senses. Rather than memorizing a label, ask the questions the label encodes — who has to be upgraded first, and what may the other side still meet in the meantime? Order alone is not enough: with forward compatibility the producers go first, but a consumer upgraded to a schema that added a field without a default cannot read the records the old producer already wrote, so the old-schema data has to be drained, kept away from the new readers, or made readable to them before the consumers move. Those questions survive moving between tools, and they are what you actually need to know on the day of the change. The mechanics underneath, where a reader resolves data written under a different schema, are the subject of writer and reader schemas, and the registry that enforces the policy per subject is where the setting lives.
Now the limit, which is the reason this section exists. Compatibility guarantees readability, not meaning. A message that deserializes correctly can still carry a value computed under a new definition. Every rule in that table would pass the change in the opening paragraph, because no field was touched. Schema compatibility is a floor, and mistaking it for a ceiling is how teams end up confident about changes that are about to cause a two-day investigation.
Contracts, and where they stop
Inside the warehouse, the equivalent mechanism is a data contract, and transformation tools have made this concrete. In dbt’s implementation, an enforced contract must include every column’s name and data type, and may add constraints such as not_null depending on the platform. The enforcement has two parts that are worth keeping separate. Column names and data types are a preflight check at build time: dbt verifies that the model’s transformation will produce a dataset matching the contract, “or it will fail to build.” Constraints such as not_null are handed to the platform as part of the table’s definition, and whether the values are then checked depends on the platform — some enforce a constraint when the model is built or updated, others record a primary-key declaration as information only, so a model can build successfully with duplicates in it. A constraint the platform does not enforce needs its own data test.
That is a meaningful improvement over discovering the problem downstream, and it is worth adopting for anything others depend on — the guidance is to define contracts for the models that are public and relied on downstream, which is also a useful forcing function for deciding which of your tables those are. The listed breaking changes are the expected ones: removing a column, changing a data type, removing or modifying a constraint, or removing the model by deleting, renaming, or disabling it.
Read that list again with the five-row table in mind, and the gap is the same one. What dbt enforces automatically is shape. Nothing in that enforcement prevents the values from being computed differently tomorrow, because names, types, and constraints say nothing about what a column means. That is a limit of what this tool checks, not of what a data contract can say: a broader contract — the Open Data Contract Standard, for instance, carries descriptions, quality rules, and service levels alongside the schema — can state the definition, but stating it and having a machine verify it are different things. A column typed decimal(18,2) named revenue satisfies its contract whether or not cancellations are included.
Which is not an argument against contracts; it is an argument for pairing them with the two things that do carry meaning. The first is a metric definition stated in words, versioned alongside the model, and reviewed when it changes. The second is a test that pins the definition to an example.
A definition change is detectable, just not by a schema check. dbt’s unit tests, for instance, “validate your SQL modeling logic on a small set of static inputs” before the model is materialized, with the expected output written out in the test. Give such a test three orders, one of them canceled, and assert the revenue the model should produce: excluding cancellations from now on makes that test fail, at the moment the change is made, in the change author’s own pull request. That is a much earlier signal than a regional manager noticing a trend break. What the test cannot decide is whether the new number is the right one — it fails, someone reads the diff, and a person still judges whether the definition should move and who needs to be told. Automation turns a silent change into a visible one; the judgment stays human.
When the past changes
Correcting historical data is a separate kind of change, and it is easy to misclassify because nothing about the model moves at all. The structure is identical, the contract holds, and yesterday’s query returns a different answer today.
From a consumer’s position, this is indistinguishable from a breaking change, and it is worth treating with the same seriousness rather than as routine maintenance. Three questions decide how much ceremony it needs: how far back the correction reaches, whether any of the affected periods were reported externally, and whether a consumer has already acted on the old figures. A correction confined to last week’s unpublished data needs an entry in a log. A correction that moves a quarter someone filed needs to be announced before it lands, not explained afterwards.
The practical minimum is to make restatements detectable without anyone asking. A published dataset that carries a version or a last-restated timestamp lets a consumer notice that their number moved for a reason, which converts a mystery into a lookup. Without it, a consumer can still detect that something moved — a scheduled reconciliation that compares a kept snapshot of a closed period with the current result, or a checksum per period, is a control that runs whether or not anyone is suspicious — but not why, or which correction it was. Detecting a change and explaining it are different capabilities, and only the producer’s record supplies the second. What is not a control is a figure someone happened to write down, which is where teams with neither end up.
Knowing who breaks
Every process for changing a model assumes you can find the consumers. That assumption is usually weaker than it feels.
Lineage answers part of it. Automatically captured lineage traces tables through transformations to dashboards and models, and where column-level lineage exists it can narrow the blast radius from “everything touching this table” to “these seven queries reading this column.” That is a genuine improvement and it is where impact analysis should start.
What it does not see depends on what has been connected to it, and the gaps are worth listing as categories of consumer: a spreadsheet someone refreshes through an ODBC connection, an application querying the warehouse directly with SQL built at runtime, an export that leaves the platform and is joined to something else elsewhere, a notebook on an analyst’s laptop, and a report whose logic lives in the BI tool rather than in a model.
Some of those are recoverable with the right integration, and it is worth knowing which. Platforms record what queries actually touched: Snowflake’s access history captures, per query, the source columns read and the projected columns returned, which covers the application and the notebook — though its own documentation warns that not every query in query history is recorded there, since “the structure of the SQL statement determines whether Snowflake records an entry.” Catalogs integrate with BI tools: Microsoft Purview inventories Power BI workspaces, datasets, reports, and dashboards and captures lineage among them and to external data assets, within documented limits on which source types are supported and on cases such as dynamic query parameters, where lineage is not captured.
So the honest statement is narrower than “lineage cannot see these.” A consumer is missing from lineage when it sits outside the tool’s integrations, permissions, or collection scope — which is a gap you can close by connecting something, at some cost. Export is the case with the longest reach, and it is a matter of degree rather than of kind: the read itself can be observed from the engine’s query or access history, and what is done with the data afterwards is visible only if the external tool reports its own lineage or someone records it — Purview, for example, accepts lineage for external assets through its API. Where nothing is connected, the answer is “unobserved,” which is not the same as “no dependency.” Lineage sees what it is connected to; it does not see the organization.
Two things narrow that gap, and neither is a tool purchase. Query logs show which accounts ran what against the table in the last ninety days, including the consumers no catalog knows about — this is the most useful single artifact for impact analysis and it is usually already being collected, with the caveat that a shared BI account or a connection pool names a principal rather than a report or a person, which a query tag, the BI tool’s metadata, or a conversation still has to resolve. And catalog coverage, in the sense of tables having a recorded owner and a stated set of consumers, is what turns “we think three teams use this” into a list you can email.
Where you genuinely cannot enumerate consumers, that is itself the finding, and it argues for the conservative path: keep the old shape available alongside the new one and let usage decline before removing anything.
Publishing a change
The software industry solved a version of this problem and the solution transfers, with one addition. Semantic Versioning assigns incompatible API changes to MAJOR, compatible additions to MINOR, and compatible fixes to PATCH. Applying those rules requires a declared public API, described precisely and comprehensively. Without a stated interface, consumers still break — a query that named a removed column fails whatever was promised — but there is no agreed basis for deciding which changes count as breaking, and no way to say whose responsibility the breakage was.
That translates directly. The declared interface for a dataset starts with its columns, their types, its grain, and the definitions of its measures, and it includes whatever else consumers are meant to rely on: when it is refreshed, how long history is kept, who may read it, how corrections are delivered. Moving a 07:00 delivery to noon breaks a consumer as surely as renaming a column. What the declaration does not cover — physical layout, intermediate tables, the SQL that produces it — is implementation you remain free to change within those promises, and should say so, because consumers otherwise assume that whatever they can see is guaranteed.
Producers need to change logic and structure, and maintaining legacy endpoints indefinitely costs work. dbt’s model-version guidance weighs that work against the greater cost of losing downstream users’ trust. The mechanism it offers is a deprecation date, and a date bounds the period over which consumers must plan migration. An old version that might be removed someday tends to be migrated last, if at all; one that will be removed on a stated date can be scheduled — provided the date is backed by a migration path — a replacement with a date it will be usable, and enough time after that date to move — evidence that migration is happening, and a decision made in advance about what happens if it has not.
A workable sequence for a breaking change, then:
- Classify it. Which of the five rows is it? Shape changes are caught by schema checks; relationship and meaning changes need tests that fix inputs and expected outputs, plus a human to trace the consumers and judge whether the new definition is the intended one.
- Find the consumers from lineage and query logs, and accept that the list is incomplete.
- Publish the new version alongside the old, where there is one. Both available, both correct under their own definitions; if the replacement will follow later, announce its date and count the migration window from then.
- Announce with a date. What changed, why, what it does to the numbers, and when the old one goes away.
- Watch usage fall in the query logs rather than waiting to be told migration is complete.
- Remove it on the date you said, or extend it deliberately, with a named reason, a new date, an owner, and an assessment of what still depends on it and what keeping or removing it risks — because a date that slips quietly stops being a date, while a recorded, re-assessed extension is a decision. Find out why usage remains before deciding: a critical report still being validated is a reason for a named exception; a version with a security or correctness problem is a reason to remove on time even with users left.
On frequency, the guidance is against versioning every small change: prefer a predictable cadence, communicated well in advance, such as once or twice a year for bumping the current version and dropping columns nobody uses. The reasoning is that each version carries a real cost on both sides — migration work for consumers, and materializing several versions for producers — so batching breaking changes into an announced window is cheaper than a steady drip of individually small migrations.
One asymmetry is worth stating plainly for anyone deciding how much of this to adopt. Making a change reversible is cheap at the moment of the change and expensive afterwards. Keeping the old column for two quarters costs storage, a second definition to keep correct, and the support that goes with it; removing it and discovering a regulatory report depended on it costs an incident. When the consumer list is uncertain — which is most of the time — keeping the old shape is usually the better trade, and it is only conservative in the short run. It stops being the safe choice when the old version is itself the problem: a version computing a wrong figure, or one with a security defect, is not made safe by uncertainty about who reads it.
Questions that reveal the wrong choice
| Question | If the answer is unclear, check whether |
|---|---|
| Which of the five kinds of change is this? | a meaning change is being handled with the process meant for a column rename |
| Is the declared interface written down — columns, types, grain, definitions? | consumers can break with nobody able to say which changes count as breaking or whose job it was |
| Does your compatibility setting check against all previous versions or only the last? | the current readers have actually been tested against every schema version still present in archived data, not only the last |
| Who must be upgraded first for this change to be safe? | the deployment order, and what old-schema data the new readers may still meet, has been worked out for this change |
| Does anything verify that a measure still means what it meant? | a test with fixed inputs and expected output would fail if the definition moved |
| Have you checked query logs, not just lineage, for consumers? | spreadsheets, apps, and exports have been looked for in query logs over a window longer than the longest reporting cycle |
| When a period is restated, can a consumer tell? | a consumer could explain a moved number without asking |
| Does the deprecation have a date, and has that date ever slipped? | usage of the old version is actually falling, and what happens if it has not by the date has been decided |
References
- Confluent Platform Documentation, Schema Evolution and Compatibility
- dbt Documentation, Model Contracts
- dbt Documentation, Model Versions
- dbt Documentation, Unit Tests
- Snowflake Documentation, Access History
- Microsoft Learn, Metadata and lineage from Power BI into Microsoft Purview
- Semantic Versioning 2.0.0
- Apache Avro 1.12.0 Specification, Schema Resolution
- Bitol, Open Data Contract Standard 3.0.1
- Microsoft Learn, Microsoft Purview Data Catalog lineage user guide
Discover more from Insightful Data Lab
Subscribe to get the latest posts sent to your email.