Data & Analytics
Why Dirty Data Costs More Than Missing Data
Wrong data is more dangerous than absent data, because people trust it. Notes from real data cleansing work.
Missing data makes people cautious. Wrong data makes people confident in the wrong decision. The difference matters.
What we found in the field
While cleansing school nutrition programme data, we found something often overlooked: the same national ID number used by two different people. Not a typo — genuinely two different names with the exact same identity number.
If data like this is used directly, beneficiary counts will be wrong. More dangerously, aid can reach people who are not entitled, or eligible families miss out because their record was treated as a duplicate.
Three most common problems
Duplication. 126 duplicated rows out of 68,367. It looks small, but it touches budget claims.
Empty values in disguise. Fields containing the text "null", a dash, or a space — not genuinely empty. The system treats them as filled, so validation never fires.
Inconsistent formats. Birth dates in three different formats within one table. Hard to spot until a report shows strange numbers.
The order we use
- Inventory first, do not delete yet. Know how much and where.
- Separate machine-decidable from human-decidable. Duplicate IDs with identical names can be merged automatically. Duplicate IDs with different names need a human.
- Test rules on a small sample. Before applying them to 68,000 rows.
- Put rules at the entry point. Cleansing old data without fixing the input process just repeats the same problem next year.
What gets forgotten
Step four. Cleaning data is a one-off project. Fixing the input process is a lasting change. Without it, you will run the same cleansing project again in two years.