How I Turn a Legacy HR Database Into Clean Data, Phase by Phase

Introduction

A. A personnel system that cannot simply be switched off

A city government keeps the working life of every civil servant in one place: who they are, their family, their education, every rank and position they have held, their performance scores, their leave, their awards and their disciplinary records. For years that place was an older personnel system built by another team. I am building its replacement, and before anyone can rely on the new system, the records in the old one have to arrive in it — all of them, and correctly.

The old system does not stop while that happens. Staff keep entering data into it every day, so there is no quiet weekend in which everything can be copied once and the old system retired. The migration has to run again and again, against fresh copies of the old database, each time a little more complete, until the moment the city decides the new system is the one of record. A migration that only works once is useless here; it has to be repeatable, and each repetition has to leave the new system in a state someone can inspect.

The challenge is to turn a legacy HR database into clean data for a new system phase by phase: without stopping the old system, without touching it, and without letting a single row disappear silently.

Volume is not what makes this hard. Tens of thousands of employees and a few hundred thousand related rows fit comfortably in any modern database. What makes it hard is the shape of the data: the old system enforced almost nothing, so the database records not what the rules said but what people happened to type over more than a decade.

B. What "clean" means for personnel data

Clean does not mean pretty. In a personnel system, clean means that every coded value refers to an entry in a reference table, every relationship points at a real record, every date is a real date, and every number means what its column says it means. Those properties matter because the new system acts on the data. It calculates when an employee is due for promotion, when their salary should step up, when they reach retirement age and which family members qualify for an allowance. A rank stored as a free-text string or a date stored as zeros does not merely look untidy; it makes those calculations wrong for a real person.

Clean also means honest about what is missing. Where the old data simply does not contain a fact, the new system should say so in a way that someone can find and fix, rather than inventing a value that looks plausible.

C. The approaches I rejected

The fastest approach is a single big script that reads every table and writes every row, run once on cut-over night. I rejected it because it fails all three requirements at once: it cannot be repeated safely, it cannot be inspected halfway, and when it meets a row it does not understand, it either stops the whole run or quietly drops the row.

The second approach is to loosen the new schema until the old data fits: make required columns optional, store unknown codes as text, accept zero dates. That moves every problem from the migration into the new system permanently, where it would surface years later as a wrong calculation. The third is to clean the old database in place first. That would mean writing to a system that is still in daily use and still the source of truth, which is exactly what must not happen.

What remains is a migration made of small, ordered phases that read the old data through a guarded door, translate it with explicit maps, keep the new system's rules intact, and report every row they cannot accept instead of guessing.

D. Fourteen obstacles in the way

Easy to state, awkward to build. Fourteen things stood between the old database and clean data in the new one.

The first is that the source must stay alive and untouched. The old system is in daily use, so the migration reads a copy of it, and a promise not to write cannot rest on good intentions or on what the database account happens to be allowed to do. Trusting discipline is the rejected alternative; the guarantee has to be enforced by the code that opens the connection.

The second is that the data has no consistent format. An old character encoding, a lenient database mode, dates written as zeros, impossible years, placeholders such as a dash or a full stop in required fields, and identity numbers stored in a spreadsheet's scientific notation. Accepting the values as they come is the rejected alternative.

The third is that types do not match their meaning. A rank stored as the text "III/a", performance scores that lost their decimal separator, salary amounts typed by hand with digits missing or extra zeros. Converting them blindly is the rejected alternative, because a wrong conversion looks exactly like a right one.

The fourth is that there are no foreign keys, and relationships are written as free text. Addresses without region codes — one city name spelled in dozens of ways — places of birth, institutions, study programmes, legal bases and signers' names. Matching them loosely at import time is the rejected alternative.

The fifth is that status codes are the old system's internal row numbers, not official codes. Reading a status column as it stands would place tens of thousands of employees under the wrong official code, and some official codes the old data needs do not exist in the new reference tables yet.

