So You Think You're Just Comparing Two Files? (Cute.)

Reading time: ~8 minutes (or 45 minutes if you stop every paragraph to think "wait, this happened at my job") 🤣

The Junior vs. Senior Brain Scan

Let's do a quick experiment. Read this sentence:

"We need to build a reconciliation system for our payment transactions."

What a junior engineer hears:

Oh nice, compare two CSV files, maybe write a SQL JOIN. 
Done by lunch. 🍜

What a senior engineer hears:

We are building a financial truth machine that must 
guarantee correctness across multiple systems that 
don't trust each other, can lie, send data late (or 
never), and the whole thing will crash at 3 AM on a 
Sunday. Also it will be blamed for everything.

Both people walked into the same meeting. Only one of them is going home with anxiety tonight.

First, Why Does Reconciliation Even Exist?

Because nothing in distributed systems actually works the way you think it does.

Let's say you pay ₹500 for your electricity bill. Simple, right? You clicked a button. Money went. Bill paid. Life is good.

Here's what actually happened behind that button:

You clicked "Pay"
  → App said "Sure!"
  → Gateway said "Let me check..."
  → Bank said "Okay fine, taking money"
  → Biller said "...hello? Anyone there?"

The biller never got the money. But your app shows Payment Successful.

Your ₹500 is now in the Bermuda Triangle of fintech.

Reconciliation exists to answer the eternal question: Where the heck is the money?

It's basically financial forensics. You're a detective, except instead of a crime scene, you have CSV files. And instead of a murderer, you're looking for a missing ₹500.

"But Surely the Systems All Agree?"

Ah. You sweet summer child.

Every system keeps its OWN ledger. Like that one group project in college where everyone maintained their own version of the Google Doc and nobody merged.

