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.

Three orders in one file, all valid values
OrderAmount
A-1100
A-1100
A-250

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

  1. Read this live chart in Vista, Oplema's browser app for dashboards, with Everything stacked chosen. Note August.
  2. Select One row per invoice.
  3. Compare the bars. Only one should move.
  4. Select Everything stacked again and find the No date bar. It's an invoice with TBC for 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.

Don's overstatement, line by line
FaultEurosWho decides
Same invoice in two exports€1,440Sydney Swell, who sets the export window
Pending invoice counted€1,300Sydney, who owns the status
June invoice counted€1,200Sydney, who owns the period
Starhopper's rebate ignored€432Don, 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

What each dimension asks, with a fault from these files
DimensionAsksFault here
AccuracyDoes it match reality?Don's old map keeps Dunder Mifflin in South
CompletenessIs anything missing?INV-1014 has no price
ConsistencyIs one thing written one way?booked, Booked and BOOKED
TimelinessIs it current enough?INV-1008 still pending in the older export
UniquenessIs each thing there once?Two invoices in both exports
ValidityDoes 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.