Dirty Data, Broken Dashboards: A Field Guide to Detecting Data Quality Issues Early
The dashboard had been live for a month when the CFO noticed revenue was $1.2 million high. The cause took a day to find: a source extract had started double-shipping one branch's transactions, and nothing in the pipeline was positioned to notice. The error was ordinary. The discovery method - an executive's instinct - was the actual failure.
Data quality issues are inevitable; discovering them in production is optional. The difference is whether the pipeline checks its own work or leaves the checking to whoever reads the dashboard with the most scepticism.
This field guide covers the taxonomy of rot, the dashboard symptom each kind produces, and the prevention engineering that catches them before publication.
Key Takeaways
Quality issues are inevitable; production discovery is optional - the pipeline either checks its own work or the executive does.
Five rot types, five symptoms: completeness, uniqueness, validity, consistency and timeliness each break dashboards in a recognisable way.
Trust is a measurable property: visible reconciliation checks convert "believe me" into "see for yourself".
What Are the Five Types of Rot - and Their Symptoms?
Each quality dimension fails with a signature a practised eye learns to recognise on the dashboard itself.
Completeness. Missing rows or values - the silent NULL join that drops records, the feed that skipped a day. Symptom: totals mysteriously low, a branch flatlining, last Tuesday missing from the trend.
Uniqueness. Duplicates - the double-shipped extract, the customer entered three ways. Symptom: revenue inflated, counts too high, the same order visible twice in drill-through.
Validity. Values outside their rules - negative quantities, dates in 2099, type drift turning numbers into text. Symptom: measures erroring or silently excluding rows, axis ranges absurd, blanks where figures belong.
Consistency. The same fact disagreeing across sources - customer hierarchies that differ by system, codes that changed meaning mid-year. Symptom: numbers that reconcile to neither source, slicers producing different totals than their sum.
Timeliness. Data older than its label - the refresh that succeeded against a stale extract. Symptom: the dashboard says today, the numbers say Thursday, and trust says nothing ever again.
Why Does Discovery in Production Cost So Much More?
Because the error ships with compounding interest: decisions made on the bad number, the credibility discount on every future number, and an investigation running backwards through a pipeline that kept no evidence. The $1.2 million duplicate cost a day to find; the month of decisions made on inflated revenue cost what it cost; and the dashboard spent the next quarter being double-checked by everyone who remembered.
The asymmetry is the argument: the same check that costs minutes in the pipeline costs days in production and credibility forever after. Quality engineering is cheap precisely where it matters.
What Belongs in the Pipeline's Own Checks?
Validation at the gate: row-count expectations per load (a feed delivering 40 per cent fewer rows than its trailing average should halt, not load); duplicate detection on business keys before merges; type and range assertions on critical columns; and referential checks that every fact row finds its dimension. In Power Query terms these are deliberate steps - tests written as queries - that fail loudly instead of loading quietly.
The design principle: checks belong as far upstream as the rot can occur, and their failures must stop the line or flag it - never both pass silently. A refresh that completes against bad data is worse than one that fails, because failure gets investigated and quiet corruption gets presented. Where the estate has a warehouse, the same tests promote upstream into it - run once at landing, protecting every consumer rather than one model - which is the natural maturing path as foundations arrive, and standard scope within data analytics consulting engagements.
What Are Reconciliation Measures - and Why Do They Build Trust Fastest?
Measures that compare the model's totals to the source's own reports, continuously and visibly: model revenue versus ledger revenue, model order count versus the sales system's count, variance displayed and thresholded. They convert the trust question from rhetoric to arithmetic - the dashboard does not ask to be believed; it shows its reconciliation.
The pattern in practice: a small hidden-in-plain-sight quality page on the certified model carrying the reconciliations, their tolerances and their history; a variance breaching tolerance turns the indicator red and alerts the owner before the audience notices anything. Finance teams in particular convert quickly once the model reconciles to their ledger in public, every day - it is the single fastest trust-building device in reporting, and it costs an afternoon to build.
What Does the Exception Dashboard Do?
It gives the rot a place to be seen and owned: the unmatched fact rows, the duplicate candidates, the out-of-range values and the stale feeds, each listed with enough context to fix and a named owner per source. Quality stops being an abstract virtue and becomes a queue - reviewed weekly, trending visibly, and emptying as upstream fixes land.
The exception dashboard also changes the politics. Source-system owners who would dispute a quality "concern" act on a queue showing their system's twelve specific orphaned records. Evidence beats advocacy; the dashboard is the evidence, refreshed nightly. Over time its trend lines become the estate's quality KPI - and the justification for the upstream fixes that finally drain the queue at its source.
Where Should a Business Start This Month?
With the one report whose wrongness would cost most - usually the revenue or board pack - and four moves against it: a row-count gate on its feeds, duplicate detection on its keys, a reconciliation measure against its source of record, and a visible quality indicator on the report itself. One afternoon of engineering; the discovery-in-production risk on your most important number retired.
Then extend by blast radius: the next most consequential report, the shared dimensions every report depends on, and finally the warehouse-level promotion of the tests that proved themselves. Quality engineering adopted this way funds itself - each gate's first catch pays for the next gate. The same gates, incidentally, are the entry requirement for anything the business later wants from AI: models reasoning over your data inherit exactly the rot you tolerate, which makes today's quality engineering tomorrow's AI readiness - foundations Power BI consulting engagements increasingly build with that future explicitly in scope.
Who Should Triage the Exception Dashboard?
The data owner of each domain, on a published rota - not the BI developer who built it. Exceptions are business facts wearing technical clothes: only the customer-data owner knows whether two similar records are a duplicate or genuinely two clients. Routing triage to IT recreates the bottleneck the dashboard exists to remove.
Give the rota a service level - exceptions older than five working days appear in the owner's leadership report - and the queue stays honest. Quality checks that nobody is accountable for answering become wallpaper within a quarter.
Make Trust Inspectable
Every dashboard makes a silent claim: these numbers are right. Quality engineering makes the claim inspectable - gates that catch the rot, reconciliations that show their work, exceptions with owners and trend lines. The executive's scepticism, finally, has somewhere better to look than the bottom line.
Start with the report that matters most, this month. The next double-shipped extract is already in someone's pipeline - the only question is who finds it, the gate or the CFO.
