Engineering Journal · ENGINEERING · November 4, 2025 · 17 min read

The Rows Moved. The Truth Did Not.

Copying data into a new schema is the easy afternoon. Proving that the new rows still mean what the old world meant is the actual migration.

By Golam Sorwar, Tech Lead and Full Stack Engineer in Dublin.

I have written about walking a live application onto a newer Laravel while the business kept moving. That was about runtime and packages. This is about a different kind of move: taking records from one shape of truth to another — a legacy platform into an ERP, a spreadsheet into tables, two customer lists that both claim to be the list. The import finishes. The counts look serious. Someone says we are done. Then a person who lives in the old world opens a record and says that is not what it meant. They are usually right. Moving bytes is a script. Moving meaning is a project. If you only budget the script, you will spend the project in production, as arguments.

Context

I have had to think about this in travel-platform records moving into a newer back-office, and in operational systems that absorbed years of exceptions stored as notes, extra columns, and "we always do it this way." The source is never as clean as the destination schema you drew.

The people who know what a blank field meant are not always still in the building. The source system may still be running. Dual writing sounds tidy in a meeting.

I treat that inventory as part of the estimate, not as a surprise in week three. If nobody can explain a status, that status is a research task. If two teams disagree about a duplicate, that disagreement is the mapping. Pretending it is a type-conversion problem is how you get a fast script and a slow year.

The problem

The apparent problem is ETL. Map columns. Run the job. Count rows.

The real problem is semantic gap. Status values that look like synonyms and are not. Currencies that were implied. Identifiers that were reused. Duplicates that were the same person and duplicates that were not. History that was stored as overwrite. Foreign keys that pointed at rows you are not allowed to create in the new world.

If you cannot say what "paid" meant in the old system, you cannot say it in the new one. You can only paint it.

The problem is also proof. Even a perfect mapping is a claim until someone who lives in the domain has walked lives and totals. A developer who stares at a diff is not that someone. They can tell you the script ran. They cannot tell you the new row still answers the old question. If you skip that person, you will discover them as a ticket after go-live.

The tempting solution

A one-shot import on a weekend. Default the blanks. Drop the rows that fail validation. Go live Monday.

Or keep both systems forever and tell staff to look at the old one "if something looks wrong." Something will always look wrong. You have built a habit.

Why that is not enough

Defaults invent history. A blank date that becomes 1970, or today, will flow into reports and look like a fact.

Dropped rows are people. They will arrive as tickets. You have not failed validation. You have postponed a conversation.

Duplicates that you merge without a rule will create a polite frankenstein: one email, two financial lives, a support nightmare.

A migration without a reverse path trains you to be brave. Brave is a bad mood for data.

Reconciliation that is only "row counts match" will miss the row that moved and changed its meaning. Counts are necessary and almost never sufficient.

Foreign keys make this sharper. The old world may have pointed at a person you are not willing to create in the new world — a deleted user, a merged company, a placeholder. If you invent the missing parent to make the import succeed, you have created a citizen who never existed. If you drop the child, you have erased a fact someone will ask for. Both are decisions. The script should not pick in the dark.

Options

Big bang, accept the mess, fix forward. Advantage: one cut. Disadvantage: you will not know what you lost until a human is angry.

Shadow run: write to the new store, compare, do not cut over until the disagreements are named. Advantage: you find the semantic gaps while the old system still works. Disadvantage: you run two writers or a lag. Worth it for money and identity.

Migrate in slices — one cohort, one year, one module — with a written definition of done for each slice. Advantage: blast radius. Disadvantage: a long period of split brain. Need a rule for which system is allowed to change which slice.

Do not migrate history, only open items. Advantage: less dirt. Disadvantage: the organisation cannot answer last year. Sometimes they can live with an archive. Sometimes they are legally or operationally unable.

Migrate the keys and the open items first, history later as a read-only archive. Advantage: go-live does not wait for every old note. Disadvantage: two places to look. That can be honest if you label the archive as archive, not as a second current system.

Trade-offs

You trade calendar time for fewer fictional defaults. That trade is correct if anyone will make a decision from the new rows.

You trade some completeness for honesty. An explicit "unknown" is better than a pretty value.

