Monthly Automated Data Redaction for Retention Compliance
A retention obligation required monthly redaction of personal and health data across 20+ tables, by hand. We turned it into a scheduled, rule-driven operation with evidence of every run.
- Service
- Operations & Support
- Industry
- Revenue Cycle & Finance
- Region
- United States
- Engagement
- SES (specialist engineers)
Outcomes
- 20+ tables covered automatically
- Run evidence every month
- Zero manual effort
Stack
SQL stored procedures · Power Automate · Orchestration
Written byOriental Nippon Team
A US revenue-cycle operator holds personal and health-related data across more than twenty related tables. A retention rule requires data in aged accounts to be redacted every month.
That was being done by hand. The risky part was not the effort but the failure mode: a missed table or a skipped month stays invisible until an audit finds it.
The problem
- Manual work across 20+ tables. Slow, repetitive, and easy to get slightly wrong.
- Coverage depended on memory. Tables added since the procedure was written were easy to miss.
- No evidence. Nothing showed automatically that this month's redaction ran and covered what it should.
- Not reversible. A mistake on live data cannot be undone.
| Before | After | |
|---|---|---|
| Tables in scope | 20+, from a manual list | 20+, from an "aged" rule |
| Schedule | When someone ran it | Monthly, automatic |
| Proof it ran | Reconstructed if asked | A run record every month |
| Testing | On live data | On a restored copy only |
What we built
A monthly redaction job
SQL stored procedures, orchestrated by scheduled Power Automate cloud flows.
Rule-driven coverage
The set of rows is chosen by the definition of "aged", not by a list someone maintains.
A run record
Each month's execution and coverage can be shown to an auditor without reconstruction.
Technology and the role it played
| Technology | Role in this project |
|---|---|
| SQL stored procedures | Field-level redaction inside the database |
| Power Automate cloud flows | Monthly scheduling and orchestration |
| Run records | Evidence of what ran, when and across which tables |
How we delivered it
Because redaction cannot be reversed, nothing was ever built or tested against live data. Every iteration ran on a restored copy until the output was proven.
- Pin down the rule. What counts as aged, which fields are in scope, and what redaction means for each field type.
- Map the estate. Every table, including the ones the manual procedure had been missing.
- Build on a restored copy. Stored procedures and orchestration verified away from production.
- One verified live cycle. A full month run and checked before the schedule was trusted.
- Evidence and hand-over. Run records and documentation.
01Rule definition
1–2 wks
Ageing criteria, fields in scope, redaction semantics
02Estate mapping
1–2 wks
Full table set, relationships, dependencies
03Build and test on restored copy
2–3 wks
Stored procedures, orchestration, verification
04Verified first cycle
2 wks
One month run and checked before automation is trusted
05Evidence and hand-over
1 wk
Run records and documentation
Risks and how we handled them
| Risk | How we handled it |
|---|---|
| Irreversible damage to live data | Built and tested only on a restored copy |
| Tables missed by a manual list | Coverage driven by the definition of "aged", with a full estate map |
| A month silently skipped | Every run produces a record that can be shown to an auditor |
| Trusting the schedule too early | One live month verified by hand before automation was trusted |
Outcome
The obligation is now met every month, on schedule, with no manual effort and no reliance on anyone remembering the table list.
This work does not make the business money. It removes a way for the business to be wrong, which is the whole point of a compliance control.
What the client keeps
- Stored procedures and orchestration definitions
- Ageing and redaction rules, documented
- The estate map
- Monthly run evidence
Sources and further reading
- HIPAA for professionals, U.S. Department of Health & Human Services
- Article 5: principles relating to processing of personal data, GDPR
- Logging Cheat Sheet, OWASP
Facing something similar? This is the kind of work our Operations & Support team delivers every day. Talk to us about your stack.
