Why do teams replay the whole ordered chain of schema change units from an empty database in tests?
answer
- the chain, not the newest file
- ordering and dependency errors
- fresh database, production engine family
- empty start hides volume and data
basics
~20 sReplaying every change unit from empty proves the chain itself still builds a valid schema, catching ordering and dependency errors that a developer's long-lived database hides. Running only the newest unit leaves the rest of the chain untested.
solid answer
~50 sProduction's schema is not authored directly; it is the accumulated result of applying every change unit in order. So the artefact under test is the whole chain, not the newest file. A developer who runs only the new unit runs it against a database that already carries years of state, some of which may never have come from the chain at all, so a unit that depends on a missing table, duplicates an object, or was never registered in the chain still succeeds locally. A replay creates a fresh empty database of the production engine family, applies every unit in order, and fails the build on the first error. It is cheap and deterministic enough to run on every commit — but with no rows present it proves applicability only, not that data survives or that the change finishes at real volume.
go deeper
Remember that the schema is built by an ordered chain of change units, and that the test runs all of them against a fresh empty database, not just the one you wrote.
Be able to name the defect classes a from-empty replay catches: missing dependencies, duplicate objects, unregistered units, and syntax errors in units that never ran anywhere.
Show you know the replay's limits and pair it with the rehearsals that cover them — a schema-versus-mapping comparison and a run against a realistically sized copy — rather than treating green as proof the deploy is safe.
The trade to articulate is cadence against fidelity: the cheap replay runs on every commit, the expensive volume rehearsal cannot, so decide which signals you buy continuously and which you buy before a release.
## What "the chain" is A schema is rarely shipped as a picture of the finished database. It is shipped as an **ordered sequence of small change units** — create a table, add a column, add an index, tighten a constraint, drop something that is no longer used. Each unit is written once, applied once per database, and recorded in a history the runner keeps inside that database. On the next run the runner looks at the history, works out which units are missing, and applies only those. The consequence is easy to miss: **nobody authors the production schema directly**. It is the accumulated result of every unit having been applied, in order, over the life of the project. The artefact you have to trust is therefore the whole ordered sequence — not the file you wrote this morning. ## What a from-empty replay proves The test is mechanically simple: create a fresh, empty database of the same engine family as production, point the runner at it, let it apply every unit from the first to the newest, and fail the build on any error. It is fast, because nothing has any rows, and it is deterministic, so it can run on every commit. What it catches: - **Dependency errors.** The new unit references a table, column, constraint or index that no earlier unit in the chain creates. It works on your machine because that object got there some other way — a hand-run statement, an abandoned experiment, a unit that was later edited. - **Conflicts inside the chain.** Two units create the same object under different names, or a later unit re-adds something an earlier one already added, so the second application errors. - **Units that are never actually executed.** A file sits in the source tree but was never registered in the chain, so it silently does nothing on every database including yours. - **Syntax errors in a unit that has never run anywhere**, because every existing database already had that state when the unit was written. - **A chain that is not self-contained** — a unit that only succeeds if particular rows already exist. Every one of those defects is invisible to a developer who runs only the newest unit against their own long-lived database, and every one of them surfaces at deploy time against the one database you cannot casually re-create. ## What it does not prove | Question | Answered by a from-empty replay? | |---|---| | Do the units apply in order without error? | Yes | | Does the resulting schema match what the code maps? | No — that needs an explicit comparison step | | Will each change finish inside the deploy window at production size? | No — the tables are empty | | Does existing data survive the change? | No — there is no existing data | | Does the increment apply on top of what is really deployed? | No — that needs a rehearsal from a restored snapshot | The last row is the one that bites. Production is the result of the chain **as it was actually applied over time**, which can differ from the chain as it stands today if anyone ever changed history by hand. A green replay says the chain can build a correct schema from nothing; it does not say the deployed database is on that chain. ## Where the replay sits among the other rehearsals Think of it as the cheapest rung of a ladder, each rung answering a different question and running less often: 1. **From-empty replay, every commit.** Is the chain internally consistent? 2. **Schema-versus-mapping comparison, same job.** Is the schema the chain built the one the code expects? 3. **Rehearsal over a restored, production-sized copy, less often.** Does the increment apply to what is deployed, and how long does it take? Skipping rung one to jump to rung three is a common instinct and a bad trade: the expensive rehearsal is slow, needs a data copy, and cannot run on every push, so ordering defects would sit unnoticed for days. ## Practical notes - Run the replay against the **same engine family** as production; the chain is written in that engine's dialect and a different one will accept or reject different statements. - Run the data-access tests **in the same job, against the database the replay just built**, so those tests exercise the real column names, types and nullability rather than a schema produced some other way. - Treat a replay failure as a broken build, not as an environment problem. The usual temptation — patch the test database by hand so the run goes green — destroys the only signal the test provides. - Keep the replay's runtime honest by letting it drop and re-create the database each run. A replay that reuses a database from the previous run is no longer a from-empty replay.
- Production started years ago, not from empty. Does a from-empty replay actually tell you anything about it?It tells you the chain is internally consistent, which is a precondition for anything else. It does not tell you the deployed database is on that chain, or that the pending increment applies to it. That needs a separate rehearsal against a restored copy — the two answer different questions and both are worth running.
- The replay is green but the application still fails at startup with a missing column. How?The chain built a valid schema, just not the one the code maps to — someone changed the mapping without adding a change unit. Applying cleanly and matching the mapping are separate assertions, so the job needs an explicit comparison of the produced schema against the mapping, run on the database the chain built.
- Does it matter which database engine the replay runs on?Yes. Change units are written in a specific dialect, and engines differ on what statements they accept and what a given change costs. A replay on a different engine can accept a unit that production rejects, or vice versa, so use the same engine family as production for the replay.
It is the difference between checking today's diff compiles and checking the repository builds from a clean checkout: only the clean build catches the file you forgot to commit.
saying these in an interview costs you the question
- Only the new change unit needs testing; the older ones already ran.
- If it applies against my local database it will apply in production.
- A green replay proves existing data survives the change.
- A green replay proves the schema matches what the code maps.
- Reviewing the change unit by reading it is equivalent to running it.