Prompt and scope
An order system produces 200 million transactions per day. The internal ledger records orders, payments, refunds, and fees; the payment provider supplies settlement-batch reports; the bank supplies actual deposits. Design a T+1 pipeline that finishes the prior day by 06:00, identifies missing, duplicate, amount, fee, and currency differences, and supports reruns, human review, refunds, multiple currencies, and audit trails.
Assume the internal ledger is the business source of truth, while the provider and bank are external sources of truth. Reconciliation must not overwrite the ledger or hide row-level differences because totals happen to agree. Amount, currency, settlement date, batch, and evidence must remain traceable.
What the interviewer is testing
First, can you define facts, time, and invariants before choosing tools? Distinguish transaction date, posting date, settlement date, and bank value date. Store money in minor units, require currency, and make repeated imports safe.
Second, can you make matching a layered strategy instead of a full-table join? Start with provider transaction IDs and batch IDs, then use constrained combinations of order ID, amount, currency, and time window. Fuzzy matches must go to review rather than being silently confirmed.
Third, can you close the discrepancy loop? Every discrepancy needs a type, severity, evidence, owner, status, deadline, and corrective action. A fix creates a new reconciliation version while preserving the original discrepancy.
Questions to clarify before answering
- What is in scope? Authorization, capture, refunds, fees, payouts, bank deposits, or all of them?
- Which timezone defines 06:00? Can settlement and report generation cross daylight-saving changes or holidays?
- Can external reports be fetched incrementally? Are filenames, cursors, versions, and retryable downloads stable?
- May the pipeline auto-adjust the ledger? Assume it creates proposed adjustments that an authorized workflow approves.
- Which FX policy applies? Keep transaction, settlement, and bank currencies, and define the rate source and precision.
- What is the resolution SLA? Define escalation for high-value, compliance, and customer-impacting discrepancies.
A 30-second answer framework
“I would first preserve all three sources in immutable raw layers, then normalize amount, currency, time, and external IDs. Imports are idempotent by report version; strong keys match first, constrained composite keys second, and uncertain results enter a review queue. Each batch gets row-level, grouped, and total controls. Discrepancies use an auditable state machine and are fixed through approved adjustment entries. I schedule backward from the deadline, monitor file completeness, latency, match rate, open amount, and reruns, and exercise duplicate files, late refunds, partial settlements, and FX changes.”
Step-by-step deep dive
Step 1: Define the fact model and accounting window
Create immutable internalentry, providerentry, and bank_entry facts. Keep source-file hash, row number, source, ingest time, report version, business date, and settlement date. Store money as integer minor units, never floating point, and require currency. Separate order state from funds state so “payment succeeded” is not treated as “cash arrived.”
Step 2: Ingest idempotently by source version
Write downloads to object storage first, then validate size, hash, signature, and expected row count. Use source + reportid + version + rownumber as a unique key. A duplicate download adds an ingestion record but cannot post money twice. Preserve old provider versions and record replacement relationships instead of overwriting files.
unique_key = source + report_id + version + row_number
if unique_key already exists: record_duplicate_download()
else: persist_raw_row()Step 3: Build a rerunnable normalization layer
Map status, amount sign, timezone, fee type, and external IDs through versioned dictionaries. Each transformation writes a normalizationrunid; fixed input snapshot and rule version make reruns deterministic. Unknown statuses, negative-amount semantics, and currencies go to quarantine instead of becoming zero by default.
Step 4: Use layered matching
Layer one uses stable provider transaction, refund, and payout IDs. Layer two uses order ID, amount, and an allowed time window within the same merchant, currency, and settlement date. Layer three produces candidates only; equal amounts in different currencies, one internal item split across several external rows, and one external row split across settlements all require review.
Step 5: Prove results with three controls
Row controls verify that each fact is matched at most once. Group controls compare count and amount by batch, currency, settlement date, and fee type. Total controls compare opening balance, inflows, outflows, fees, refunds, and closing balance across the ledger, provider report, and bank deposits. Preserve evidence for each layer when they disagree; do not show a single “passed” flag.
Step 6: Make discrepancies a state machine
At minimum classify missing, duplicate, amount mismatch, fee mismatch, currency or FX mismatch, date shift, and unknown external state. States can be open, investigating, adjustment_pending, resolved, and accepted, with actor, reason, evidence, and timestamp on every transition. High-value or compliance discrepancies need two-person approval; automation may only create a proposal.
Step 7: Handle late data, refunds, and partial settlement
When a report is late, close only the received scope and keep the batch incomplete. Do not turn temporary absence into a permanent missing record. Refunds and disputes can arrive after the transaction date, so record event date and settlement date separately. Preserve one-to-many relationships when a transaction is split across payouts; an equal balance does not erase unmatched rows.
Step 8: Plan recovery, reruns, and audit
Save input-file manifests, snapshot time, rule version, match results, and output discrepancies for every run. Restart from the last successful stage, isolate rerun output by run ID, and merge by unique keys. Retain raw files, hashes, report versions, human decisions, and adjustment vouchers for traceability by batch, transaction, or discrepancy. Stripe also separates balances, transactions, and payouts and recommends using a payout ID to retrieve its balance transactions, showing why an external settlement batch cannot be reduced to one total.
Trade-offs and boundaries
Trade-off 1: Strong keys or higher match rate
Strong-key automation may match fewer rows, but its false-positive cost is controlled. Wider amount and time windows improve coverage while increasing the chance of merging similar transactions. Version thresholds, windows, and fallbacks; send every non-deterministic result to review.
Trade-off 2: Daily batch or near real time
T+1 batches are easier to reproduce and audit. Near-real-time streams surface high-value issues earlier but must handle report versions, late data, and duplicate events. Use the batch as the accounting conclusion and the stream as an alerting layer.
Trade-off 3: Automatic adjustment or human approval
Small, explicit, reversible differences can use constrained automation. High-value, currency, duplicate-charge, and compliance differences require human approval. Both paths preserve original facts and adjustment vouchers instead of editing history.
Failure drills and evolution plan
Drill 1: Duplicate files and rows
Deliver the same report three times and verify that file hash, report version, and row keys prevent duplicate posting. Put one transaction in two versions and verify the version relationship and selection rule are explainable.
Drill 2: Late refund and partial settlement
Deliver a refund two days after settlement and split one order across two payouts. Verify that transaction, settlement, and deposit dates remain separate, and that a new run closes the discrepancy without changing historical runs.
Drill 3: Missing page and wrong FX rate
Remove one report page or replace the rate table. Row count, hash, total, and currency controls should block publication and mark the batch awaiting completion rather than declaring it reconciled.
Common mistakes and follow-ups
Mistake 1: Comparing only three totals
Equal totals can hide one missing and one duplicate row. Keep row matches, grouped controls, and total controls.
Mistake 2: Storing money as floating point
Floating-point error creates false differences. Use integer minor units or fixed decimal and always store currency.
Mistake 3: Overwriting old reports
Overwriting destroys audit and rerun evidence. Store reports immutably and express corrections as versions.
Mistake 4: Auto-posting fuzzy matches
Equal amount and nearby time do not prove identity. Put fuzzy results in review with candidate evidence.
Mistake 5: Treating payment success as a bank deposit
Payment state, provider settlement, and bank deposit are different facts. Model them separately and connect them through batch relationships.
Mistake 6: Ignoring report generation and timezone
Reports may become available after settlement; timezone and holidays create false missing records. Schedule from source availability commitments and store timezone explicitly.
Mistake 7: Editing historical ledger rows to fix an issue
Direct edits remove original evidence. Use an approved adjustment entry linked to the discrepancy and new run.