Schema Compatibility Has Limits

11 Jun 2026 visibility

Here is a scenario.

Old API:

{
  "email": "user@example.com"
}

New API:

{
  "email": "user@example.com",
  "email_verified_at": "2025-01-08:00:00Z"
}

New API introduces a new rule: email_verified_at means this email was verified.

Now imagine an old script or a service that knows how to change a user’s email. It preserves unknown fields exactly.

It makes this change:

{
  "email": "new@example.com",
  "email_verified_at": "2025-01-08:00:00Z"
}

New email was never verified but the record says it was. The JSON is valid and the unknown fields are preserved.

Old script did nothing wrong

According to the old model.

Old script was read-compatible, not edit-compatible.This same pattern happens everywhere. Let me tell you the flour story.

The Incident

I am on call with an angry purchasing team. “Inventory shows we have plenty of flour, but the planning system shows a shortage. We cannot schedule production.”

My first thought: “dashboard cache is stale, Power BI model needs a refresh”

But the planners have already checked our ERP. The raw table is the problem. Before anyone opens Power BI, numbers are already wrong. Stock on hand has fallen below soft reservations.

Not a visualization bug, data corruption.

This has already propagated to downstream tables.

What Actually Happened

Three months ago, the team added reserved_quantity column to the inventory model. Before that, planning could only see raw stock, without knowing what was already promised to production orders.

The new column enabled soft-reserving raw material for upcoming batches. The rule was simple: reserved quantity cannot exceed on-hand quantity.

reserved_quantity <= on_hand_quantity

The Backfill Knew Too Little

Inventories tend to drift. Discrepancies exist between physical count and derived balance from purchase order and production consumption, and someone needs to fix the drift.

The backfill job is very simple. It recomputes on_hand_quantity from transaction log, using the original business rule. This job has not been changed in a year.

Here is what the backfill sees:

on_hand_quantity = 10,000 kg
reserved_quantity = 8,000 kg

It calculates: actually, based on receipt and consumption, only 5,000 kg exists.

The backfill writes on_hand_quantity = 5,000 and leaves reserved_quantity alone because no one told it about this new field.

on_hand_quantity = 5,000 kg
reserved_quantity = 8,000 kg

The record is valid, types are correct, schema is happy, but the constraint is violated.

Read-Compatible Not Edit Compatible

I filed this under “schema compatibility”. If old code reads a new table, it ignores column’s that it does not know. Reading means decode what you need. Writing means preserve every rule your change touches.

The backfill was read-compatible: look at table shape, find familiar column, do the job.

It was not edit-compatible: it had no concept of reservations.

This is not just a bug in the program.

  • First is the compatibility problem: the meaning of editing on_hand_quantity changed after reservations were introduced.
  • Second is an undefined business state:
Previously believed stock: 10,000
Reserved against that belief: 8,000

Physical reconciliation discovers:
Actual stock: 5,000

What should happen to the 3,000 kg of now-unfulfillable reservations?

If the system has no answer to this question, then the backfill isn’t just violating semantics. It has uncovered a state that the application’s business model never designed for.

This is why simply adding a database constraint such as:

CHECK (reserved_quantity <= on_hand_quantity)

…doesn’t really solve the problem. Yes, it prevents the bad row from being stored, but it doesn’t tell us what should happen instead. A reservation is a commitment made using a previous belief about stock. This is a legitimate state, we just haven’t thought about what to do when it happens.

When a new constraint was added, someone should have asked: what happens when an old writer changes on_hand_quantity?

Legitimate response:

    1. Reduce or cancel enough reservations so they fit within the actual stock
    1. Keep the reservations and record the shortage/backorder

Compatibility Belongs To Operations, Not Versions

I often hear something like “Version N backward compatible”. To me this statement is almost useless.

Same old code scanned the table safely, copied the row safely, updated description safely, but could not safely change on_hand_quantity without knowing about reservation.

Compatibility is not a property of the schema, but rather a property of operation.

Which old mutation can break the new meaning?

Hidden Writers

In data systems, the old writers never die. They hide inside schedulers.

dbt model that ran last Tuesday is a writer. So is a migration script you ran seventeen times. The analyst Jupyter notebook with production credentials is a writer. “One-time” repair that you run again in six months is also a writer.

When a new column grows a relationship to an old column, every job that touched the old column is now part of the compatibility contract, whether you like it or not.

Schema tools check: field exists? Type correct?

But the business meaning lives between the fields. Old jobs see columns but they cannot see the obligation weaving through them.

What We Do Now

When the team adds derived fields, constraints, or enrichments, we don’t just review the new pipeline. We search production for every writer of this field.

Then we choose what happens to these writers:

Teach them: Update old job to recompute dependent value. Clean when rule is shared and simple. Dangerous when we copy the same logic across many old scripts, creating many slightly different truths.

Make them fail: Enforce this at the warehouse level. Reject impossible states. I prefer red jobs to green corruption.

Invalidate: Let old job clear dependent value, but mark it as stale. Invalid state is now obvious to the pipeline, not just null on a dashboard that gets interpreted as zero.

Revoke access: Old code can calculate but only the current component is allowed to validate and publish. One maintained writer owns current truth.

Write Tests That Matter

Normal test: new writer, new schema. Of course this passes.

The real bug lives in the mix:

    1. Create record with new field
    1. Run old mutation on familiar field
    1. Check: invariant still holds? Is failure explicit?

For inventory:

Given:  on_hand = 10,000, reserved = 8,000
When:   old backfill set on_hand = 5,000
Then:   reject, recompute, or invalidate
Never:  on_hand = 5,000, reserved = 8,000 and green check

This simple test catches the deep lie.

Adding columns is easy. Adding a relationship changes obligation of every writer that can touch either side.

What old writer can still change the thing your new field depends on?

Related posts