The Test That Proves the Rows Arrived and Nothing Else

Here is a migration test protocol that gets written and approved every week somewhere in this industry. Count the records in the source. Count the records in the target. Confirm they match. Run a checksum across two or three numeric columns and confirm the totals agree. Have a reviewer open thirty records side by side and confirm the fields look the same. Attach the screenshots. Sign.

That protocol is not wrong. It is incomplete in a specific and predictable way. Every check in it is a check on transport. None of them is a check on interpretation. The protocol will pass cleanly on a migration where a code list has been consolidated, where a derived field is now calculated under different logic, where every timestamp has moved by five hours, or where a result has been rounded to the target field’s precision and crossed a specification limit on the way. The rows arrived. The counts agree. The data no longer says what it said.

These failures do not announce themselves at cutover. They surface months later, when someone trends a stability data set and the slope looks wrong, when a batch investigation pulls a historical result and it does not match the paper record, when a safety signal changes shape because historical cases were recoded, or when an inspector asks why a result that failed in the legacy system passes in the new one.

What the guidance actually says

EU GMP Annex 11, clause 4.8, is one sentence and it is the whole problem in miniature: if data are transferred to another data format or system, validation should include checks that data are not altered in value and/or meaning during this migration process.1 The phrase “and/or meaning” is doing enormous work. Value is testable with a comparison. Meaning is not, at least not by comparing two fields.

The draft revision of Annex 11 published for consultation in July 2025 promotes migration to its own clause. Clause 10.3 states that where an ad hoc process requires that critical data or a whole database be migrated from one system to another, for example when moving data from a retired to a new system, this should be based on a validated process, and it should consider the constraints on the sending and receiving side.2 That last phrase is the one to sit with. Constraints on the receiving side are exactly what cause silent semantic change: a field that is two decimals wide, a code list that does not contain a legacy value, a date column that carries no time zone.

The MHRA’s data integrity guidance says it plainly in section 6.8. Data migration is the process of moving stored data from one durable storage location to another, and it may include changing the format of data, but not the content or meaning. It then adds the sentence that should open every migration kickoff meeting: the challenges of migrating data are often underestimated, particularly regarding maintaining the full meaning of the migrated records.3 The WHO guideline on data integrity carries the same wording in its section 9.6.4 When two regulators independently publish the same sentence, it is because inspectors keep finding the same problem.

PIC/S PI 041-1, written for inspectors, sets the procedural expectation: migration of data from one system to another should be performed in a controlled manner, in accordance with documented protocols, and should include appropriate verification of the complete migration of data. It also flags the readability problem that outlives the project, requiring that where conversion to a new format is not possible, the old software be maintained so archived data can still be read during an investigation.5

The EMA’s guideline on computerised systems and electronic data in clinical trials is the most specific of the set. Section 6.10 states that validation of the data migration process should have no less focus than the validation of the system itself, that the intended procedure should first be validated with mock data, that verification methods may include checksum, case counts and quality control of records, and that mapping from the old system onto the new system should be retained.6 It also requires that data, contextual information and the audit trail not be separated, and that a detailed explanation be provided if no method exists to migrate the audit trail alongside the data.

5 to 19 Pages in EU GMP Annex 11, from the 2011 version to the July 2025 draft, which gives data migration its own clause for the first time [2]
117 ICD-10 terms re-mapped to a different MedDRA lowest level term in a single mapping release, plus 249 newly mapped terms [7]
25 years Minimum period the clinical trial master file must be archived and remain complete and legible, so migrated records outlive the project by decades [8]

Put together, the guidance tells you the required outcome with unusual clarity and tells you almost nothing about how to test for it. That gap is where migration projects live. The rest of this article is a test design that closes it.

A Four-Tier Test Design for Migration Regression

The design below is layered on purpose. Each tier answers a different question, each is cheaper to run than the one above it, and each is meaningless unless the tier below it passed. Teams that skip straight to a semantic review of a sample are trying to catch subtle problems in a data set they have not yet proved is complete. Teams that stop at counts and checksums have proved completeness and nothing else.

0

Baseline profiling of the source, before anything moves

Produce and freeze a controlled statistical portrait of the legacy data: counts by entity and by partition, null rates per column, the full distinct value inventory for every coded field, numeric ranges and distributions, date distributions, orphan and duplicate counts. This is the only reference you will have once the legacy system is read-only, and it is also where you discover the legacy defects you will have to make a decision about.

1

