Lesson 2 of 4 · 25 min

Evolve schemas without silently changing meaning

Plan schema changes with reader and writer compatibility.

Mechanism and reasoning

A schema change has a structural side and a semantic side. Adding a nullable column may be structurally compatible, yet the new field's meaning can still be ambiguous. Renaming a column can preserve its identity in a table format while breaking a downstream query that references the old name. A type widening may preserve stored values but change application assumptions.

Inventory readers and writers before the change. Identify strict deserializers, scheduled transformations, dashboards and exported files. 'The table accepts it' is only one checkpoint. The pipeline contract must say which versions can coexist and how long consumers have to migrate.

Use an expand-and-contract plan when compatibility requires it. Add the new representation, teach readers to understand it, update writers, verify adoption and only then remove the old representation. If both fields exist temporarily, define the authoritative one and the consistency check. Dual writing without a conflict rule creates a second source of truth.

Field identity matters in formats that support schema evolution. A column's position is not a durable identity. Reusing a removed name for different meaning can confuse historical data and readers. Keep provenance and test old files against the new schema behavior using the actual engine and format version.

Semantic changes often deserve a new field or version even when the physical type remains unchanged. A price that changes from pre-tax to post-tax is not a harmless decimal update. In an interview, use a concrete reader/writer matrix and name the rollback limit. Once new values cannot be represented in the old schema, rollback may require a data conversion or a forward repair.

Build a reader and writer compatibility matrix

A migration plan needs a table of supported combinations, not just a statement that the new table schema accepts writes. List old and new writers against old and new readers. Include exports and cached datasets if they remain part of the user contract. A reader that was not deployed recently can still be active through a scheduled job or a downloaded file.

Combination	Example behavior	Allowed during migration?
Old writer, old reader	USD amount_cents only	Yes, under old contract
Old writer, new reader	New reader derives supported USD representation	Yes, with explicit fallback
New writer, old reader	New fields also populate valid old USD field	Only for representable data
New writer, new reader	Currency-aware minor units	Yes
Non-USD new data, old reader	Old reader assumes cents mean USD	No

The third row is where many plans fail. Dual writing does not make every new value representable in the old contract. If the new schema supports currencies with different minor-unit conventions, an old USD-only reader can produce incorrect amounts even when the integer parses successfully. Gate unsupported data until the old reader is retired or provide a genuinely compatible versioned output.

sql

1-- Teaching validation for the temporary USD overlap.2SELECT transaction_id, amount_cents, amount_minor_units3FROM payments4WHERE currency = 'USD'5  AND (6    amount_cents IS NULL7    OR amount_minor_units IS NULL8    OR amount_cents <> amount_minor_units9  );

This query checks a defined overlap, not universal monetary correctness. It does not validate the source amount, exchange rates or every currency. Null handling is explicit because a simple inequality can fail to return null mismatches under SQL's three-valued logic. A migration check should state the subset where equivalence is expected and return the keys that violate it.

Choose one authoritative representation during each phase. If writers can independently update both fields, divergence becomes inevitable. Prefer deriving one representation from the authoritative one where the mapping is valid. Record when readers switch and how adoption is measured. Query logs, version metadata or explicit owner confirmation can provide evidence; elapsed time alone does not prove that all consumers migrated.

Schema-evolution features in a table format address particular storage problems. Iceberg uses field IDs to preserve identity across supported changes, so column position is not the sole identity. This helps avoid some errors during rename or reorder. It does not automatically rewrite every downstream SQL query, dashboard formula or exported file contract. The reader engine and catalog behavior also matter. Test the actual tools used by consumers.

A semantic change may require a new metric even when physical storage is unchanged. If net revenue starts subtracting tax as well as refunds, old historical comparisons can shift without a parser error. Define the formula, effective date, historical restatement policy and rounding behavior. Decide whether old periods are recomputed or whether the new definition applies only from a given date. Mixing both meanings in one unlabeled column makes trend analysis unreliable.

Rollback has a representability boundary. Before new-only values are written, reverting readers and writers may be straightforward. After such values exist, an old reader may be unable to interpret them. Rolling back code does not remove that data. A safe plan can block those writes until the migration is committed, preserve a compatible projection, or use a forward repair. State the point after which simple rollback is no longer valid.

