PII on the Dev Server
A hospital analytics team copies the production EHR extract to a shared internal dev server every night so analysts can prototype. The extract contains patient name, date of birth, medical record number, and ZIP. Everyone uses a shared 'analyst' login whose password is stored in a team wiki page. A quarterly audit found three defects: 14% of appointment rows carry a provider_id with no matching provider; 3.2% of rows have a discharge date earlier than the admission date; and after a 2022 system migration the same patients appear under two different MRNs. A patient has now submitted an erasure request. Legal confirms a 7-year retention obligation under state law, and one dataset is subject to a litigation hold.
1. Rank the findings by risk and map each to the control that is missing. Where do you start on Monday?
Read the worked answer
Rank by (likelihood × impact), not by how easy it is to fix.
| Finding | Risk | Missing control |
|---|---|---|
| Identifiable PII on a shared dev server | Critical | Data minimization / de-identification in non-production; environment segregation |
| Shared 'analyst' login, password in a wiki | Critical | Unique user identity, RBAC, secrets management, audit logging |
| Duplicate MRNs post-migration | High | Uniqueness control; master data governance |
| 14% orphan provider_id | Medium | Referential integrity constraint |
| Discharge before admission | Medium | Validity rule / business-rule validation |
Start with access, because it is the largest exposed surface. The PII copy is an unauthorized-access incident waiting to happen: no attribution, no revocation, no audit trail. Concretely:
- Delete or de-identify the existing dev copies today. Dev almost never needs names or DOB - substitute a tokenized patient key and synthetic dates. Retain only what prototyping requires.
- Kill the shared account. Named accounts, least-privilege roles, MFA, and credentials in a vault - never a wiki. Rotate the exposed password; assume it is compromised.
- Enable audit logging on the dev server so you can answer 'who read this' - currently you cannot.
- Only then turn to the data defects. Note the contrast: the quality issues are visible and bounded; the access issues are unbounded and invisible.
2. How do you handle the erasure request given the retention obligation and the litigation hold?
Read the worked answer
Erasure is a right with exceptions - not an absolute delete-everything instruction.
- Legal obligation exception: where retention is required by law (the 7-year state requirement), the data is not erased; processing is restricted to the legally mandated purpose.
- Litigation hold overrides deletion. Deleting records under hold may constitute spoliation. The hold must be checked before any deletion runs, and the deletion process must consult the hold list programmatically - not rely on someone remembering.
What to actually do:
- Acknowledge and log the request with a timestamp. Silent non-response is itself a compliance failure; you respond, you do not simply ignore it.
- Inventory where that patient's data lives - production, the dev copy, backups, extracts, any analytics tables, any exports on someone's laptop. This inventory is precisely what the team currently lacks.
- Delete what is not covered: the dev copy (which should not have existed), any secondary extracts, marketing or research datasets with no retention basis, and free-text fields not needed for the mandated record. Erase the maximum lawful scope.
- Suppress/restrict the rest - flag the record so it is visible only to staff who need it for the retention or legal purpose.
- Document the decision and the legal basis, and communicate the outcome to the requester along with their right to complain to the regulator.
The finding that matters: the team could not have completed step 2 reliably. Fix data mapping - a maintained record of where PII flows - as a governance deliverable, not a one-off project.
3. For the three data defects, name the quality dimension each violates and describe how you would detect, root-cause, and remediate - including the risk in remediation.
Read the worked answer
Dimension mapping:
- Orphan
provider_id(14%) → referential integrity (a subset of consistency). - Discharge before admission (3.2%) → validity if the combination is impossible for the business; accuracy if it is a capture error. Decide which, because the fix differs.
- Duplicate MRNs post-migration → uniqueness.
Detect: implement these as automated rules with row counts and thresholds in a profiling job that runs on every load, trended over time. A 14% orphan rate is not a surprise - it means nobody was measuring. Set acceptable failure thresholds and alert on drift, not just absolute breach.
Root-cause before repairing:
- 14% orphan is far too high for random entry error. Check whether a provider dimension load is being truncated or filtered, or whether the provider feed lags the appointment feed. Fixing the join may be the whole fix.
- The 3.2% discharge-before-admission spike likely traces to a timezone or date-truncation bug in one interface. Segment by source system - if it clusters in one feed, it is a pipeline defect, not a user error.
- Duplicate MRNs trace to the 2022 migration. Use the migration crosswalk to reconstruct the intended mapping rather than guessing from names.
Remediate - and mind the risk: merging duplicate patients with fuzzy matching can join two different people's medical records, which is a patient-safety event, not just a data-quality one. Require deterministic matches (exact MRN mapping from the migration table) to auto-merge; route probabilistic matches to human review with an audit trail, and keep the pre-merge history so a merge can be reversed.
Finally, publish a quality scorecard by dimension to the owners of each source system. Unowned defects do not get fixed.