Completeness and mechanical fidelity

Did every record that should have moved arrive, and did each one arrive byte-for-byte where a byte-for-byte comparison is meaningful? Record counts by partition, record-level hashing over a canonical serialization, binary content hashes for attachments, and reconciliation of exclusions against a documented scope rule.

2

Structural and referential conformance

Does the migrated data satisfy the target model without silent repair? Referential integrity across parent and child records, mandatory field population, conformance of every value to the target’s controlled vocabularies, uniqueness constraints, field length and character set fidelity, and audit trail linkage to the records they describe.

3

Semantic equivalence

Does a record still mean what it meant? Code list crosswalk testing in both directions, independent recomputation of derived values, boundary testing of dates and time zones, precision and rounding comparison at specification limits, distribution comparison rather than totals, and expert review of a stratified golden record set.

4

Functional regression in the business process

Does the migrated data behave correctly inside the workflows that use it? Run the real processes end to end against migrated records: release a batch, resolve an out-of-specification result, submit a case, run a period close, produce a listing for an inspector. This is where defects that no data comparison can find will appear.

TierQuestion it answersTypical techniqueWhat it cannot detect
0. BaselineWhat did the data look like before we touched it?Data profiling, distinct value inventory, distribution capture, hash of frozen extractNothing yet. It is the reference, not a test.
1. CompletenessDid everything arrive?Counts by partition, record hashing, file hashing, exclusion reconciliationAny change in interpretation. Identical counts survive every semantic failure.
2. ConformanceDoes it fit the new model without silent repair?Referential checks, vocabulary conformance, null and length checks, audit trail linkageValues that are structurally valid in the target but semantically wrong.
3. SemanticsDoes it still mean the same thing?Crosswalk testing, independent recomputation, boundary and precision testing, distribution comparison, expert reviewProcess behavior. A correct value can still break the workflow that consumes it.
4. FunctionalDoes it work in the process that uses it?End-to-end business scenario testing on migrated records, including exception pathsProblems in data outside the scenarios you chose to run.

Where effort usually goes, and where risk usually sits. Tiers 1 and 2 attract most of the effort because they automate cleanly and produce satisfying evidence: big tables of matching numbers. Tiers 3 and 4 are where the damaging failures live, and they need people who understand what the data is for. If your migration test plan allocates ninety percent of its execution hours to counts and conformance, it is testing the part of the problem that was least likely to go wrong.

Tiers 0 to 2: The Cheap Checks, Done Properly

Tier 0: profile before you move, and freeze what you find

The single most common gap in migration testing is the absence of a controlled baseline. Once the legacy system is read-only or decommissioned, any question about what the data looked like beforehand becomes unanswerable, and every dispute defaults to the new system being right. Produce the baseline as a formal, approved artifact before extraction begins.

A workable baseline includes record counts for every entity broken down by the partitions that matter to the business, not just a grand total. For a LIMS that means counts by year, by site, by test method, and by result status. For a safety database it means counts by case type, by seriousness, by reporting year, and by submission status. It also includes null and blank rates for every column, the complete distinct value inventory for every coded field with a count per value, minimum, maximum, mean and standard deviation for numeric fields, the distribution of dates by month, and counts of orphans, duplicates and records in states the current process no longer produces.

Two practical notes. First, hash the frozen extract and record the hash in the plan, so that later disputes about which extract was tested have a factual answer. Second, treat the profiling output as your defect discovery exercise. Almost everything you will later argue about under the heading of legacy data quality is visible in a distinct value inventory and a null rate table.

Tier 1: counts that mean something

A total record count is the weakest possible completeness check because compensating errors cancel. A load that drops 4,000 records from one site and duplicates 4,000 records at another produces a perfect total. Reconcile by partition, and choose partitions that would separate the plausible failure modes: by source table, by year, by site or facility, by status, by owning function.

Above counts, use record-level hashing. Serialize each record’s in-scope fields into a canonical string with a fixed field order, fixed formatting rules and a defined treatment of nulls, hash it on both sides, and compare hash sets. This finds moved, altered and duplicated records rather than just counting them, and it scales to full population testing rather than sampling. Where the migration deliberately transforms values, hash the untransformed fields and handle the transformed ones in Tier 3, rather than abandoning hashing altogether.

Attachments and binary content need their own treatment. Comparing file counts and file sizes is not sufficient. Hash the file content. Migrations of document-heavy systems routinely produce files that are present, correctly sized and subtly corrupted, usually by an encoding or line-ending transformation applied by a transfer tool that assumed everything was text.

