Schema Evolution: Advanced

The running example for this lesson is a marketplace whose checkout service emits an orders event with 4 fields: order_id, customer_id, amount_cents, created_at. To launch in Japan, the payments team proposes a fifth field, currency, with a default of USD. The diagram below shows the route that event takes.

The table sorts into 2 groups. The gate and the SELECT * load fail loudly, which is costly at 3 a.m. but cheap overall, because nothing wrong reaches a consumer. The explicit-column load keeps running, and so do the CSV export and the revenue fact, all producing output that looks valid. Interviewers give most of the credit for spotting those silent failures.

raw.orders_parsed now returns 5 columns while staging.stg_orders holds 4, so the engine rejects the insert. Adding the column to the target as well only moves the risk: INSERT ... SELECT * matches columns by position, so the load succeeds only while both sides list them in the same order. If the producer places the new field in the middle of its column list, a loud failure becomes a silent swap of values.

That is what happens to the partner export once staging adopts the field. The checkout team put currency third, next to the amount it describes; staging takes the column in that order, the export's SELECT * writes it third, and the partner's parser still reads 4 positions.

The partner's loader may reject JPY as a non-numeric amount, which is the good outcome. Had the new column been numeric, such as a tax amount in cents, every value would parse and every value would sit under the wrong header.

Every schema check in this pipeline approves currency: a new field with a default is the most compatible change a schema can make. Then the first Japanese order arrives as o-1002 with amount_cents of 125000 and currency of JPY. Yen has no minor unit, so that number means 125,000 yen, and the revenue fact, which sums amount_cents as US cents, books it as $1,250.00. The change added a column and, in the same release, redefined an existing one, from US cents to minor units of whatever currency says. No compatibility rule inspects meaning, so this hop is where a strong answer slows down.

Two assumptions sit under every Yes in that table. The reader resolves fields by name, so a field it does not list is skipped, and the format can promote int to bigint while reading. A positional reader breaks the first assumption, which is why, for the partner's positional CSV export, almost no change is forward compatible: only a field appended at the end leaves the old positions intact.

Rollout order follows from the verdict. Adding currency with a default is fully compatible, and the producer can deploy before or after its consumers. Removing a field with no default is backward only: every consumer must stop reading it and redeploy before the producer stops writing it, and the producer's deploy waits on the slowest consumer.

Formats differ in details, such as which numeric promotions they allow and whether a field can carry aliases for renames; the 2 directions are the same in all of them.

The v1 event comes back with currency set to USD, and app_build, which the reader does not list, is dropped. The USD default is correct for every order placed before the Japan launch, and a hazard for every writer after it: a new mobile build that forgets to set currency on a yen order also resolves to USD, and the schema has hidden the bug. The stricter design gives currency a null default. That keeps compatibility while leaving missing values visible, and staging makes the decision. A v1 event predates the field, so it becomes USD; a v2 event with a null currency goes to the dead-letter table.

A registry can check a new schema against the latest version only, or against every version it holds, which is called transitive checking. The difference shows up wherever old data stays readable. Take a members event, separate from orders:

Each version passed the check it faced. A backfill that reads 3 years of raw member events, most of them written as v1, with the v3 reader meets a bigint where it expects a string and stops. A stream that keeps 7 days of events can often live with checks against the previous version, because nothing older is read from it. Any store holding history needs transitive backward compatibility.

No tool reads intent, so the contract works through people and rules. With unit: USD cents on the page, the payments engineer adding currency reads that line in review and has to decide what yen orders put in amount_cents, which is the question the Japan plan skipped. The change policy classes the redefinition as semantic, so it ships as a new field with a 30-day overlap rather than as a reinterpretation of an old one. The gate can also enforce a rule derived from the contract: while amount_cents is US cents, an event whose currency is not USD fails.

Ownership decides who can change what. The producer owns the contract and is the only party that publishes a new version. Consumers add themselves to the consumers list, which makes that list the notification list for every change and the evidence needed before anything is dropped.

The fix agreed with finance: yen sales wait, and amount_cents is replaced by amount_minor, in each currency's own minor unit, alongside the currency field that v2 already added. Removing and redefining a field breaks both directions, so no single version can ship it. A sequence of versions can, each compatible with the one before, ending where the breaking change would have.

The hard decision comes in the migrate phase. Once yen orders exist, what does amount_cents hold for them? Leaving it null makes old readers undercount Japan, and copying the yen amount repeats the original bug. The producer fills it with the US-cent equivalent at the booking-time exchange rate, which keeps the old field true to its contract until the last old reader is gone.

The contract phase needs evidence before it starts: query logs or access audits showing that nothing has selected amount_cents for 30 days. A consumer missing from the contract's list is the usual reason a drop breaks someone, which is why consumers maintain that list themselves.

The exponent matters because amount_minor is in each currency's own minor unit: yen has 0 decimal places, the dollar 2. Dollar orders pass amount_minor through unchanged, so they never depend on a rate. The join to fx_daily is a LEFT JOIN on purpose. Rates for a day can land hours after that day's orders, and an inner join would drop those orders from the export without a trace. With the left join they appear with a null amount, and because a view is computed at read time, the next export after the rate lands is correct with no reprocessing. A materialized table built from the same query would keep the nulls until someone reruns it.

Each step rests on a fact about its version. The checkout service wrote every v1 and v2 event before it accepted any non-USD order, since yen sales open only with v3, so every one of them is in US cents and copying amount_cents into amount_minor is exact. Upcasters are deterministic, so a replay produces identical rows and the staging merge on order_id absorbs the duplicates a retry creates. An upcaster can be deleted only when no retained store holds its version. With 3 years of raw history, that means keeping it for 3 years or rewriting the raw table, which costs a full scan and cannot be undone; a 3-line function is usually the cheaper of the two.

Renaming shows the difference. On an ID-based table, renaming customer_id to buyer_id changes 1 entry in the metadata, and every file written before the rename still answers to field ID 2. On name-matched files, the same statement leaves 3 years of history returning null for buyer_id, though the data is still in the files.

This case separates the 2 binding styles most sharply. The marketplace dropped discount_cents in March when a promotion ended, and in October added a new discount_cents for loyalty discounts. A February file still contains the old promotion values.

Table formats that record the partition scheme per data file let that scheme change without a rewrite. When Japan's order volume makes daily partitions on created_at too coarse, the table switches to hourly: new files are written hourly, old files keep their daily layout, and the planner prunes each file by the scheme it was written with. Queries filter on created_at itself, so none of them change.

The sink job that writes stream events into lake tables is where the stream's schema and the table's schema must agree. If the sink evolves tables automatically, any producer can add columns to any table it writes; if it never evolves them, new fields are dropped or the write fails. The layer policy applies here as well: raw tables auto-evolve so nothing is lost. Staging and curated tables change only through a migration that runs ALTER TABLE before the producer ships the field.

Time travel adds one question to check for each format: whether a query against an old snapshot uses that snapshot's schema or the table's current one. A backfill that compares a pre-rename snapshot against today's table depends on the answer. If it uses the snapshot's schema, the old snapshot exposes customer_id and the comparison must map it to buyer_id. If it uses the current schema, the old snapshot already shows buyer_id, the field ID carrying the values across.