The workflow before#
Kane's accounting team reconciled transactions between two operational systems, Physical Ops and CMR Ops. Every cycle, someone exported both sides, cross-referenced millions of records in Excel with lookup formulas and worked down the list of rows that didn't match. Currency differences were converted by hand, so a correct transaction often looked like a mismatch and had to be chased down anyway. False positives ate hours. Aged, unmatched transactions surfaced late, usually at period end when there was the least time to fix them. The work was slow and it depended on a few people who knew where the traps were.
What we built#
We built a cloud reconciliation engine that does the matching for the team. Records from both systems are pulled automatically, converted to a common currency and matched in Snowflake against the full data set. Accountants open a dashboard that shows only what's left to resolve, so their time goes to real breaks instead of spreadsheet tabs.
Extraction with currency built in
Apache NiFi pulls records from both systems, aligns the schemas and applies currency conversion before anything loads. We put conversion in the pipeline instead of the matching step, so the rules always compare like with like and a rate difference never shows up as a false break.
Matching rules on the full data set
Matching rules run in Snowflake over every record at once and sort each one as matched, mismatched or aged. The rules are written down and versioned, so the team can see exactly why a transaction landed where it did instead of trusting a formula buried in a workbook.
An exception view for accountants
Metabase dashboards show reconciliation mismatches and aged transactions, refreshed daily. Accountants work the exceptions, and managers see the same view, so the aging problem is visible weeks before period end.
How we delivered it
We started with the extraction and currency logic, since that's where most false breaks came from, then ran the new matching alongside the existing Excel process until the results agreed. The accounting team helped write the matching rules, which made trusting the output easier. We also laid out a second phase to expose the clean, reconciled data to Kane's own clients as a paid service.
Before and after#
| Measure | Before | After |
|---|---|---|
| Reconciliation cycle | About 3 days of manual Excel work | Under 4 hours, mostly exception review |
| Currency conversion | By hand, frequent mismatches | Automatic in the pipeline |
| Aged transaction visibility | Found at period end | Daily dashboard |
| Matching logic | Formulas in individual workbooks | Shared, documented rules |
What's next#
The matched data in Snowflake is the base for a workflow agent that triages the remaining exceptions. It would propose the likely match for each break, explain why, and send only the true breaks to an accountant. The paid data service for Kane's clients runs on the same reconciled tables.
