Getting the data out
Pick the exit a customer system offers — export, read-only view, vendor API or SFTP drop — by the freshness the workflow needs, and land the first row in week 1.
On this page
The prototype ran on a CSV a claims handler exported by hand on a Tuesday afternoon. It proved the model could triage a claim; it proved nothing about getting 1,200 claims a day into a queue every working day for eight weeks. The feed that does that is the pilot's first real deliverable, and the pilot's clock starts the day its first row lands — not at kickoff, and not at the demo.
Four ways out, and what each one costs
Every customer system offers some subset of four exits. The decision is which one the pilot can get in its first week, with the freshness the workflow needs.
A manual export. A report screen that writes a CSV. Anyone with a login can produce it in an afternoon, which is why the three-day prototype ran on one. It cannot be the feed: a person runs it, so it arrives when the person remembers, and the columns change when someone edits the report.
A read-only database view. The customer's DBA creates a view over the claims tables that exposes the columns the pilot needs and nothing else, and grants one account SELECT on it. Freshness is whatever your poll interval is. The cost is two tickets, to the DBA and to security; in the worked pilot they took nine working days, where the prototype's CSV had taken one.
The vendor's API. If the claims platform is a hosted product it has an HTTP API, paginated and rate-limited — 600 requests an hour is a common shape. Freshness is minutes, or a webhook. The cost is that the API returns what the vendor chose to expose, which may not include the one field the handler actually uses.
An SFTP drop. A batch file the customer's integration team already produces at 02:00 for their data warehouse. Nothing to build on their side. Freshness is T+1: a claim filed at 09:00 reaches you the next morning.
| Exit | First row by | Freshness | Who has to act | What breaks |
|---|---|---|---|---|
| Manual export | day 1 | when someone runs it | a handler | columns drift, runs stop |
| Read-only view | day 5–10 | your poll interval | DBA, security | a grant is revoked, a table is renamed |
| Vendor API | day 2–5 | minutes, or a webhook | the vendor admin | the rate limit, a missing field |
| SFTP drop | day 3–7 | T+1 | the integration team | the 02:00 job fails silently |
Freshness is set by the workflow, not by the pilot
The triage pilot promises handlers a route and the governing clauses while the claim is in front of them. A handler opens a claim at 09:14; if the feed is a nightly drop, the model's answer is ready at 02:00 the next day, after the handler has done the work by hand. The drop fails the pilot before a token is spent, whatever the model scores.
The question for the extraction path is the one the system-design course asks of batch against stream: how stale may a row be before the answer is useless? For same-day triage, minutes. For a monthly fraud review it is a day, and the drop is the right exit — cheapest, already built, nothing new to secure.
The arithmetic is small. 1,200 claims arrive over a 10-hour day, 60% of them between 09:00 and 12:00, so the peak is 720 claims in 3 hours — 4 a minute. A 60-second poll of the view returns about four rows at peak and one or none the rest of the day, and costs 600 queries a day against a database that handles roughly 5,000 simple QPS. The DBA's concern is the grant, not the load.
The 40-column CSV with three date formats
The first export has 40 columns. Eleven of them the pilot uses; the rest are workflow state, audit columns and two that nobody at the customer can explain. The prototype read the eleven and ignored the rest, which was correct for a demo.
What the prototype got wrong was loss_date. The column held three formats, because the claims platform had been migrated twice: 2026-03-04 from the current system, 04/03/2026 from the previous one, and 4-Mar-26 from a spreadsheet import before that. The prototype parsed the first format and let a library guess the second, and the library guessed month-first. 35 of the export's 200 rows were in the slash format; for the 13 of those whose day was 12 or under and differed from the month, the library produced a valid, wrong date, and nothing failed. The demo ran clean. A reviewer at the customer noticed in week 3 that the model was citing a policy term that had not started yet.
The lesson is not "parse dates carefully". It is that an export carries the history of every system the customer has migrated off, and that a parser which guesses is a parser that lies. The normaliser in schemas, identity and joins rejects a row it cannot read, counts the rejections and reports the count in the daily load log; the silent row is the one that reaches the sponsor.
The same export shows amounts as 1,250.50, 80 and 0.5, statuses as Open, OPEN, open and Re-opened, and a free-text note with embedded newlines. Each is a mapping to write once and a test to keep.
ETL is the first deliverable
The order that works:
- Day 1, before the prototype demo: raise the two tickets for the view and the account. They are the long pole; nothing you write shortens them.
- Extract into a landing table with provenance —
source_system,source_row_id,source_updated_at,extracted_at— and the raw columns untouched. When a handler asks in week 7 why a claim was routed somewhere, the row the model saw is there. - Normalise into the canonical table, one row per claim, rejecting and counting what cannot be read.
- Load the triage queue from the canonical table and nowhere else.
The extractor will run twice — a crash, a retry, Tuesday's file re-sent on Wednesday — so the load is keyed on (source_system, claim_no, source_updated_at) and a repeated row is a no-op. That is idempotency applied to a load, cheaper on day 2 than explaining duplicated claims on day 20.
The backfill reuses the path. The eval set in module 6 draws 200 cases from history, so the extractor runs once over 65 working days: 65 × 1,200 = 78,000 rows at roughly 2 KB each, about 160 MB. One overnight run; not a sizing problem but a permission one, and the answer is the drop, not a wider view: the integration team cuts one file of the 65 days from the job it already runs, and the view keeps its row filter to claims reported since the pilot start. Ask for that file on day 1 too.
Then the clock. The definition of done promises 90% of daily volume covered and a named owner after week 8, and week 8 counts from the first row. If the view takes nine working days, week 8 lands nearly two weeks after the date on the sponsor's calendar, and the sponsor hears that in week 1, in writing, not in week 7.
In an interview
What is being tested is whether you ask where the data comes from before you draw the model, whether you can name the four exits and pick between them by freshness, and whether you know that the first week of a deployment is tickets, not code. Say the dates out loud: "the view is the long pole, I raise it on day 1, and the eight weeks start when the first row lands."
The mistake that loses points is designing against the sample CSV — assuming the production feed looks like the export a handler produced, and that it will keep coming. Close behind it: picking the nightly drop because it is already built, for a workflow that needs the answer in minutes.
Check yourself
1. The customer can give you the nightly SFTP drop today or a read-only view in about two weeks. The pilot is eight weeks and the workflow is same-day triage. Which do you take?
Both, in order. The drop today, for the 78,000-row backfill and the eval set, so weeks 1–2 go on labelling rather than waiting; the view for the live feed, with the ticket raised today. The drop alone fails the workflow — T+1 is useless at 09:14 — and waiting for the view wastes two weeks of work that never needed live data.
2. The vendor API allows 600 requests an hour and returns 50 claims a page. Peak arrival is 240 claims an hour. Does a one-minute poll fit?
Yes. At peak a one-minute poll returns about four claims, one page, so the feed costs 60 requests an hour against 600, a tenth of the limit; a page would overflow only above 50 claims a minute. A backfill through the API is different: 78,000 rows is 1,560 pages, 2.6 hours at the limit, so it runs once, overnight.
3. The load log shows 1,200 rows extracted and 1,160 normalised. Do you start the day's triage run?
Not yet. 40 rejections is 3.3% of the day against a promise of 90% coverage, and the cause matters more than the count. If one rule rejected all 40, a new format arrived and the fix is one pattern; if the 40 are one line of business, the view changed under you. Either way the count goes in the daily log and the customer hears it today, with the claim numbers.