Finally, reconcile exclusions explicitly. Every record in the source that did not arrive in the target must fall under a documented scope rule, and the count of records covered by each rule must be produced and reviewed. “The difference is the records we did not migrate” is a statement, not evidence.

Tier 2: conformance without silent repair

The target system is not a passive container. It has constraints, defaults and coercion rules, and it will apply them. Tier 2 exists to find the places where the target quietly made the data fit.

  • Field length and character set. A legacy comment field of 4,000 characters loading into a 2,000 character target field truncates. Test the maximum observed length per field in the source against the target definition, and count records where the migrated length differs from the source length.
  • Controlled vocabulary conformance. Every distinct value in every coded source field must land on a defined target value. Compare the Tier 0 distinct value inventory to the target’s configured lists and produce an explicit unmatched value report. Values that got defaulted to “Other” or “Unknown” are semantic failures dressed as successful loads.
  • Referential integrity. Parent and child records, result to sample, case to follow-up, document to milestone, batch to order. Count orphans on both sides. An orphan in the target that did not exist in the source means the parent was dropped or its key changed.
  • Mandatory field population. Compare null rates per column against the Tier 0 baseline. A field that was thirty percent null in the source and zero percent null in the target has been populated with something, and you need to know what and why.
  • Audit trail linkage. Confirm that migrated audit trail entries still resolve to the records they describe, that user identifiers resolve to real identities, and that the chronology is preserved. The EMA is explicit that data, contextual information and the audit trail should not be separated, and expects a detailed explanation where migration of the audit trail alongside the data has not been achieved.6

Watch the load log, not just the load status. The most common way a migration defect enters production is a load that reports success with a non-zero warning count. Truncations, coerced data types, defaulted values and rejected rows are frequently logged as warnings rather than errors. Make “zero unexplained warnings” an explicit acceptance criterion, require the warning log as an evidence attachment, and require a disposition for every warning class.

Tier 3: Testing Whether the Data Still Means the Same Thing

This is the tier that earns the project. It is also the tier that cannot be delegated entirely to a script, because the question it asks is a subject matter question. Four failure classes account for most of the damage.

Failure class 1: a code list that was remapped

Coded values are where meaning is most concentrated and most fragile. A legacy LIMS with forty test result qualifiers consolidating into a target list of fifteen. A quality event system whose eight deviation classifications become five. A safety database migrating across a MedDRA version boundary, where terms have been added, merged, renamed or moved in the hierarchy.

The scale of terminology movement is not hypothetical. The MedDRA MSSO considered 1,380 change requests for Version 29.0, and the accompanying ICD-10 to MedDRA mapping release re-mapped 117 ICD-10 terms to a better matching lowest level term or attribute and newly mapped 249 terms that had previously been out of scope or unmapped.7 Any of those changes, applied to a historical case series during migration, changes the frequency distribution of events and therefore changes what the historical data says.

The test. Build the crosswalk as a formal, approved artifact, not as logic buried in a transformation script, and then test it in both directions. Forward: for every source value, confirm the target value is the approved mapping and count the records affected. Reverse: for every target value, list every source value that now lands on it and have a subject matter expert confirm that the collapse is acceptable. Then run the population comparison: the count of records per source code against the count per target code, with a pre-agreed threshold above which a change requires a written explanation. Finally, and this is the step teams skip, test the codes that should not have moved. Confirm that values with a one-to-one mapping have identical populations before and after.

Failure class 2: a derived field recomputed under new logic

Derived fields are the second concentration of meaning: percent of label claim, yield, a stability slope, days on study, inventory valuation, batch cycle time, a calculated due date for a regulatory submission. In the legacy system these values were computed by legacy code under legacy rules. In the target they will be computed by target code under target rules, and the two are rarely identical in every edge case.

The tempting approach is to compare the legacy stored value to the target stored value and investigate the differences. That is a two-way comparison, and when it disagrees you cannot tell whether the target is wrong or the legacy value was wrong all along.

The test. Recompute independently and compare three ways. Write a calculation specification from the approved requirement, not from a reading of the legacy source code, and implement it separately from both systems. Then compare the independent value to the legacy stored value and to the target stored value. Three outcomes, three meanings: all three agree and the field is fine; independent and target agree while legacy differs, which means you have found a legacy calculation defect and now have a decision to make; independent and legacy agree while target differs, which means the target configuration is wrong and must be fixed before cutover. Run this over the full population where you can, and over a stratified sample weighted toward boundary conditions where you cannot.

