Erasure Requests, Seven-Year Snapshots, and Three Attrition Models
A global HR analytics platform stores 2.3 million employee records across 31 countries. Analysts query a cloud warehouse; row-level access exists only by country, and 220 analysts have broad SELECT on the core employee table. PII fields (national ID, bank account, home address, health-claim flags) are unmasked in 42 derived tables, including three machine-learning feature stores used for attrition scoring. A data subject erasure request from Germany must be completed in 30 days; the platform keeps immutable monthly snapshots for seven years for audit. The security team proposes field-level encryption and dynamic masking; the analytics team says both will break 60% of existing models. Legal has asked for a defensible design that meets GDPR and keeps the attrition program running.
1. Set out how the erasure request should propagate across live tables, 42 derived tables, feature stores, models, and the immutable snapshots. Distinguish what can be erased from what must be retained.
Read the worked answer
Erasure is not a single delete command. Start with a lawful-basis analysis. Live HR data, derived tables, and feature stores generally have no overriding retention basis for the individual's PII and should be erased or anonymized. Immutable audit snapshots may be retained where a legal obligation or audit purpose applies, but only if access is restricted, the data is minimized, and the retention is documented. Legal must confirm the basis for each snapshot set.
Propagation plan:
- Locate. Use lineage and a PII inventory to find every occurrence across source, 42 derived tables, and feature stores. Record the search as evidence.
- Erase live data. Delete or irreversibly anonymize the subject's records in source and derived tables. For feature stores, remove subject-level features; where features are aggregated, verify that the individual cannot be re-identified.
- Handle snapshots. Do not rewrite immutable audit records without a legal basis. Apply a suppression or pseudonymization layer at access time, or use crypto-shredding if per-subject keys were designed in. Retrofitting crypto-shredding is a project, not a 30-day fix; document the control actually used.
- Models. Retrain or invalidate models that used the subject's data where required, and assess memorization risk. Do not claim full erasure from a trained model unless you can evidence it.
- Respond. Provide the subject with the actions taken, the retained categories and basis, and the contact point, within 30 days.
The defensible position is documented minimization and access restriction for retained audit data, plus real erasure everywhere else.
2. The security team proposes field-level encryption and dynamic masking; analytics says this breaks 60% of models. Recommend an approach that protects PII without stopping the attrition program.
Read the worked answer
Separate three different problems: protecting data at rest, controlling who can see identifiable values, and preserving analytical joins.
Field-level encryption at rest is table stakes but is not access control; anyone with the key or a privileged role sees the data. It also breaks range queries and some model features. Dynamic masking in the warehouse is better suited to 220 broad analysts: tag PII columns, apply role-based masking, and allow unmasking only through a purpose-bound role with approval and audit. This addresses the broad SELECT problem without moving data.
For derived tables and feature stores, use deterministic tokenization for identifiers that must be joined, such as employee ID, and remove raw national ID, bank account, and home address entirely. Deterministic tokens preserve joins and group-by analysis but are vulnerable to frequency analysis on small populations, so pair them with access controls. Health-claim flags should not be model features at individual level; use aggregated or purpose-limited access, because their predictive value rarely justifies the privacy risk.
The '60% of models break' claim should be tested rather than accepted. Most attrition models need stable join keys, tenure, role, performance, and engagement features, not raw PII. Replace raw attributes with tokens, bands, or derived indicators, and retrain. Expect some rework, but the design should keep the program running while removing unnecessary identifiable data. Document the classification, masking rules, and approvals so Legal can defend the control set.
3. Redesign access for 220 analysts and the global attrition program. Include row-level, column-level, and purpose-based controls, and explain how you would test them.
Read the worked answer
Country-level row access is too coarse for a 31-country platform, and broad SELECT on the core employee table is excessive. Move to purpose-based roles combined with row and column controls.
- Roles by purpose. Define HR operations, benefits administration, workforce analytics, and data science roles. Each role gets the minimum columns and rows needed for its purpose. The attrition program should run on a de-identified or pseudonymized analytical dataset, not the core table.
- Row filters. Filter by country and legal entity where the role requires it. For global analytics, use aggregated data with minimum cell sizes; small countries should be suppressed or grouped to prevent re-identification.
- Column controls. Mask national ID, bank account, home address, and health flags by default. Unmasking requires a separate role, a stated purpose, time-bound approval, and query logging.
- Break-glass access. Provide an audited emergency path for HR or legal investigations, with notification and post-hoc review.
- Separation of duties. The team that administers access should not also approve it.
Testing: run quarterly access reviews against role definitions; execute test queries for each role to confirm row and column restrictions; sample audit logs for unmasked access; and test that the analytical dataset cannot be joined back to identifiable records without the controlled key. Also test leaver access promptly. Record results as evidence for Legal and auditors, and treat failures as incidents with owners and remediation dates.