Generate Synthetic Test Data Preserving Referential Integrity
Development teams cloning production databases into staging environments create a predictable failure mode: test workflows trigger real emails, SMS messages, or webhook callbacks to actual customers. A QA engineer running a batch notification test sends 12,000 promotional emails to real users because the staging database contained production email addresses. The standard mitigation — pseudonymizing the export before import — introduces a subtler problem when done naively. Referential integrity requires that the same customer ID always maps to the same synthetic email across all tables. If customer_id: 847291 appears in both orders.csv and subscriptions.csv, both must receive the identical pseudonymized email. Random replacement breaks foreign key relationships and causes import failures. This workflow builds a deterministic mapping table during the first pass — hashing each real identifier to a consistent synthetic value — then applies that mapping across all related tables.
Why this matters?
An e-commerce platform's DevOps team copied a 340,000-row production customer table to staging for load testing. Naive email replacement using random UUIDs broke the foreign key constraint between customers, orders, and loyalty_points tables — the staging import failed with 89,000 referential integrity violations. The team's second attempt used a simple REPLACE(email, '@company.com', '@test.local') approach, which passed import but caused a worse incident: a misconfigured staging cron job processed 4,200 abandoned cart emails using the original domain portion of addresses, sending real customers emails with staging template errors and broken links. The incident required customer communication, support ticket surge handling, and a post-mortem that mandated deterministic pseudonymization.
The 3-Step Solution
Follow this streamlined workflow to transform your raw data export into a clean, analysis-ready dataset. Each step leverages our browser-based tools to ensure your sensitive data never leaves your device.
By following these three steps, you eliminate manual data wrangling, reduce human error, and maintain full GDPR compliance throughout the process.
Ready to clean your data?
100% local processing. Zero uploads. Blazing fast.
Recommended Tools
Related Workflows
How to Anonymize Customer Data Before Sharing with ChatGPT or Developers
A step-by-step SOP for stripping PII (names, emails, phone numbers, physical addresses) from production datasets while preserving the statistical shape of the data. Includes guidance on which columns to mask, which to drop, and how to verify the output is truly de-identified.
How to Convert Stripe Payout Reports into QuickBooks Import Format
Stripe's payout CSV has 15+ columns that don't map to QuickBooks' expected schema. This guide shows how to separate gross revenue from processing fees, reformat dates to MM/DD/YYYY, and produce a clean CSV that QuickBooks accepts without manual reconciliation.
Sanitize Email Lists Before Klaviyo Import
Importing a dirty contact list into Klaviyo is the fastest way to torch your sending domain's reputation. Exported suppression lists from legacy ESPs are riddled with invisible zero-width spaces, malformed addresses like user@@domain.com, and role-based emails (info@, admin@) that trigger spam traps. If your hard bounce rate exceeds 2% on a single campaign, Klaviyo will throttle your account and your transactional emails start landing in Gmail's Promotions tab. This workflow cross-references your prospect list against historical bounce logs, strips non-printable Unicode characters, and validates RFC 5322 email formatting entirely in your browser.
Clean CRM Data for Facebook Offline Conversions API Upload
Uploading offline conversion events to Facebook's Conversions API requires customer identifiers like email addresses and phone numbers to be hashed with SHA-256 before transmission. But Meta's hashing algorithm is case-sensitive and whitespace-sensitive — an email like " [email protected] " with leading spaces and mixed casing produces a completely different hash than "[email protected]", causing the match to fail silently. Worse, CRM exports from Salesforce or HubSpot often contain invisible zero-width joiner characters (U+200D) copied from web forms, which pass visual inspection but break the hash entirely. This workflow normalizes all email fields to lowercase, strips leading/trailing whitespace and non-printable Unicode characters, validates RFC 5322 email syntax, and reformats phone numbers to E.164 international standard (+1XXXXXXXXXX for US numbers) with country codes prepended.
Sample Datasets
Stripe Payout Export Sample
A Stripe payout CSV with 80 transactions including charges, refunds, disputes, and fee breakdowns across USD and EUR. Timestamps are in raw UTC format. Use this to test the Stripe Payout Formatter and validate QuickBooks-compatible output.
Apollo.io B2B Leads Export Sample
An Apollo.io lead list export with 300 contacts including duplicate emails, role-based addresses (info@, admin@), invalid domains, and inconsistent company name casing. Perfect for testing the Apollo Leads Cleaner and CSV Deduplicator on a realistic dirty dataset.