Your integration ran clean and still dropped records: how to catch the orders that never made it across

The sync said it worked. Green check, no error, the job finished. And six orders from Saturday are just… not in your ERP. Nothing failed loudly — that is the whole problem. A seller in r/FulfillmentByAmazon described the version that at least announces itself: "Just spent three hours trying to reconcile missing units because the amazon api decided to randomly stop talking to my woocommerce site over the weekend. again." The quieter version never even goes dark — it drops a handful of records mid-run and reports success on the rest. Here is how to catch them: not by trusting the sync, but by counting what should have crossed against what actually did.

Search those threads and the advice is almost always "switch tools." A user in r/QuickBooks calls the QuickBooks "Connect to Square" integration "so horribly broken"; the top reply tells them to switch connectors. Maybe that is right. But swapping connectors does nothing about the records the next one will also drop, because the gap is not which tool you use — it is that you are trusting the tool to tell you when it failed. It cannot tell you about a record it never received. The fix is a completeness check you run yourself: count the source, count the destination, and find the difference. That is the entire move, and almost nobody on these threads names it.

Why a clean sync still loses records

Most integrations move data one of two ways, and both lose records without erroring. The first is webhooks — the source fires an event when something happens and your side catches it. The catch: delivery is not guaranteed. Shopify says so directly — "because webhook delivery isn't always guaranteed, you should implement reconciliation jobs to periodically fetch data". Stripe guarantees at-least-once delivery, not exactly-once, which means an event can arrive twice or, if your endpoint times out during a deploy or a traffic spike, not arrive at all. A missed webhook is a record that never enters your system, and nothing logs it as missing because, from your side, it simply never happened.

The second is polling — the connector pulls "everything changed since last run" on a schedule. That works until a run overlaps an export window, a pagination cursor skips, a rate limit truncates the page, or the source API goes quiet (the Amazon-stopped-talking case above). The job still reports success on the rows it did pull. The ones it missed are invisible by construction: you cannot see a row that was never fetched. This is why the symptom list is always the same — the Square poster's "mis-imports, missing imports, duplicates, incorrect transaction values, missing transfers". Duplicates and drops: the two signatures of a sync that processes what it sees and stays silent about what it misses.

The three ways records go missing — and why none of them error

"Records are missing" is three different failures wearing one complaint. They need different fixes, but you sort them the same way — by where the record was last seen. Find the failure mode first; the repair follows from it.

The first two are missing-on-arrival; the third crossed and then got dropped. But all three end the same way: a record that exists on one side and not the other. Find that set difference and you have found every one of them, no matter how it went missing. That is what makes one detection method cover all three.

Stop trusting the green check — reconcile on counts

Before you hunt individual records, answer one cheaper question: did the totals even cross? Two control totals catch most drops in seconds. A record count — how many orders, transactions, or lines should exist for the window — against how many landed. And a sum of one meaningful numeric field (order total, amount, quantity) on each side. If either disagrees, something dropped or duplicated. If both agree, you are very likely whole. This is an old, boring control, and it is boring because it works.

Window completeness check (run per sync window)
-----------------------------------------------
source_count  = rows in source for [start, end]       e.g. 1,000 orders
dest_count    = rows in destination for [start, end]   e.g.   994 orders
source_sum    = SUM(amount) in source                  e.g. 48,210.00
dest_sum      = SUM(amount) in destination             e.g. 47,930.40

if source_count != dest_count  -> records dropped or duplicated
if source_sum  != dest_sum     -> records dropped, duplicated, or altered

Here: 6 orders and 279.60 are missing on the destination side.
The counts told you *that* in one query. The next step finds *which*.

Find exactly which records dropped: the anti-join

Counts tell you something is missing; an anti-join tells you which IDs. List every record ID on the source side, list every ID on the destination side, and keep the source IDs that have no match on the destination. In a spreadsheet this is exactly the COUNTIF / MATCH set-difference move; in SQL it is a LEFT JOIN where the destination ID is null. Either way the output is your dropped records, by key.

This only works if you match on a stable primary ID that both systems carry unchanged — the source order ID, the gateway transaction ID — not a display number the destination reformats or a date that drifts by timezone. Match on the wrong field and the anti-join reports records as "missing" that are actually present under a mangled key, which is its own CSV-and-formatting trap. Get the key right first; the rest is mechanical.

1. Export the source IDs for the window — from the system that physically owns the event (the storefront for orders, the gateway for payments).
2. Export the destination IDs for the same window.
3. Anti-join: keep every source ID with no match in the destination. That set is your dropped records.
4. Reverse it once: destination IDs with no source match. Those are duplicates, test rows, or records imported under the wrong key — worth knowing too.
5. Spot-check three of the "dropped" IDs by hand in the destination before backfilling, to confirm they are truly absent and not just mis-keyed.

Backfill without creating duplicates

Now re-send the missing records — and this is where people turn a drop into a double. Re-pulling a window almost always re-sends records that already landed, so the backfill has to be idempotent: keyed on the source ID so a record that already exists is updated or skipped, never inserted twice. Both major payment platforms give you a clean way to pull the gap. Stripe lets you list undelivered events (GET /v1/events with delivery_success=false; events are retained for 30 days) and dedupe on the stable evt_ id. Shopify’s reconciliation jobs fetch everything updated since the last run. Use the platform’s own ID as the dedupe key and a re-run is safe to repeat.

Make it a standing check, not a Saturday fire drill

You found this batch because you happened to look. The point is to stop depending on happening to look. Run the count-and-control-total check on every sync window automatically, and treat any mismatch as an exception to investigate — the same exception-first discipline good auditors expect to see. Keep the webhooks, but stop trusting them alone: Shopify is explicit that a periodic full pull is the safety net under event delivery, not an optional extra. The trap is the pitch one r/Accounting poster called out after counting seven apps wired into their books — "the pitch for every single accounting adjacent tool is the same, seamless QuickBooks integration, automatic sync. Set it and forget it". Set it and forget it is exactly how records go missing for three weeks. Set it and reconcile it.

Plenty of tools will do the parsing for you — A2X and Synder for marketplace and gateway settlements, NetSuite connectors like Celigo at volume — and one Square user said the only stable pattern they found was to "sync one Sales Receipt per day per location instead of individual orders" and let a clearing account absorb it. All reasonable. None of them remove the need for the completeness check, because every one of them is still a copy of someone else’s data, and copies drop rows. This is the same lesson as reconciling Shopify orders against your ERP or matching your OMS against the source of truth: whatever moves your records, count them after. And it is the record-level cousin of inventory drifting out of sync across channels — there the counts disagree; here the rows are gone entirely.

← All writing