Skip to content

Software Quality

Data error detection

Finding the wrong values in a table that no declared rule catches.

Data error detection searches a table for the individual values that are probably wrong: a typo in a city name, a price in the wrong unit, a date that contradicts another column, a number stored as text. It works without a complete set of rules written in advance. Detectors infer what the column should look like from the data itself, from other tables, or from a few cells a person labels, and flag the values that do not fit. A data-quality assertion catches a violation of a rule someone declared; error detection looks for the wrong values nobody wrote a rule for.

Kinds of detector

Detectors are usually grouped by the kind of error they find (Abedjan et al. 2016)1:

  • Pattern violations. A value whose format differs from the rest of its column, such as one date written day-first among month-first dates.
  • Rule violations. A row that breaks a dependency the data otherwise satisfies, such as two rows with the same postcode and different cities.
  • Outliers. A value far from the distribution of its column.
  • Duplicates. One entity stored as several records, which duplicate-record detection covers.

Two further kinds learn what an error looks like. Corpus-based detectors compare a column's values with statistics gathered from a large collection of other tables, rather than judging a value only against the other values in its own column (Huang and He 2018)2. Learned detectors train a classifier on a few cells a person labels as right or wrong and apply it to the rest of the table (Heidari et al. 2019)3.

What it catches

  • Typos and format slips. A misspelled category, a stray character, a number stored as text.
  • Values that contradict other columns. A city that does not match its postcode, an end date before its start date.
  • Implausible values. A negative age, a salary three orders of magnitude off, a measurement in the wrong unit.

What it does not catch:

  • a wrong value that is plausible, such as a valid but incorrect phone number;
  • an error that is consistent across the table, which every detector learns as the norm;
  • a value whose correctness depends on facts outside the data.

Tools

  • Raha — research system that runs many base detectors and combines their outputs into one decision from a few tuples a person labels; its companion Baran proposes corrections.
  • OpenRefine — open-source tool for cleaning messy data, with facets and clustering that group a column's inconsistent spellings for review.

When to use, when not

Use:

  • On data entered by hand or integrated from several sources, before it feeds reports or models.
  • When building a rule set for a table, to find the error types the rules should cover.
  • With several detectors at once, since each kind finds a different share of the errors.

Don't:

  • To apply automatic repairs without checking them on a sample. A repair can replace a correct value with a wrong one.
  • As the only check on a table that a pipeline reloads every run. Turn the errors it finds into assertions that run each time.

Evidence

  • No single detector finds most errors. On five real datasets with known errors, every tool's coverage was well below 100%, and no tool dominated across datasets. The union of all tools typically reached above 60% recall, at a significant cost in precision (Abedjan et al. 2016)1.
  • Tables believed to be clean hold many errors. A corpus-based detector found tens of thousands of errors in public Wikipedia tables and in enterprise Excel files, both supposed to be of high quality, at a manually verified precision above 0.98 (Huang and He 2018)2.
  • Learned detectors reach high accuracy with few labels. A few-shot detector trained with data augmentation reported an average precision of about 94% and recall of about 93%, an average gain of 20 F1 points over competing methods (Heidari et al. 2019)3.
  • Automatic repair often makes the data worse. In an evaluation of 12 repair algorithms, most raised the error rate of the data rather than lowering it, and models trained on fully clean data sometimes performed worse than models trained on the dirty data (Ni et al. 2024)4.
  • Cleaning does not reliably improve models. Across 14 real datasets and seven model types, the effect of cleaning on classification varied with the dataset; cleaning duplicates was likely to hurt, and an advanced repair system was not noticeably better than simple imputation for missing values (Li et al. 2021)5.
  • Large language models can detect errors with a few examples. Prompted with a few labeled demonstrations, GPT-3 outperformed the learned detector HoloDetect on two error-detection benchmarks (Narayan et al. 2022)6.

Classification

  • Data-quality characteristics: Accuracy (a wrong value in a table gives every query, report and model built on it a wrong answer), Consistency (a value that breaks a pattern or dependency the rest of its column follows contradicts the other data).
  • Area: Tables whose values nobody has checked cell by cell — records entered by hand, data integrated from several sources, spreadsheets and web tables — where no complete set of declared rules exists.
  • Guarantee: Empirical — a flag is a candidate for review; a wrong value that fits every pattern, rule and distribution the detectors check passes.

Referenced by

References

  1. Abedjan, Ziawasch, Xu Chu, Dong Deng, et al. 2016. "Detecting Data Errors: Where Are We and What Needs to Be Done?" Proceedings of the VLDB Endowment 9 (12). https://doi.org/10.14778/2994509.2994518. ↩↩

  2. Huang, Zhipeng, and Yeye He. 2018. "Auto-Detect: Data-Driven Error Detection in Tables." Proceedings of the 2018 International Conference on Management of Data (SIGMOD 2018). https://doi.org/10.1145/3183713.3196889. ↩↩

  3. Heidari, Alireza, Joshua McGrath, Ihab F. Ilyas, and Theodoros Rekatsinas. 2019. "HoloDetect: Few-Shot Learning for Error Detection." Proceedings of the 2019 International Conference on Management of Data (SIGMOD 2019). https://doi.org/10.1145/3299869.3319888. ↩↩

  4. Ni, Wei, Xiaoye Miao, Xiangyu Zhang, Yangyang Wu, Sheng Liang, and Jianwei Yin. 2024. "Automatic Data Repair: Are We Ready to Deploy?" Proceedings of the VLDB Endowment 17 (10). https://doi.org/10.14778/3675034.3675051. ↩

  5. Li, Peng, Xi Rao, Jennifer Blase, Yue Zhang, Xu Chu, and Ce Zhang. 2021. "CleanML: A Study for Evaluating the Impact of Data Cleaning on ML Classification Tasks." 2021 IEEE 37th International Conference on Data Engineering (ICDE). https://doi.org/10.1109/ICDE51399.2021.00009. ↩

  6. Narayan, Avanika, Ines Chami, Laurel Orr, and Christopher Ré. 2022. "Can Foundation Models Wrangle Your Data?" Proceedings of the VLDB Endowment 16 (4). https://doi.org/10.14778/3574245.3574258. ↩