The Format Checks Out but the Numbers Look Wrong
In one line
To say that data that passed the validator is strange, you need grounds, and those grounds are not a single value but a distribution. And what a distribution gives you is not evidence but a lead.
Why this was needed
The schema is right, the column count is right, and the missing values are as the rules say. The validator is green. Yet this month's sales look off somewhere. If someone asks where it is off, you have nothing to say.
What you can do at this point is build a table that describes the data itself. For each column, what the type is, what percent is empty, how many different values there are, how many characters the values usually are in length, what the most common value is and what percent of the total it accounts for. We call this table a profile.
A single profile alone makes it hard to tell whether something is strange. This is because strangeness shows up not as an absolute value but as a difference from last time. So you keep the profile for each delivery and compare.
How it works
What to extract from one column is roughly this.
| Item | What it catches |
|---|---|
| Missing rate | The counterpart system has started failing to fill in a field |
| Unique ratio | The same value has started repeating in a field that should be an identifier |
| Value length distribution | The area code dropped out of phone numbers, or the number of digits of a code changed |
| Mode and its share | A single value suddenly became a third of the total |
| Minimum and maximum | The unit changed or outliers came in |
The value length distribution is especially cheap and works well. Even for values that pass the format check, if the length differs, the producer has changed, and if you pull out the few cases that deviate from the modal length and look at them directly, the cause is usually visible right away.
Next is comparison between deliveries. The simpler the rules, the better. Did the missing rate move by more than 5 percentage points, did the unique ratio move by more than 0.2, did the modal length change, did the share of the mode move by more than 10 percentage points. The thresholds have to be set differently for each dataset, and writing down which values you used matters more than the thresholds themselves.
The last is the first-digit distribution. For naturally gathered numbers, the first digit shows up as about 30% for 1, about 18% for 2, and about 5% for 9. This observation is called Benford's law. The standard way to compare an observed distribution against an expected distribution is a goodness-of-fit test, and the chi-square goodness-of-fit test in the statistics handbook of the U.S. National Institute of Standards and Technology writes out that procedure. This lab judges more simply, by a single maximum difference from the expected ratio — not to replace the test, but to use it only as a yardstick for picking where a person should look. Numbers that a person made up by hand do not follow this shape well — they cluster at particular digits, like the 40,000s or 50,000s.
What it looks like in the field
First, you use Benford as evidence. This is the most dangerous mistake. That the first-digit distribution is off means "a person should look at that column", not "someone tampered with it". There are many reasons a distribution is off besides tampering — if the price list has a few fixed prices, if there is a rounding rule, or if there is a minimum order amount, that alone throws it off.
Second, you apply it to data where it does not work. For Benford to hold, the values have to be spread across several orders of magnitude. A column whose scores lie only between 100 and 130 has a first digit that is effectively just 1, so even though the data is honest, it deviates greatly from the expected distribution. So you must judge applicability first — are there enough cases, and does the ratio of maximum to minimum cover several orders of magnitude? A Benford run without this judgment keeps reporting perfectly fine data, and then nobody reads those reports.
Third, you judge by a single signal. What is really usable is overlaying signals. If in the same column the share of the mode jumped, the unique ratio dropped, and the first-digit distribution was also off, those three are likely to point to the same event. A column caught by only one and a column caught by three are handled differently.
Fourth, you throw out the lead and finish. The last line of the report must not be "what is strange" but "what to check next". Which staff member the hand-written-looking amounts are concentrated on, and which system the phone numbers with changed digit counts came from — writing down the places to look at next is part of this job.
What really matters in practice
- Keep the profile for every delivery. Strangeness shows up not as an absolute value but as a difference.
- Write down the thresholds. A warning without a threshold cannot be reproduced, and a threshold that is not written down cannot be fixed by the next person.
- Judge applicability first. A test run on data where it does not work makes warnings cheap.
- Speak of leads and evidence separately. And write down the place to look next along with it.
What you will do in the next lab
You build three months of deliveries of vouchers with the same shape. Two months are ordinary, and in one month the format is all right but the values are strange. You extract a profile per column, pull out the values that deviate from the value length distribution and look at them directly, and judge the distribution change between deliveries by thresholds. Then you measure the first-digit distribution and suspect signs of tampering, and put in a column with a narrow value range as a counterexample so that the code itself says when Benford does not apply. In the end you overlay several signals and leave a list of investigation targets and what to check next. The grader makes a different delivery each time, actually runs your tool, and checks the answers.