Item master hygiene: the unglamorous foundation

An item master is the table that says what each product is: brand, sub-brand, variant, size, pack count, category, and whatever else the business analyses by. Every report is built on it, and almost nobody owns it.

When it drifts, the reports do not break. They quietly become wrong, which is considerably worse.

What breaks first?

Size and pack attributes, because they change with every packaging revision and nobody is obliged to update them. A wrong size corrupts equivalised volume, price per volume, and every comparison built on either.

After that, brand and sub-brand mapping, which drifts as portfolios are restructured and new items are added by whoever added them.

Why does drift happen?

Because new items arrive continuously and there is usually no gate. An item gets set up to be sold, not to be analysed, and the analytical attributes are filled in later or not at all.

The fix is a gate rather than a clean-up. A periodic clean-up of a table with no gate produces the same state again within a year.

What should the gate check?

That every required attribute is populated, that values come from a controlled list rather than free text, and that the item maps into the hierarchy. Three checks, applied when the item is created.

Free text is the specific enemy. A brand field typed by hand will contain several spellings of the same brand within months, and every one of them is a separate row in a report.

How do you find existing problems?

Profile the table. Count distinct values per attribute, look for near-duplicates, count nulls, and check size distributions for implausible values. An afternoon of profiling on a neglected master usually finds several hundred issues clustered into a handful of patterns.

Fix the patterns rather than the rows. Several hundred individual corrections is a project; four rules is an afternoon.

What about discontinued items?

They should be marked rather than deleted, because history needs them. Deleting a discontinued item removes it from past periods and rewrites history, which is a different and worse problem than leaving it.

A status field with a date is the usual answer, and it also makes tail analysis possible.

Who should own it?

One named person or team, with authority to reject a badly-formed item. Ownership without authority produces a well-intentioned owner watching the table degrade.

That authority is a commercial conversation as much as a data one, because the person creating the item is usually in a hurry.

What does good look like?

Every item has every required attribute, every attribute comes from a controlled list, the hierarchy has no orphans, and a new item cannot be created without passing the checks. It is not sophisticated; it is maintained.

How does this affect a data purchase?

The provider's master matters as much as yours. Ask how their attributes are sourced, how often they are refreshed, and what happens to an item they cannot classify. An unclassified item is either excluded or dumped into a catch-all, and both distort.