The Integration Dropped 1,400 Records and We Found Out Ninety Days Later

The quarter did not add up, and it took a week to figure out why. The billing system showed 1,400 more active subscriptions than Salesforce did. The integration that synced subscriptions into the CRM had a green light on every dashboard, no errors in the logs, no failed jobs. It had been quietly skipping records for about ninety days because a required picklist value on the CRM side had been renamed, so any record carrying the old value hit an unhandled mapping case and got dropped without an error. The integration reported success because from its own point of view it had processed every record it chose to process. The 1,400 it skipped were never in its count.

That is the failure mode of an integration with no reconciliation: it can only tell you about the records it knows it touched. It cannot tell you about the ones that fell out of the pipe, because from inside the pipe they were never there. A green dashboard means “the job ran,” not “the data is correct.” The fix is to build reconciliation as a first-class part of every integration, so the question “does the source still equal the destination” gets asked automatically, every day, and a divergence pages someone the morning it happens instead of surfacing when the quarter fails to close.

A job status is not a data check

Every integration platform reports job status, and every team mistakes it for correctness. Job status answers “did the run complete without throwing,” which is a different question from “does the destination now match the source.” The dropped records did not throw. A record with an unmapped picklist value that gets silently skipped, a batch that times out and gets marked partial-success, an upsert that matches on the wrong external id and updates the wrong record, all of these leave the job green and the data wrong. The only way to catch them is to compare the two systems by count and by value, independent of what the job says about itself. Reconciliation is that independent comparison.

Three levels of reconciliation, cheapest first

Reconciliation is not one thing; it is a ladder you climb as the data matters more. The cheapest check is a count reconciliation: does the source have the same number of records as the destination, filtered to the same scope? It catches wholesale drops like the 1,400 and costs one query on each side. Above that is a checksum or hash reconciliation: sum a key numeric field (total ARR, count by status) on both sides and compare, which catches value drift that a count misses. The most thorough is a row-level reconciliation: pull the set of external ids from both systems and diff them, so you get the exact records that are missing or extra, not just that a gap exists. Run the count check every sync, the checksum daily, and the row-level diff on demand when the cheaper checks flag a divergence.

The count check that would have caught it in a day

The cheapest rung is a scheduled job that counts the source and the destination on the same scope and alerts when they diverge beyond a tolerance. In Salesforce this is a scheduled Apex or Flow that queries the CRM side, calls the source system for its count, and posts to Slack or creates a case when the delta exceeds a threshold. The whole thing is a few lines.

// Scheduled daily. Compares CRM active-subscription count to the billing
// source count and raises an alert when they drift past tolerance.
Integer crmCount = [
    SELECT COUNT() FROM Subscription__c WHERE Status__c = 'Active'
];
Integer sourceCount = BillingApi.activeSubscriptionCount(); // callout
Integer delta = Math.abs(crmCount - sourceCount);
if (delta > RECON_TOLERANCE) {
    Recon_Alert__e evt = new Recon_Alert__e(
        Object__c = 'Subscription__c',
        Crm_Count__c = crmCount,
        Source_Count__c = sourceCount,
        Delta__c = delta
    );
    EventBus.publish(evt); // platform event → Slack / case / on-call
}

The worked example: what the row-level diff finally showed

Trust the green light versus verify the data

Here is how I build reconciliation into every integration

The integration was never broken in the way anyone was watching for. It ran, it succeeded, it reported green, and it silently dropped 1,400 records for a quarter because a picklist got renamed and nobody told the mapping table. A job status tells you the pipe ran; only reconciliation tells you the same water came out the other end. Build the count check into every sync from day one, add a daily checksum, keep the row-level diff a query away, and route divergence to a human who is awake. Then the next silent drop is a Slack message on a Tuesday morning, not a missed quarter and a week of forensics. The reconciliation instinct is the same one behind designing CPQ that ties out to finance: trust the numbers only when two independent systems agree, and check that they agree on purpose rather than by luck.