How do you mask test data without breaking joins and tests?

Mask with a consistent mapping: the same real value always becomes the same fake value, so foreign keys, joins and duplicate checks still line up. Use a secret key stored outside the test environment, mask every column that carries the same identifier, and verify the result with queries before anyone uses it.

Why naive masking breaks things

Suppose customer email appears in the users table, the orders table and a mailing log. If you replace each occurrence with a fresh random value, the three tables no longer agree. Joins return nothing, uniqueness constraints behave differently, and support tooling that looks up by email finds no match.

The opposite mistake is masking only the table you remember. The identifier survives in the table you forgot, and anyone can join back to the real person.

Definitions

• Deterministic masking means the same input always produces the same output.
• Referential integrity means every foreign key still points at a real parent row.
• Format preserving means the fake value has the same shape as the real one, such as a ten digit string for a ten digit account number, so validation code still passes.

Steps

1. Inventory identifiers. List every column that holds a name, email, phone, address, national identifier, account number or free text. Search by column name and by sampling values, because names are inconsistent across teams.
2. Group columns by identity. Put all columns that hold the same kind of value into one group, such as "customer email". Each group shares a mapping.
3. Choose the mapping. A keyed function, such as a keyed hash that picks from a generated name list, gives stable outputs. A lookup table of real value to fake value also works, but the table is itself sensitive and must not travel with the dataset.
4. Keep the key outside. Under the GDPR definition, pseudonymised data is only safe to the extent the additional information is kept separately and protected. The same logic applies to your mapping key.
5. Mask in a pipeline, not in the target. Run masking in a controlled environment, then ship only the result to staging. Never load raw production data into staging "for a moment" and mask afterwards.
6. Add constraints before loading. Load into tables that already have foreign keys and unique constraints, so mapping collisions fail loudly.
7. Verify. Run queries that check row counts per table, orphan foreign keys, and duplicate values in unique columns. Then search the masked output for the original values of a few known test customers.

Collisions and uniqueness

Two different real values can map to the same fake one if the output space is small. Pick generators with enough variety, or append a stable suffix derived from the row identifier. Check uniqueness constraints explicitly.

What masking will not fix

Combinations of fields can identify a person even when direct identifiers are replaced. Birth date plus postcode plus job title is a classic example, and NIST SP 800-188 treats re-identification risk and quasi identifiers as core topics in de-identification. Free text columns need separate handling, because notes and messages hold names, numbers and secrets in unpredictable places.

If your tests do not need realistic relationships, skip masking and generate synthetic data instead. It is simpler and carries less risk.

Mistakes to avoid

• Reversible schemes with the key in the repository.
• Masking the application database but not exports, search indexes, caches or the data warehouse copy.
• Using different mappings per team, so integration environments cannot be joined.
• Forgetting that backups of the masked database are fine, but backups of the pre masking input are not.

Quick check

Sources

• GDPR Article 4, definition of pseudonymisation
• NIST SP 800-188, De-Identifying Government Datasets

Last reviewed: 19 October 2026.