How to create safe test data from production data
For developers, QA and data teams
Real data makes the best test data: real formats, real edge cases, real volumes. It is also real people. The usual answer is a copy with the personal data replaced by realistic stand-ins, so the system behaves the same and nobody is exposed.
Why not just copy production?
Under the GDPR, personal data collected to serve customers should not quietly become test data: data should be limited to what each purpose needs (Article 5), and systems should protect it by design (Article 25). Test environments also tend to have more people with access and weaker controls than production.
What good test data needs
- Valid formats and checksums. A fake IBAN, card number or national ID must pass the same validation as a real one, or your application rejects it.
- Consistency. The same customer should get the same fake name and ID in every table and file, or joins and foreign keys break.
- Realistic shape. Lengths, character sets, date ranges and empty values like the real data, so edge cases still show up.
- No way back in the test environment. Keys and mappings stay with the person who created the data, or are not created at all.
A note on national ID numbers
A made-up ID number that passes the checksum can belong to a real person. Finland and Norway reserve ranges for test data, and the Finnish henkilötunnus and Norwegian fødselsnummer generators use them. For countries without a reserved range, such as the United States, keep test numbers deliberately invalid or clearly isolated from real systems.
Doing it with Scrubsheet
- Export the table to Excel or CSV and open it in the Scrubsheet browser tool.
- Choose Fake (realistic) for names, emails, phones, IBANs, cards and IDs: fakes keep the format and pass the checksum.
- Turn on consistent output and use the same project secret for every table, so the same value always gets the same fake and joins still work.
- Generalize dates of birth or postal codes if exact values are not needed.
- Download and load the result into the test environment.
For data that lives in Excel workbooks, the Excel add-in does the same inside the workbook and keeps other columns and formulas as they are.
Try it on your own file. The Scrubsheet browser tool anonymizes Excel and CSV files in your browser, free and without uploading anything. In Excel, use the Scrubsheet add-in.
Anonymize a spreadsheet