The sixth is that some meaning is hidden in the old code, not in the schema. A "verified" flag that a scheduled job set rather than a person, rejections recorded by deleting the row, columns whose content differs from their name. Reading the schema alone is the rejected alternative.

The seventh is that unverified submissions can be neither discarded nor accepted as fact. Throwing them away loses employees' recent work; importing them as official data skips the review they were waiting for.

The eighth is that the new system has required columns where the old data is empty. Making those columns optional for the sake of the migration is the rejected alternative.

The ninth is that the phases depend on each other, and every phase must be repeatable. A family cannot be imported before the employee it belongs to, and a document not before the record it is attached to. Running a phase twice must not duplicate anything.

The tenth is that bad rows must neither stop the run nor pass silently. Stopping on the first problem makes the migration impossible to finish; skipping quietly makes it impossible to trust.

The eleventh is that identity cannot be trusted to join people together. Identity numbers that are invalid or duplicated, children with no identity number at all, children not linked to any marriage. Merging records on whatever matches is the rejected alternative.

The twelfth is deciding when the old system stops being the source. Because it keeps running, the migration needs a boundary in time, and picking a convenient date by feel is the rejected alternative.

The thirteenth is that every rehearsal needs a fast, trustworthy starting point. Re-running every phase from nothing for each test is too slow to rehearse often.

The fourteenth is that the documents must move without holding everything else back. Hundreds of thousands of files cannot be copied in the time it takes to import their records, and the records cannot wait for them.

The order follows the path a row takes. The first decides how the old data may be read at all. The second to sixth are about what a single value means. The seventh and eighth are about what the new system is allowed to accept. The ninth to eleventh are about running the migration as a whole, and the last three about running it again and again until the old system can be left behind.

Method

A. A door to the source that can only read

Here is the answer to the first obstacle: every query to the old database goes through a single class, and that class enforces read-only access in two independent layers. Before the first query, the database session itself is switched to read-only, so the server refuses any write whatever the account could otherwise do. And every SQL statement is checked before it is sent: it must begin with a reading keyword, be a single statement, and contain none of the keywords that write, lock or change session state.

private const LEADING = '/^(SELECT|WITH|SHOW|DESCRIBE|EXPLAIN)\b/i';
private const FORBIDDEN = '/\b(INSERT|UPDATE|DELETE|CREATE|ALTER|DROP|TRUNCATE|SET|CALL|INTO|LOCK)\b/i';

public function select(string $sql, array $bindings = []): array
{
    self::assertReadOnly($sql);   // refuse anything that is not one read
    return $this->connection()->select($sql, $bindings);
}

Either layer alone would be enough most of the time. Two layers mean that a mistake in one — a forgotten session setting, a pattern that misses a keyword — is still caught by the other, and the source of truth never depends on a single line being right.

B. Normalising each value where it is read

The second obstacle is answered column by column, at the moment a value is read. Zero dates and impossible years become empty, never a guessed date. Placeholders such as a dash or a full stop count as empty. Identity numbers are checked for length and shape, and a number that arrived in scientific notation is recognised as damaged rather than repaired, because the digits it lost cannot be recovered. A value that cannot be normalised is not invented; it is reported.

C. Repairs that must prove themselves

Where a type does not match its meaning, I accept a repair only when its result falls inside the range the value is allowed to have. A performance score that lost its decimal separator is divided back only if the result lands between the lowest and highest possible score; anything else is reported. A rank written as text is translated through a table of known spellings, not parsed. Salary amounts are not repaired at all: the new system derives them from the official pay table, so a hand-typed amount is only compared, never trusted.

D. Free text becomes references through explicit maps

The answer to the fourth obstacle is a set of mapping tables kept as CSV files in the repository, one per kind of reference: institutions, study programmes, academic titles, government departments. Each maps the old free-text value or old identifier to an entry in the new reference tables. Duplicates in the old master data — the same institution entered several times — get a merge map, so all of them point to one entry. Addresses are matched against the official region codes, and an address that does not match is kept as text and reported, not forced onto the nearest region.

