Data validation checks for retailer and distributor files

Most FMCG teams do not discover bad retailer data because they validate it. They discover it when the monthly reconciliation produces a variance nobody can explain, and the analyst spends two days deciding whether the retailer file, the ERP data or the mapping is wrong. The validation that would have identified the cause in ten minutes never ran, because it was never separated from the reconciliation itself. Validation is not a step inside reconciliation; it is the gate that decides what is allowed into it.

This guide sets out the checks to run on retailer and distributor files before they reach the comparison: structural checks that test the file against its own format, and business checks that test it against your master data, your calendar and your expectations. Each check names the failure mode it catches, because a check without a named failure mode is decoration.

Structural vs business checks

A structural check tests the file against its own definition: the expected columns exist, the data types are right, the date format is consistent, the units are declared. It answers one question: did the file come out of the partner system in the shape we agreed? A business check tests the file against something we hold: the product master, the store list, the calendar, the previous period's totals. It answers a different question: does the data inside a correctly shaped file make sense?

The distinction matters because the two checks fail for different reasons and need different responses. A structural failure usually means the partner changed a template, exported a different tab, or switched reporting tools. The fix belongs to the source, and patching the file by hand hides the change. A business failure usually means the data changed in a way the process must handle: a new store, a discontinued product, a corrected quantity. The fix belongs to the reconciliation process, and the failure is information, not noise.

Run structural checks first, because everything downstream depends on the file being shaped as expected. A file with a column missing does not need twenty business checks; it needs to go back or be re-exported. This is the first place where standardization work pays off: the canonical template you agreed with the partner is exactly the specification a structural check compares the file against.

Required fields

Every partner file has a small set of fields without which a row is meaningless: the product identifier, the store or customer identifier, the period, the quantity, and the unit. A required-field check flags rows where any of these is empty. The check is trivial to run and surprisingly often skipped, because analysts see a few complete rows at the top of the file and assume the rest looks the same.

Empty product codes and empty stores are the cases that cause the damage. A row with a blank product code cannot be mapped and will be dropped by any join, silently removing real sales. A row with a blank store cannot be attributed to a location, which matters when the reconciliation is done at store level. The required-field check turns these silent drops into a visible count before the comparison runs.

There is a subtlety worth naming: required fields are per-role, not per-file. A quantity is required on a sales line and not on a header. A customer code is required in distributor sell-out and not in a price list. The check needs the field roles defined, which is the same definition work as building the canonical schema — and it is why the check belongs in a documented rule set rather than in an analyst's head.

Key uniqueness

The key of a sell-out or sales file is the combination of fields that should appear once: product by store by period by transaction reference. A uniqueness check counts how many rows share the same key and flags duplicates. This is the single most valuable check on a sales file, because duplicate rows are the error that silently doubles a total without ever looking wrong.

Duplicate keys come from realistic events: a file assembled from two extracts with an overlapping range, a correction appended instead of replacing the original rows, a re-run of a partial export. None of these are caught by looking at the numbers, because a doubled store total is internally consistent. Only a key-level check catches them. The check also needs to be defined on the right key: some files legitimately repeat a product code across stores, and treating the wrong combination as the key produces false failures that train people to ignore the check.

Where the reconciliation runs at a mapped grain, uniqueness should be checked on both the raw key and the mapped key. A file can be unique on the retailer product code and duplicate once mapped, if two retailer codes point to the same internal SKU. Catching that at validation is faster than explaining doubled volume after the fact.

Reference validity

A reference check tests that every value in an identifier field exists in the corresponding reference table: product codes against the product master, store codes against the store list, customer codes against the distributor customer file. It is the check that converts unknown identifiers into a countable exception instead of a silent join failure.

In practice the check has two layers. First, direct lookup: is this retailer product code in the mapping table? Second, validity: is the mapped target itself active — not discontinued, not expired, not a relaunch waiting for a successor? The first layer catches unknown codes; the second catches codes that are known but stale. Both belong at validation, because an unmapped product line is exactly the kind of difference the missing-SKU handling must resolve before the comparison, not after.

The reference check should report counts and sample rows, not just a pass or fail. Knowing that 47 of 12,000 product codes are unmapped is a manageable exception queue. Knowing that a file "failed reference validity" with no count tells you nothing about whether the run can continue. Validation output is only useful at the level of detail where someone can act on it.

Coverage

