OTC Desk Reconciliation Pipeline
Daily OTC desk reconciliation ETL that ingests a human-maintained operational spreadsheet (no stable primary key, with rows reordered, deleted and recreated at the source) into a relational data warehouse, resolving inconsistently typed counterparty names via fuzzy matching with a 92% similarity threshold. Instead of incremental upsert, the load runs a Full Replace (truncate + full reload) on every execution, guaranteeing the database is always a faithful mirror of the spreadsheet. Deduplication runs on a SHA-256 hash over the transaction's signature plus an occurrence counter (nonce), deliberately preserving identical legitimate splits instead of collapsing them. A schema-shielding layer extracts strictly the expected financial columns and ignores any new column the business side adds without notice, so a business-user edit never breaks ingestion.
Case Study
Problem
The data source was a highly complex operational spreadsheet, manually maintained by a business team, with no stable primary key whatsoever: rows were inserted, deleted and reordered constantly, with no traceability. The business side kept adding new calculation columns without notice, breaking traditional automation scripts. And each trade's counterparty name was typed by hand, often inconsistently; the same counterparty appearing spelled differently within the same spreadsheet.
Solution
With no primary key, incremental upsert would turn into a source of ghost records the moment a row got reordered or deleted at the source. The load runs a Full Replace instead: on every execution, the fact table is truncated entirely and reloaded through PostgreSQL's COPY protocol, guaranteeing the database is always an exact mirror of the spreadsheet's current state, without inheriting inconsistent history. Since there's no primary key to deduplicate against, every transaction gets a SHA-256 fingerprint computed over the row's logical signature (operation type, counterparty, asset, quantity, date, source hash) combined with an occurrence counter, the nonce. This deliberately preserves identical legitimate splits instead of collapsing them as accidental duplicates. Extraction applies schema shielding: instead of reading the whole spreadsheet, it extracts strictly a closed list of expected financial columns and ignores any unknown, temporary or loose calculation column; a business-user edit to the spreadsheet never breaks the next day's ingestion. Finally, counterparty name resolution runs in cascade: first against an explicit alias table, then by exact match against the already-known dimension, and only then by fuzzy matching (92% similarity threshold) against the registered counterparty list; below the threshold, the name is flagged as a new client rather than forced into a wrong match.
Impact
The trading desk's spreadsheet stopped being a single point of fragility and became a reliable source for a relational data warehouse, with no manual counterparty triage step and no breakage every time the business side added a new column. Daily reconciliation, run after run, guarantees the database never accumulates ghost records nor drops a legitimate duplicate by mistake.
Got a data source with no primary key giving you headaches? Let's design the reconciliation that actually fixes it.
Discuss your case