The Dashboard That Nobody Trusts
A retail chain's finance team publishes a weekly sales dashboard used by 40 regional managers. Complaints have grown: two regions show identical revenue figures for different stores, the "monthly total" occasionally drops when a day is added, and one region's numbers lag a day behind. Investigation finds that store transactions land in a staging table at varying times depending on each store's upload schedule; the dashboard queries the staging table directly; currency conversion is applied in the visualization layer using a manually maintained rate file; and a store that closed last year is still counted because its status flag was never updated. The data team proposes adding a nightly batch job and a data quality dashboard. Regional managers say they have started keeping their own spreadsheets, and the CFO is asking why the numbers do not match the general ledger.
1. Diagnose the root causes of the three reported symptoms and explain why the proposed nightly batch is only a partial remedy.
Read the worked answer
Each symptom maps to a distinct defect:
- Identical revenue for different stores suggests a join or key problem - likely records matched on a non-unique key, or a duplicated aggregation. This is a data modeling/integration fault, not a timing fault.
- Monthly total dropping when a day is added is a classic late-arriving data problem: the dashboard reads a staging table whose contents vary by upload schedule, so totals are not stable snapshots. It may also reflect a missing or incorrect date filter.
- One region lagging a day is directly explained by the variable upload schedule - the staging table is being treated as a system of record when it is not.
- The closed store is a reference data failure: the status flag was never maintained, so the entity dimension is stale.
- Currency conversion in the visualization layer with a manual rate file means the same figure can be computed differently by different viewers and cannot be reconciled to the ledger.
A nightly batch stabilizes timing, which helps the lag and the dropping total. It does not fix the join defect, the stale store flag, or the misplaced currency logic. Those require modeling and governance changes.
2. Propose a remediation plan with sequencing, and address the managers' shadow spreadsheets and the CFO's reconciliation concern.
Read the worked answer
Sequence by dependency, not by visibility.
- Establish a governed source of truth. Define a curated reporting layer built from staging, with documented transformation rules, rather than querying staging directly. Apply currency conversion in that layer using a versioned rate table with effective dates, so every consumer sees one number.
- Fix data quality at the source. Correct the store status flag and add validation rules: uniqueness on transaction keys, completeness checks on daily loads, referential checks on store IDs. Publish these as the data quality dashboard - but as a monitoring tool, not the fix itself.
- Stabilize timing. The nightly batch is appropriate here, with a defined cutoff and a late-arrival handling rule, so totals are reproducible.
- Reconcile to the ledger. Build a documented bridge between dashboard revenue and GL revenue, with known timing and classification differences itemized. Give the CFO that bridge, not an assurance.
Shadow spreadsheets. Treat them as a signal, not a discipline problem. Managers built them because the dashboard was untrustworthy. Publish a data quality score, a change log, and a clear definition of each metric; retire the spreadsheets by making the official source faster and more transparent than the workaround. Name an owner for each metric.
Governance. Assign data stewardship for store reference data and for the rate table, with review cadence, so the same decay does not recur.