Failure class 3: a date that moved because of a time zone assumption

Date and time defects are the quietest failures in this entire category, because a timestamp that has shifted by a few hours still looks like a plausible timestamp. Legacy systems frequently store local server time with no offset recorded. Modern targets frequently store an instant in UTC and render it in the viewer’s time zone. Migrating the first into the second without an explicit, documented assumption about the source time zone moves every timestamp in the database.

Small shifts have real consequences because so many regulated processes are defined at date boundaries. A sample collected at 23:40 on the 30th becomes the 1st of the following month, and moves into a different month-end reporting period. A stability pull scheduled at a fixed interval shifts a day and appears out of window. An adverse event awareness date moves and changes an expedited reporting clock. A batch record step appears to occur before the step that preceded it.

The test. Build an explicit boundary case set and run it before you run anything else: records within one hour either side of midnight, records on daylight saving transition dates in every jurisdiction represented in the data, February 29 in leap years, December 31 and January 1, and records with a null or partial date. Then run two population tests. First, count every record whose date component changed at all between source and target, which should be zero unless a shift was intended and approved. Second, test event ordering rather than event timestamps: for every pair of related events with a required sequence, confirm the sequence is preserved. Ordering tests catch shifts that a timestamp comparison forgives because both values moved.

Failure class 4: a precision change that crosses a specification limit

Numeric precision is a semantic property, not a formatting preference. A result stored to four decimal places in a legacy LIMS and loaded into a target field defined to two decimal places is rounded on the way in. Most of the time nobody notices. The exception is the case that matters: a result of 99.4951 against a lower specification limit of 99.5. In the legacy system that result is below the limit. After rounding it is 99.50 and it passes. The migration has silently changed a conformance decision on a historical batch.

The same class covers unit of measure conversions, where a conversion factor applied at migration introduces a rounding difference at each conversion, and scientific notation handling, where very small values collapse to zero in a target with a fixed decimal field.

The test. Compare numeric fields at full source precision, not at display precision, and count every record where the migrated value differs from the source value by any amount. Then run the targeted test that matters most: for every numeric field with an associated limit, select all records whose value falls within one rounding unit of that limit and review every one of them individually. This is a small, high-value set and it is where the entire risk of the precision class is concentrated. Where the target genuinely cannot hold the source precision, that is a design decision requiring an impact assessment and approval, not a defect to be quietly accepted at load time.

Four techniques that make the semantic tier work

TECHNIQUE 1

Independent recomputation

Compute derived values a third time from an approved specification, outside both systems. Turns an ambiguous two-way disagreement into a diagnosis, and separates a migration defect from a legacy calculation defect.

TECHNIQUE 2

Distribution comparison, not totals

Compare per-value and per-bucket distributions between baseline and target. Totals are preserved by compensating errors; distributions are not. A histogram that changes shape is a defect signal even when every summary statistic agrees.

TECHNIQUE 3

A golden record set

A stratified set of records selected before extraction and reviewed field by field by someone who uses the data professionally. Include the difficult ones: amended results, cancelled batches, cases with many follow-ups, records with legacy defects, the oldest records in the system.

TECHNIQUE 4

Round-trip reconstruction

Regenerate a legacy output from migrated data and compare it to the original: a certificate of analysis, a case narrative, a batch record summary, a submission listing. If the reconstructed document matches the archived one, the meaning survived the move in a way no field comparison demonstrates.

Round-trip reconstruction deserves particular emphasis because it is the closest thing to a direct test of meaning. Field comparisons test values in isolation. A regenerated certificate of analysis tests values, their relationships, their units, their precision, their specification context and their formatting all at once, against a document that was reviewed and approved by a human being years ago. When a reconstructed output differs from the archived original, you have found something real, and you have found it in a form that a quality reviewer can immediately understand.

The Legacy Defect Question You Have to Answer First

A perfectly executed migration reproduces existing bad data perfectly. This is not a flaw in the method. It is the method working. Every test in Tiers 1 through 3 is a like-for-like comparison, and like-for-like comparison is the only way to demonstrate that meaning was preserved. A migration that also corrects data will fail its own tests, by design.

This creates a decision that has to be made before the test protocol is written, not discovered during user acceptance testing when someone notices that fourteen thousand records carry a supplier name that has been wrong since 2014.

