Data cleansing

Correct the values, not just the gaps

Filling a blank is the easy half. Cleansing fixes what is already there and wrong: the manufacturer sitting in the brand field, the seller's own name, the placeholder somebody typed to get past a required field, the same value in four casings. Every correction keeps the original.

One field, one hundred and forty-six answers

One product familyData cleansing

Distinct values in the brand field

146

One breakfast cereal family, 2,834 records, and 146 different things written in the brand field. Only one of them is the brand.

Live catalog values · 1,849 UPCs · brand field only

What kind of wrongI · III

Error class · records among the most common values

IWrong in seven ways7 classes

Manufacturer in the field562

Missing411

Placeholder42

Category name33

Seller name32

Casing variant31

Typo11

Each class needs a different repair. None of them is caught by checking whether the field is empty.

IIMissing arrives two ways309 and 102

309 records carry a null and 102 carry an empty string. Most validation treats those as the same state. Most databases do not, and the two behave differently in every join downstream.

Null is not blank

IIISeven are only a shift key apart146 to 139

Lowercase the whole field and 146 values become 139. Seven of them were never different values, and every one had been splitting its product's numbers in two.

Free, if you look

A field is not clean because it is full. It is clean when every value in it is the kind of thing the field is for.

Values and counts taken from the live catalog for one cereal family: 2,834 records across 1,849 UPCs. Class counts are drawn from the forty most common values, so each is a floor rather than a total.

How a value gets corrected

Correcting is riskier than filling, because a wrong fix is harder to notice than a blank.

Classified before changed

the error type picks the repair

A casing variant, a category name in the wrong column and a seller's own name are three different faults and take three different repairs. Naming the fault first is what stops a cleanse from flattening real differences along with the errors.

Reversible by design

the original is never discarded

Every corrected field keeps the value it replaced and the reason. You can audit any single change, and you can roll the whole pass back. A cleansed file you cannot check against what came in is just a different set of numbers to take on trust.

Measured on correctness

not on how full the field is

Completeness is the metric most catalogs report because it is the one that is easy to compute. Every value on the page below is populated, so all of them pass a completeness check and none of them are right. The score that matters is how many fields hold the kind of thing the field is for.

Twelve real values, and what each one should say

Taken verbatim from the brand field for one cereal family. Every one of these is populated, and every one of these is wrong.

Field brand Breakfast cereal 146 distinct values

As it arrivedAs it readsWhy it was wrong

General Mills›CheeriosThe manufacturer, correctly spelled, in the field for the brand

null›CheeriosMissing, and read as unknown by most systems

""›CheeriosAlso missing, but read as a real value by most systems

Generic›CheeriosA placeholder somebody typed to get past a required field

BREAKFAST/WARM BEVERAGES›CheeriosA merchandising category pasted into the brand column

CHEERIOS›CheeriosRight value, wrong case, counted separately

cheerios›CheeriosRight value, wrong case again, counted separately again

Honey Nut Cheerios›CheeriosA sub-brand promoted into the brand field

Honey›CheeriosTruncated at the first space on the way in

XYZ Traders›CheeriosA marketplace seller's own name, not the brand

Cherios›CheeriosMisspelled, one letter out

General Millsss›CheeriosWrong field and misspelled at the same time

The original is kept

Every correction keeps the value it replaced and the reason it was replaced. A cleansed field you cannot audit is just a different set of numbers you have to take on trust.

Twelve values taken verbatim from the brand field for one cereal family in the live catalog, alongside what each one resolves to. None of these are hypothetical, and none of them would be caught by a rule that checks whether the field is populated. That is the whole problem with measuring completeness instead of correctness.

Harmonya Helps You Make Faster, More Confident Decisions

Correcting a file once is a project. Keeping it correct as the file changes is the part that has to be automated.

Automated product coding

Your file, your schema, coded every cycle, with coverage and accuracy measured rather than asserted.

See the coding engine