Product Data Cleansing: Workflow, Checklist, and Before-and-After Example

Clean supplier, PIM, and ERP product data by resolving invalid identifiers, duplicates, units, missing attributes, classifications, and schema drift.

The supplier file is not wrong. It is written in the supplier’s language—their units, abbreviations, part-number format, and category hierarchy—and nothing downstream speaks it.

Product data cleansing detects and corrects records that are invalid, duplicated, inconsistent, misclassified, or incompatible with the target schema. Good cleansing preserves the raw source, makes every transformation explicit, and sends ambiguous decisions to review instead of silently forcing a value.

What needs cleaning

Defect	Test	Safe action
Invalid identifier	Check digit, length, prefix, identifier type	Quarantine or recover from evidence; never invent
Duplicate identity	Exact and fuzzy identity evidence	Merge only above an approved confidence threshold
Inconsistent unit	Parse value and unit; compare dimension and target unit	Convert value and retain original
Missing attribute	Required-by-class rules	Mark missing for enrichment, not as an empty string
Wrong classification	Class evidence and required-attribute compatibility	Reclassify with confidence and rationale
Schema drift	Unexpected columns, types, enumerations, or renamed headers	Version the mapping and alert the owner

For identifier-specific failures, use the common barcode errors guide. Unit of measure explains why EA, pack quantity, and measured dimensions must remain separate concepts.

Normalization across supplier conventions

Normalization creates comparable representations without destroying the original. Standardize whitespace and Unicode; resolve brand aliases to a governed identity; parse numbers and units; map supplier headings to canonical fields; and maintain controlled-value crosswalks. Do not strip punctuation from every part number: AB-12 and AB12 may or may not be equivalent depending on the manufacturer.

A robust record stores source_value, normalized_value, transformation_rule, source, and timestamp. Read data normalization for the distinction between representation and truth, and schema mapping for field-level crosswalks.

A step-by-step product data cleanup workflow

1

Profile without editing

Measure nulls, types, distinct values, unit patterns, identifier validity, duplicate candidates, and unexpected columns.
2

Freeze the target contract

Define canonical fields, types, units, enumerations, required-by-class rules, and accepted identifier namespaces.
3

Normalize representations

Apply reversible text, numeric, unit, date, brand, and category mappings. Keep raw values beside transformed values.
4

Resolve identities

Generate candidates using identifiers, manufacturer, MPN, dimensions, and descriptions. Block false merges involving variants or pack sizes.
5

Validate and classify

Run format, range, cross-field, taxonomy, and provenance rules. Route uncertain cases to an evidence-rich review queue.
6

Enrich only after cleansing

Once identity and schema are stable, source genuinely missing attributes. This is where cleansing ends and enrichment begins.
7

Write back and monitor

Publish approved values, reconcile acknowledgements, and detect new schema or value drift on the next supplier delivery.

Data cleansing vs data enrichment shows the handoff: cleansing makes existing values usable; enrichment adds evidence-backed values that were absent. Product deduplication and classification should precede mass enrichment so new content lands on the right identity.

Before and after: an industrial valve

Field	Supplier input	Clean canonical output	Decision
Supplier SKU	VX 0042	VX 0042	Preserved as supplier identifier
Manufacturer / MPN	Acme / A-42 1/2	ACME / A-42-050	Mapped alias with manufacturer evidence
Connection	0,5 inch	12.7 mm	Parsed decimal comma and converted unit
Pack	10 EA	quantity: 10; unit: each	Separated quantity from UOM
Category	Valves > Other	Ball valves	Reclassified from specifications
GTIN	0401234567890	exception	Failed GTIN-13 check digit; not auto-corrected

The output is not merely prettier. It can now be matched, filtered, classified, validated, and loaded without losing what the supplier originally asserted.

Product data cleansing checklist

Raw files are immutable and versioned
Every transformation is deterministic or reviewable
Identifiers are validated before matching
Values and units are parsed into separate fields
Duplicate merges preserve source records and can be reversed
Missing values are distinguished from invalid and not-applicable values
Classification is checked against product evidence
Write-back success and new schema drift are monitored

Upload a dirty catalog for assessment

Claro profiles the file as received, identifies cleanup and matching risks, and returns a concrete exception set before any write-back. Upload a catalog for a free assessment.