Four systems. Four IDs. Completely different. And one of them is lying (or just confused — it's finance, hard to tell).

Reconciliation is asking: Do all of these guys agree? If yes — great, matched. If not — welcome to the Exception Queue, population: your entire afternoon.

The 5 Ways Real World Data Will Ruin Your Day

Problem 1: IDs Don't Match (Obviously)

Junior brain: "I'll match on transaction ID!"

Great plan. Except your ID is TXN-123, the gateway calls it GW-987, the bank says BANK-ABC, and the biller's file just has REC-555 in a column labeled "ref".

You need mapping tables that stitch these identities together like a financial FBI's most wanted board — red strings and everything.

InternalTxnId → GatewayTxnId → BankRef → PartnerRef

Senior engineers design these relationships to be stored forever. Because three years from now, an auditor is going to ask "what happened to transaction TXN-123?" and you better have an answer.

Problem 2: Timestamps Are a Lie

Your database says the transaction happened at 10:00:01.

The bank says 10:00:05.

The settlement file says 10:15 PM — and by the way, that's the date the file was processed, not the transaction time.

You cannot match on exact timestamps. You'll match nothing and feel nothing.

Instead, you use window-based matching: allow a ±30 minute window and match on amount + customer + reference. It's like saying "I know you said you'd be home at 7, I'll wait till 7:30 before I panic." 😒

Problem 3: Data Arrives Late (Or Never)

Junior assumption:

Transaction happened → Partner immediately sends data

Reality:

Transaction happened → Partner sends settlement file 
                       next day → Sometimes it's 
                       partially uploaded → Sometimes 
                       they just... don't?

So instead of a binary Matched / Not Matched, real systems have states like:

Pending
Matched  
Exception
Expired (a.k.a. "We gave up, please investigate")

Expired is the saddest status in all of engineering. It means you waited, and waited, and nobody came. Like waiting for a Zomato order that never arrives but the app says "Delivered." 🥲

Problem 4: Everyone Has a Different Format

Partner A sends:

{ "amount": 500.00 }

Partner B sends:

{ "amt": "500" }

Partner C sends:

{ "paymentAmount": "500.00 INR" }

Partner D (your nightmare) sends an Excel file with merged cells and color-coded rows.

If your reconciliation engine handles all these formats directly, you end up with spaghetti code that even the pasta gods won't bless.

The senior move: use the Adapter Pattern.

Partner's Weird Format
         ↓
    Adapter (translator)
         ↓
    Normalized Model  ← Everything looks the same here
         ↓
    Recon Engine (finally peaceful)

Your recon engine should be blissfully unaware that Partner D exists. That trauma stays in the adapter where it belongs.

Problem 5: Partial Data Isn't Always Wrong

Internal says: 1,000 transactions.
Partner file says: 700 transactions.

Junior reaction: "Partner is wrong! Exception! ALERT! ALERT!"

Senior reaction: "Hmm. Maybe the remaining 300 are in tomorrow's file. Let me wait."

Missing ≠ Failure.
Missing = Unknown.

Never assume the worst until the SLA expires. This is both a systems design principle and honestly pretty good life advice.

The Matching Algorithm That Broke Junior Brains

Here's a classic junior implementation for matching 10 million transactions:

for internal in internal_transactions:      # 10,000,000 rows
    for external in external_transactions:  # 10,000,000 rows
        if they_match(internal, external):
            mark_matched()

Complexity: O(N²)
Translation: 100 trillion comparisons.
Time to complete: Longer than the heat death of the universe.

Your laptop will cry. Your manager will cry. You will cry.

Senior solution: Build a hashmap. Index by transaction ID. Lookup becomes O(1).

lookup = {tx.id: tx for tx in external_transactions}  # O(N)

for internal in internal_transactions:                 # O(N)
    match = lookup.get(internal.partner_ref)           # O(1)

Total: O(N) instead of O(N²).

This is the single biggest performance improvement in reconciliation engineering. And it's literally just... using a dictionary. 🥲

Also: don't load 10 million records into memory at once. Process in chunks of ~100k. Your production server is not a supercomputer. It's probably a t3.medium someone forgot to upgrade in 2019.

Idempotency: The Word That Separates the Adults

Imagine your reconciliation job runs at 1 AM and crashes halfway through.

You rerun it at 2 AM.

If it's not idempotent: duplicate entries, double adjustments, corrupted reports, and an on-call incident at 3 AM.

If it is idempotent: reruns 100 times, same result, you sleep peacefully.

How? Every run gets a unique ID:

RUN-20250602-001

Every matched record stores which run matched it. On rerun:

Already processed? → Skip.

Simple. Elegant. Life-saving.

Idempotency is the engineering equivalent of "measure twice, cut once" — except it's "process once, reprocess safely." 😎

Exceptions Are Not Failures. They're Feature Requests.

Every reconciliation system WILL produce exceptions. This is not a bug. This is the system doing its job.

Common exceptions:

• Amount mismatch (someone rounded differently — looking at you, Partner C)

• Missing transaction (see Problem 5 above)

• Duplicate transaction (customer clicked "Pay" three times like a maniac)

• Status mismatch ("We said Success, they said Pending, money in limbo")

These go into an Exception Table, which feeds an Ops Dashboard, which feeds an operations team, which feeds your Slack at 11 PM.

The exception is not the end of the story. It's the beginning of an investigation. Build workflows for it. Give ops teams the tools to investigate and resolve. Your job isn't done when you find the mismatch — it's done when someone can fix it.

Audit Logs: Because Future-You Will Be Questioned

In finance: trust = auditability.

Bad:

Status = Matched

Good:

Matched because:
  InternalTxn: TX123
  PartnerTxn: P987
  Amount: ₹500 (both agree)
  Time difference: 12 seconds (within 30-min window)
  ReconRun: RUN-20250602-001
  MatchedAt: 2025-06-02T01:03:42Z

Three years from now, a regulator will ask "Why was TXN-123 marked matched?" and you should be able to answer in 30 seconds, not 3 weeks.

Log everything. Store it forever. Future-you (and your legal team) will send present-you a thank-you note.

The Real Architecture (A Love Story)

Internal Ledger → Ingestion Layer → Adapter (normalizes the chaos)
                                          ↓
                                    Matching Engine
                                    /             \
                              Matched           Exceptions
                                 ↓                  ↓
                             Reports          Ops Dashboard
                                              (where careers go to be tested)

Beautiful, isn't it? Every component has one job. The chaos stays at the edges. The engine stays clean.

The Senior Mindset, Summarized

A junior engineer asks: "How do I compare two records?"

A senior engineer asks: "How do I guarantee financial correctness when millions of transactions arrive late, out of order, duplicated, partially available, formats are different everywhere, and the job can crash at any moment?"

Here's the senior cheat sheet:

1. Never trust a single system as the source of truth — they all lie, some just lie more politely

2. Design for late and incomplete data — patience is a system design principle

3. Make every operation idempotent — reruns should be boring, not disasters

4. Normalize external data before touching it — adapter pattern, always

5. Audit everything — if it can't be explained, it didn't happen (legally)

6. Build exception workflows — mismatches are not edge cases, they're the main case

7. Hash and batch — O(N²) is a war crime at 10M transactions

8. Reconciliation is a financial control, not a reporting afterthought

Closing Thoughts

The next time someone says "we just need to match two files," you now have the full picture.

You're not building a script. You're building the system that answers the question: Did the money actually go where we said it went?

Millions of rupees, thousands of customers, multiple systems that don't trust each other, data that arrives three days late — and you're the one making sure it all adds up.

That's not "just comparing files."

That's financial infrastructure. And it's genuinely hard, important work.

Now go build it. Idempotently. 🫡

P.S. — If you actually built a nested for-loop for this in production, no judgment. We've all been there. The hashmap awaits you.