Test the matrix on a small fixture containing nulls, zero amounts, negative adjustments, old records and new-only values. Check the actual reader outputs, not just whether a query executes. Include an old export consumer if positional columns matter. The result should prove which combinations are supported and which are deliberately rejected during the transition.

Worked example

Teaching change: amount_cents is an integer in v1. v2 introduces amount_minor_units plus currency. During transition, USD rows populate both fields and a check requires equality. New readers prefer the v2 fields. Old readers continue on amount_cents until retired. Non-USD rows cannot safely enter the old contract merely because both fields are integers. Gate those rows or use a separate versioned output. A structurally valid integer is not enough to establish monetary meaning.

Exercise

A nullable column called net_revenue changes definition from after refunds to after refunds and tax. The type stays decimal. Is this backward compatible for reports? Propose a safer change.

Model solution and rubric

It is semantically incompatible even though the type is unchanged. Introduce a clearly defined new field or versioned metric, retain the old definition during migration and compare both on a known dataset. Notify downstream owners through the normal contract process. Verify that historical reports use the intended definition. A rollback can then restore reader selection without pretending the two metrics mean the same thing.

Score out of four: one point for the correct result, one for showing the intermediate reasoning, one for identifying the stated failure case, and one for a verification that could disprove the answer. Do not award the reasoning point for a tool name alone.

Failure modes and misconceptions

“Field IDs make every downstream rename safe.” They protect identity within supported format behavior; named queries and external consumers can still break.

“Rollback means deploying the old code.” New data can exceed the old representation. Recovery must address the data boundary as well as the binaries.

Interview probe

Evidence class: recommended. Original practice.

When is adding a column a breaking change?

Strong answer: Strict readers, positional exports, select-star consumers or semantic assumptions can make it break. I would test the actual reader set and separate structural acceptance from business meaning.

Follow-up: What prevents a dual-written old and new field from drifting?

Weak answer indicators: Equating type compatibility with semantic compatibility; removing old fields before consumers migrate; relying on column position as identity.

Sources

Technical references: Apache Iceberg evolution; dbt data tests; PostgreSQL ordinary comparisons and null predicates. Sources support the documented mechanisms. The numbers, decisions, rubrics and interview prompts in this lesson are original teaching examples, not measurements or employer question claims.

docsApache Iceberg evolutioniceberg.apache.org docsdbt data testsdocs.getdbt.com docsPostgreSQL ordinary comparisons and null predicatespostgresql.org

Checkpoint

An integer changes from USD cents to currency-dependent minor units. What can happen to old readers?

AThey must reject every row at parsingBThey can parse values successfully while assigning the wrong monetary meaningCField IDs automatically convert every amount to USDDAdding a currency column automatically changes their calculations

Checkpoint

During dual writing, what limits divergence?

AOne authoritative representation with explicit derived mapping and checksBAllow both fields to change independentlyCWait a week without observing readersDDrop the old field before checking adoption

Checkpoint

A supported column rename keeps its Iceberg field ID. A downstream SQL query still uses the old column name and no compatibility view exists. Which result should the migration plan expect?

AThe field ID rewrites the SQL query text automaticallyBThe query still needs migration or an explicit compatibility layerCMatching physical file positions make the old name resolve automaticallyDKeeping old data files means the table exposes both names automatically

Checkpoint

Why does the overlap check use IS NULL as well as inequality?

AOrdinary inequality always selects null mismatchesBA nullable field must always be dropped from comparisonsCIS NULL proves that the non-null amounts are equalDOrdinary comparison with null yields unknown and can omit the violating row

Checkpoint

New-only currency values have been written and old readers cannot interpret them. What does rollback require?

AOnly redeploy the old application binaryBOnly rename the new fields backCA compatible data projection/conversion or forward repair under an explicit contractDOnly retain more schema history without changing readers

Explain how you would plan schema changes with reader and writer compatibility without reading the solution. State one assumption that could change your answer, and one observation that would make you revise it.

Not yetGetting thereConfident

Wrap-up

Version meaning as carefully as structure. Test real readers and define which representation is authoritative during migration.

Sources

Free to read · better with Enzo

Learn it with Enzo

Save your progress, answer the checkpoints, and let Enzo quiz you on what you just read.

Continue with Enzo Sign up free