Lesson 4 of 15 · Lab
Data quality dimensions and how they move a metric
Data quality means data that's fit for a stated measure, not just valid values. A file can load perfectly and still give the wrong answer. Judge quality against the metric, and describe faults by the standard dimensions: accuracy, completeness, consistency, timeliness, uniqueness and validity.
| Order | Amount |
|---|---|
| A-1 | 100 |
| A-1 | 100 |
| A-2 | 50 |
Every value is a clean number, and the total of 250 is still wrong if the metric says one row per order. The right answer is 150.
At fictional Vandelay Coffee Supply, Don Tremendous's Sales report says €15,850, against a metric that says €11,478. His total starts from two stacked invoice exports.
INV-1008 (€540) and INV-1010 (€900), both dated late August, are in both exports. Which bar falls when you keep one row per invoice, and by how much?
August, by €1,440. No other month has a copy to lose.
Keep one row per invoice
- Read this live chart in Vista, Oplema's browser app for dashboards, with Everything stacked chosen. Note August.
- Select One row per invoice.
- Compare the bars. Only one should move.
- Select Everything stacked again and find the No date bar. It's an invoice with
TBCfor a date.
The one bar that moves
August falls from €5,830 to €4,390. The total falls from €15,850 to €14,410.
Every euro has a cause and an owner
The duplicates' columns were all valid. No check on values alone could find them. Now tie every fault to euros and a person.
| Fault | Euros | Who decides |
|---|---|---|
| Same invoice in two exports | €1,440 | Sydney Swell, who sets the export window |
| Pending invoice counted | €1,300 | Sydney, who owns the status |
| June invoice counted | €1,200 | Sydney, who owns the period |
| Starhopper's rebate ignored | €432 | Don, who owns the rebate |
They add up to €4,372, the whole of Don's overstatement. His total is explained. Sydney's is next.
The six data quality dimensions
| Dimension | Asks | Fault here |
|---|---|---|
| Accuracy | Does it match reality? | Don's old map keeps Dunder Mifflin in South |
| Completeness | Is anything missing? | INV-1014 has no price |
| Consistency | Is one thing written one way? | booked, Booked and BOOKED |
| Timeliness | Is it current enough? | INV-1008 still pending in the older export |
| Uniqueness | Is each thing there once? | Two invoices in both exports |
| Validity | Does it fit the rules? | TBC in a date column |
Use them as a checklist when you inherit a file, and report only the ones that move your metric.
Checks that miss the fault
- Testing the file instead of the metric. "It loaded" says nothing about what it counts.
- Treating a missing amount as zero. Unknown and zero are different claims.
- Using the wrong key. One row per invoice is wrong for a line-item table, where several lines on one invoice are legitimate.
To find repeats in SQL, use GROUP BY invoice_id HAVING COUNT(*) > 1. In Excel, fill =COUNTIF(A:A, A2) down and look for anything above 1. Finding duplicates covers the mechanics.