When two systems report different counts: a reconciliation method

Reference article · 6 min read

A program manager reports one caseload figure. Finance pulls a different figure from the warehouse. The quarterly submission to the state shows a third. All three people are competent, all three queries ran without error, and none of the numbers is obviously wrong. This happens routinely in agencies that operate a transactional system of record, a reporting warehouse fed by scheduled extracts, and published reports assembled on a calendar.

Most count disputes are not caused by corrupted data. They are caused by three figures answering three slightly different questions. Reconciliation is the discipline of making the questions identical, then explaining whatever difference survives. The method below is repeatable, can be handed to an analyst as a checklist, and produces a written result that keeps the same argument from being relitigated next quarter.

Capture the numbers and their provenance first

Before anyone investigates, write down each figure with enough detail that another person could reproduce it. For each number, record the value, the system or artifact it came from, the person who produced it, the named query or report that generated it, the timestamp of the run, and every parameter that was entered. Screenshots are acceptable evidence; recollections are not.

This step alone resolves a meaningful share of disputes, because it often reveals that two people ran the same report with different date parameters, or that one figure came from a saved spreadsheet nobody has refreshed in months.

Fix the as-of time

A count is a statement about a population at an instant. If the instant is not fixed, nothing else in the method works.

• Extract latency. Warehouse tables usually reflect the source system as of the last successful load, not the present moment. Confirm when the load ran and whether it succeeded.
• Late and backdated records. Source systems accept entries after the fact and allow edits with effective dates in the past. A count run today for last month can differ from the same count run last month.
• Time zone and day boundary. Confirm whether the cutoff is local time, coordinated universal time, or a business day that closes at something other than midnight.
• Snapshot versus current state. Ask whether the warehouse retains history. If it stores only current values, it cannot reproduce a past count, and that limitation is itself a finding to document.

Choose a single as-of instant, state it in writing, and require all parties to reproduce their figure against it.

Align the definition, then the filters

Write a one-page definition that all parties sign off on before any query is rerun. It should settle the unit being counted, such as distinct people, open cases, or submitted applications. It should settle which statuses are in scope, including pending, withdrawn, voided, and administratively closed records. It should settle the date basis, which is usually the hardest item: event date, data entry date, and effective date routinely differ, and a report that uses one while the warehouse query uses another will never agree.

Then align the filters: organizational unit, geography, program or funding stream, and any exclusion of test, training, or duplicate records. Deduplication deserves its own line, because the rule that collapses two records into one person is a policy choice, not a technical detail.

Compare at the record level

Totals cannot explain themselves. Export the identifiers behind each figure rather than the counts, then join them on a stable key.

1. Produce three buckets: records present in the first source only, the second source only, and both.
2. Within the records present in both, compare the specific fields that drive the agreed filters, such as status, date, and organizational unit.
3. If the volume is too large to inspect directly, aggregate by month, office, or status first, find the bucket where the gap concentrates, then drill into that bucket.

If the reporting system cannot export identifiers, note that as a constraint on the agency's ability to audit its own published numbers.

Classify every difference and name an owner

Assign each explained record or group of records to exactly one category, and assign a person responsible for closing it:

• Timing. The record existed in one system and not yet in the other at the as-of instant.
• Definition. The systems apply different rules for the unit, status, date basis, or deduplication.
• Parameter. Someone ran the report with a different filter than agreed.
• Transformation defect. The extract, load, or report logic drops, duplicates, or misclassifies records.
• Source data defect. The underlying record is wrong or incomplete.
• Access restriction. Row-level security or permissions hid records from one requester.

The first three categories are explanations. The last three are defects and need a remediation ticket, not a memo.

Record the accepted explanation

Close the exercise with a short reconciliation memo stored alongside the published report. It should contain the as-of instant, the agreed definition, each figure, an itemized list of differences with counts and categories, the defects opened, and a plain statement of which figure is authoritative for which purpose. Naming the authoritative figure matters: a number correct for a federal submission may be the wrong number for internal workload planning, and both can be right for their own use.

Rerun the same comparison on the next cycle. A reconciliation that holds steady becomes a routine control. A reconciliation that drifts is an early warning that something changed in an extract or a report definition.

What to ask vendors and require in agreements

When procuring or renewing a reporting system, warehouse, or line of business application, ask for the following and reflect the answers in the agreement:

• Documented definitions and logic for every delivered report, in readable form rather than only as compiled queries.
• Whether the system retains history sufficient to reproduce a count as of a past date, and for how long.
• The extract or load schedule, expected latency, how failures are detected, and how restatements of prior periods are handled.
• Data lineage from source field to published figure, kept current as the system changes.
• Agency rights to export record-level data with identifiers, on demand and at exit, in a nonproprietary format.
• Advance notice and version history for any change to a report definition or transformation.
• Named support for reconciliation requests, with a response expectation, so analysts are not dependent on informal goodwill.

Procurement terms, records retention duties, and reporting requirements vary by jurisdiction and program, and they change. Confirm specifics with the official program guidance, your records officer, and your counsel before relying on them.

HQT works with state and local agencies on data engineering, reporting, and systems integration, including the lineage and definition work that makes counts reconcilable.

Frequently asked questions