Detecting and repairing dirty data.
Records that parse fine and still mislead go in. Cleaner, reconciled records come out. Here is the field that makes that happen, in plain language.
Dirty data is data that is present but wrong. The field that deals with it has two jobs: find the records that are off, and decide what the right value should have been. We walk it the way the field is built: what it is, why it is hard, how a modern system does it, what it makes possible, and where it is heading. The papers behind each idea are linked, so you can read the source yourself.
- Quantity
- 3
- Unit price
- $200
- Stored total
- $500
$500$600
Data that is present, and still wrong.
Dirty data parses fine and still misleads. The field's job is to find those records and fix them without making things worse.
Open any CRM that has been alive for a few years and you meet the same mess. The same account is in there twice, once as "Acme Corp" and once as "Acme Corporation," so two reps think they own it. A contact has a name and a company but no email and no title, so it cannot be routed or scored. A deal still says "negotiation, closing this month" three months after it went cold. None of these records looks broken on its own row. Together they make every report and every forecast a little bit wrong.
The field organizes this trouble into a small taxonomy. Duplicates, the same real thing recorded twice. Missing values, a field that should be filled and is not. Outliers, a value that does not fit the pattern of its column. And rule violations, a row that contradicts a known rule. The Abedjan survey (2016) is built around exactly these four shapes.
One distinction is kept separate across this whole page, because confusing the two is how explanations overclaim. Detection decides which cells are likely wrong. Repair decides what the right value is. A system can be good at one and weak at the other.
Detection
Quantity 3 × $40, but total says $90.
Repair
- Possible explanations
- Quantity, price, or total could be wrong.
- Needed evidence
- Check the original order before choosing a correction.
Most data errors fall into four shapes.
Detection starts by naming which one you are looking at. The same CRM example carries through each.
| Problem | Example |
|---|---|
| Duplicate candidate | Acme Corp and ACME Corporation, pending identity checks |
| Missing value | A contact has no recorded email |
| Outlier | A $14.2m deal among mostly $14.2k deals |
| Rule violation | Quantity × unit price differs from invoice total |
The four shapes from the Abedjan survey: duplicates, missing values, outliers, and rule violations.
There is usually no answer key.
Cleaning is not checking against a correct copy, because the correct copy rarely exists. The system has to infer the intended value from the data it already holds.
Nothing to check against.
There is rarely a clean, correct copy to compare to. The right value has to be inferred from the rest of the data, which is the central difficulty of repair rather than detection.
The same thing wears many names.
"Acme Corp," "Acme Corporation," and "ACME, Inc." are one account. They share no exact key and differ by abbreviation, punctuation, and typos, so matching them is a similarity judgment, not a lookup. The Elmagarmid survey is built around this.
Repairing one error can create another.
Satisfying one rule can break a second, or overwrite a value that was actually correct. Naive, one-rule-at-a-time repair is known to introduce fresh errors, which is why holistic approaches exist.
Rules hold most of the time.
Real constraints like "zip determines city" hold often and fail at the edges. Treating them as hard truths over-corrects; ignoring them wastes signal.
No single detector catches everything.
Coverage comes from combining tools, and combining them well is its own problem. The Abedjan survey makes this explicit.
Most detectors need tuning.
Many need rules or thresholds set by hand per dataset, which assumes the user already knows where the errors are. Reducing that burden is an active research goal.
Find the errors, then infer the fixes.
Detection leans on three families of signal. Repair combines those same signals to decide what each suspect cell should have been.
1Detection signals
Three ways to flag a suspect cell.
Rule-based detection flags rows that break a stated constraint. Statistical detection flags values that sit far from the rest of their column, like a deal size three orders of magnitude above the others. Pattern or format detection flags cells that do not match the expected shape, like a phone number where an email should be.
The Abedjan survey (2016) reports that running several detectors and unioning their findings beats any one alone. No single detector is treated as complete.
| Detector | Signal |
|---|---|
| Constraint | Invoice total differs from quantity × price. |
| Distribution | Total is unusual for comparable orders. |
| Format | Currency value is not parseable. |
Combine the signals, then investigate the proposed repair.
2Integrity constraints and functional dependencies
A rule the data is supposed to obey.
A functional dependency says one set of fields determines another. A phone number determines a single account; a zip determines a city. When two rows agree on the left side and disagree on the right, at least one of them is wrong. That turns "something is off" into a located, checkable error, which is what makes the rest of repair possible.
Keep it plain: a rule, and the rows that break it. The constraint does not say which row is right, only that the pair cannot both be.
| Postal code | City |
|---|---|
| 94107 | San Francisco |
| 94107 | San Jose |
Under this reference mapping, both city values cannot be correct for the same ZIP.
3Matching duplicate records
Compare, then decide, then scale.
The Elmagarmid survey (2007) frames deduplication as three steps. A similarity step compares fields with string-similarity measures, so "Acme Corp" and "Acme Corporation" score as close. A decision step calls them the same entity above some threshold. A scaling step, called blocking, only compares records that already share a cheap signal, so you do not compare every pair to every other pair.
More recent work treats "are these two records the same?" as a learning problem. The DeepMatcher study (Mudgal et al., 2018) reports that deep learning helps most on textual and dirty records, while simpler methods stay competitive on clean, structured ones. That nuance is worth keeping: learning does not always win.
A trained model or a calibrated rule decides match, non-match, or review.
4Holistic, probabilistic repair
Combine the signals, then infer the value.
The reframing that organized modern repair: instead of fixing one rule at a time, treat every candidate fix as uncertain and find the set of values that best satisfies the rules, the statistics, and the matching records all at once. HoloClean (Rekatsinas et al., 2017) builds a probabilistic model over the dataset and infers the most likely correct value for each suspect cell, unifying rule-based and statistical repair. Its accuracy figures belong to that paper, not to anyone reading this page.
Filling a blank field uses the same machinery, pointed at empty cells instead of wrong ones. A missing value can be inferred from the same row, from what is typical in the column, or borrowed from a matched record that already has it.
| Candidate total | Order arithmetic | Source invoice |
|---|---|---|
| $90 | Conflicts | Not supported |
| $120 | 3 × $40 agrees | Matches signed invoice |
Retain the source and the original value in the correction record.
5Lowering the setup cost, and keeping a human
Fewer hand-written rules, a few labels instead.
A recurring goal is detection that does not need heavy per-dataset configuration. Raha (Mahdavi et al., 2019) generates many detector configurations automatically and asks a person to label only a small sample of rows, then learns which detectors to trust. That is a direction the field is moving, not a claim about any one product.
Across all of the above, a small amount of human labeling or confirmation is often what makes the automation trustworthy. Fully unattended repair is not a safe default, and honest systems say so.
- Generate candidates
Run different error detectors or configurations.
- Label a sample
A person marks actual errors and clean cells.
- Learn which signals help
Use the sample to select or combine detectors.
- Evaluate
Check performance on separate examples.
Cleaner records are raw material for everything built on top.
At the field level: deduplicated accounts, routable contacts, forecasts closer to true, and mail that reaches a real inbox.
The honest framing is "closer to correct," not "perfect." A repaired record can be well-formed and still wrong, so cleaning is not the same as truth. With that caveat kept in view, the payoffs are concrete.
One account, one owner.
Deduplicated records mean a rep is not unknowingly working a deal another rep already owns, and account-level reports stop double-counting.
Routable, scoreable contacts.
Filled-in fields mean a lead can be routed, scored, and emailed instead of stalling because a required field was blank.
Forecasts you can trust.
A pipeline whose stages reflect reality, with stale deals flagged, produces a forecast that is closer to true. Closer, not perfect.
Less silent drift.
Continuous detection catches errors as they enter rather than at the annual cleanup, so a database degrades more slowly. Clean, validated contact fields also reduce bounces and bad sends.
The direction the field is heading.
We describe the direction, not a roadmap of our own. The honest beat closes here, not on a victory lap.
A handful of labels, not a rule set.
The trend, visible in Raha and its successors, is toward systems that need a few labels instead of a hand-written rule set per dataset.
One model over many signals.
HoloClean's framing, combining rules, statistics, and matching records in one model, is now the default mental model for repair rather than picking one signal.
Powerful on messy text, costlier to run.
Deep and, more recently, language-model-based matchers extend the DeepMatcher line, with the caveat that they help most on messy textual records and cost more to run.
Every gain has a cost.
Precision against recall: an aggressive cleaner fixes more and breaks more. Automation against oversight: more autonomy, more risk of confident wrong repairs. Coverage against cost: more detectors, more compute and more false alarms to triage.
The papers behind this page.
- Abedjan et al. (2016). Detecting Data Errors: Where are we and what needs to be done? VLDB 2016.
- Rekatsinas, Chu, Ilyas, Ré (2017). HoloClean: Holistic Data Repairs with Probabilistic Inference. VLDB 2017. arXiv:1702.00820.
- Elmagarmid, Ipeirotis, Verykios (2007). Duplicate Record Detection: A Survey. IEEE TKDE 2007.
- Mudgal et al. (2018). Deep Learning for Entity Matching: A Design Space Exploration (DeepMatcher). SIGMOD 2018.
- Mahdavi et al. (2019). Raha: A Configuration-Free Error Detection System. SIGMOD 2019.
Keep reading.
Sibling explainers in the Research section.
Talk. Done.
Clean records start with a complete record of the call. You talk, and the notes are waiting. See it on a real call.
No app required. Just your phone number.