Masked UAT data refresh for an insurer

UAT ran on a copy of production taken in 2019, refreshed never, because refreshing meant a DBA's whole weekend and a week of broken test setups afterward. Testers filed bugs that were really just stale data; product owners demoed against members whose policies had expired years ago; and the copy predated two product launches entirely, so their features couldn't be tested realistically at all. Everyone agreed it was a problem and nobody owned it, which is how I ended up owning it.

QA engineer at the HMO, initially doing this as a side-of-desk project with one sympathetic DBA, later formalized to half my time for about five months once the compliance office got interested — in the supportive sense, which surprised me too.

I owned the masking ruleset, the refresh pipeline itself, and the verification suite that runs before the refreshed database is opened to testers. The DBA owned the extraction from production and the restore mechanics; the split meant I never touched raw production data directly, which was the design and not an accident.

The decision that made testers accept the refresh was deterministic masking: the same real member maps to the same fake identity on every refresh, so a tester's bookmarked test member survives from month to month — the previous attempt at masking had randomized everything each time, and testers had quietly gone back to the stale copy rather than rebuild their setups monthly. I subset by cohort rather than copying everything, keeping full history for a representative slice and thin records for the rest, which cut the refresh from a weekend to six hours. And the verification suite runs before the database opens: queries that hunt for any surviving real name, birthdate, or member number pattern, with the refresh failing closed if anything hits.

Every masking rule needed sign-off from the compliance office, individually, in writing — tedious, and exactly right, and the audit trail from that process is why the ruleset was later reused elsewhere. The production extract crossed a maintenance-window link with a hard cutoff, which is what forced the cohort subsetting design, and about 400 GB of source had to become something restorable before Monday morning.

The refresh has run monthly and unattended since, at about six hours per cycle. Stale-data bug reports — once a fifth of everything testers filed — effectively disappeared, and the two untestable product launches got their first realistic UAT coverage. The masking ruleset passed an external audit without findings and was adopted by another subsidiary's team, ruleset and sign-off trail together, which is the part I'd point to as the real deliverable.

The DBA co-designed the pipeline and owned everything that touched production — this project is half his. The compliance officer turned from gatekeeper to sponsor once the verification suite gave her something she could show an auditor, and the testers' complaints about the randomized-masking prototype were the most useful requirements document I've ever received.