Schema Evolution, Migrations, and Historical Backfills

Classify changes by how consumers behave, not by how small the code diff looks. Start with the easiest class, because its mechanical cost has collapsed. Adding a column used to rewrite the whole table. It stopped doing that in PostgreSQL 11, released on 18 October 2018, whose release notes list the change: “Allow ALTER TABLE to add a column with a non-null default without doing a table rewrite (Andrew Dunstan, Serge Rielau)”. One condition attaches to it: “This is enabled when the default value is a constant.” MySQL made the same move on the other side of the ecosystem. For ADD COLUMN, Oracle's MySQL 8.0 manual says “INSTANT is the default algorithm as of MySQL 8.0.12, and INPLACE before that”, and its table of online DDL operations marks “Adding a column” as Instant and “Only Modifies Metadata”.

So an additive field is now close to free structurally. Its remaining risk is temporal, not mechanical. Rows written before the release have no value for the new column, and that absence is legitimate. A constant default is a statement about a past in which the field did not exist. Adding device battery state to new events changes availability across time even when not one reader breaks. Tolerant readers survive it. A model comparing examples across the release date does not automatically survive it.

A structural breaking change alters shape, type, field identity, or required presence. A string account ID becomes a nested object; a declared-required field disappears. This class announces itself with parse failures and build failures. It needs a new version or a migration window, and it has to account for nested and repeated fields.

A semantic breaking change is the expensive one. It preserves the shape and changes the meaning, so ordinary schema validation passes. The best-documented case of reusing an existing identifier for an unrelated concept is not a data warehouse at all. On 1 August 2012 Knight Capital Americas deployed new Retail Liquidity Program code. The SEC's order records the decision in one sentence: “The new RLP code also repurposed a flag that was formerly used to activate the Power Peg code.” Every message stayed well formed. One of eight servers never received the new code, and the order finds that “orders sent with the repurposed flag to the eighth server triggered the defective Power Peg code still present on that server”. In roughly 45 minutes the firm produced 4 million executions in 154 stocks, for over 397 million shares and a loss of over $460 million. Knight paid $12 million to settle. Weeks after the event Carol Clark, in a Chicago Fed Letter, used the contemporaneous pre-tax figure: “On Wednesday, August 1, 2012, a $440 million loss in 45 minutes brought market maker Knight Capital to the brink of bankruptcy.” Same shape, old meaning, one consumer left behind on the previous release. Redefining “active” from 30 to 90 days is the same class of change with a slower fuse.

The protocol should create evidence before the new shape becomes authoritative. Propose: state the motivation, the compatibility class, the semantic effect, and the affected consumers. Implement: publish a versioned representation and preserve stable field identities where the format supports them. Validate: run contract, distribution, join, historical, and downstream model checks. Migrate: dual-read or dual-write while consumers adopt the new version. Retire: deprecate the old version only after usage is zero and rollback evidence is preserved.

Payment networks run that sequence in public, on dates anyone can look up. On 19 June 2023 the Bank of England announced that “The Bank, working closely with the payments industry, has today successfully migrated CHAPS, the UK's high-value payments system, to ISO 20022”. Federal Reserve Financial Services ran the same standard migration for the Fedwire Funds Service two years later, on 14 July 2025. The notable artifact is the announcement issued weeks in advance. On 18 June 2025 FRFS confirmed the date: “FRFS will move forward with implementing the new ISO 20022 message format on July 14 as planned.” The same notice describes the run-up: “For the past several years, Federal Reserve Financial Services (FRFS) has worked with the industry to prepare for the upcoming ISO 20022 implementation for the Fedwire Funds Service on July 14, 2025.”

Notice what years of preparation and a separate confirmation announcement buy. No participant discovers the new message format at read time. No participant has to infer the cutover from the data. The date on which interpretation changes is a published fact, not an archaeological question.

