Case study | Consumer products | Retail EDI and master data
A global consumer-products company receives sell-through and inventory data from every
major retail account it sells through, in a different format from each one. This case study
illustrates how that was transformed into a single validated feed into SAP, inside a fixed daily
window.
At a glance
Metric | Number | What it covers |
Trading partners | ~25 | Department stores, e-commerce marketplaces, drugstore chains, and international retailers |
Inbound formats | 3 | ANSI X12 852, EDIFACT SLSRPT, and manual file drops from partners who send neither |
Pipeline patterns | 1 | Every partner follows the same ingest, validate, distribute path whatever the format |
Reports served | 5 | Business reporting drawn from one validated source |
The challenge
Every retailer reports the same three things. None of them report them the same way.
A retail sell-through feed carries three facts – what sold, what is still on the shelf, and what it
sold for. Merchandising, replenishment and finance depend on these three. The formats diverge immediately.
North American accounts send ANSI X12 852 transactions. European and international accounts
send EDIFACT SLSRPT. A handful of partners drop a spreadsheet on a server. Cadences range
from daily to monthly. Even among the X12 partners, the same quantity figure arrives under a
different qualifier code – QS for sold, QU for returned, QA for on hand – and the column holding
the real product barcode depends on the retailer that sent it. If handled feed by feed, this
becomes a point-to-point integration – One script per partner, each with its own quirks, and no
shared view of which files have landed or failed. Any failure that follows is silent, not dramatic.
That makes it expensive.
A retailer changes an item code and the rows keep loading, matched to nothing. A file arrives
late and a weekly report gets built on partial data while nobody notices. Weeks later a number in a business review disagrees with the ERP, and the work of finding out why falls to whoever can still reconstruct what the pipeline did that day.
The hard problem arrives at 6:40 in the morning, when a retailer’s item code matches
nothing in the item master and the daily window still has to hold.
Three decisions shaped the approach
Where it was breaking | What we did about it | What it produced |
Twenty-five partners across three incompatible formats, each arriving on its own channel and schedule. | One ingestion point per channel type, feeding a single decoding layer. | A validated feed reaching SAP inside the same window every day. |
Point-to-point feeds with no shared view of which files had landed, processed, or failed. | One pipeline pattern every partner runs through, whatever format they send | File and job status readable in one place, for every partner at once. |
Records referencing unknown customers or items reaching production tables unnoticed. | Master-data validation ahead of the load, with a quarantine queue for anything unresolved. | Five business reports drawing on one source they can trust. |
The approach
Treat unmatched records as a queue to work
The first instinct with a failed lookup is to reject the row and move on. That trades a data problem for a support problem – because nobody can tell you afterwards what was lost or why.
The ordering matters as much as the rule. Validation sits ahead of the load, which is the deliberate and more expensive choice – checking after the write is easier to build. Once an unmatched row is sitting in a reporting table, every consumer downstream has already inherited it, and removing it means working out which reports were built on it and which numbers have to be restated. Validating first keeps that problem from being created at all. Instead of untangling what got corrupted, the team is just deciding what to do with a flagged row.
The platform resolves every partner identifier against shared master data – customer, item, barcode cross-reference, price list, before anything is written. Each of those lookups is deliberately permissive – a row that fails to match carries forward with the gap intact. What happens next is the key design decision
Outcome | What happens to the record |
Resolved | Rows with a complete identity - shipping location, item, brand and price all matched - pass to the shared sales and inventory table and on to SAP. |
Held | Rows with a gap go to a quarantine queue carrying a plain-language diagnostic - which lookup failed, and for which barcode, item code or account. |
The difference shows up in the working day. An analyst opening the queue sees that a specific barcode is missing from the item master, so the fix is a master-data correction someone can actually make, and the record reprocesses on the next run. One partner’s bad week shouldn’t stop the other twenty-four from loading.
A day in the pipeline
Files land in the morning and validated data has to reach SAP the same evening. Every stage is scheduled against that window, and each one depends on the one before it.
Time | What happens |
04:00 | The day’s archive folder is created, ahead of anything arriving. |
05:30 | Customer, item, barcode and price master data refresh from SAP. |
06:00 | Partner files land by SFTP; manual uploads land alongside them. |
07:00 | Decoding, validation, and the split into resolved and held records. |
Evening | Validated sales and inventory data is pushed into SAP. |
Under the hood
Built to absorb a format change
Trading partners upgrade EDI versions on their own timeline. The platform holds the schema library for every X12 release, EDIFACT directory and EANCOM syntax version side by side, so a partner declaring an older version in their file header parses correctly on day one. Keeping schemas nobody is using yet costs almost nothing to store. Not having the right one ready is what actually costs a window, and that gap only shows up the day a partner switches versions.
The two standards also loop differently, and that difference carries real consequence. X12 nests
one loop of each kind, so a segment tag maps to a single scope. EDIFACT reuses the same
leading tag across several distinct loops, separated only by a numeric suffix, and a mapping written for one does not transfer to the other. Each standard therefore has its own decoding path. A shared path appears to work and quietly mis-reads half the feeds.
Archiving and shadow testing matter more in the second year than the first. Processed files are archived to dated storage, and every source and outbound table has a history counterpart, so a question about what a retailer sent three months ago is answerable without asking the retailer. Every production object also has a test-schema counterpart, so a new partner or a changed rule runs against a mirror of production before release. Onboarding a new retailer stays a routine change: a mapping and a dry run.
Platform
Azure Logic Apps · Azure Data Factory · Azure Databricks · Azure SQL Data Warehouse · Azure
Blob Storage · BizTalk EDI schemas · SAP · Power BI
Most partner-feed problems are master-data problems wearing an EDI costume
We see this with other clients too. When partner feeds land late, arrive inconsistently, or quietly disagree with an ERP, the fault usually sits in the reconciliation between a partner’s identifiers and your own. Teams that start with the decoding layer tend to rebuild it later, once it becomes clear the real work was never there.
If your retail or supplier feeds look like this, we are happy to look at what you are receiving today and tell you where the fault actually sits.