Three options, and what each one obliges you to do

OptionWhat it meansWhat it obliges you to doWhen it fits
Migrate as is, remediate afterward The defect moves with the data. Correction happens later in the target under normal change control and data correction procedures. Record the defect in a known-defect register before cutover, with an impact assessment and a remediation owner and date. Keep the like-for-like test design intact. The default. Almost always correct for GxP records where the original value has evidentiary weight.
Correct in flight The transformation applies a documented correction rule during migration. Pre-specify every affected record class and count. Every planned difference must be individually justified, counted, and reconciled in the migration report. Preserve the original value and the reason for correction. Rarely, and only where the defect is mechanical, fully enumerable in advance, and the correction rule is deterministic.
Exclude and archive The affected records do not move. They remain retrievable from a validated archive or a maintained read-only legacy instance. Document the exclusion rule, count the excluded population, and prove retrievability for the full retention period. Confirm no active process depends on the excluded records. Obsolete record classes, superseded structures, data outside the target system’s scope.

The rule that keeps this honest is simple. If you cannot enumerate the affected records in advance, you cannot correct in flight. A correction rule that will be applied to “any record where the value looks wrong” is not a rule, it is an unlogged edit to a GxP record set performed by a project team under schedule pressure. Correcting in flight is defensible only when the affected population is identified, counted and approved before execution, so that the difference between source and target is a known quantity rather than a residual.

There is a second obligation that teams underweight. Under 21 CFR Part 11 and the ALCOA+ principles that sit behind it, correcting a record does not remove the original.9 An in-flight correction must preserve the pre-correction value and the reason for the change, in a form that is retrievable for the retention period. If the target system has no field to hold the original value, that is an argument against correcting in flight, not an argument for losing it.

A decision rule that holds up in an inspection. Default to migrating as is. Move to in-flight correction only when all four of these are true: the defect is mechanical rather than judgmental, the affected records can be counted before execution, the correction rule is deterministic and written down, and the original value survives the correction in a retrievable form. If any one of those fails, migrate as is and remediate under change control in the new system, where the correction gets its own audit trail, its own approval and its own reason code.

The known-defect register

Everything Tier 0 profiling reveals and the project decides not to fix belongs on a register that survives the project. For each entry: what the defect is, how many records it affects, when it originated if known, an assessment of GxP impact, whether it triggers a deviation or a CAPA in its own right, the decision taken, who approved that decision, and the remediation plan if there is one.

The register does two things. It converts an oversight into a documented decision, which is the difference between a finding and a conversation. It also gives the business a realistic picture of what it is inheriting, which matters because the most common post-migration complaint is not that the data changed. It is that the data was always this bad and nobody said so.

Parallel Run, Freeze, and the Reconciliation Report

Be honest about what a parallel run buys

A parallel run tests the new system against live inputs with real variability, real users and real exceptions. It catches interface defects, configuration gaps, workflow problems and training gaps that no amount of static data testing will reveal. It is genuinely valuable for those things.

It does not validate historical data. This distinction gets blurred constantly. Running two systems side by side for six weeks tells you nothing about whether a result from 2011 still means what it meant, because no 2011 transaction is being processed. Historical accuracy comes from the tiered test design described above. A parallel run that is being used as a substitute for semantic testing is an expensive way to feel confident about the wrong thing.

It also carries a burden that is often understated: running in parallel usually means entering data twice, and double entry is itself a data integrity risk. Where a parallel run is justified, prefer single entry with automated replication into the second system over dual manual entry, and where dual entry is unavoidable, treat the reconciliation between the two entries as a test in its own right.

How long to run: define it by cycles, not by calendar

“Four weeks” is a project management answer. The useful answer is that the parallel period should cover at least one complete instance of every high-risk business cycle the system participates in, plus the exception paths within those cycles.

  • ERP: a full period close, including the reconciliation and reporting steps that only happen at month end, and at least one inventory revaluation or physical count if those are in scope.
  • LIMS: a complete sample lifecycle from login to authorization, at least one out-of-specification investigation from initiation to closure, and one stability pull with its scheduled time point.
  • MES: one batch from dispensing through to record review and release, including at least one deliberate exception and its resolution.
  • Safety database: an expedited case from intake through assessment, coding, distribution and regulatory submission, plus a follow-up on an existing case and one periodic report cycle if the timing allows.
  • eTMF: a monitoring visit and its full filing cycle, a milestone completeness check, and at least one quality review pass against the expected document list.
  • Clinical data warehouse: a full refresh and re-derivation cycle with reproduction of a previously issued output.