Coverage checks answer a question the other checks never touch: not whether the rows present are correct, but whether rows are missing. A retailer file covering 41 of 45 stores, or a distributor file missing the last week of the period, can pass every structural and business check above and still be wrong for the period as a whole.

The practical coverage checks are: stores present versus the agreed store list, weeks or days present versus the reporting calendar, and products present versus the expected sellable range. Each needs an expectation to compare against, which is why coverage is the check teams skip — it requires maintaining a store list and a calendar, and those live outside the file itself. Without them, coverage cannot be measured, and the file is judged only on what it contains rather than on what it omits.

Coverage is where validation connects to the wider sell-out consolidation effort — but the practical point is simpler. Decide which dimension is critical for the use of the file. For a market-level sell-out dataset, store coverage is the one that matters. For a period-close reconciliation, period coverage is. Build the expectation tables for the critical dimension first; perfect coverage on three dimensions nobody reads is still a reasonable process, but it is not the one that stops the bad reconciliation.

Operational completeness checks

Structural validity asks whether the file has the right shape. Record validity asks whether the rows contain usable keys, dates, measures and references. Completeness asks whether the expected population arrived at all. Keep those answers separate so a file can pass row-level checks while still carrying a coverage exception.

These checks do not create a universal completeness percentage. The source contract determines the expected population and the reporting use determines whether a gap stops publication, becomes a warning or is shown as a caveat. The reliable dataset page carries the broader model; this page owns the intake decision.

Total controls

Total controls compare the file's summary figures against an independent expectation: total units against the previous period adjusted for calendar, total value against the corresponding invoice or shipment figure, row count against the intake count recorded last month. They are the cheapest way to catch a problem that structural and business checks miss — a problem that is spread across many rows rather than concentrated in a few.

A well-chosen total control has two properties. It uses an independent source, otherwise it is just re-checking the file against itself. And it is defined at a grain that means something: total by market, by category, or by channel, not just one grand total. A single grand total can hide two compensating errors in different categories; a category-level control cannot.

Total controls are also the natural bridge to the reconciliation itself. A variance between the file total and the ERP total is the same comparison the reconciliation will run in detail. Running the total check at intake tells you early whether the period is going to reconcile cleanly or whether there is a known gap that will need an explanation. It does not replace the detailed comparison; it gives you the summary answer first.

Severity

A check that does not classify its failures is a report, not a control. Assigning severity turns validation output into decisions: which failures stop the run, which are logged as exceptions, and which are informational. The severity model should be fixed and simple — three levels is enough.

Stop failures are structural: wrong template, missing required columns, an empty file, a total off by an order of magnitude. These mean the file is not the file the process expects, and continuing produces a comparison that has no meaning. Warning failures are business: duplicates, unmapped codes, coverage gaps. The file can proceed, but the exceptions must be visible and owned. Informational items are changes: a new store code, a template tweak, a new product range — worth recording, not worth stopping for.

The point of severity is that someone decides. A file with a warning becomes an exception with an owner and a due date, per the exception management process. A file with a stop failure goes back to the source with the reason stated. What should never happen is a file quietly proceeding past a failed check because nobody defined what the failure means.

Quarantine

Quarantine is what a stop failure looks like operationally. The file is not deleted and not merged; it is held with its failure record — file name, arrival date, checks failed, samples of the offending rows — while the source fixes it. Quarantine makes bad inputs visible to the process instead of letting them evaporate, and it gives the reconciliation a clean intake: the comparison only ever runs on files that passed the gate.

The quarantine area can be a folder for raw files plus a log, or a database table with a status. The mechanism matters less than the discipline: a quarantined file stays out of the run until it is replaced by a corrected version, and the log records the arrival and the return so the pattern of partner corrections is measurable. If a distributor quarantines a file every month, that is a partner problem to raise, not a monthly surprise.

Validation, severity and quarantine together form the intake stage of the reconciliation operating model. Once the intake gate is stable, the same checks that protect a manual reconciliation are the specification for an automated one: the rules are already written, tested and owned. That is why validation is worth building before automation, not after.

Frequently asked questions

Sources

1. Gartner, Data quality: best practices for accurate insights.
2. TechTarget, Why businesses should know the importance of data quality.

Gartner is cited for the finding that poor data quality costs organizations an average of $12.9 million a year (2020 research), and TechTarget for the 1-10-100 rule of data verification and correction costs. Both support the argument that validating at intake is materially cheaper than fixing bad data after it reaches reporting. No specific figure in this guide is presented as applying to a particular Marksyte engagement.