Agree the definition before trusting the dashboard
The challenge
Reconciling business definitions and transaction references across operational systems.
An anonymised engineering account. Customer identities, operating figures and engagement dates are withheld. The focus is the problem, design decisions and validation, rather than a claim of commercial improvement.
A business can have several internally consistent reports that disagree. An ERP may recognise a posted transaction, a commerce platform may show an accepted order, and a marketing tool may attribute a purchase using its own window. Copying these values into a shared dashboard does not resolve the different definitions.
The engineering requirement is to preserve lineage and explain differences before presenting a single view. Refunds, cancellations, time zones, currency treatment and missing transaction references all need explicit handling.
The approach
Create a definition register
Agree the meaning of each reported measure, its source, its date basis and the person or function responsible for approving it. Keep an operational order measure separate from a financial measure when they answer different questions.
Preserve transaction lineage
Retain stable source references and the relationship between an order, fulfilment, refund and financial record. Use controlled mappings for customer and product references. A record that cannot be matched belongs in an exception process, not an unexplained adjustment.
Build from the correct grain
Identify whether a dataset represents an order, a line, a payment or a refund. Aggregate only after joins have been checked for duplication. Keep adjustments and deleted or cancelled records visible so a later refresh does not silently change the interpretation of a previous report.
Reconcile before presenting
Compare source and destination populations using a consistent cutoff. Investigate unmatched records, repeated keys and differing amounts. Explain legitimate differences such as timing or attribution rather than forcing every report to display the same number.
Make reporting changes accountable
Version the extraction and transformation rules. Document refresh timing and late-arriving data. Give changes to a metric definition an approval path and make the impact on existing reports visible.
Design outcome and validation
The design produces a traceable reporting foundation: an agreed definition, a known source and a route back to the underlying transaction. A single source of truth means governed interpretation, not the disappearance of every legitimate difference between systems.
Validation evidence to retain
Use a controlled sample covering completed orders, cancellations, partial refunds and late adjustments. Verify joins at their intended grain and explain every unmatched record. Confirm that a repeated import does not duplicate results and that a refresh uses the documented cutoff.
Retain definitions, lineage, reconciliation exceptions and approval records. No customer revenue, reporting improvements, processing volumes or other business figures are disclosed.