Where a cycle is quarterly or annual and cannot be covered inside a reasonable parallel window, say so explicitly and decide what compensating control replaces it: enhanced first-cycle review after cutover, a simulated cycle using historical inputs, or a rollback provision that stays available until the first real cycle completes. What matters is that the gap is a documented decision with a named owner, not a silent omission.

The freeze

A cutover freeze is a control, and like any control it needs to be enforced technically rather than announced by email. Define the freeze precisely: the exact date and time at which the legacy system stops accepting new records and edits, how read-only status is enforced in the application and at the database level, who holds the authority to break the freeze and under what circumstances, and what happens to records that must be created during the freeze window because the business cannot stop.

That last point is the one that generates defects. Every freeze produces a small set of records created on paper or in a spreadsheet because a sample arrived or a case came in. Plan the catch-up load for those records before the freeze starts, define who reconciles them, and treat the catch-up load as a scoped migration with its own verification rather than as manual data entry performed by whoever is available at the weekend.

What the reconciliation report has to show

The reconciliation report is the document an inspector will ask for, and it is the document that most often disappoints. It should be a standalone record that a reviewer with no project context can follow. At minimum it contains:

  • Scope and cut-off. Exactly which data was in scope, the extraction timestamp, and the hash of the frozen source extract.
  • Baseline against target, by partition. Source counts and target counts for every entity and partition, with variance and explanation for every non-zero variance.
  • Planned differences. Every difference that was expected, with the record count, the approved justification it traces to, and confirmation that the actual count matched the pre-approved count.
  • Unplanned differences. Every difference that was not expected, with root cause, GxP impact assessment, disposition, and where corrected, evidence of retest.
  • Tier-by-tier test results. The acceptance criterion for each test, the result, and a pointer to the executed evidence.
  • Open defects and residual risk. What remains unresolved at the point of signature, its impact, the mitigation in place, and the owner and date for closure.
  • Legacy system disposition. The date and time the legacy system became read-only, its retention plan, and confirmation that archived data remains readable for the retention period.

Who signs, and why it should be more than two people

Migration reconciliation needs signatures from people with different accountabilities, and treating them as interchangeable is how reconciliation reports become paperwork.

IT or the migration lead signs that the transfer was executed as specified, that the scripts and their versions are recorded, and that the results presented are the results obtained. The system owner signs that the system is fit to operate with this data. The data owner, meaning the business function that owns the meaning of the data, signs that the migrated data still supports the decisions it is used to make. That is a different assertion and frequently a different person. QA signs that the process was executed under an approved plan with pre-approved acceptance criteria, that deviations were handled, and that the residual risk is acceptable. Where the data is clinical or safety data, the relevant functional head signs as well, because responsibility for those records does not transfer to a project.

The failure pattern to design against. The most common weakness in migration evidence is a reconciliation report prepared by the project team, reviewed by the project team, and approved by a quality reviewer who has never worked with the data in question. Nobody in that chain would recognize a wrong number. Require at least one signature from someone whose day job involves reading this data, and give that person the golden record set and the distribution comparisons rather than the summary counts.

What Changes by System: LIMS, ERP, MES, eTMF, Safety, Warehouse

The tiered design is the same everywhere. What changes is where meaning is concentrated, and therefore which semantic tests are worth the effort.

SystemWhere meaning is concentratedThe semantic test that earns its keepReconciliation anchor
LIMS Specifications and their effective versions, result precision, test method versions, the link between a result and the specification in force at the time Regenerate a historical certificate of analysis from migrated data and compare it to the approved original Results per method per year, and pass/fail counts per specification
ERP Master data: material master, batch and lot identity, unit of measure conversion factors, valuation method, bill of materials effectivity dates Recompute inventory valuation and open commitment balances independently and compare three ways Trial balance and inventory quantity by material by location at the cut-off instant
MES Recipe and parameter versions, equipment identifiers, batch genealogy, the link between an exception and the process step where it occurred Rebuild a full batch genealogy for a completed batch and compare it to the reviewed batch record Batches per product per period, and exception counts per exception type
eTMF Metadata, which in a TMF is the record: classification, milestone and artifact assignment, version, status, expected document lists Compare the classification distribution against baseline, not just the document count, and re-run completeness against the expected document list Documents per artifact per study, and completeness percentage per milestone
Safety database Coding dictionary versions, case version history and follow-ups, submission history and clocks, narrative text encoding, E2B field mappings Full crosswalk testing of coded terms in both directions, plus regeneration of a submitted E2B message from migrated data Cases by seriousness, by year, by submission status; and per-term case counts
Clinical data warehouse Derivation logic, standards mappings, subject and visit and event structure, analysis dataset definitions Re-derive an analysis dataset and reproduce a previously issued output exactly Subjects, visits and observations per study per domain