You should not trade away the mapping document. If the mapping lives only in a script, the next argument has no evidence. A comment in a pull request is not a document the office can read.

Decision

I want a written map from old meaning to new meaning, including the meanings we will refuse to guess. I want a reconciliation that checks those meanings on a sample a human can understand, and on the aggregates finance or operations already trust — totals, counts by status, "this person has this open item."

I do not want a go-live that is only "the job returned 0."

I also want a leftover pile with an owner. Unknowns that sit in a spreadsheet called misc will become the next source system. Name the pile. Decide whether it is archive, manual review, or out of scope. Out of scope is allowed. Unnamed is not.

Implementation / Thinking process

Inventory the source like you would inventory a dependency. What are the statuses. What is allowed to be blank. What is unique in theory and not in the table. What is a key that other systems already stored.

Build the mapping as data, not only as code. A table of old_status → new_status → confidence. The unmapped ones are the project.

Preserve the old identifier. You will need it when a human says "in the old system I was 4412." If you lose the join, you have failed the first support ticket.

Run the import so it can be repeated. Idempotent upserts keyed on the old id. The first run will be wrong. That is not a scandal. An unrepeatable run is.

Reconcile in public. Show the mismatches to the people who know the domain before you show a dashboard that says 100%. They will find the lie the count cannot.

Pick a handful of lives and walk them end to end: the person, the open items, the last payment, the last letter. Aggregates hide the frankenstein merge. A single walked life will not. If you cannot walk ten lives without a specialist, you are not ready to walk ten thousand.

Keep a diff you can rerun. "It looked fine on the day" is not a method. A query that still compares old_id to new meaning after cutover is how you catch the late correction someone made in the source while you were celebrating.

I treat unknown as a first-class value. A status you cannot map is not "the closest enum." It is a row in the leftover pile with a reason. Painting it as the closest enum is how you get a tidy import and a lying report. Tidy is not the goal. Explainable is the goal.

Failure modes

Character sets and trimmed spaces that split one person into two.

Timezones that shift a date across a term boundary. A "day" is a business object.

A unique constraint in the new world that the old world never had. The import fails or silently suffixes. Both are decisions. Do not let the database pick.

A rollback that restores the old application and not the old identifiers you already sent to a vendor. The outside world has moved.

A sample that was only the pretty records. The ten lives you walked were the ones someone already understood. The dirt is in the notes, the duplicates, and the status nobody can define. If the sample avoids those, the reconciliation is a brochure.

Operational consequences

Staff will compare screens for months. If you cannot explain a difference in one sentence — we mapped X to Y because Z — they will not trust either screen.

Auditors, if they exist in your world, will ask how you know. "The developer ran a script" is not an answer. The reconciliation output is.

Staff will also invent a third system if the new one cannot do last year's trick. That trick is often the reason the old dirt existed. If you migrate the rows and forbid the trick, you have not finished the domain. You have finished the table and postponed the argument.

Support will speak in old identifiers for a long time. If you cannot join 4412 to the new record in one lookup, you have made every conversation a research project. Preserve the join. Put it on the screen. That is not nostalgia. That is how you keep the human map when the schema changes.

Lessons

A completed import is a claim about meaning. Treat it like a claim. Evidence, sample, aggregate, leftover pile of unknowns.

If you cannot say what you would do if the new truth is wrong, you are not migrating. You are jumping. A jump can be survived. It cannot be explained to the person who lived in the old row. They will ask what it meant. You will want the map.

What I would do differently today

I would have reserved time for reconciliation in the first estimate, as a first-class phase, not as "testing." Testing sounds optional to people who are tired. Reconciliation is the product.

I would have refused silent defaults in the mapping. Every default is a policy. Policies get names. Unnamed defaults are how fiction enters a tidy import.

I would have insisted on a repeatable import before the first "real" run. A migration you can only do once is a stunt. Stunts do not get a second chance to be honest about the leftover pile.

Closing thought

The rows will move. They always do. The work is to keep the meaning in the cart with them, or to admit which meanings you left behind. Admission is a kind of correctness. Silence is not. Until you can prove the new record still answers the old question, you have copied a table, not migrated a truth.