Premium Billing Reconciliation
Version v0.1.0 · technical reference. Back to the case study →
Overview
Premium Billing Reconciliation is a Python pipeline that automates a monthly premium-billing reconciliation previously performed by hand by a six-analyst team: consolidating hundreds of carrier reports across a multi-client book (up to 20 carrier feeds per client), scrubbing them by the team’s own rules, reconciling enrollment to billing penny-exact, and packaging the client workbooks and per-carrier invoices.
The design principle is fail-loud: a missing input, an unreadable file, or an unresolvable rate halts the run with a named, specific error rather than producing a plausible wrong number. Nothing is ever quietly guessed.
The pipeline is in parity rollout: it runs the same months the analysts worked, and its deliverables are compared to theirs document by document before any client is cut over.
Features
Core capabilities of the pipeline.
- 46 clients · 336 carrier feeds configured in reviewable YAML
- Content-based source detection — the folder decides the carrier, the file is identified by its content
- Per-layout parsers for workbooks, PDFs, and portal exports
- Scrub engine: four deterministic tiers gated automatically, four judgment tiers surfaced for review
- Per-carrier wash rules codified from the analysts’ own method
- Penny-exact invoice reconciliation — sub-dollar drift is flagged, never absorbed
- Coverage report on every run: each configured carrier is expected, produced, or explained
- Zero-install operation — the first run bootstraps its own environment
System Requirements
Required software and environment specifications.
| Requirement | Specification |
|---|---|
| Runtime | Python 3.11+ |
| OS | Windows (Excel automation covers print-style carrier deliverables) |
| Key libraries | pandas, openpyxl 3.1.5+, PyMuPDF, pypdf, Click, PyYAML |
| Install | None for the operator — a batch launcher bootstraps the environment on first run |
| Inputs | Monthly carrier reports dropped into per-carrier folders + a one-line month file |
Architecture
Five stages, each config-driven; the configuration — not code — carries the book.
- Detection: each dropped file identified from content against configured patterns
- Parsers: per-layout readers normalize every source into one model
- Reconciliation: scrub tiers, wash rules, and penny-exact matching of enrollment to billing
- Consolidation: per-client workbooks assembled in the team’s own deliverable formats
- Invoicing: per-carrier invoices built and packaged, print-style deliverables rendered
- Config inventory: client roster (the source of truth for counts), invoice templates, render profiles, detection patterns, scrub and wash rules
Usage
The monthly operator loop — four steps, no installation.
- Open the input folder and drop the month’s files into their carrier folders
- Type the month (YYYY-MM) into the month file
- Double-click the run launcher; the first run bootstraps its own environment
- Review the outputs: client workbooks, invoice packages, coverage report, and any review flags
Validation & Guarantees
The guarantees are structural, not aspirational — each one is enforced by the run itself.
- Penny-exact: invoice-side reconciliation tolerance is $0.00; accounting rejects a payment one cent off
- Sub-dollar rounding drift is flagged for review — flagged, never absorbed
- A source that cannot be acquired or resolved is a recorded refusal: surfaced in the coverage report, and the run exits non-zero
- Judgment-tier scrub rows are detected and counted but never auto-deleted — the keep/delete call is a person’s
- Inputs are archived untouched beside what they produced; nothing is deleted
- Scanned-image sources are flagged for hand-keying rather than unreliably OCR’d into the numbers
Outputs
What a run produces, per client and per month.
| Artifact | Contents |
|---|---|
| Reconciliation workbook | The client’s consolidated monthly deliverable, in the team’s own format |
| Invoice package | Per-carrier invoices, zipped per client; print-style deliverables rendered to document form |
| Coverage report | One row per configured carrier: expected vs produced vs why-not |
| Review flags | Only when needed — the judgment calls and sub-dollar drifts a person should look at |
| Run archive | Inputs moved untouched into an archive beside a copy of what they produced |
Testing & Parity
Every rule — including the refusals — is under test, and the rollout is gated on reproducing the team’s own work.
- Test suite: 1,335 passed · 2 skipped (measured 2026-08-04); refusal paths tested as deliberately as success paths
- Integration tests reproduce real client deliverables byte-for-byte from source files
- Suite runs in parallel by rule — 14m parallel vs 68m serial, identical results, so it actually gets run
- Local gates before anything lands: pytest, ruff, mypy, and a config validator
- Parity standard: 239 of 244 tracked documents tying (98.0%) as of 2026-08-08, remaining gaps externally gated
Results
The before/after is structural: the process changed shape.
| Dimension | Before | After |
|---|---|---|
| Monthly workload | ~80 hours/week team burden (charter estimate) | One run per client + exception review |
| Consolidation | Hand-keyed from up to 20 sources per client | 336 configured feeds, parsed and matched automatically |
| Errors | Found downstream, after invoices went out | Flagged at the gate; the run refuses to finish incomplete |
| Knowledge | Key-person dependent, undocumented | Codified in versioned, reviewable configuration |
| Verification | Spot checks | Byte-for-byte parity against worked months + 1,335-test suite |