The Handoff: Weekly 1 of 4
The Handoff
Freeze Sunday Sep 20, 23:59 UTCThe challenge
You're a data engineer migrating away from a CRM your company has used since 2019, through 3 rewrites and a change of owner. Its nightly export is the only feed left: 1 JSON object per customer, written by whoever last touched the exporter. The finance team needs 1 clean row per customer to close the fiscal year. Your CRM team all left the company, but before they did, they set up 1 final data stream that you must capture accurately. Write the code to turn a messy stream of questionable data into a clean table of actionable financial raw data.
The mess
Treat the stream as what it is: 6 years of a product sold into several countries, rewritten 3 times, and maintained at the end by nobody in particular. Fields changed shape and were never migrated back. Free-text boxes stood in for picklists. Regional teams ran their own instance. Integrations wrote on customers' behalf, sometimes twice. And the exporter was patched under pressure, so not every line reads to the end.
Streamed record examples
1,000,000 messagesNormalized output schema
Click a column for its requirements, or click the bottom of the sheet to see the 10 sample rows to produce.
Notes from finance
- 1 row per customer.
- US product: an ambiguous date is month first.
- A time with no zone is UTC.
- ingested_at and other export extras are bookkeeping.
- Lines are built by string concatenation, never validated.
Scoring, per field
- +1the right value. A right null counts.
- 0null where there was an answer. Silence is free.
- −1a wrong value. Silent corruption costs.
- −2a row that should not exist: no matching key, or a second row for the same key. The last row for a key is the one scored.
Score = points over points possible, 4 decimals. Types are strict: a string where an int belongs is wrong. At the reveal: every class of damage, and which ones beat you.