In October 2020, at the height of the pandemic, Public Health England lost track of nearly sixteen thousand positive COVID cases. The data was not hacked or deleted. It was quietly dropped because a file used to move case records between systems was an older spreadsheet format with a hard limit on the number of rows it could hold. When the data went past that limit, the extra rows simply did not come through, and nobody noticed until the totals stopped adding up. Almost sixteen thousand people who should have been contacted and told to isolate were not, because of a row limit in a spreadsheet.
It is the most consequential example I know of a problem that quietly afflicts almost every organisation running cross-platform dashboards. The numbers on the screen are wrong, and nothing tells you. Reporting systems that pull data from a customer database, a booking platform, and a payment system into one view do not usually fail loudly. They fail in the joins, the encodings, and the definitions underneath, and the chart looks perfectly healthy while it lies to you.
After building and repairing a number of these systems, I have come to run the same three checks every time, in the same order. I call it the JVS Audit, for Join, Validate, Standardise. It is deliberately tool-agnostic. It works whether your dashboard sits on top of a data warehouse, a spreadsheet, or a business intelligence tool, because the failures it catches live below all of those.
Why this gets worse the moment you add AI
This used to be purely a reporting problem. Now it is an everything problem, because the same flawed tables increasingly feed models and automated decisions. If your join is silently multiplying revenue, a forecasting model trained on that table learns from inflated numbers. If a collation mismatch is dropping a chunk of your customers, any model trained on the remainder is learning from a biased sample it does not know is biased. The dashboard at least had a human looking at it who might eventually sense that something was off. An automated pipeline has no such instinct. It ingests the corrupted numbers and acts on them at machine speed. Getting the foundations right has gone from a matter of clean reporting to a precondition for trusting anything you build on top of the data.
Layer one, Join: the silent row multiplication
The most common way a dashboard starts lying is a join that multiplies rows. Suppose you join a table of customers to a table of their orders. The relationship is one-to-many. A customer with five orders now appears five times in the joined result. If you then sum revenue, or count customers, across that joined table, your totals are inflated, sometimes wildly, and the inflation is uneven because it depends on how many orders each customer happens to have. The chart looks plausible. It is simply wrong.
The reason this survives so long is that nothing errors. The query runs, numbers appear, and they are in roughly the right ballpark. The way to catch it is to refuse to trust a join until you have checked what it did to your row count.
A simple cardinality check like this, run as part of building the report rather than as an afterthought, turns an invisible problem into a loud one. If the row count balloons and you did not expect it to, you have found your bug before it reached a single chart.
// a join should not balloon your row count without a reason
const before = await count('customers');
const after = await count('customers_joined_orders');
if (after > before * EXPECTED_MAX_FANOUT) {
warn('this join is multiplying rows');
}The fanout check above is the cheap version. The thorough version is to know, before you join, what relationship you expect between the two tables, one-to-one, one-to-many, or many-to-many, and to assert it. If you believe a join is one-to-one and the row count grows at all, the join is wrong and you want to know immediately, not after the number has reached a board paper. Encode the expectation as a check that runs every time the report is built, and a whole class of inflated metrics simply cannot survive to production.
Layer two, Validate: the records that vanish across a boundary
The second failure is subtler and, in some ways, more dangerous, because instead of inflating your numbers it silently shrinks them. When two systems store text using different collations or character encodings, a join on a text key, an email address or a name, can fail to match records that look identical to a human eye. The keys differ by an invisible detail, a trailing space, a case rule, an encoding of an accented character, and the database treats them as different values.
I once traced a reporting discrepancy to exactly this. A large share of paying customers in one system appeared to have no matching record in the customer database. The records existed. The join simply could not see them, because the keys did not match across a collation boundary. The data was all there. The report was missing most of it. This is the same family of failure that cost Public Health England its sixteen thousand cases. The data existed and the pipeline lost it in transit.
The defence is to validate match rates, not just whether the query runs. When you join two systems on a shared key, measure what fraction of records actually found a match. If you join a hundred thousand paying customers and only sixty thousand match, that forty percent gap is not a curiosity to investigate later. It is the report being wrong right now.
There is a particularly nasty version of this when one system treats text as case-sensitive and another does not, or when one trims whitespace and another preserves it. Two records that a person would read as the same customer fail to join, so that customer appears in one report and not another, and the two reports disagree forever with no obvious cause. The defence is the same in spirit: normalise keys to a single canonical form before you join, lower-cased, trimmed, with a consistent encoding, and then measure your match rate so any drop is visible. A match rate is a number you can put on a monitor. A vague sense that the data feels off is not.
Layer three, Standardise: one definition, used everywhere
The third failure has nothing to do with the data and everything to do with people. It is the case where two parts of the business compute the same metric in two different ways. I once had a dashboard that said twelve items were pending while another page in the same system said seven. Both queries were correct. They simply defined pending differently. One counted items flagged for a decision, the other counted items blocking downstream work. Each definition was reasonable. The disagreement between them quietly eroded trust in everything the dashboard showed.
The fix is a single authoritative definition for each metric, written down once and reused everywhere, rather than re-implemented in each report by whoever happened to build it. The hard-won lesson here is that agreement is more valuable than accuracy. A metric that is computed one consistent way across the whole business, even if you might quibble with the definition, is far more useful than a metric that is computed three subtly different ways, because the moment people see the same word mean two numbers, they stop believing any of it.
The practical form of this is a small registry, even just a documented file, that maps each business metric to exactly one definition and one query. Reports do not re-derive what active customer or monthly revenue means. They reference the one definition. When the definition needs to change, it changes in one place and every report moves together, rather than drifting apart as different people patch different dashboards over the years. The registry is boring infrastructure, and it is among the highest-leverage things you can build for trust in your numbers.
Running the JVS Audit
None of these three checks is sophisticated. That is the point. Cardinality checks catch joins that multiply. Match-rate validation catches records that vanish across an encoding boundary. A single source-of-truth definition catches metrics that disagree with themselves. Run all three, in that order, as part of building any cross-platform report, and you close off the three commonest ways a dashboard betrays the people relying on it.
In practice the audit becomes a short checklist you run whenever you build or change a cross-platform report.
- State the expected cardinality of every join and assert it. If the row count changes in a way you did not predict, stop and find out why.
- Measure the match rate of every cross-system key. A low match rate means records are vanishing across an encoding or collation boundary.
- Point every metric at one written definition. If two surfaces show the same metric, they must compute it from the same source.
- Re-run all three checks on a schedule, not just at build time, because data drifts and integrations change underneath you.
Public Health England’s lost cases were not a data science failure. Nobody’s model was wrong. It was a plumbing failure, the kind that hides one layer below the dashboard where nobody thinks to look. The JVS Audit is simply the discipline of looking there on purpose. Check that your joins are not multiplying, validate that your keys actually match across systems, and standardise every metric to one definition. Do that, and your dashboard stops being a confident liar and becomes something you can bet a real decision on.
Sources
Public Health England COVID-19 case reporting data loss, October 2020, widely reported.
Approximate figure: just under 16,000 cases, attributed to a spreadsheet row limit.