Finance says the number was 4,182. Operations says it was 4,310. Both people pulled their report this morning, both reports came from systems the company paid for, and neither can explain the other's figure. The meeting stops being about the business and becomes about whose report is right. Somebody suggests the integration is broken. Somebody else suggests a data warehouse. An hour disappears and the decision the number was supposed to inform gets pushed to next week.
This is one of the most common problems in operational reporting and one of the most misdiagnosed. The instinct is to look for a defect, because a defect has an obvious owner. In practice a genuine bug accounts for a small minority of these disputes. Far more often both numbers are computed correctly from the data each system holds, and the difference comes from decisions nobody realized they were making.
Two correct answers to two different questions
Every report is the answer to a question, and the question has more parts than the report title suggests. "Jobs completed in June" contains at least four hidden decisions: what counts as a job, what counts as completed, which timezone June is measured in, and what happens to a job that was completed in June and cancelled in July. Two teams building the same report will each make those four decisions in a way that is obvious to them and invisible to everyone else.
This matters because it changes what you look for. If you assume a bug, you go and read code, and you will not find anything, because the code is doing what it was asked to do. If you assume a definitional difference, you compare the two questions, and the answer usually surfaces in under an hour. Fall back to the bug theory only after the definitions have been shown to match.
Different definitions
Definitional differences cause more variance than everything else combined. The word that does the most damage is revenue, because it has at least three legitimate meanings and every department has a favorite. Sales means booked, and the number belongs to the month the deal closed. Finance means invoiced, which may be a month later and may be split across several periods. Cash flow means collected, which may be later still and may never happen. Nobody there is wrong. They are answering three different questions with the same word.
Cancellations are the second definitional trap and a more subtle one. If a job is cancelled in July, does June's completed-jobs figure change? One system excludes the job retroactively, so June's number silently drops each time the report is re-run. The other records the cancellation as a July event and leaves June alone. Both are defensible. Only one lets you re-run a report and get the same answer.
- Revenue: booked at signature, invoiced at billing, or collected at payment
- Active customer: has an open contract, has ordered in the last 90 days, or is flagged active in a CRM field somebody maintains by hand
- Completed job: the technician marked it done, a supervisor approved it, or it was invoiced
- Headcount: employees on payroll, including or excluding contractors, on the first or the last day of the period
- Unit price: before or after discount, before or after tax, before or after freight
Test for this first, because it is cheap. Ask each side to write one sentence describing exactly what their number counts. If the two sentences differ at all, you have found the cause and no further investigation is required.
Time boundaries, timezones, and cut-off
Once definitions match, look at time. Systems disagree about when a period starts and ends far more often than anyone expects. One system stores timestamps in UTC and reports in UTC. Another stores UTC and converts to the user's local timezone before grouping by day. For a business on Eastern time, that difference moves every event between 7pm and midnight into the following day. On a normal Tuesday nobody notices. At a month boundary it moves several hours of activity into the wrong month, and the two reports will differ by roughly one evening's volume (reliably, every month, in the same direction).
Cut-off timing is the related problem. An operations dashboard is often read at 6am against a replica refreshed at midnight, while the finance report is run after close against live data that includes the whole working day. Both are labelled with the same date. The operations number is a day behind and never catches up, because it is re-read fresh each morning rather than compared against a stored snapshot. Any comparison between a nightly extract and a live query needs the extract time stamped on the page.
Boundary handling adds a smaller but persistent gap. Inclusive versus exclusive end dates, and date fields stored as text and compared lexically, each cost you a day at the edge of the period. Check for these when the variance is small, stable, and always in the same direction.
Missing, duplicated, and quietly altered records
The next tier of causes is structural. A record exists on one side and not the other, or exists twice, or exists in a state the other system does not model.
Sync failures nobody saw
Integrations fail quietly. A handful of records are rejected for a validation error, land in a dead-letter queue nobody drains, and the run reports success because the batch completed. The downstream system is short by forty records and neither interface says so. The signature to look for is a variance that grows over time rather than staying flat.
Soft deletes and amendments
One system marks a record deleted and keeps the row. The other removes it. If the sync only reads rows and does not check the deleted flag, or only sends inserts and updates and never deletions, the two systems drift apart permanently. Amended records behave the same way: an invoice corrected after the fact may be a new version in one system and an in-place edit in the other, and the totals will diverge depending on whether the original is still counted.
Currency and duplicates
Multi-currency figures diverge when the two systems convert at different rates or on different dates: transaction date in one, month-end rate in the other. The difference is proportional rather than fixed and moves with the market. Genuine duplicates are last on the list because they are least common, but they happen: a retry without an idempotency key creates a second record, and one system deduplicates on read while the other does not.
Symptom, likely cause, how to confirm
The shape of the variance narrows the search considerably. Match the symptom before you start reading data.
| Symptom | Likely cause | How to confirm |
|---|---|---|
| Gap is a fixed, round number and never changes | One side filters a category the other includes, such as internal, warranty, or test records | Group both reports by category and compare the breakdowns, not the totals |
| Gap appears only at month and quarter boundaries | Timezone conversion, or an inclusive versus exclusive end date | Re-run both for a single mid-month day, then for the last day of the month |
| Gap grows steadily week over week | A sync is dropping records and failing silently | Compare counts by created date and find where the two counts separated |
| Counts match but values differ | A calculation rule, a discount or tax treatment, or a currency conversion difference | Join on the business key and list the records where amounts disagree |
| Values match but counts differ | Duplicates on one side, or zero-value records included on one side only | Group by business key and look for keys appearing more than once |
| A historical report changes when re-run | Cancellations or amendments applied retroactively rather than as dated events | Save today's output, re-run the same period next week, compare row by row |
| Operations is always one day behind finance | The dashboard reads a nightly extract while the report queries live data | Compare the extract timestamp against the report run time |
How to run the reconciliation
The failure mode of these investigations is scope. Somebody proposes reconciling the whole reporting suite, the work expands, and it is abandoned after two weeks with nothing resolved. Take one metric and finish it.
- 1
Pick one metric and one period. A single month is enough. Do not start a second metric until the first is settled.
- 2
Write the definition down as one sentence and have both sides agree to it before any data is pulled. Name the entity, the state that counts, the date field, and the timezone.
- 3
Compare record counts before values. A count difference and a value difference have different causes, and mixing them makes both harder to find.
- 4
Trace one single record end to end through every system, transformation, and report it touches. Most causes become obvious on the first record.
- 5
Bucket the variance rather than explaining it record by record. Group by category, date, status, and source until one bucket accounts for most of the gap.
- 6
Fix the definition first, the pipeline second. Correcting data without correcting the rule that produced it guarantees the same conversation next month.
- 7
Publish the agreed definition where both reports are read, with the metric owner's name next to it.
Publishing the definition is the fix
When the reconciliation finishes, the useful output is not a corrected spreadsheet. It is a written definition both sides accepted, stored where both reports link to it. Without that, the same disagreement returns the next time someone builds a report, and the investigation is repeated from scratch by people who were not in the room.
A definition worth publishing states what the metric counts, which date field determines the period, which timezone applies, how cancellations are treated, what is excluded, and who decides when the rule changes. That last item does most of the work. A metric with a named owner gets updated when the business changes; a metric without one drifts until it disagrees with itself again.
The reason this is worth doing is not tidiness. Two numbers for the same thing means nobody fully trusts either, so decisions get delayed and people build private spreadsheets they believe more than the system. One agreed definition removes that, and it usually costs less than a week of somebody's time.
Common questions
- Which system should we treat as correct?
- Neither, until you have compared the definitions. Once the definitions match, the system that owns the business event is normally the authority. That is the field system for work performed and the accounting system for money. Name that authority per metric rather than per system, because one system is rarely the right source for everything.
- Will a data warehouse fix this?
- It removes the mechanical causes, such as extracts refreshed at different times and reports written against different replicas. It does not fix a definitional disagreement, because the warehouse still has to be told whether revenue means booked or invoiced. Agree the definition first; the warehouse then makes it consistent everywhere rather than making the wrong answer authoritative.
- How large a variance should we worry about?
- Any variance you cannot explain, regardless of size. A stable 0.2% gap with a known cause is fine to live with. An unexplained 0.2% gap is a signal that something is behaving in a way nobody understands, and the size of it today says nothing about the size of it next quarter.
- How long does a reconciliation take?
- For one metric over one period, usually a few days of part-time effort spread across the people who own each system. Most of that is scheduling rather than analysis. Investigations that run for weeks have almost always expanded to cover several metrics at once, which is why the scope should be fixed before the work starts.
- The numbers matched last quarter and do not now. What changed?
- Something was added rather than broken. A new record type, a new status, a new business line, or a rule change on one side that the other side does not know about. Compare the two datasets by category and by created date, and look for a group that exists on only one side or a date after which the counts diverge.
- Can we just have one report instead of two?
- Sometimes, and it is worth asking. Often the two reports exist because two teams genuinely need different views of the same event, in which case consolidating them removes information rather than confusion. The goal is one definition per metric, not one report for everyone.