A schema registry can approve syntax. An ML pipeline still changes behavior. Five steps produce evidence. Compile and parse, verifying that current and previous readers handle the new representation as intended. Compare semantics: units, categories, null behavior, population coverage, effective time. Rebuild sample datasets so joins, features, labels, and splits run under both versions. Measure downstream effects on distributions, example counts, slice coverage, and model predictions. Then exercise rollback until producers and consumers can demonstrably return to the prior version.

The reference implementation of step three belongs to a national statistics agency. The United States switched cause-of-death coding from ICD-9 to ICD-10 in 1999. The National Center for Health Statistics did not assert that the two revisions were comparable. It coded the same records under both. Anderson and colleagues set out the design in 2001: “The comparability ratios presented in this report are based on coding the same deaths occurring in 1996 by both the Ninth and Tenth Revisions and measure the net effect of ICD–10 by cause of death.”

The measured discontinuity was not small. “The comparability ratio for Alzheimer’s disease (113-list number 052) is 1.5536, which indicates a 55 percent increase in Alzheimer’s disease deaths when classified by ICD–10.” Over 10,000 additional deaths were assigned to that cause. The same deaths, a different revision, no change whatsoever in the underlying certificates, and no structural break to detect. A validator would have passed this migration in silence. A dual-coded sample priced it to four decimal places and let every downstream user correct their own series. That is the standard: dual-run old and new over identical input, publish the ratio per category, and let consumers adjust rather than discover.

Record schema version, event version, transformation version, and label-policy version when each can change how a row is read later. A single global version rarely captures every layer.

Avoid branching every harmless new field into a new consumer-facing product. Version when behavior is breaking, or when two readings must legitimately run side by side.

Provide defaults only when they have legitimate meaning. Avro's 1.12.0 specification states the rule precisely. A reader field with a default takes that default when the writer's schema lacks the field; if the reader field has no default and the writer omits it, “an error is signalled”. Confluent's schema-registry documentation makes the same point from the producer side: “The default value specified in the new schema will be used for the missing field when deserializing the data encoded with the old schema. Had the default value been omitted in the new field, the new schema would not be backward compatible with the old one”. The formats will happily supply your default. They cannot tell you whether it is true. Filling a newly added risk score with zero implies low risk for every historical row. Explicit absence is often safer.

Measure version distribution in production. A migration is not complete while old clients or delayed sources keep producing earlier formats. And version the values, not only the fields. Official statistics restate their own history on a schedule. On 7 February 2025 the U.S. Bureau of Labor Statistics applied its annual benchmark and reported that “Compared with the sample-based, seasonally adjusted published estimate for March 2024, total nonfarm employment had a revision of −589,000 or −0.4 percent.” The March 2024 employment level a feature pipeline read in April 2024 is not the number it reads today, and neither is wrong. This is why the Federal Reserve Bank of St. Louis maintains ALFRED, which “allows you to retrieve each economic data release (vintage) that was available on a specific date in history.” A model that trained on a restated series and is evaluated as if it had known the restatement is being graded on information it could not have had.

A backfill can improve completeness while also inventing values that were unavailable in the past. The cases below are measured ones. Each carries the size of the discontinuity it created.

New fields appear, categories expand, nested payloads change, and identifiers move to new systems. Preventing all change is neither realistic nor desirable, and the mechanics have stopped being the obstacle: adding a column is a metadata-only operation in PostgreSQL 11 and in MySQL from 8.0.12. The engineering goal is controlled evolution. Consumers should know whether a change is compatible, which version produced each record, and how historical data should be interpreted.

ML systems add sensitivity, because a model compares examples across time. A category rename can split one concept into two tokens. A backfilled field can exist historically even though it was unavailable at the original prediction time. A redefinition can move 55 percent more deaths into a category, as the ICD-10 transition did, without breaking a single reader. The failures in this lesson are all of that kind: a repurposed flag at Knight Capital that every format check accepted, a coding revision that changed counts and not certificates, a benchmark revision of −589,000 that arrived on schedule.

Safe evolution therefore combines structural compatibility, semantic versioning, temporal truth, migration evidence, and consumers who move together on a published date.