Data Reconciliation: Detecting Mismatches Between Two Systems

Last verified on Nov 8, 2026.

Two systems that should agree sometimes do not. Your payment processor says 1,204 payments were captured, and your database has 1,198 orders. A warehouse table has fewer rows than the source it copies. Nothing throws an error, because every component believes it did its job. The gap only shows up when someone compares.

Reconciliation is that comparison, run on purpose and on a schedule. It matters most where the two sides are loosely coupled: webhooks, queues, batch syncs and exports. Stripe's webhook docs show why. They say events are retried for up to three days in live mode, may arrive out of order and may be delivered more than once. Any one of those can leave your records short, duplicated or stale until a check notices.

What the evidence looks like

A mismatch is a record, or a total, that exists on one side only or differs between sides. This example shows the output of a daily job:

reconcile payments_vs_orders window=2026-11-07
  processor_captured   count=1204  total=61,482.50 USD
  orders_paid          count=1198  total=61,203.00 USD
  missing_in_orders    6   (pi_3Q8...a1, pi_3Q8...b7, ...)
  missing_in_processor 0
  amount_differs       0

The six IDs are the useful part. Totals tell you something is wrong, and the IDs tell you where to look.

How to detect it

1. Pick a shared key. Use the processor's payment ID, an order reference or a stable record ID that both systems store. Do not match on amount and time.
2. Pull both sides for a closed window, such as yesterday or the hour that ended 30 minutes ago.
3. Full-outer-join on the key and classify each row.

WITH p AS (
  SELECT payment_id, amount_minor FROM processor_payments
  WHERE status = 'succeeded'
    AND created_at >= timestamptz '2026-11-07 00:00+00'
    AND created_at <  timestamptz '2026-11-08 00:00+00'
), o AS (
  SELECT payment_id, amount_minor FROM orders
  WHERE status = 'paid'
    AND created_at >= timestamptz '2026-11-07 00:00+00'
    AND created_at <  timestamptz '2026-11-08 00:00+00'
)
SELECT coalesce(p.payment_id, o.payment_id) AS payment_id,
       CASE
         WHEN o.payment_id IS NULL THEN 'missing_in_orders'
         WHEN p.payment_id IS NULL THEN 'missing_in_processor'
         WHEN p.amount_minor <> o.amount_minor THEN 'amount_differs'
         ELSE 'match'
       END AS status,
       p.amount_minor AS processor_amount,
       o.amount_minor AS order_amount
FROM p FULL OUTER JOIN o ON o.payment_id = p.payment_id
ORDER BY status;

Then emit the counts as metrics, one gauge per status, and alert on the non-matching ones. Keep the list of offending keys in the job log so whoever responds can start from them.

For feeds with no stable key, compare aggregates by bucket: count and sum per hour, per tenant and per type. A bucketed comparison narrows a mismatch to the hour and the tenant even when you cannot tie out single rows. Stripe also publishes a payout reconciliation report that ties each payout to the transactions it settles, which is a good second reference for payments.

Thresholds and pitfalls

Start strict on counts and loose on timing. For a closed window, a missing record is a fault. For the most recent window, allow a lag, because events in flight are normal. Run the check twice: a first pass flags candidates and a second pass, 30 to 60 minutes later, confirms which are real.

Common sources of false alarms:

• Time zones. One system cuts the day in UTC and the other in local time. Store and compare timestamps in UTC.
• Units. Minor units (cents) against major units (dollars), or currencies with no minor unit.
• Lifecycle states. Pending, refunded and disputed records legitimately differ. Compare like status to like status.
• Duplicates. If either side can contain the same record twice, deduplicate by key before comparing, or a count will mislead you.
• Dropped context. A filter on one side only, such as test mode or a region, produces a permanent diff.

Watch the trend as well as the value. A steady 0.1% gap that is explained by refunds is fine. The same gap doubling after a deploy is not.

What to do when it fires

Sort the mismatch by type. Missing on the receiving side usually means a lost delivery or a handler that failed after acknowledging. Look at the delivery logs, replay the missing events and fix the handler. Make handlers idempotent by recording processed event IDs, as the Stripe docs advise, so a replay does no harm. For outgoing calls, use idempotency keys so a retry cannot create a second object.

Amount differences point to a transformation bug, so diff one record field by field. Duplicates point to a missing uniqueness constraint.

Related guides: webhook gaps covers missing deliveries, lost writes covers acknowledged writes that never land and stale data covers feeds that stopped. For provider-specific setup, see Webhook Events and the Stripe page.