Oriental Nippon Development
Back to portfolio
Case studyUS revenue-cycle operator

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.

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.
BeforeAfter
Tables in scope20+, from a manual list20+, from an "aged" rule
ScheduleWhen someone ran itMonthly, automatic
Proof it ranReconstructed if askedA run record every month
TestingOn live dataOn 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

TechnologyRole in this project
SQL stored proceduresField-level redaction inside the database
Power Automate cloud flowsMonthly scheduling and orchestration
Run recordsEvidence 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.

  1. Pin down the rule. What counts as aged, which fields are in scope, and what redaction means for each field type.
  2. Map the estate. Every table, including the ones the manual procedure had been missing.
  3. Build on a restored copy. Stored procedures and orchestration verified away from production.
  4. One verified live cycle. A full month run and checked before the schedule was trusted.
  5. Evidence and hand-over. Run records and documentation.
Delivery timelineIndicative total: 7–10 weeks
  1. 01Rule definition

    1–2 wks

    Ageing criteria, fields in scope, redaction semantics

  2. 02Estate mapping

    1–2 wks

    Full table set, relationships, dependencies

  3. 03Build and test on restored copy

    2–3 wks

    Stored procedures, orchestration, verification

  4. 04Verified first cycle

    2 wks

    One month run and checked before automation is trusted

  5. 05Evidence and hand-over

    1 wk

    Run records and documentation

Risks and how we handled them

RiskHow we handled it
Irreversible damage to live dataBuilt and tested only on a restored copy
Tables missed by a manual listCoverage driven by the definition of "aged", with a full estate map
A month silently skippedEvery run produces a record that can be shown to an auditor
Trusting the schedule too earlyOne 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

  1. HIPAA for professionals, U.S. Department of Health & Human Services
  2. Article 5: principles relating to processing of personal data, GDPR
  3. 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.