Missing records between systems: why the job says it succeeded

How does a record go missing between systems?

An integration is a rule written down once: take the records that match this condition and put them over there. The condition reflects the ways records were known to arrive on the day someone wrote it. Web orders raise an event, so the job listens for the event.

Then other routes appear. Staff key an order for a caller. A bulk import loads a partner's list. A repair script corrects a batch and writes straight to the table. A record gets saved in a state the job's query leaves out, or edited after the job's window has moved past it. None of these routes is wrong and none breaks the job. They sit outside what the job was told to look for. The diagram below follows a hand-keyed order that never raises the event the job relies on.

So the orphan isn't a fault in the integration in the usual sense. The job did what it was written to do. The definition of what should move was right for a business that has since added routes. The receiving side can produce the same result from the other direction: tighten a validation rule and it starts refusing records it used to accept, and the refused record stays behind in the sender, unreported.

Why does the job say it succeeded?

Because a job reports on what it handled. Finished means it reached the end of its list. It doesn't mean the list was the right one, or that every item on it landed. A record that fails validation is logged and skipped, usually at a severity that pages nobody.

Queues give a record another place to go quiet. Amazon's SQS developer guide describes a dead-letter queue as where messages that weren't processed successfully are set aside, so you can find out why. Messages don't stay there forever. The guide advises setting a dead-letter queue's retention longer than the source queue's, and it points to an alarm for any message moved there. Somebody has to choose to set that alarm. Without it, the record sits in a holding area whose only regular visitor is the expiry.

Bookmarks are a third place. Some jobs ask for the records changed since the last run, then move the bookmark to the end of the batch. If a record in that batch failed and the bookmark moved on anyway, the next run never asks for it again. A failure you could have retried becomes a permanent absence. A job that advances its bookmark only past records it has confirmed, and keeps the rest to retry, turns the same fault into a delay.

The vendors who send events say much the same from their side. Shopify's guidance tells an app not to depend on webhooks alone, because delivery can't always be relied on and handlers can fail or be down, and it recommends reconciliation jobs that fetch the data again. Stripe keeps retrying a failed delivery for a bounded time, and its documentation notes that you can use the API to retrieve objects you missed. Both are telling the integrator to compare, not only to listen.

Who finds the gap, and when?

Everything people watch routinely sits above the individual record. The job reports that it finished. The totals at each end look plausible, because one absent record is a small absence in a large number. Dashboards are built from the destination, and the destination can't show what isn't in it. The only signal at the level of a single record is the person who needs that record: a customer chasing an order, a clerk who can't find the matching entry, an auditor who draws a sample from one system and looks for it in the other. The diagram below sets out the levels of checking.

Direction matters. A sample drawn from the source and looked up in the destination finds records that never arrived. A sample drawn from the destination and looked up in the source finds records that came from nowhere. An audit that runs in one direction leaves the other unexamined.

By the time anyone notices, the evidence has usually gone. Logs have rotated, queues have expired their messages, and the person who added the route has moved on. Somebody adds the record by hand, the customer is satisfied, and nobody looks for the cause. Every hand repair proves the gap exists and, in the same act, removes the proof.

What does a gap like this cost a business?

These are composites of patterns we see, not any one client. In retail, an order keyed by phone staff sits in the shop system and is absent from the warehouse feed, because the feed reads only web checkouts. The customer waits, support resends the order by hand, and the stock count is wrong for that item in the meantime, since the unit was never reserved. At a dental or clinical site, a patient booked at the front desk is missing from the reminder list because the reminder service reads only online bookings. The patient doesn't attend, the slot is lost, and the record reads as a no-show.

Where either system is a regulator's own record, as in regulated cannabis markets or gaming, the missing record is a compliance matter, and the person who finds it is an inspector, not a customer. The mechanism is the same in each setting. What differs is who finds the gap and what it costs by then.

What finds a missing record first?

A check that looks at both ends, at record level, from outside the job. After each run, a separate process lists the identifiers that should now exist in the destination, lists those that do, and reports the extras on each side. Compare an agreed total as a cross-check on the identifiers, never as a substitute for them, and run the comparison in both directions. Write it from the business definition of what should exist, not from the job's own query. That's how it catches routes the job doesn't know about.

Start with the integration whose output your leaders rely on most, and run the first comparison once by hand. It will usually find something, and what it finds shows which routes the job's definition leaves out. That list is the specification for the check, more than any tool is.

These rules keep the check useful. A difference is an incident, however small. The alert goes to a named person, not a mailbox. Record the cause each time, including the route the record took, so a repeat gets resolved faster. Give any new way of creating a record a line in the check before it goes live. Where queues are used, an alarm on the dead-letter queue and retention longer than the source queue's keep the evidence around long enough to read.

The check has limits. It finds records that differ between the systems. It can't find a record that both systems lack, and it can't tell you whether a record that matches is right. So ask the owner of any integration how they would learn, and how soon, that a single record had not arrived.