Verification is where the procedure earns trust. Run it as an automated job in the restore pipeline, and fail the restore if any check returns a row. Three families of checks cover most leaks.
Check 1: unmasked columns
Build a manifest of tables and columns classified as sensitive, then assert that each one conforms to its declared transform output. Concretely: sensitive emails must not match the domain set of real users, phone columns must not match the production pattern, and free-text columns must not contain any string from a denylist of known PII samples.
-- Any row here is a failed restore.
SELECT 'customers.email' AS column_name, count(*) AS suspect_rows
FROM customers
WHERE email LIKE '%@your-real-domain.com'
UNION ALL
SELECT 'customers.phone', count(*)
FROM customers
WHERE phone ~ '^\+1-\d{3}-\d{3}-\d{4}$' -- real customers came in unformatted
UNION ALL
SELECT 'customers.address', count(*)
FROM customers
WHERE address IS NOT NULL
AND address = original_address; -- column kept by mistake in a join
The UNION ALL form lets one job report every failure instead of stopping at the first, which is what you want when a restore goes wrong at 2 a.m. PostgreSQL's constraint machinery can enforce the same idea going forward: you can add CHECK constraints that reject values in a known-real set, so a future load fails rather than silently shipping PII the PostgreSQL constraints documentation.
Check 2: cross-table re-identification
Anonymization that is correct per column can still leak across columns. Two rows with the same date of birth, postcode and signed-up date are effectively one person. The test is cardinality: if the number of distinct (birth date, postcode, signup month) combinations is close to the row count, your environment is re-identifiable.
SELECT
count(*) AS rows_total,
count(DISTINCT (date_of_birth, postcode_prefix, date_trunc('month', created_at))) AS quasi_id_combos
FROM customers;
A healthy ratio keeps quasi_id_combos well below rows_total. When the ratio is near 1.0, coarsen one field (postcode to a prefix, birth date to a month) and rerun. This is a judgment call, not a compliance test, but it catches the class of mistake that survives a column-by-column review.
Check 3: broken joins and integrity
Foreign keys are the database's own referee: every value in a referencing column must appear in the referenced column, and NOT NULL references reject the row outright the PostgreSQL constraints documentation. So the strongest check is to restore with constraints enabled and see whether the load succeeds. When constraints were disabled for speed, run an orphan query against each declared relationship:
SELECT 'orders.customer_id' AS fk, count(*) AS orphans
FROM orders o
LEFT JOIN customers c ON c.id = o.customer_id
WHERE c.id IS NULL
UNION ALL
SELECT 'order_items.order_id', count(*)
FROM order_items i
LEFT JOIN orders o ON o.id = i.order_id
WHERE o.id IS NULL;
Expect zero rows. A non-zero result means the subset expansion missed a path, or an anonymization transform changed a key in one table but not the other. If constraints are enabled, a failed restore is actually the better outcome, because it stops the pipeline before anyone opens a client.
Ship a restore report
Publish a short report per restore: row counts per table (production versus staging), the subset seed range, transform versions and salts by name only, and the pass or fail line for each of the three checks. Reviewers can then sign off on facts instead of trust. Feature flags can also let testers exercise specific flows on a small, known subset of the staging data without needing identical data for every persona, which further shrinks the surface the Datadog feature flag overview.
Antonio Evans
