Generate Synthetic Test Data Preserving Referential Integrity
Naive pseudonymization (replacing emails with random UUIDs) breaks referential integrity. If customer_id 847291 appears in both orders.csv and subscriptions.csv, random replacement breaks foreign key relationships—JOINs fail in staging. This workflow builds a deterministic mapping table via salted SHA-256 hashing, ensuring that customer 847291 always maps to the same synthetic email (e.g., [email protected]) across all related tables. The per-environment secret salt ensures staging data cannot be reverse-mapped without the salt. Safely clone production databases into staging without triggering GDPR Article 33 breach notifications.
Why This Matters
A DevOps team used a simple REPLACE() script to mask emails for load testing. The import passed, but a misconfigured staging cron job processed 4,200 abandoned cart emails using the original domain portion, sending real customers emails with broken staging links. Each accidental email triggers a GDPR Article 33 breach notification requirement—costing an estimated $1.05M in legal administration across 4,200 notifications. Deterministic hashing with a .test.local domain prevents this entirely: even if cron fires, the emails are undeliverable.
Why Excel & Python Fail Here
The tension: random replacement breaks JOINs (bad for testing), but deterministic replacement without salt enables frequency analysis attacks (bad for privacy). The solution: deterministic hash WITH per-environment secret salt. Same input → same output (preserves JOINs), but the mapping is unrecoverable without the salt (prevents re-identification). The salt must be stored in a secrets manager, never in the data files.
| Criterion | Excel / Sheets | Python / Cloud | DataPrep (This Page) |
|---|---|---|---|
| Max Rows | ~150K (crashes) | RAM-limited | 1M+ via Wasm |
| Setup Time | Manual formulas | 30–60 min env setup | 0 seconds |
| Data Privacy | Local but fragile | Uploads to cloud | 100% in-browser |
| Cost | License fees | EC2 / SaaS $49+/mo | Free forever |
Step-by-Step Solution
We will use the GDPR Anonymizer to execute this entirely in your browser.
- 1
Upload all related tables
Drag and drop orders.csv, customers.csv, subscriptions.csv—any files that share foreign keys. The tool auto-detects join columns by name matching (customer_id, user_id, order_id).
- 2
Configure deterministic mapping with salt
The tool generates a random per-session salt (or accepts your environment-specific salt). All PII columns are hashed: SHA-256(salt + original_value) → deterministic synthetic value. Emails map to @test.local domain (undeliverable by design).
- 3
Export all tables with preserved relationships
Download all files. customer_id 847291 maps to the same synthetic identity across ALL tables. JOINs work perfectly in staging. The salt is displayed once for storage in your secrets manager.
Privacy Guarantee: Zero Bytes Uploaded
All processing happens in your browser via WebAssembly. Your data never touches a server. Try it: disconnect your Wi-Fi right now—the tool will still work.
Common Errors & Fixes
| Error / Symptom | Root Cause | Fix |
|---|---|---|
Staging emails sent to real customers | Pseudonymized emails retained original domain (@gmail.com) and staging cron fired | Use .test.local domain for all synthetic emails—RFC 2606 reserves .test as non-deliverable |
Foreign key JOINs fail in staging | Random UUID replacement broke cross-table consistency | Use deterministic salted hashing: same input always produces same output across all tables |
Frequently Asked Questions
Where should I store the salt?
Can I reuse the same salt across environments?
Ready to clean your data?
100% local processing · Zero uploads · Blazing fast
Trusted by 2,400+ data teams · 18M+ rows processed monthly