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.
- Service
- AI Business Applications
- 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.
| Before | After | |
|---|---|---|
| Trigger | Someone starts the work | File arrival |
| Validation | By eye | Automatic, with alerts on bad or missing files |
| Output | Charts sent around | Web portal with several dashboard tabs |
| Consistency | Varied by cycle | Same 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
| Technology | Role in this project |
|---|---|
| Python ETL | Ingestion, cleaning and transformation on arrival |
| SQL | Repeatable measures for coverage and claims |
| Scheduled orchestration | Triggers the pipeline when a file arrives |
| Web portal | Dashboard 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.
- Document the manual cycle. Every step, as actually done.
- Automate ingestion. Scheduled load, validation and alerting.
- Automate transformation and analysis. The same measures, every cycle.
- Publish to the portal. Tabs designed around what staff asked for.
- Parallel cycles. Automated output compared with the manual result before switch-over.
01Process documentation
1–2 wks
The manual cycle, captured as it is performed
02Ingestion automation
2–3 wks
Scheduled load, validation, alerting
03Transformation and analytics
3–4 wks
ETL and the measures, made repeatable
04Portal
3–4 wks
Dashboard tabs for hospital personnel
05Parallel cycles
3–4 wks
Automated output compared against manual, then switch-over
Risks and how we handled them
| Risk | How we handled it |
|---|---|
| Automating judgement calls nobody wrote down | The manual cycle was documented exactly as performed first |
| A malformed file poisons the week | Validation on arrival with alerts for missing or bad files |
| Pipeline output quietly disagrees with the old process | Several 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
- Administrative simplification, Centers for Medicare & Medicaid Services
- Parallel change, Martin Fowler
- 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.
