Data Migration Testing with Anonymized Data

Key Takeaways:

Anonymize, and keep the structure. Test data must carry no PII and no PCI data, and it still has to behave like the real dataset. Both at once is the actual requirement.
Random replacement is the failure mode. Swapping values blindly destroys formats, distributions and relationships. The test then passes on a dataset that does not exist, and the live run meets the one that does.
Anonymization needs a rule per field. Format-preserving where there is structure, distribution-preserving where gaps carry meaning. The EU's data protection authorities made the same point in their 2014 opinion on anonymisation techniques: every technique trades utility against protection, and the trade has to be chosen deliberately.
The safe path has to be the easy path. When anonymization is tooling instead of a request, engineers build and test transformations without ever touching personal or payment data. When it is manual and slow, workarounds appear, and workarounds do not meet security requirements.
Validation still happens in the target system. An import that completes is not proof. Proof is a business-level comparison against the source.

Anonymization is a fixed part of how we test data migrations: test data must carry no PII and no PCI data, and it still has to keep the structure of the real dataset, so that anyone can work with it and build transformations against it. The failure mode is not anonymizing too much or too little. It is anonymizing randomly, replacing values in a way that destroys formats, distributions and relationships, so the test passes and the live run fails.

This month we ended up where every larger migration project eventually ends up. The first test run completed cleanly, and we still had to request additional data. Not because too little had been delivered. What was delivered had been anonymized by random replacement, and that had removed more than the personal data.

Why anonymization is the right call

The reasons are solid. A supplier should not see personal data without cause. Payment data has its own rules entirely. A test environment rarely has the protection level of production. And with records spanning many years, nobody can say with certainty which consents are on file. Anonymized test data is not a workaround for these constraints. It is the correct answer to them.

It just must not be treated like a switch: data in, scrambled data out. Anonymization is a business decision per field. Handled as a purely technical find-and-replace, it produces a dataset that looks like the real one and does not behave like it.

What random replacement destroys

Formats. A customer number stored as K-2004-0813 becomes X-1111-1111. The migration never notices that in three hundred cases the year segment is missing, because in the scrambled set every number is complete.

Lengths. Free-text fields get replaced with short placeholders. In the real dataset those fields contain paragraphs, and the target system has a limit.

Distributions. If 4 % of records have no date of birth, that is information. Generating random dates for 100 % of records deletes it.

Relationships. Two tables anonymized independently lose their link. The foreign key then points, formally correctly, at nothing.

Special cases. The record that has been in the system since 2011, in a character encoding that existed then and does not now. That one gets silently skipped or repaired during scrambling, and reappears in the live run.

None of this is exotic. When Tadhg Nagle, Tom Redman and David Sammon had managers measure their own data, 47 % of newly created records contained at least one critical error. Those errors are the reality a migration has to survive. An anonymization step that cleans them away is testing a dataset that does not exist.

How we anonymize instead

Before anonymizing, we walk the field list once and decide for each field which property has to survive:

Format-preserving: customer numbers, contract numbers, card-adjacent references, anything with structure. Replace the content, keep the pattern.
Length-preserving: free text. The placeholder is as long as the original.
Distribution-preserving: anything where a gap carries meaning. If a value is missing in the source, it is missing in the copy.
Relationship-preserving: key fields are replaced using the same mapping across all tables, not re-randomized per table.
Untouched: everything that is neither personal nor payment-related. Status codes, tariffs, timestamps, quantities. Scrambling these destroys, without need, the very things you need for debugging.

The point of all this is not compliance alone. It is that the result stays workable: a migration engineer can build transformations, run them, and debug the failures on this dataset, without a single record of PII or PCI data leaving the source. The anonymized set is not a degraded copy. It is the working copy.

That sounds like effort. It is a list you build once and reuse. The effort occurs either way. The alternative is to spread it across several re-request rounds, later, under deadline pressure.

The safe path has to be the easy path

Hold data to the highest standard by default, and then make that standard cheap to follow. Those two belong together. If the anonymization process is manual, slow or someone else's job, the workarounds write themselves: a production dump on a developer laptop, a "temporary" copy in a test environment, a spreadsheet sent around by mail. None of those hold up against any security requirement, and none of them were malicious. They were just easier than the official path.

So the anonymization step has to be a process, not a favor: one run that produces the working copy, repeatable whenever the source has moved on. That is the derisking. And it integrates seamlessly into the live process, because the pipeline that consumed the anonymized set is the same pipeline that runs the real migration. The rehearsal happens on the safe copy, the performance on the real data, on identical rails.

The re-request cycle is the actual problem

Work out what a single re-request costs. We write down which fields are missing. On the customer side someone has to adjust the export, rarely the person who built the first one. The new export needs an approval. Then it reaches us, we load it, and we find that one more field is affected.

Two weeks per round is realistic, three rounds not unusual. That is a lost month in a project where the go-live date is already fixed. And it is a month in which trust erodes on both sides although nobody did anything wrong.

Which is why the field list at the start of the project is the cheapest investment available in a migration.

Validation belongs in the target system

An import that completes without an error message only tells you the target accepted the data. It says nothing about whether the data is correct.

What we check instead:

Record counts per entity, source against target.
Sums: wherever there are amounts, they get summed and compared. A difference of €1.20 is a signal, not a rounding artifact.
Business-level sample: twenty records, selected for being awkward rather than at random, reviewed by someone who knows the business.
Negative check: records that were not supposed to migrate must not appear in the target.

Only when those four are clean has a test run passed. And only then is the dress rehearsal actually a dress rehearsal.

Anonymize, keep the structure, and give the team a dataset it can genuinely work with. If you are planning a migration and want to know what that looks like in practice, this is the part of the work we do every week.

Sources

Article 29 Data Protection Working Party: Opinion 05/2014 on Anonymisation Techniques (WP216, April 2014)
Nagle, Redman, Sammon: Only 3% of Companies' Data Meets Basic Quality Standards, Harvard Business Review, September 2017

Back to overview

Data migration testing with anonymized data: keep the structure