I Built a Spaceship for a Bicycle Ride: A Week in Data Enrichment

The Setup

Financial institutions process transactions all day, every day, without rest, with the noble exception of one or two establishments who have made a sacrament of scheduling maintenance on Saturday afternoons, precisely when you need cash most urgently. Names withheld. You know who you are. We all know who you are.Somewhere in this ceaseless churn lies a small, unglamorous, deeply consequential problem.

When money moves within an institution, everything is tidy. The bank knows the sender, knows the recipient, knows the whole sordid family history of both accounts. But when money crosses institutional borders say e.g from a mobile money wallet into a bank account, the receiving bank suddenly finds itself hosting a stranger's money with no idea who the stranger is. It dutifully logs what it can: amount, timestamp, reference, recipient and then, where the sender's identity should live, it leaves four polite, gaping holes for the columns.

The columns can be identified as the Other Party Details and include:

• Other party name
• Other party account number
• Other party institution
• Other party institution number

Four fields. That's it. Four fields standing between a compliant AML report and a regulator's raised eyebrow.Here's the twist that makes this a data problem rather than a forms problem: the data isn't missing. It's just somewhere else. The peripheral system can be thought of as the middleman that watched the money leave one platform and arrive in another and logged the entire journey end to end. The bank logged its half.
The wallet logged its half.

So the task was never invention. It was finding the counterpart record, and restore the four orphaned fields to their rightful home without so much as breathing on the integrity of any source system's data.

Simple. Right?

My Approach

I reached for Spark, with the confidence of a man who has read too many engineering blogs.
And why not? The reasons write themselves:

1. Parallel processing — transactions from all peripheral systems' databases could be ran and cross matched at the same time.
2. Transformation logic — the matching rules were going to get gnarly
3. Large-scale processing — the scope was every transaction ever recorded
4. Retry and failure handling — things break, and they break at 2 a.m.

Then I paired it with Airflow, because a batch job without an orchestrator is just a cron entry with self-esteem issues. Airflow brought:

A) Dependency-aware scheduling — enrichment shouldn't start before the day's records have settled
B) Retry and failure handling — with semantics you can actually read
C) Backfilling and historical runs — essential when your scope starts at the beginning of time
D) Observability — a DAG view, task logs, a clear answer to "did it run?"

The pipeline would sweep every transaction from the institution's date of inception (or migration, whichever indignity came first) up to the present, drop everything already carrying complete Other Party Details, and keep only the incomplete ones. Those would go through a strict set of rules i.e the transaction ID, transactionamount and customer ID to find their counterpart in the peripheral systems. Matches would land in a separate enrichment table where data stays untouched. That table gets mapped to the goAML schema, handed to the institution, and is ready for reporting.The whole thing dockerized, needing nothing more than instantiation at end of day processes to keep each day's transactions and
their OPD columns current.

Award winning!!! Clients arriving in their hundreds. Every financial institution needs FRC compliance, little to none have it sorted and here I was holding the answer.

And Then Reality Cleared Its Throat, After a lot of heavy lifting and a humbling series of consultations, i found the flaw.

It wasn't built for the world it had to live in.
A great many financial institutions here run on Windows servers. Not temporarily. Not mid-migration. As a settled fact, defended by policy and maintained by people with very good reasons for their conservatism. Introducing a new technology in these environments isn't a technical decision but a constitutional amendment.
Airflow is built to run on linux primarily. Docker sorts that easily, I was smug about it but Docker is itself a new dependency in an environment allergic to new dependencies. And even if the container runs flawlessly, someone still has to operate it. Someone has to read what a Directed Acyclic Graph (DAG) and a scheduler is and why it has stopped scheduling.

Upgrading environments and retraining staff while transactions keep flowing and the regulator keeps asking can be best described as building the plane while flying it.
The technology wasn't wrong, it was irrelevant to the constraints.
The Lesson
There is such a thing as over-engineering, and It arrives dressed as diligence. Every choice defensible, every component justified, the whole thing gleaming and completely unusable by the people it was built for.
Sometimes the right move is to step back, touch some grass and soliloquise on whether you've designed a spaceship for a journey that was always a bike ride.

Elegance you can't deploy isn't elegance. It's taxidermy.

So: restructure and rebuild. Same problem, same integrity guarantees, same goAML output but built around the architecture that actually exists rather than the one i wish existed. Something a Windows shop DBA can run, inspect and trust without a fortnight of upskilling.

There are many ways to kill a rat. Dropping a nuke shouldn't be your first option. It works, admittedly. But the neighbours have questions, and the rat is no longer the main concern. As always, Until next time, keep your data clean and your terminal keen. Peace ma'dudes .