Versioned schemas and migration

Why do you migrate the schema rather than the data, and what invariant follows?

Why this is different from application migrations

In an application database you can usually migrate the data: run a script that updates every row. In a large analytical table, you usually cannot. There is too much of it, it is immutable, and old files remain in the table indefinitely. So the reader must resolve a set of schemas simultaneously, forever.

Two strategies

Strategy	What it does	Benefit	Cost
Migrate the data	rewrite the files under the new schema	one schema at read time; simple readers	cost proportional to table size; it changes file identity and invalidates every cache keyed on file name; viable for small or hot tables
Migrate only the schema	resolve old files against the current schema at read time	free at change time	imposes an invariant: every past schema must be reachable from the current one using the safe operation set

The invariant is why the operation set must be closed

That invariant is the real reason the allowed operations must form a closed set. You are never making one change. You are maintaining the composition of every change ever made. The requirement is:

Transitive compatibility is exactly that invariant, stated as a setting.

Migration scripts and the last escape

Migration scripts here alter metadata and may start a backfill. Both must be idempotent and restartable, because they will be interrupted.

The remaining escape is envelope versioning: carry a version field in the data and branch on it in the reader.

def read_shipment(rec):
    v = rec.get("schema_version", 1)
    if v == 1:
        return {"price_cents": rec["price"]}              # old: price meant cents
    elif v == 2:
        return {"price_cents": rec["price_dollars"] * 100}
    else:
        raise ValueError(f"unknown schema_version {v}")

It handles changes the algebra cannot express, at the permanent price of dual-path reader code. It is ugly, honest, and the only thing that works when a change is genuinely not a widening.

You migrate the schema, not the data, so the current schema must remain a legal reading of every schema you have ever had.