Data Reconciliation Checklist for Business Reporting

• what each metric means;
• which records belong in it;
• which system is the source of truth;
• when the data is expected to refresh;
• how duplicate or missing records are handled;
• how discrepancies are investigated;
• who decides whether a mismatch is acceptable or requires correction.

This is the purpose of data reconciliation.

It is not simply checking whether two screens show the same number. It is establishing a repeatable way to determine whether operational reporting reflects the intended underlying records.

1. Define the Metric Before Building the Dashboard

A label such as Open Requests sounds clear until different teams define “open” differently.

For each important metric, write a definition before implementing it.

Metric: Open Requests Definition: Requests in Submitted, Under Review, Waiting on Client, or In Progress status. Cancelled and Completed records are excluded. Time basis: Current state at report generation time. Source: Operational request system. Owner: Operations manager.

Once that definition exists, the report can be tested against it.

2. Identify the Source of Truth

A reporting workflow may read data from several systems.

Do not assume that the most convenient data source is automatically authoritative.

3. Document the Population Being Counted

Every total represents a population of records.

Define that population explicitly.

Monthly completed requests includes records whose completion timestamp falls between the first and last calendar day of the selected month in the reporting timezone.

That definition answers hidden questions about created dates, reopened records, cancelled items, and late imports.

4. Define the Reporting Timezone

Dates are a common source of discrepancies.

Define:

• storage timezone where relevant;
• reporting timezone;
• how date filters are applied;
• whether daylight-saving changes affect the reporting period;
• whether “today” means system time or business-local time.

The exact choice depends on the business. The important point is that the choice should be explicit.

5. Define Refresh Expectations

A dashboard does not always need real-time data, but users should know how current the data is supposed to be.

For each report, record:

• expected refresh method;
• expected refresh frequency;
• latest successful refresh time;
• what happens after a failed refresh.

Freshness should be visible when it materially affects interpretation.

6. Check Filters Carefully

A reconciliation mismatch may be a filter difference rather than a data problem.

Common examples include different date ranges, inactive-record rules, department filters, assignment filters, test data, cancelled items, or a leftover search filter.

Record the filters before investigating the underlying data.

7. Watch for Duplicate Records

Duplicates can enter reporting through repeated events, repeated exports, one-to-many joins, multiple identifiers, or retry behavior.

The reconciliation requirement should define what constitutes one unique business record.

8. Check Join Logic

Ask:

• what identifier connects the records;
• whether the relationship is one-to-one or one-to-many;
• what happens when the related record is missing;
• whether old identifiers can change;
• whether unmatched records should be excluded or flagged.

A technically valid join can still produce a misleading report.

9. Decide How Missing Data Is Treated

Possible outcomes include:

• exclude;
• show as unknown;
• show as unassigned;
• stop calculation;
• flag for review;
• use an explicitly defined fallback.

Avoid converting unknown data to zero unless zero is actually the correct business meaning.

10. Make Failed Refreshes Visible

A useful requirement might be:

The dashboard displays the last successful refresh timestamp. If the scheduled refresh fails, the data remains available but is marked as potentially stale until a later successful refresh.

The correct behavior depends on the operational risk, but the failure state should be explicit.

11. Choose Sample Reconciliation and Full Reconciliation Deliberately

Sample Reconciliation

Select a defined sample of records and verify that each reported value matches the underlying source.

Full Reconciliation

Compare complete populations or aggregate totals.

12. A Worked Reconciliation Example

Consider an illustrative operations dashboard.

It displays Open Requests: 284 while the operational source produces Open Requests: 287.

Step 1 — Compare Definitions

Definitions match.

Step 2 — Compare Time and Filters

Snapshot time and organization filter match.

Step 3 — Compare Record IDs

Three IDs exist in the source population but not the dashboard population.

Step 4 — Inspect the Missing Records

All three were created after the dashboard's most recent successful refresh.

Step 5 — Determine the Root Cause

The dashboard calculation is not the issue. Its refresh job failed earlier.

Step 6 — Decide the Correct Response

Possible remediation:

• restore the refresh process;
• show a stale-data warning;
• record the last successful refresh;
• alert the responsible team if the freshness threshold is exceeded.

This is an illustrative example, not a client case study.

13. Keep a Discrepancy Worksheet

If the cause is not yet known, record Root cause: Unknown — investigation in progress rather than guessing.

14. Assign an Owner for Each Type of Discrepancy

Different problems may belong to different people: metric-definition disagreement, missing source records, integration failure, incorrect mapping, duplicate records, or dashboard presentation error.

A useful reporting specification states not only how mismatches are detected, but who is responsible for deciding what happens next.

15. Separate Data Error From Reporting Error

Reconciliation should distinguish:

• source-data issue;
• transformation/mapping issue;
• integration issue;
• reporting/query issue;
• display issue.

This distinction makes remediation much faster.

16. Define Acceptance Criteria for Reporting

Examples:

Open Request count must equal the number of unique request IDs in the approved open-status population for the same organization and reporting snapshot.

The dashboard must display the timestamp of the last successful data refresh.

A failed refresh must not update the displayed refresh timestamp.

Completed Request totals must exclude Cancelled records.

A request with multiple documents must count once in request-level metrics.

17. Reconcile Before and After Changes

A report that works today can become incorrect after a source schema change, a new status, an integration change, a new business rule, an identifier change, a filter update, or a query optimization.

Preserve a known comparison set and rerun the reconciliation after important changes.

18. Reconciliation Is Different From API Integration Testing

A successful API call does not prove that the resulting report is correct.

Integration testing asks whether systems connect and exchange data correctly. Reporting reconciliation asks whether the correct population reached the report, was transformed correctly, is current enough, and produces the intended business numbers.

If your issue is primarily connectivity, data flow, permissions, retries, or endpoint behavior, the API Integration Planning Guide is the more relevant starting point.

If your issue is whether operational numbers can be trusted, reconciliation is the better frame.

19. A Reusable Reporting Checklist

Metric definition

What exactly does the metric mean? Which records are included and excluded?

Source

What is the source of truth? Which identifier connects related records?

Time

What timezone is used? How often should data refresh? What is the last successful refresh?

Transformation

Are statuses mapped? Are values aggregated? Can joins create duplicate rows?

Missing data

What happens to missing records and null values? Is unknown different from zero?

Validation

How is a sample checked? How is a full population compared?

Exceptions

What happens if refresh fails? Who investigates a mismatch?

Acceptance

What exact checks must pass before the dashboard is considered ready?

20. Reporting Should Explain Its Numbers

A useful business dashboard does more than display data.

Its important numbers should be traceable to clear definitions and identifiable sources.

For a practical example, review the Reporting and Management Dashboard solution pattern.

If the reporting workflow depends on moving information between systems, also review Systems and API Integration Services and the API Integration Planning Guide.

For reporting work itself, Northbridge's Business Reporting and Data Integration Services focus on metric definitions, source ownership, validation, refresh expectations, and the operational decisions the report needs to support.