Migrate pharma records with traceable reconciliation

Matching counts can conceal a changed result

A migration transfers 500 laboratory records and reports 500 successful imports. One result changed from 2.5 milligrams per litre to 2.5 micrograms per litre because the target unit mapping was wrong. The count is perfect, but the record's meaning has changed by a factor of 1,000.

Pharma data migration validation should demonstrate that the retained evidence remains complete and interpretable for its intended use. Counts are an important control, but they cannot detect every semantic, relationship or access failure.

EU GMP Annex 11 specifically addresses preserving value and meaning during data migration. The practical workflow below builds a reconciliation approach around that objective, with the site's applicable requirements and approved strategy determining the evidence. EU GMP Annex 11, section 4.8.

Decide what should move before designing the import

Inventory record types, status, age, relationships and retention needs. Some records may need active use in the new system; others may remain in a controlled archive with reliable retrieval. The decision should reflect business and quality needs rather than the convenience of the import tool.

Define the authoritative location after cutover. If a closed record remains in the legacy archive, users need a clear path to it. If an open record moves, the target must preserve the information needed to continue work, including ownership, pending actions and relevant approval history.

Record exclusions and their rationale. “Old data” is too vague when different record types have different retention or use requirements. The migration scope should explain what happens to every relevant population, even where the decision is not to import it into the new application.

Map meanings rather than column names

Compare source and target definitions for identifiers, dates, units, classifications and statuses. Two fields called “complete” may represent different milestones. A source status meaning testing finished should not become a target status meaning quality approved.

Document transformations with examples and review them with the relevant process owner. Include permitted values, null handling, precision, time zones and historical identities. Avoid a generic rule that replaces all missing values with a convenient default.

Where the target cannot represent a source feature directly, agree a controlled alternative and verify that it preserves the required meaning. Do not discard the feature silently because it is difficult to map.

Worked example of 500 records and one wrong unit

Consider the illustrative migration of 500 records described above. Source and target counts match, and every source identifier has a corresponding target identifier. A field-level check then compares the value and unit pair for each applicable result.

The record containing 2.5 milligrams per litre is found in the target as 2.5 micrograms per litre. If the target intentionally uses micrograms per litre, the corresponding value would be 2,500. If no conversion is intended, the unit should remain milligrams per litre. The mapping must choose and justify the correct representation before re-execution.

The team identifies all records using the same mapping rule, not only the first detected discrepancy. It corrects the approved mapping, repeats the affected transformation and reconciles the resulting population. The evidence retains the original defect and resolution rather than replacing the initial test report with a clean one.

Next, the team checks relationships and attachments. A migration can pass the value check while linking a result to the wrong sample or omitting a report. Each control addresses a different failure mode, so the final conclusion should be based on the combined evidence.

This example shows why migration acceptance needs more than a success percentage. A single consequential error can matter even when 99.8 percent of the records appear correct.

Use layered reconciliation

Begin with population controls: counts, identifiers and inclusion rules. Follow with field and transformation checks, relationship checks and evidence-access checks. Use the approved risk assessment to determine where complete automated comparison is feasible and where additional representative review is needed.

Reconcile exceptions explicitly. Every rejected, transformed or unresolved record should have a reason, owner and disposition. A migration tool's warning log is not a completed assessment if nobody checks whether the warning affects required evidence.

Preserve the source snapshot and migration version used for each trial. This allows differences between runs to be explained and prevents a moving source dataset from making every comparison ambiguous. If new source changes are included, identify their boundary and expected effect.

Handle open work as a special population

An open deviation, purchase workflow or laboratory sample has future actions as well as historical data. Confirm that the target preserves the current owner, state, due date, required approvals and linked evidence. A record imported as closed to simplify migration can remove necessary work from the operating queue.

Agree how users stop or continue work during cutover. If the source remains active after the initial extraction, capture and reconcile late changes through a defined process. Do not rely on users remembering which system to update.

Test reassignment and approval after migration. Historical identities may be inactive, while current responsibilities belong to different people. Preserve the original attribution and assign future work through an authorised process rather than rewriting the past to use current accounts.

Plan cutover and rollback around data changes

Set the cutover checkpoints, decision owners and acceptance evidence. Define what must be true before the target becomes authoritative and before the source is restricted or retired. A calendar date alone should not override unresolved migration defects.

Rollback needs a data strategy. If users have created new records in the target, returning to the source requires a controlled approach to that work. Restoring an old database may erase the very transactions performed during the attempted cutover.

The backup and restore guide explains record-level recovery checks. Use that capability as part of the migration plan, while recognising that recovery and reconciliation of newly created work are separate tasks.

Preserve legacy access deliberately

Where records remain in the old system or archive, test retrieval with the intended user roles. Include attachments, history and the software context needed to interpret them. Record the operating owner and support arrangements for the retained environment.

Consider licence expiry, authentication changes and obsolete dependencies before retirement. A database dump can preserve bytes while leaving users unable to read the record meaningfully. The archive approach should be demonstrated, not assumed.

The ERP, MES, LIMS and QMS architecture guide helps identify authoritative records and cross-system relationships. Migration needs to preserve or intentionally replace those links across the wider system landscape.

Validate the migration process and its outcome

Assess the transformation tools, scripts and environments for their intended role. Control versions and execution permissions, and preserve the evidence of each approved run. A repeatable script is valuable only if its mapping and error handling are correct.

Connect the migration work to the computer-system validation strategy. Test known difficult cases such as wrong units, duplicate identifiers, missing attachments, inactive users and records crossing the cutover boundary.

NextEdge Labs can help build the mapping, reconciliation and exception workflow. A useful pilot uses a representative subset and demonstrates how a detected defect is traced, corrected and rechecked before the full migration is accepted.

Summary and next actions

Migration success means records remain complete, traceable and meaningful in their new or retained home. Counts support that conclusion, but units, states, relationships and usable evidence need their own checks.

• Define migrated, archived and excluded populations with approved reasons and retrieval plans.
• Review mappings with process owners, especially units, dates, statuses and historical identities.
• Run layered reconciliation and resolve every relevant exception before cutover acceptance.
• Test open-work continuation, late changes and rollback without losing newly created records.