Schemas, identity and joins

The first export joined cleanly in the prototype because the prototype never joined anything. The pilot needs the claim, the policy term in force on the loss date, the wording for that term, and whatever has already been paid — four tables from four systems — and the key that links them is not the key you were told it was.

The customer key that is not unique

Ask anyone on the claims floor how a claim connects to its policy and they say policy_no. It is printed on every letter, and it is not unique in the policy table. A policy renews every year; each term is its own row in policy admin, with the same policy_no, a different term_start and often different wording. The old term stays; claims against it arrive for years.

Join a day's 1,200 claims to the policy view on policy_no and the result is 1,460 rows: the view carries every term in force since early June, where the backfill window opens, and 260 of the policies renewed since then, so they carry two terms. The prompt for those 260 claims carries two wordings, and which one the model reads first is a matter of row order. On the eval set, agreement on recently renewed policies came out at 81% against 94% elsewhere; because they were a fifth of the set, the overall number fell about 3 points, roughly six cases, which read as general weakness rather than one segment's. Nobody found the cause until the results were cut by renewal as well as by the segments building the eval set draws.

The join key is not policy_no; it is the term in force when the loss happened: policy_no plus the row where term_start ≤ loss_date < term_end. That is why getting the data out parses loss_date with a fixed pattern per format: a date with its day and month swapped, up to eleven months out, silently picks the wrong term, and the model cites a clause that was not in the contract.

The claimant has the inverse problem: the claims system's claimant_id identifies the claimant on that claim, not the person, so one person across two claims has two ids. The CRM has a customer_id that nearly matches by name, date of birth and postcode. For triage, do not resolve it: the pilot does not need to know two claims are the same person, and a fuzzy match on people is the customer's decision. Resolve the identities the pilot needs and leave the rest unjoined, on purpose, in writing.

Expand

The canonical record: claims and policy terms resolved by loss date into one row per claim, with the free-text description redacted inside the customer tenant100%−+FitSVG (opens in a new tab)CloseEsc

Pinch to zoom · drag to panScroll to zoom · drag or arrow keys to pan · 0 fits · Esc closes

A canonical record

The canonical table has one grain — one row per claim — and every join either preserves that grain or is an aggregate folded into one column. Thirteen columns in the worked pilot, ten of the claim and three of provenance:

A UNIQUE index on claim_id makes the database refuse a doubled row at load time, rather than in a demo where the sponsor's finance lead reads a doubled total aloud. The system-design course covers what a relational schema buys; for a pilot it is one line: the load fails loudly when the grain breaks.

Two checks run on every load and go in the daily log: every distinct claim_no in the landing table is either a canonical row or on the rejected list, and the sum of paid_to_date equals the ledger's total for the same claims. The second catches the join that doubled the invoices before anyone outside the team sees it.

Deduplication

Exact duplicates arrive for dull reasons: the extractor ran twice after a crash, the SFTP job re-sent Tuesday's file, the DBA refreshed the view and every source_updated_at moved. The load is keyed on (source_system, claim_no, source_updated_at) and stores a hash of the row's content: a key already loaded is a no-op; a newer source_updated_at with the same hash is a no-op too, which absorbs the DBA's refresh; a newer one with a different hash replaces the old row; an older one is ignored. In memory that is a dictionary keyed on the claim, one pass, what a hash map buys — 78,000 backfill keys at about 100 bytes each is 8 MB, deduped in well under a second.

Near-duplicates have a different owner. A handler opens a second first-notice-of-loss for the same incident — same policy_no, loss dates a day apart, same loss type — and the claims system has two claims because, as far as it knows, there are. In the worked pilot that was 1–2% of claims. Do not merge them. Merging moves money and the customer's reserving, and a rule that is right 95% of the time, run over the 60–120 near-duplicates a week (1–2% of 6,000 claims), still merges three to six wrong pairs every week. Flag the pair, route both to a handler, and count the pairs in the rollout notes.

Which columns never leave the tenant

Of the 40 exported columns, nine are personal data: claimant name, date of birth, address, postcode, phone, email, national ID, bank account, and the free-text loss description — which contains all of the others, in prose, whenever the claimant typed them. The triage prompt needs the loss description, loss type, amount, line of business and policy wording. It never needs a name.

Three rules, in order of how cheap they are:

1. The structured personal columns are not in the view. The DBA leaves them out, the pilot's account never sees them, and "which personal data does the service read" has a short answer. That is least privilege at the source, and auth and the boundary writes it down.
2. The free-text column is redacted inside the tenant before it reaches a prompt. A pass replaces names, phone numbers, account numbers and addresses with placeholders — PERSON_1, ACCOUNT_1 — and keeps the mapping in a table that stays in the tenant, so the handler's screen can put the names back. The pass is imperfect; assume it misses 2–5% of mentions, and say so when the security questionnaire asks what leaves the tenant: the honest answer is "the redacted description and the wording", not "nothing".
3. The prompt log is personal data wherever the redaction missed. Keep it in the tenant, keep it 30 days, and treat it as the threat model does: a leak of the log is a leak of the claims.

The first rule removes eight columns with one ticket; the second needs code and a measured miss rate for the ninth; the third is a setting someone has to remember after you leave.

In an interview

What is being tested is whether you treat the customer's business key as a claim to verify rather than a fact, whether you can state a table's grain and the check that proves it held, and whether you know which columns are personal data without being told. A strong answer says "one row per claim, resolved by the term in force on the loss date, with a uniqueness constraint and a reconciliation against the ledger on every load."

The mistake that loses points is joining on the key everyone uses because everyone uses it, and finding out it fans out when the total on a slide is twice the ledger's. Behind it: resolving identities the pilot never needed — wasted weeks and a privacy decision you were not asked to make.

Check yourself

1. The policy view has 1,200 matching policies for a day's 1,200 claims, 260 of them with two terms. How many rows does a join on policy_no return, and what does the resolver return?

940 single-term policies give 940 rows and 260 two-term policies give 520, so 1,460 rows — 260 claims with two wordings in the prompt. The resolver picks the term with term_start ≤ loss_date < term_end and returns exactly 1,200; the UNIQUE index on claim_id refuses the 1,460 if the resolver is ever bypassed.

2. The redaction pass misses about 3% of personal mentions and the customer's security lead asks whether any personal data leaves the tenant. What do you say?

Yes, in two forms: the redacted loss description with an estimated 3% residual, and the policy wording, which is not personal. Give the measured miss rate from a 200-description sample rather than a promise, name the 30-day retention on the prompt log in the tenant, and offer the control that removes the residual — the model placed inside the tenant, the decision module 3 left open.

3. The nightly SFTP job re-sends Tuesday's file on Wednesday and the DBA also refreshed the view, so every row's source_updated_at moved. How many rows does the keyed load insert?

From the re-sent file, zero: every (source_system, claim_no, source_updated_at) key is already loaded. From the refreshed view, zero as well: every source_updated_at is newer, but every hash matches, so nothing is replaced and nothing goes back through triage. The count check must still hold — every claim a canonical row or a named rejection — because it is what says the refresh was a refresh, not a different population.