Every failure comes with the rows behind it.
Engineering generates commission data, what insurance carriers owe agents and agencies, as six CSV files per carrier, market and statement month. The data team has to prove every file is right before anyone is paid. I turned the checks into a Python script that Claude runs in a workspace I set up, then designed the dashboard and Teams summary that show pass, warning or fail and where each failure is.
An internal tool. The screens are my wireframes redrawn with made-up numbers. Carrier and agency names are real; every dollar figure is changed.
Designed and built it: the checks, the workspace that runs them, the pass, warning and fail rules, the dashboard, the Teams summary and the handoff spec
The data team, the engineers who fix what fails, and teammates who are not engineers
May to August 2026
A 1 to 2 hour review cut to 10 to 15 minutes, then built into the company’s internal tools
Six files, and every one has to agree
Each statement month produces six CSV files per carrier and market: a one line summary, the exceptions, a calculation file with one row per policy, and three payment files. All six have to agree, on top of a set of general checks and each carrier’s own.
Done by hand in Excel, that took 1 to 2 hours per statement review depending on the carrier, and engineers spent a long time just finding where a file failed. The need was real: of the 40 runs on the dashboard, 20 failed and 16 came back with warnings.
- DATA TEAMValidates every file before anyone is paid.
- ENGINEERSFix what fails, so they need to know exactly where.
- EVERYONE ELSETeammates who are not engineers, and stop at check names.
The bug no total could see
On Sentara Health Plans every total tied to the penny, and agencies were still paid wrong. Leon Partners and Marketplace were each paid exactly 80% of their contract rate. The missing money stayed in the house override, so every total still added up and no existing check could see it.
- NEW CHECK
- Reads back what each agency was paid per member per month and holds it against the CRM rate. A mismatch is a fail, because it is real money.
- DIAGNOSIS
- The ratio was exactly 0.80 on every row, so I treated it as a formula: the house share was taken out twice. Solving it predicted the correct rate for nine agencies.
- PROOF
- When engineering shipped the fix, all nine landed on the predicted rate.
Show what passed, not only what broke
- PROBLEM
- Priority Health’s first run was clean: 35 checks passed and 2 small warnings. The Teams summary listed only the two warnings, so the engineer could not tell the run was good.
- PICKED
- Every summary ends with a Passed list, and a run with only warnings says outright that it is safe to release.
- WHY
- A report that lists only problems reads like a bad run, even when it is a good one.
Two readers, one screen, and rules everyone reads the same way
- PROBLEM
- Engineers read the dashboard fluently. Anyone else hit check names like Override_Amt decomposition and stopped.
- OPTIONS
- Rework the layout for both groups, or leave it as engineers knew it and add a layer on top.
- PICKED
- Nothing moved. Hovering any check, status or file answers what it looked at, why it matters and what to do next. Terms like NPN get a dotted underline and a one line definition.
- MY RULES
- Missing data is a fail, never a warning: a blank agent ID means someone does not get paid. A new check starts as a warning until a carrier’s data confirms it. A run with only warnings is safe to release.
A spec instead of a script
I handed engineering a 14 section rebuild spec rather than my Python file: every rule and the reasoning behind it, with no real names, IDs or dollar figures, ending with the design debt to fix rather than copy.
- 10 TO 15 MINPer statement review, down from 1 to 2 hours by hand in Excel, depending on the carrier.
- 27 CARRIERSCovered at handoff, across ACA and Medicare.
- 35 TO 57 CHECKSPer run, growing as new problems turned up.
- 40 RUNSOn the dashboard, May to August 2026.
From my workspace into the company’s tools
By the end, it ran every carrier and statement month automatically. Each time a new problem turned up, I added the logic to catch it. It worked well enough that my manager decided to make it part of the company’s internal tools, and engineering built it in.