Building a Validator That Could Prove It Was Wrong

PHASE 1 · DATA

A generator that kept an answer key

The first draft claimed to inject about 3% errors. Counting what it actually did gave about 0.5%, and it never created two of the error types it promised. It also recorded nothing about which rows it corrupted, so I could never measure a validator against it. I rewrote it to log every injected problem in anomaly_manifest.csv. Result: 150 injected anomalies across 5,000 claims (3.0%), eleven rule types, and a claims table with real procedure and diagnosis columns.

PHASE 2 · PYTHON ENGINE

Rules as small functions, plus a catalog

Each rule is a pure function, and a rule catalog (ID, severity, logic, business impact) feeds both the code and the Excel scorecard. Severity sets a failure-rate tolerance. First result: 100% precision and recall, 18 tests passing. I noted at the time that this was circular, because I had written the rules to match the errors I injected.

PHASE 3 · THE HARD SET

14.8% precision

I built a harder dataset: near-duplicate billing, claims with two problems at once, 1,366 formatting problems (spacing, case, US-style dates, $1,234.50-style charges), and legitimate look-alikes that must not be flagged. The unchanged validator scored 14.8% precision and 88.5% recall.

The fix was a cleaning layer (trim, case, two date formats, currency text) that logs what it corrected and never counts it as a failure, plus a twelfth rule for near-duplicate billing. Result: 182 of 182 found, zero false alarms, 27 tests. Each look-alike has its own unit test.

PHASE 4 · SQL

The same rules in SQL, and a parity check

I rebuilt the pipeline in SQLite as layers: raw tables (all text), staging views that clean, twelve rule views, and a star schema. SQLite has no regular expressions, so the ICD-10 format check became a set of GLOB patterns. A parity script compares the SQL rule output with the Python output, rule by rule, on records and on dollars. All twelve match. Seven more tests brought the suite to 34.

PHASE 5 · AZURE SQL

Porting to T-SQL

I created a free-tier Azure SQL database and set the free-limit behaviour to auto-pause so it cannot incur charges. Porting meant changing the dialect: GLOB became LIKE, date() became TRY_CONVERT, CREATE TABLE AS became SELECT … INTO, every subquery needed an alias, and view definitions had to be split into GO batches. Azure’s default text comparison ignores letter case, so ordering that decides which duplicate is “the original” uses an explicit binary collation. The build loads about 9,000 rows and finishes in about a minute. All twelve rules matched Python on the first full run, with identical dollar totals.

PHASE 6 · POWER BI

Model, measures, and a cross-check

The model uses the star schema with a role-playing date dimension (service date active, submission date inactive) and DAX measures for denial rate, collection rate, days to pay, pending AR, clean claim rate and dollars at risk. Import mode keeps the free-tier database from waking on every click. Row-level security is one static role per payer. I then checked the report against the SQL results. Denial rate (609 denied of 3,961 decided claims = 15.37%) and the 182 findings matched. I worked out the clean claim rate independently (158 flagged of 5,052 = 96.87%) and it matched too.

The cross-check also caught real mistakes: an “AR aging” chart that plotted all claims instead of pending ones, and report notes that described the duplicate-payment and procedure-code rules more strongly than the code implements them.