// old free text → reference id, through an explicit, reviewable map
$id = $institutionMap[normalise($row->institution)] ?? null;
if ($id === null) {
    $report->issue('INSTITUTION_UNMAPPED', "education:{$row->key}");   // keep the text, flag it
}

The maps are built by a command that lists every distinct old value and can append the ones not yet mapped, so the gap between the old data and the maps is always visible and shrinks with each pass.

E. Fixing the reference data before translating codes

The fifth obstacle is answered in a deliberate order. First, the reference tables of the new system are completed: every official code the old data needs, including those for newer employment types, is added with its name and legal basis. Only then is a translation table built from the old system's internal row numbers to the official codes. Reading the old status column directly would have looked correct and been wrong for a large share of employees, because the numbers happen to overlap with real codes that mean something else.

F. Reading the old code, and making meaning explicit

For the sixth obstacle, I read the old system's source code, not just its schema, because some rules exist only there: which status really means verified, where a rejection is recorded, what a column is actually used for. Each of those findings becomes explicit in the new system. Every coded term gets its own reference table with a code, a name, a description and, where there is one, the regulation behind it, and behaviour that depends on a code — whether a type of leave changes an employee's legal status, for instance — becomes a column of that reference table rather than a condition buried in code.

G. Unverified data becomes a proposal

The answer to the seventh obstacle is to import every row, but not every row as fact. Verified rows become official records. Rows still waiting for review, and no more than a year old, become proposals in the new system's own review workflow, so a reviewer decides them exactly as they would decide a new submission. Older unverified rows become proposals already rejected as expired by the migration, so nothing is lost and nothing stale is quietly accepted. Because a migrated proposal has no one who submitted it, it cannot be sent back for correction; it can only be approved or rejected.

$state = match (true) {
    $row->verified            => 'official record',
    $row->ageInDays <= 365    => 'proposal: submitted',
    default                   => 'proposal: rejected (expired in migration)',
};

H. Required stays required

For the eighth obstacle, I kept every required column required and gave missing values a traceable placeholder instead. An empty decree number becomes a marker that is unique within its column and easy to search for, so the gaps can be listed and filled later by the people who hold the paper records. Where a date can be derived honestly — the middle of the year a diploma was issued, for example — it is derived and the derivation is consistent; where it cannot, it is reported.

// A required field that the old data left empty: keep it required,
// but make the gap findable instead of inventing a plausible value.
function placeholder(string $column, int $n): string
{
    return "Not yet recorded #{$n}";   // unique per column, searchable
}

I. Ordered phases that can run twice

The ninth obstacle is answered by splitting the migration into thirteen phases, each a single command, run in the order their data depends on: employees, identity, family, career, organisation structure, performance, credit points, salary steps, positions, older positions, other histories, documents and, last, proposals.

import-legacy employees --dry-run          # roll back every chunk, report only
import-legacy family --id=1042,1043        # rehearse on a few employees
import-legacy documents --cutoff="$CUTOFF"       # the agreed moment

Every phase is idempotent. Each imported row carries the source it came from and its key in that source, and a second run updates the existing row instead of creating another. A dry run executes the phase and rolls back every chunk, so a full rehearsal leaves nothing behind but its report, and a phase can be tried on a handful of employees before it runs on everyone.

J. A report instead of a crash

For the tenth obstacle, each phase writes a report with one line per problem and a stable code for the kind of problem: an invalid identity number, an identity number that does not match the birth date, a family relationship that had to be rejected, a spouse pointing at the employee themselves, a score outside its range. The run carries on, and the report is the list of what it could not accept.

// keep going, but write down exactly what was not accepted: codes and keys, no names
$report->issue('IDENTITY_NUMBER_INVALID', "employee:{$row->key}");
$report->issue('RELATION_REJECTED', "family:{$row->key}");

