Before building the dashboard, agree the reporting contract

Two dashboards show different totals for the same period. Both jobs completed successfully. The disagreement is not necessarily a broken query: one report may count submitted orders while another counts settled payments. Technical execution can be correct while the business interpretation remains incompatible. A reporting contract makes those differences explicit before the pipeline becomes harder to change.

A metric is a definition with an owner

Start with one decision the report supports. For a fictional operations team, the question might be how much approved expenditure remains unfulfilled. That requires definitions for approval, fulfilment, partial execution, cancellation and the reporting cut-off. The finance owner must decide the meaning. The engineering team records how source fields and transformations express it.

This is a sample contract, not an accounting policy. Whether a cancellation changes a prior period, creates a current-period adjustment or is prohibited depends on the organization’s rules. That decision should not be buried in an SQL expression owned by nobody.

Keep event time, ingestion time and publication time distinct

A branch records fulfilment on Monday but reconnects on Wednesday. An ingestion timestamp cannot establish when the business action happened. Retain the business timestamp and enough source metadata to trace its origin. Record when the platform received the information and when a report was published. A report can then state whether it includes late arrivals and which revision it represents.

Timezone and currency treatment are also part of the definition. Adding amounts in different currencies without an agreed conversion policy creates an apparently precise but meaningless total. Preserve source currency, conversion basis where applicable and the authority that approved the policy. Do not infer official rates or accounting treatment from a library default.

Reconciliation is a control with an explanation

Choose a known period and compare source coverage, counts, amounts, duplicates and exclusions. A simple equation can help: eligible source records = included records + identified exceptions, provided those sets are mutually exclusive and the rules define eligibility. An unexplained residual is an investigation item, not a number to round away.

For one reporting cut-off:
  list expected sources and arrival status
  identify eligible records by the agreed definition
  separate accepted records from assigned exceptions
  compare counts and amounts at the same grain
  publish coverage, cut-off, revision and unresolved exceptions

Preserving raw inputs is useful when permitted, but retention is not unlimited by default. Source identifiers, processing versions and access rules should be sufficient to investigate a result while respecting classification and deletion requirements. A database foreign key can enforce a reference; it cannot decide whether the referenced business fact means what finance intended.

Design correction and replay before the first incident

A repeated file should not double the total. A corrected record should not disappear behind a permanent deduplication rule. Define the business key, the version policy and whether transformations replace, append or supersede prior outputs. Replay a small known dataset twice, introduce a late correction, then compare the outputs with the expected contract.

Keep rejected records visible with a reason and responsible owner. A pipeline that drops invalid rows and publishes “success” has transferred the problem to the report reader. Consider distinguishing complete, incomplete and withheld publications. The institution decides which exceptions permit publication and which block a critical report.

Publish operational meaning alongside the chart

• Metric definition and named owner.
• Reporting period, cut-off and publication revision.
• Expected sources, received sources and missing coverage.
• Late-arrival and correction policy.
• Unresolved exceptions and the route for investigation.

A first engagement can produce a source-to-report map, one agreed contract, a representative reconciliation and a prioritized remediation plan. A larger warehouse or streaming platform may follow when scale and analytical needs justify it. The immediate value is that a decision-maker can ask “where did this number come from?” and receive a reproducible answer.

Technical references

• PostgreSQL: data definition constraints