Data plumbing · Extract and normalise

The join that doubled the invoices

Decide which join ships for Monday after a paid-to-date total came out at twice the ledger in front of the finance lead, and what to do with the invoices that carry no claim number.

casemediumExercise · 4 min · 31 of 64

On this page

A number on a demo slide was twice the number finance publishes, and the finance lead was in the room. The decision is what you do in the four days before the next demo.

The situation

The triage prompt carries a paid_to_date field so the model can see whether a claim already has repair invoices against it. The pilot joins the demo's 1,200 claims to their 1,460 repair-shop invoices in the payments ledger on policy_no, the key both tables carry. The join returns 2,870 rows, and the paid-to-date total comes out at about 1.97 times the ledger's own report. In the Thursday demo the sponsor's finance lead reads the total aloud and asks why it is double. Afterwards you check: invoices carry a claim_no in 96% of rows; the other 4%, about 60 invoices, were keyed in by hand with only the policy_no. 310 of the policies have two open claims, and between them they carry 1,410 of the 1,460 invoices. The next demo is Monday. The customer's definition of done has five lines (agreement on the 200-case set, p99, cost, coverage and a named owner), none of which mentions paid-to-date, and nobody has yet measured whether the field moves agreement at all.

The question

Which join do you ship for Monday, what do you do with the 4% of invoices that carry no claim number, and what do you say to the finance lead before then?

A strong answer · Rubric · Where answers go wrong

Write your answer first, then open

A strong answer

The join key has to match the grain, and invoices belong to claims, not policies. On policy_no, every invoice on a policy with two open claims matches both and is counted twice: 1,460 + 1,410 = 2,870 rows, and a total that nearly doubles. Ship the join on claim_no. The 96% of invoices that carry it attach exactly, the row count after the join is bounded by the invoice count, and the paid-to-date total for those invoices must equal the ledger's figure to the cent. That equality becomes a check that runs on every load, as schemas, identity and joins prescribes, not a check that ran once on Friday.

The 4% are not attributable without a rule, and Monday is no time to invent one. Leave them out of paid_to_date and put a flag on the claim — unattributed invoices on this policy: 2 — so the handler knows there is money the field cannot see. A rule that guesses by date window would rescue 60 uncertain rows at the price of making the certain 1,400 unexplainable.

Write to the finance lead on Friday, before anyone asks: the figure was wrong because the join was on policy, not claim; the corrected figure matches their report to the cent on the 1,400 invoices with a claim number; the 60 hand-keyed ones make up the difference; and the check now runs on every load. The evidence that would change the decision: if the 60 concentrate in one line of business or the high-value band, the field is unreliable exactly where triage matters, and it comes out of the prompt until the ledger is fixed — and the eval set says whether it was lifting agreement at all.

Rubric

identifies the grain mismatch as the cause: each invoice counted once per claim on its policy, so 2,870 = 1,460 + 1,410
chooses the key that matches the grain and states the bound it puts on the row count
refuses to invent an attribution rule for the 4% and makes the gap visible to the handler
names a reconciliation check against the ledger that runs on every load
tells the finance lead in writing before the next demo, with the corrected number
says what evidence would remove the field from the prompt entirely

Where answers go wrong

"Divide each invoice by the number of claims on the policy so the total matches." The total now matches and every per-claim figure is wrong: a claim with no invoices inherits half of another claim's repair bill, and the model triages on money that was never spent on it.

"Join on policy_no plus a 30-day window on the loss date, for all rows." 96% of invoices already carry the right key; applying a heuristic to all of them breaks the certain rows to rescue the uncertain ones, and nobody can tell finance which rule produced which number.

"Take paid_to_date off the slide and say nothing." The finance lead saw it and said so aloud. Silence turns a data bug into a trust problem, and the renewal case in week 8 is written by the person who read the doubled number.