Oriental Nippon Development
Back to portfolio
Case studyUS hospital operation

Weekly Hospital Insurance Data: From Manual Wrangling to an Automated Pipeline and Portal

Every weekly coverage and claims file was extracted, cleaned and charted by hand before staff could use it. We automated the whole cycle, triggered on arrival, and published it to a multi-tab portal.

Industry
Healthcare & Social Care
Region
United States
Engagement
SES (specialist engineers)

Outcomes

  • Zero-touch weekly processing
  • Consistent measures every cycle
  • Direct staff access via portal

Stack

Python · ETL · SQL · Web portal · Orchestration

Written byOriental Nippon Team

A US hospital operation receives insurance data every week: coverage and claims for its patients. Before clinical and administrative staff could use any of it, someone had to load the files, clean them, transform them, run the analysis and build the charts.

The delay between a file arriving and the data being usable ate into the very week it described.

The problem

  • The same manual work every week. Extraction and transformation repeated from scratch each cycle.
  • Stale by the time it was ready. Staff saw the week's numbers late in the week.
  • Variation. Manual handling meant small differences from one cycle to the next.
  • No direct access. Hospital staff depended on whoever produced the charts.
BeforeAfter
TriggerSomeone starts the workFile arrival
ValidationBy eyeAutomatic, with alerts on bad or missing files
OutputCharts sent aroundWeb portal with several dashboard tabs
ConsistencyVaried by cycleSame measures every week

What we built

  • Automated ingestion

    Each weekly file is loaded and validated on arrival, with alerts for missing or malformed files.

  • Repeatable transformation and analysis

    Python ETL and SQL produce the same measures every cycle.

  • A portal for hospital staff

    Multiple dashboard tabs, structured around what staff actually need to see.

Technology and the role it played

TechnologyRole in this project
Python ETLIngestion, cleaning and transformation on arrival
SQLRepeatable measures for coverage and claims
Scheduled orchestrationTriggers the pipeline when a file arrives
Web portalDashboard tabs for clinical and administrative staff

How we delivered it

We documented the manual process exactly as it was performed, including the judgement calls, before automating any part of it. Then the new pipeline ran beside the old process for several weeks.

  1. Document the manual cycle. Every step, as actually done.
  2. Automate ingestion. Scheduled load, validation and alerting.
  3. Automate transformation and analysis. The same measures, every cycle.
  4. Publish to the portal. Tabs designed around what staff asked for.
  5. Parallel cycles. Automated output compared with the manual result before switch-over.
Delivery timelineIndicative total: 12–17 weeks
  1. 01Process documentation

    1–2 wks

    The manual cycle, captured as it is performed

  2. 02Ingestion automation

    2–3 wks

    Scheduled load, validation, alerting

  3. 03Transformation and analytics

    3–4 wks

    ETL and the measures, made repeatable

  4. 04Portal

    3–4 wks

    Dashboard tabs for hospital personnel

  5. 05Parallel cycles

    3–4 wks

    Automated output compared against manual, then switch-over

Risks and how we handled them

RiskHow we handled it
Automating judgement calls nobody wrote downThe manual cycle was documented exactly as performed first
A malformed file poisons the weekValidation on arrival with alerts for missing or bad files
Pipeline output quietly disagrees with the old processSeveral weekly cycles run in parallel before switch-over

Outcome

Hospital staff get their insurance picture on a predictable schedule, without anyone wrangling data first. Coverage and claims decisions can be made while the week is still current.

An automated pipeline that quietly disagrees with the manual process it replaced is worse than no pipeline. That is why the parallel run took as long as it did.

What the client keeps

  • Pipeline code, in their own repository
  • Documented transformation logic
  • Validation rules
  • The portal and its source
  • The parallel-run comparison record

Sources and further reading

  1. Administrative simplification, Centers for Medicare & Medicaid Services
  2. Parallel change, Martin Fowler
  3. Logging Cheat Sheet, OWASP

Facing something similar? This is the kind of work our AI, Data Science & Automation team delivers every day. Talk to us about your stack.