The report deliberately contains no personal data — only codes and keys — so it can be handed to the personnel team for review without becoming a privacy problem of its own. Counting the codes after each run also shows whether the data is getting cleaner from one pass to the next.

K. Relationships are built, not guessed

The eleventh obstacle is answered by modelling family as a graph of people, marriages and parent–child relations, and by refusing to merge people on weak evidence. An identity number that is invalid or appears twice does not join two records into one. A child who cannot be placed in a particular marriage is linked to the employee alone. Each refusal appears in the report, so the cases that need a human decision are listed rather than resolved by accident.

L. A cut-over taken from the evidence

For the twelfth obstacle, I let the data choose the boundary. I matched every document record against a listing of the files actually present on the old server. Up to one particular evening, almost every record had its file; after it, almost none did. That moment became the cut-over, and every query to the old database now carries it as a condition, so all phases agree on exactly which rows belong to the migration. It is a setting, not a constant, so each rehearsal can move it.

-- every source query is limited to the same moment in time;
-- rows without a timestamp are old data and come along
AND (created_at IS NULL OR created_at <= :cutoff)

M. A starting point that is only used when it fits

The thirteenth obstacle is answered by keeping a data-only dump of a completed migration and restoring it onto a freshly migrated schema whenever I want to start a rehearsal from a known good state. The restore runs only if the two schemas are identical, checked by comparing schema-only dumps first. Restoring old data into a schema that has moved on would produce a database that looks right and is subtly wrong, which is worse than waiting for a full run.

N. Documents that follow at their own pace

For the fourteenth obstacle, the records of documents are imported together with everything else, and the files follow separately. Each document keeps its original path as its key in object storage, so the record already knows where its file will be. Until the file has been copied, the document shows as not yet available rather than broken. Every file is checked for what its content actually is before it is accepted, regardless of the name it carries.

Results and Discussion

A. What a full pass produces

A full pass now runs all thirteen phases against a fresh copy of the old database in well under an hour and leaves behind two things: a new database in which every coded value refers to a reference table and every relationship points at a real record, and a set of reports that list, by code, every row the migration declined to accept. Rehearsals have become routine rather than events, which is what a migration that runs alongside a living system needs.

B. Why the reports matter more than the code

The most useful output turned out to be the reports, not the data. They turn "the old data is messy" into a list that the personnel team can work through, sorted by kind of problem, with no personal data in it. Each pass shows which codes have shrunk, so the question of whether the migration is ready stops being a feeling and becomes a number per code.

C. Where this approach fits

This shape suits any migration where the source keeps running and its data was never constrained: ordered phases, a read-only door, explicit maps, honest placeholders and a report instead of a crash. It is more work than a single script, and for a one-off copy of clean data it would be too much. For a source whose rules lived in people's habits rather than in the database, it is the difference between a migration that finishes and one that can be trusted.

Conclusion

The hard part of moving a legacy personnel database was never the number of rows. It was that the old data recorded habits rather than rules, and the new system acts on rules. Every decision above follows from refusing to blur that difference: the old data is read but never touched, every value is translated through something explicit, nothing invalid is accepted as fact, and nothing missing is invented.

The single habit I would carry to any migration is to report rather than guess. A migration that stops at the first bad row cannot finish, and one that silently skips cannot be trusted; one that keeps going and writes down, in a form people can act on, exactly what it would not accept, can be run as often as needed until there is nothing left to write down.

Terms

Migration

Moving data from an old system into a new one, translating it into the new system's structure and rules on the way.

Idempotent

Safe to run more than once: a second run leaves the same result as the first instead of creating duplicates.

Foreign key

A database rule that a value in one table must refer to an existing row in another, so a relationship cannot point at nothing.

Reference table

A table that lists the allowed values of a coded field, each with a code, a name and a meaning, which other records refer to.

Cut-over

The moment after which the old system is no longer treated as the source of the data being migrated.

Dry run

Executing a step fully but discarding its changes, so its effects and reports can be seen without keeping them.