Laboratory and manufacturing systems

In a LIMS the most frequently damaged relationship is between a result and the specification that applied when it was generated. Specifications change. Methods are revised. A result migrated with the current specification attached rather than the contemporaneous one is not just wrong, it is quietly wrong in a way that only becomes visible during an investigation, when a historical result is reassessed against a limit that did not exist at the time. Test this directly: for a sample of results spanning every specification version change, confirm that the migrated result resolves to the specification version in force on its own result date.

In an MES the equivalent relationship is between an exception and the step at which it occurred, and between a batch and its genealogy. Batch genealogy is a graph, and graphs survive migration badly when parent and child keys are regenerated. Rebuild a complete genealogy for at least one closed batch per product family and compare it to the reviewed batch record.

ERP

ERP migrations are dominated by master data, because master data is where the meaning of every transaction is defined. Unit of measure conversion factors, valuation method, batch and lot identity, and effectivity dates on bills of materials are all fields where a small change alters the interpretation of every record that references them. A great many of these projects are running now against platform support horizons, which compresses schedules and increases the temptation to treat master data cleanup and migration as one activity. They are two activities and they need separate approval.

Clinical, TMF and safety systems

In an eTMF the metadata is the record. Two systems can hold identical documents and disagree completely about what the trial master file contains, because completeness in a TMF is assessed against classification and expected document lists rather than against a file count. Compare the distribution of documents across artifacts and milestones, not the total. Remember that the retention obligation is long: sponsors and investigators archive TMF content for at least 25 years after the end of the trial, and the media used must keep the content complete and legible throughout.8

Safety database migrations concentrate almost every failure class in this article into one project. Coding dictionary version changes are code list remapping. Regulatory clocks are date arithmetic. Case versions and follow-ups are referential structure. Submission history is evidence that must survive intact. The EMA’s clinical systems guideline uses the migration of individual safety reports from one safety database to another as its worked example of migration, and requires that the mapping from the old system onto the new be retained.6 The decision about whether to re-code historical cases to the current dictionary version, or preserve them at their original version, is a scientific and regulatory decision with signal detection consequences. It belongs in the migration plan, approved by pharmacovigilance and regulatory affairs, not in a transformation script.

For clinical data warehouses and analysis environments, the definitive test is reproduction. Re-derive an analysis dataset from migrated source data and reproduce a previously issued output exactly. If the numbers in a table that has already been submitted cannot be regenerated from the migrated data, the migration has changed the data regardless of what the field comparisons say. ICH E6(R3) sets clear expectations for data governance across the data life cycle, including traceability from source through to reported results, and reproduction is the most direct demonstration that traceability survived.10

The Evidence QA Will Actually Ask For

A passing test suite is not a validation deliverable. It becomes one when it is traceable to requirements, executed against acceptance criteria that were approved before anyone saw a result, and summarized in a report that a reviewer independent of the project has read and approved. Six artifacts carry that weight.

ARTIFACT 1

An approved migration plan

Approved before execution. Scope, in-scope and out-of-scope data, the tier structure, roles and responsibilities, the freeze and cutover approach, the rollback provision, and the legacy defect decision with its rationale.

ARTIFACT 2

Requirements the tests trace to

Including an explicit requirement that data are not altered in value or meaning, stated as a testable requirement rather than assumed. If it is not a requirement, no test traces to it and nothing obliges anyone to check.

ARTIFACT 3

A traceability matrix

Requirement to test case to executed result to disposition. This is what converts a set of scripts into validation evidence, and it is the first thing an auditor follows backward from a number they do not believe.

ARTIFACT 4

Pre-approved acceptance criteria

Numeric where possible. Any non-zero tolerance needs a written justification. “Results reviewed and found acceptable” is not an acceptance criterion, and neither is “100% match” without a definition of what match means field by field.

ARTIFACT 5

Executed evidence with attribution

