Data migration when replacing an agency system

Reference article · 5 min read

A system replacement is usually described as a software project, but the work that sets its schedule is the data. Moving records into a system with different structure and stricter validation is slow, detailed work, and most of the decisions it requires can only be made by agency staff. Data problems are the most common cause of a delayed go-live, and conversion is the line item agencies are least likely to staff honestly.

Knowing the sequence in advance does not shorten it. It tells you what to put in the statement of work and how much staff time to reserve.

Profile the legacy data first

Profiling means measuring the source before anyone designs a mapping: record counts by table, completeness by field, distinct values in fields that are supposed to be coded, orphan records, and values the new system will reject. Legacy data carries the history of how people worked around the old system, so a free-text field used for three different purposes is a normal finding rather than a sign of a badly run agency. Profile before the vendor writes the mapping: one built from documentation rather than data gets rewritten later, on the critical path.

Decide what moves and what stays in a read-only archive

Not everything belongs in the new system. Converting all history is expensive and imports old quality problems into a clean environment. Sort records three ways:

• Convert. What staff need on screen: open cases, active accounts, current permits, balances, and the history behind them.
• Archive. Closed and historical records kept in a queryable copy of the old database, or an export with a documented layout, retained as long as the retention schedule requires.
• Dispose. Only where the schedule allows it, the records officer approves, and no litigation hold or audit applies.

This is a records decision as much as a technical one. Retention schedules differ by state and record series and change over time, so confirm yours with your records office and counsel, and decide who can query the archive.

Cleanse, deduplicate, and write the rules down

Correct data in the source system where you can, and script anything corrected in transit: spreadsheet cleanup cannot be reproduced during the cutover window.

Deduplication needs explicit rules. The same resident, parcel, business, or account will appear several times across sources. The agency sets the matching rules and the threshold for automatic merges, and names who adjudicates the cases the rules cannot decide. A wrong merge affects benefits, citations, and billing, so that path matters more than the algorithm.

Then write the field-level mapping. For every target field, record the source field, the transformation, the default when the source is empty, the rule for values that do not fit, and who approved it. Version it and have the program owner sign it, not IT alone. It is the practical contract between the agency and the implementer.

Run trial loads and reconcile with evidence

Plan several full trial loads into a conversion environment, each followed by validation from the staff who use the data daily. Track defects per run: the trend is the most reliable signal of whether the go-live date is real. Reconciliation should produce evidence someone outside the project can follow:

• Record counts. Source to target for every object, with a written explanation of each difference. A difference is acceptable when it is explained, not when it is small.
• Control totals. Balances, receivables, and fund totals tied back to the general ledger, with finance signing the result.
• Spot checks. Real cases reviewed end to end by the people who handle them.
• Exception reports. Every record rejected, defaulted, or transformed in a lossy way, with a disposition for each.

Keep the output. It is the acceptance record for conversion, and the answer when an auditor asks later why a record reads as it does.

Rehearse the cutover

Make the final trial a rehearsal of cutover itself, run against the clock: freeze the source, extract, load, reconcile, validate, and reach a go or no-go decision against criteria agreed in advance. Time each step. The rehearsal, not an estimate, tells you how long the outage really is, and that is the number you publish. It should also exercise rollback and confirm how work done on paper during the window gets entered afterward.

Who decides what

• Program owner. What converts, what the data means, and which business rules apply.
• Records officer and counsel. Retention, archive format, and disposition.
• Finance. Balancing and sign-off on control totals.
• IT or data steward. Extracts, access, environments, and the authoritative source for identity.
• Vendor. Target structure, load tooling, transformation mechanics, performance, and defect fixes.

A vendor cannot decide which history matters, which of three conflicting addresses is correct, what a retired code meant, or whether a record may be destroyed. Any plan that assumes the vendor will clean the data assumes work no vendor can do alone.

What belongs in the statement of work

• The number of trial conversion cycles included, and the price and process for additional cycles.
• Who produces the mapping document, who reviews it, and what sign-off means.
• Conversion acceptance criteria and reconciliation deliverables, as a milestone separate from software acceptance.
• Whether the read-only archive is in scope, in what format, and for how long.
• Data handling: where data is processed, who may access it, and return or destruction at the end. Criminal justice, health, student, and tax records carry added rules, so confirm current requirements with the governing authority or your counsel.
• A stated assumption about agency staff hours, by role, so the effort is budgeted rather than discovered in month four.

HQT works with agency teams on data profiling, conversion mapping, and reconciliation during system replacements.

Frequently asked questions