Useful test data.
Private details, replaced.

1. The Database Dump Problem

When debugging a tricky production issue, developers need realistic data. However, traditional database dumps (pg_dump or mysqldump) present major compliance and engineering hazards:

• Compliance Violations (GDPR, HIPAA, SOC 2): Copying unmasked customer emails, phone numbers, and payment details onto developer laptops exposes organizations to severe regulatory penalties.
• Enormous File Sizes: Production databases are frequently hundreds of gigabytes or terabytes in size. Restoring an entire dump locally takes hours and wastes storage.
• Broken Anonymization Scripts: Hand-rolled SQL scripts (e.g. UPDATE users SET email = '[email protected]') are slow, risk timeout errors, and frequently overwrite data before foreign keys are resolved.

2. Preserving Referential Integrity

Relational databases are not isolated tables—they are connected graphs of foreign keys. If you copy an order record from shop.orders without copying the corresponding record in shop.customers, the target database will immediately throw a foreign key constraint violation:

dbmigrate solves this by performing topological foreign key traversal:

1. Filter Source Rows: You specify a target condition on the root table, such as WHERE created_at > NOW() - INTERVAL '7 days' or a list of specific Primary Key IDs.
2. Collect Dependencies: dbmigrate queries related parent tables (such as customers) and child tables (such as order_items).
3. Dependency-Ordered Insertion: Rows are written to the target database in strict parent-first order, in dependency order. Review cycles, constraints, and pre-existing target rows for your dataset.

3. Deterministic In-Memory Masking

Why is deterministic masking required? If a customer's email or user ID appears across multiple tables (e.g. in users, audit_logs, and notifications), random masking would replace each occurrence with a completely different random value. This breaks analytical JOINs and makes local debugging nearly impossible.

dbmigrate applies deterministic seeds and configurable salted-hash rules:

Because the salt is stored locally in your workspace, a consistent rule and seed can map repeated input values to consistent replacements. Review configuration across related columns and check the final result.

4. Step-by-Step dbmigrate Workflow

Here is how you execute a masked environment refresh in dbmigrate:

1. Select Source & Target Connections: Choose your production or staging source and your target development database.
2. Define Table Scope: Choose the tables you want to copy. Enable Include Related Rows to pull parent and child dependencies automatically.
3. Apply Row Filtering: Add a WHERE condition or specify a row limit (e.g. a limit of 1,000 rows; ordering and representativeness require separate review).
4. Review Masking Rules: dbmigrate scans column names against your Masking Dictionary (recognizing email, first_name, phone, ssn, etc.). Ensure every sensitive column has an active rule or is marked "Not PII".
5. Execute In-Memory Copy: Rows are extracted from source, transformed in local memory, and streamed directly into target.

5. Environment Column Overrides

In addition to masking sensitive fields, certain environment flags must be forced to safe values in development. dbmigrate provides Column Overrides that apply after masking:

• notifications_enabled = FALSE (prevents development jobs from spamming real customers).
• payment_gateway_mode = 'sandbox' (forces sandbox payment processing).
• auth_domain = 'localhost:3000' (redirects authentication callbacks).

6. Security Controls & Privacy Review

Use masking alongside a privacy review of the resulting dataset. Check indirect identifiers, overrides, target access, and retention rules: