A nightly reconciliation that had grown to nine hours

The situation

The client moves freight across a regional network. Every night, a batch job matched carrier invoices against dispatch records and wrote the differences to a worksheet the finance team cleared by hand each morning.

The job had been written five years earlier against a much smaller network. It worked by loading both sides fully into memory and comparing them row by row. Volume had grown roughly eightfold. Runtime had grown faster than that, because the comparison was quadratic in the number of unmatched rows — and the number of unmatched rows was itself growing.

By the time we were called, the job started at 21:00 and typically finished after 06:00. Twice that quarter it had still been running when the morning load began, which corrupted the day’s dispatch table and cost a full day of manual recovery.

What we found

The first fortnight was spent instrumenting the existing job rather than rewriting it. That produced three findings that changed the shape of the work:

1. Ninety-four percent of runtime was in one function. The row-by-row comparison. Everything else — extraction, loading, reporting — accounted for about thirty minutes combined.
2. Most “unmatched” rows were not genuinely unmatched. They were the same shipment recorded with a different reference format by two carriers who had been acquired and never migrated. Roughly 60% of the daily exception queue was one normalisation rule away from clearing itself.
3. Nobody wanted the report. Finance had stopped reading the worksheet eighteen months earlier and worked from a filtered copy of it instead. The filter encoded business rules that existed nowhere else.

The third finding was the important one. A faster version of the original job would have shipped a report nobody used.

What we built

We replaced the in-memory comparison with a set-based match in the warehouse, staged as dbt models with the matching rules expressed as tested SQL rather than buried in Python. Carrier reference normalisation became an explicit, version-controlled lookup that the operations team can amend without us.

The exception queue was rebuilt around the filter finance had actually been using — which we reconstructed by sitting with them for two days and writing down what they did — with each exception carrying the reason it was flagged.

Orchestration moved to Airflow with a hard dependency on the morning load, so a late run can no longer collide with it. It has not needed to.

Where it landed

The batch now completes in about eleven minutes against a larger dataset than the one that took nine hours. Manual corrections fell from around 340 a month to six.

The number we care about more: the operations team has added four new carrier normalisation rules since handover, without contacting us.