High-volume ETL and regulatory letters that had to be right

Five years of building and running regulated data systems, where a job finishing successfully didn't mean the result was right.

The two interactive illustrations below are my own rebuilds of the core ideas, using synthetic data and original code.

Catching silent data errors

I maintained ETL processing more than 10 million rows a day. In remediation work, an omitted customer or an incorrect balance has consequences well beyond a failed batch, so I built a parity framework to check that upstream and downstream systems agreed before data moved on.

Counts are a cheap first signal. Key coverage establishes who belongs in the result, totals check aggregate value, and record reconciliation checks that each value belongs to the right customer. Pick an incident below and see which checks catch it. The offsetting-balances case passes every aggregate check.

Delivering every letter exactly once

I architected a SQL/SAS system that generated more than 100,000 regulatory-compliant customer letters a month. Rendering the document was the easy part. Invalid records needed somewhere to go, temporary failures needed a retry limit, and re-running a campaign must never send the same letter twice.

In this rebuild, bad data is quarantined before rendering, transport failures get a bounded retry budget, and replaying a campaign checks a ledger keyed on campaign and recipient. Try the lost-acknowledgment incident, where the provider accepts a letter but the acknowledgment never arrives.

Download the source for both rebuilds, including the simulation engines, behavior tests, and Python/SQL companions.