How do you clean up employee data before a migration?

Nobody knows the true state of their data until they count it, and every migration plan built on an assumption gets rewritten. Profiling means running the boring queries first: how many active records exist, how many have a blank in each mandatory field, how many distinct values appear in each code column, what the oldest and newest date in every date field is, and how many records fail an obvious sanity check. The output is a one-page picture of where the problems concentrate, and it almost always contradicts what the team expected. That picture drives the plan. If almost all the gaps sit in one field for one population, that is a focused task rather than an organisation-wide cleanse, and knowing this before you commit a timeline is worth more than any tooling.

Most organisations hold employee information in several places at once: a legacy system, a payroll register, a shared drive of joining forms, a headcount spreadsheet maintained by finance, and whatever recruitment kept. These disagree, and the disagreement is usually not random. Payroll tends to be right about bank details and compensation because money moves on them, joining paperwork tends to be right about identity references and date of birth, and the reporting hierarchy in the working spreadsheet is often fresher than the one in the system nobody updates. Decide field by field which source wins, write it down, and apply it mechanically rather than arguing case by case. Where two sources disagree on a field with no clear winner, that is a manual review list, and keeping it explicit stops someone quietly choosing during the load.

Duplicates come from rehires, contractor conversions, entity transfers and simple retyping, and they cannot be found by name matching alone because names are entered inconsistently. Match on a stable identifier where you have one, then use combinations - date of birth with personal contact, or bank account with identity reference - to surface the rest for human review. Never merge automatically on a fuzzy match. Orphans are the other half: a record whose manager has left, a person assigned to a department that no longer exists, a cost centre that was closed, a location shut two reorganisations ago. Each needs a decision rather than a default, because loading an orphan into the new system reproduces the problem with a fresh timestamp. Fix the hierarchy before the people, since employee records depend on the structures they point at.

Free-typed values are the quiet killer of a migration. A single office appears as four spellings, a department has three names depending on who created the record, and designations proliferate until a company has hundreds of unique titles for a few dozen actual roles. None of this matters much while people read the records; all of it matters the moment you want a report, a permission scoped by location, or a mapping into the new system's picklists. So build the master lists first - locations, legal entities, departments, cost centres, designations, grades, employment types - agree them with the people who own each, then map every existing value onto the master before you touch anything else. This is also the moment to retire the codes nobody uses, since a migration is the cheapest opportunity you will get to shorten those lists.

Dates carry more downstream consequence than any other field and receive the least scrutiny. Joining date drives leave accrual, probation, service-linked entitlements and the first salary calculation. Date of birth drives statutory registrations. Confirmation, transfer and last working dates drive history and reporting. The errors are systematic rather than random: format ambiguity between day-first and month-first sources, spreadsheet columns silently converted to text or to a serial number, placeholder values entered when the real date was unknown, and dates that are internally impossible such as a confirmation before a joining. Run each of those checks as a query rather than trusting a sample. Then check the boundary cases in your HR system after load, because a date that imported one day off produces a proration error nobody traces back to the migration.

The new system will refuse records missing fields it treats as required, and there are only three honest responses. Find the value in another source, ask the employee for it, or make a documented decision to load a defined placeholder and correct it later. What fails is the fourth option, where someone invents a plausible value to get the load through, because that error becomes indistinguishable from real data the moment it lands. Prioritise by consequence: a missing identity reference or bank detail blocks pay and must be closed before go-live, while a missing emergency contact can be collected from employees after launch through self-service. Where you do collect from employees, do it once with a clear deadline rather than chasing individuals, and reconcile the responses against the gap list rather than assuming completeness.

Announce a freeze window in which no changes are made in the old system, and make it short enough that the business tolerates it. Everything arising during the freeze goes onto a change log to be applied after go-live, not into the source. Then load in dependency order: entities, locations, departments and cost centres, then grades and designations, then employee master records, then reporting relationships, then balances and history. Loading employees before the structures they reference produces failures that look like data problems and are really sequencing ones. Run the full load into a test environment first and reconcile counts and totals against the source before the real one. Above all, do the cleaning in the source or in a staging file, never inside the new system - a cleanse performed after go-live means you have tested a load you will never repeat, and your employee records carry two histories instead of one.