Making data quality measurable
My bachelor's thesis “Key Figures for Measuring Data Quality” (grade 1.3), worked through again: with more than a million real, uncleaned retail records from a public dataset.
The idea of the thesis
Decisions are made based on data, but bad data often only shows up at the very end: in the data warehouse, when a KPI doesn't add up. My thesis looked at how to make data quality measurable beforehand.
The approach: pick quality criteria from the literature, turn them into percentages through metrics, and get the facts straight from the database through data profiling. In the thesis I applied this to the customer master data of an industrial company. Here I show the same approach on public data.
How to recognize data quality
Credibility
Usefulness
Interpretability
Key integrity
Taxonomy after Hinrichs (2002). Data quality is always multidimensional and depends on the purpose the data is used for.
Data profiling in four steps
Integration
Copy the data from the source system into a separate area so the analysis doesn't load the system and the state is frozen.
Analysis
Examine attributes, tuples and relations with standard methods: data type, value range, patterns, domains, NULL values, keys, relationships.
Presentation
Prepare the findings so the business side understands them, and calculate key figures from them.
Assessment
Clarify with the business side what really is an error and fix the causes. Then the cycle starts again.
The metric: completeness on three levels
Following Hinrichs, completeness is built bottom up. A value counts as missing if it is NULL or something that means the same in business terms, such as “UNDEFINED”, “?” or a 0 used as a placeholder.
g is an attribute's weight between 0 and 1, depending on how important it is for the use case.
Applied to a public dataset
Online Retail II contains every invoice line of a UK online retailer over two years, uncleaned and with all the quirks of real systems. It's the same kind of data as in my thesis: customer and sales data. The analysis follows the approach from chapter 4 step by step.
The data, up close
16 real rows from the dataset, picked so that every finding shows up at least once. Choose a check or click a highlighted cell to see what's behind it.
| Case | Invoice Invoice number | StockCode Product code | Description Product description | Quantity Quantity | InvoiceDate Invoice date | Price Unit price | Customer ID Customer number | Country Country |
|---|---|---|---|---|---|---|---|---|
| Unremarkable | 489434 | 85048 | 15CM CHRISTMAS GLASS BALL 20 LIGHTS | 12 | 09-12-01 07:45 | 6.95 | 13085 | United Kingdom |
| Cancellation | C489449 | 22087 | PAPER BUNTING WHITE LACE | -12 | 09-12-01 10:33 | 2.95 | 16321 | Australia |
| No customer number | 489525 | 85226C | BLUE PULL BACK RACING CAR | 1 | 09-12-01 11:49 | 0.55 | NULL | United Kingdom |
| Country “EIRE” | 489520 | 35751C | PURPLE CURRENT CANDLE RING | 12 | 09-12-01 11:41 | 0.75 | 14911 | EIRE |
| Unknown country | 494860 | 84832 | ZINC WILLIE WINKIE CANDLE STICK | 5 | 10-01-19 11:01 | 0.85 | 12470 | Unspecified |
| Postage as product | 489439 | POST | POSTAGE | 3 | 09-12-01 09:28 | 18.00 | 12682 | France |
| Note instead of name | 489463 | 71477 | short | -240 | 09-12-01 10:52 | 0.00 | NULL | United Kingdom |
| “?” as name | 495027 | 21314 | ? | 1,146 | 10-01-20 13:48 | 0.00 | NULL | United Kingdom |
| Ghost row | 489521 | 21646 | NULL | -50 | 09-12-01 11:44 | 0.00 | NULL | United Kingdom |
| Write-off | A506401 | B | Adjust bad debt | 1 | 10-04-29 13:36 | -53,594.36 | NULL | United Kingdom |
| Duplicate | 489517 | 21912 | VINTAGE SNAKES & LADDERS | 1 | 09-12-01 11:34 | 3.75 | 16329 | United Kingdom |
| Duplicate | 489517 | 21912 | VINTAGE SNAKES & LADDERS | 1 | 09-12-01 11:34 | 3.75 | 16329 | United Kingdom |
| Same code, other name | 495362 | 15058A | BLUE WHITE SPOTS GARDEN PARASOL | 2 | 10-01-24 12:12 | 7.95 | 15727 | United Kingdom |
| Same code, other name | 538200 | 15058A | BLUE POLKADOT GARDEN PARASOL | 1 | 10-12-10 11:11 | 7.95 | 17912 | United Kingdom |
| Same customer, other country | 497363 | 22055 | MINI CAKE STAND HANGING STRAWBERY | 8 | 10-02-09 09:41 | 1.65 | 12370 | Austria |
| Same customer, other country | 538826 | 85123A | WHITE HANGING HEART T-LIGHT HOLDER | 64 | 10-12-14 12:58 | 2.55 | 12370 | Cyprus |
Tip: the finding cards further down highlight the matching cells too.
Attribute level
Tuple level
Relation level
Completeness per attribute
At attribute level, almost everything looks very good. With one exception, and that's exactly the one that matters for customer analyses.
Try it yourself: weighting can hide gaps
This was the key insight of my thesis. Depending on how the attributes are weighted, the same data produces a very different key figure. Move the sliders or pick a use case.
A key figure above 95 % even though almost every fourth line has no customer: that's the effect the thesis warns about. Always look at attribute level too.
How the metric calculates
The same rows, now rated: 1 = present, 0 = missing (placeholders included). On the right is each row's completeness Q(t) with the weights from above. Click a row to see the calculation.
| Case | Invoice Invoice number | StockCode Product code | Description Product description | Quantity Quantity | InvoiceDate Invoice date | Price Unit price | Customer ID Customer number | Country Country | Q(t) |
|---|---|---|---|---|---|---|---|---|---|
| Unremarkable | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1.00 |
| Cancellation | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1.00 |
| No customer number | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 0.98 |
| Country “EIRE” | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1.00 |
| Unknown country | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 0.95 |
| Postage as product | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1.00 |
| Note instead of name | 1 | 1 | 0 | 1 | 1 | 0 | 0 | 1 | 0.75 |
| “?” as name | 1 | 1 | 0 | 1 | 1 | 0 | 0 | 1 | 0.75 |
| Ghost row | 1 | 1 | 0 | 1 | 1 | 0 | 0 | 1 | 0.75 |
| Write-off | 1 | 1 | 1 | 1 | 1 | 1 | 0 | 1 | 0.98 |
| Duplicate | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1.00 |
| Duplicate | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1.00 |
| Same code, other name | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1.00 |
| Same code, other name | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1.00 |
| Same customer, other country | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1.00 |
| Same customer, other country | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1 | 1.00 |
| Q(T) of this sample = Σ Q(t) / 16 | 0.95 | ||||||||
Each 0 or 1 is multiplied by its attribute's weight (same order as the table), and the sum is divided by the sum of the weights.
The sample is deliberately full of problem cases. The big figures above apply to the whole dataset.
What I take away
- Data quality can't be defined in general, only for a purpose. For a sales analysis this data is good, for a customer analysis it isn't.
- A single key figure isn't enough. Only several criteria and several levels together give an honest picture.
- Data profiling doesn't just find errors, it also hints at their cause, for example the ghost rows or the cancellations hidden in the key.
What I'd add today
- Automate the checks as rules so they run on every data import, as I do today in my migration projects.
- Measure further criteria such as consistency and timeliness, not just completeness.
- Let machine learning find unusual patterns instead of knowing every rule in advance. That's a topic of my master's.
Sources & notes
Dataset: Chen, D. (2019). Online Retail II [Dataset]. UCI Machine Learning Repository. https://doi.org/10.24432/C5CG6D (CC BY 4.0).
Bachelor's thesis: Ackermann, P. (2021). Kennzahlen zur Messung der Datenqualität (Key Figures for Measuring Data Quality). Europäische Fachhochschule (now CBS International Business School). Grade 1.3.
The original thesis contains confidential company data and is therefore not public. All figures on this page come from the public dataset.