Who ran what, when, using which script version, against which extract, with which parameters. The comparison scripts are now GxP software: version them, control them, and qualify them proportionally to the risk of the decision they support.

ARTIFACT 6

A reviewed summary report

Deviations, dispositions, residual risk, and an explicit conclusion on fitness for use, approved by QA and by the data owner. The conclusion should state what was demonstrated, not that the project completed.

On the test scripts themselves

The comparison scripts and profiling queries that produce your migration evidence are software producing GxP records, and they need controls proportional to what they support. That does not mean a full validation package for a query that counts rows. The FDA’s computer software assurance approach, finalized in September 2025, is explicitly built on applying assurance effort in proportion to risk, with lighter methods where the risk is low and greater rigor where a failure would affect product quality or patient safety.11 The practical consequence is not that low-risk checks need no evidence. It is that the record of what was run, on what, and with what result still has to exist and still has to be attributable, whatever method produced it.

Two specific expectations follow. Version-control the scripts and record the version used for each execution, because a comparison rerun after a fix must be demonstrably the same comparison. And record the input data set identity, ideally by hash, because a comparison that passed against a different extract than the one loaded to production proves nothing.

The findings that come up again and again

  • Acceptance criteria written after the results were known. Visible in document version history and in criteria that fit the observed data suspiciously well.
  • “100% match” with no definition of match. Which fields, compared how, with what treatment of nulls, whitespace, case and precision.
  • Sampling with no rationale. Thirty records because thirty is a familiar number. Sample size and stratification need a documented basis tied to risk and population structure, and the sample should deliberately include the difficult records rather than the convenient ones.
  • Screenshots without identifiers. Evidence that cannot be traced back to a specific record, run, script version and reviewer is decoration.
  • Defects closed as “as designed” with no impact assessment. If the behavior was designed, the design decision needs an assessment of its effect on the data, not a status change.
  • No evidence that the reviewer understood the data. Signatures from the project team only, with no participation from the function that uses the records.
  • Audit trail migration handled as a footnote. Regulators expect data, context and audit trail to stay together, and expect a written explanation where that could not be achieved.

One test of whether your evidence package is ready. Hand the reconciliation report and the traceability matrix to a colleague who was not on the project and ask them to answer one question from the documents alone: how do you know that a result recorded in 2013 still means the same thing today as it did then? If they can trace that question to a requirement, to a test, to an acceptance criterion, to an executed result, and to an approval by someone qualified to judge it, the package is ready. If the answer is a count of records, it is not.

The evidence outlives the project

One final point that gets lost in cutover week. Migration evidence has to be retained for as long as the migrated data is retained, which in clinical and safety records means decades. The reconciliation report, the approved crosswalks, the mapping from old system to new, the known-defect register and the acceptance criteria are all part of the record of how the data in your current system came to say what it says. Store them where they will be found in fifteen years, under the same retention rules as the data itself, and not in a project folder that gets archived to a share nobody can name.

Conclusion

Migration testing in regulated environments has drifted toward the checks that are easy to automate and easy to present, and away from the ones that address the actual risk. Counts, checksums and a small side-by-side review produce clean evidence and prove transport. The failures that damage a company are semantic: a code list that collapsed, a derived field recomputed under different rules, a timestamp that moved across a date boundary, a rounding change that reversed a conformance decision on a historical batch. Every one of those survives a well-run Tier 1 test perfectly intact. The regulators have been saying this for years in almost identical language, from Annex 11’s requirement that data not be altered in value or meaning, to the MHRA and WHO warning that the difficulty of preserving the full meaning of migrated records is routinely underestimated. What they have not provided, and what a project actually needs, is a test design.

The design in this article is deliberately layered so that effort follows risk: profile before you move, prove completeness and conformance cheaply and completely, then spend your expert time on semantic equivalence and on the business processes that consume the data. Decide the legacy defect question before the protocol is written rather than during user acceptance testing. Scope the parallel run by business cycles rather than by calendar weeks, and be clear that it validates process, not history. Then build the evidence package so that a reviewer who was not there can follow a claim about a fifteen-year-old record back to a requirement, a test, a criterion and a qualified approval.

Sakara Digital works with pharma and biotech organizations planning and validating migrations of LIMS, ERP, MES, eTMF, safety and clinical data platforms, where the question is not whether the records moved but whether they still support the decisions they were used to make. If you are scoping a migration and want an independent view on the test design, the legacy data decisions, or the evidence your quality unit will expect, we are happy to have that conversation.

For Further Reading