From 50-Person Manual Operation to Fully Automated Pipeline

The Problem

Large European retailers refresh their product catalogs multiple times per year — at season changeovers, on the back of new supplier contracts, and in response to regulatory requirements around pricing and labeling accuracy. For a retailer carrying hundreds of thousands of SKUs across multiple regions, each refresh is a project in its own right, not a routine operation.

Before this engagement, the client's catalog process worked like this: dozens of suppliers would send product data in whatever format each vendor had standardised on — CSV exports, XML files, proprietary flat files, occasionally half-populated spreadsheets. A central operations team would receive these feeds, manually review each one for obvious errors, then mobilise fifty operations staff to key the data into the product information management system. The cycle ran for three to four consecutive days, always timed to a weekend to minimise disruption to live trading.

Product information management platforms are the industry's standard answer to catalog complexity — but they manage structured data that is already clean and inside the system. They solve nothing upstream. The real cost sat in the gap between what vendors sent and what the PIM could ingest: schema variance across suppliers, missing mandatory fields, currency formatting inconsistencies, duplicate SKUs introduced when suppliers recycled identifiers across seasons, and locale-specific pricing that needed mapping to the retailer's internal taxonomy. Manual entry existed because the upstream data was untrustworthy, and trusting it required human judgement applied row by row.

That human judgement added errors of its own. A QA cycle ran after each entry batch — and QA sometimes uncovered problems that required a partial re-run, adding a full day to an already expensive process. The hidden cost was structural: fifty experienced operations staff — people with institutional knowledge of supplier relationships and the product hierarchy — were unavailable for anything else during the cycle, multiple times per year. Across the multi-day cycle, each refresh consumed roughly 3,000 person-hours. Run three times a year, that exceeded 9,000 staff-hours of lost capacity, conservatively measured, before accounting for the QA cycles and error rework.

The business had attempted automation twice before. Once with a bespoke ETL pipeline that broke every time a vendor changed their file format, and once with a middleware platform that proved too slow to clear a full refresh inside the weekend trading window. The appetite for a third attempt was cautious. The goal was a system resilient to vendor schema drift, fast enough to complete inside a weekend, and accurate enough to eliminate the QA cycle entirely.

The Architecture

The pipeline follows a fan-in, process, fan-out pattern with two clearly defined processing boundaries: one at ingestion, one at publication to downstream systems.

On the inbound side, each vendor's feed arrives as a batch file dropped into Azure Blob Storage. A lightweight blob-triggered Function detects new arrivals and publishes individual product records as messages onto Azure Service Bus — one topic per feed type. This topic-per-feed-type design was a deliberate early decision: it confines the blast radius of any single vendor's malformed feed to exactly one topic. Its dead-letter queue fills, its consumers retry, and no other vendor's data is affected. Replaying one vendor's batch is a two-click operation; it never touches anyone else's.

The core processing runs in a Function App bound to each Service Bus topic. Before any business logic executes, the handler performs an idempotency check: each inbound message carries a composite dedup key derived from vendor_id + sku + revision. The key is checked against a Redis cache with a 24-hour TTL. Azure Service Bus guarantees at-least-once delivery — meaning the same message can arrive more than once under failure or retry conditions. The dedup check collapses those duplicates to a single write without needing distributed transaction semantics.

Validation runs as a pipeline of small, composable IValidationRule implementations registered in the DI container. Each rule receives the payload and returns either a pass or a structured error record. The first failure short-circuits the chain and routes the message to the dead-letter queue with the error reason attached as a message property — not as a logged exception. The operations team can query the dead-letter queue directly in Service Bus Explorer and see exactly which field failed, for which SKU, from which vendor, at what time.

After validation passes, an enrichment step decorates the canonical product record with data from internal reference sources: category hierarchy, regional VAT rates, locale-specific labels. The enriched record is written to Cosmos DB and an outbound event is published for downstream consumers. Three systems subscribe: search indexing, inventory management, and the pricing platform. Each is independently deployed with its own subscription and dead-letter queue — a failure in search indexing never holds up the inventory update.

The entire pipeline runs on consumption-plan Azure Functions, so cost aligns directly with volume: near-zero during off-peak months, predictable burst cost during refresh cycles — with no pre-provisioned capacity sitting idle. The design we rejected was a traditional batch ETL job. The client's previous failed attempt used exactly that model, and its fundamental weakness was sequential processing: one large vendor feed would hold up all subsequent feeds behind it in the queue. An event-driven architecture eliminates head-of-line blocking entirely — every product record becomes an independent unit of work, processed in parallel up to the Function App's concurrency limit.

What We'd Do Differently

The vendor validation rules are implemented as individual C# classes — one class per rule, one class per vendor quirk. This worked well for the first twelve months while the rule set was relatively stable. As new vendors were onboarded and existing vendors changed their feed schemas, the rule set grew. By the end of the first year, the repository had over forty validation rule classes, each requiring a code change and a deployment cycle to add or modify.

In retrospect, a small configuration-driven rule definition — something as simple as a JSON schema specifying field names, required/optional, type, and range constraints — would have paid back inside six months. The C# class-per-rule approach is transparent and unit-testable, properties we still value. A configuration-driven engine doesn't have to sacrifice those — it just needs the engine itself to be tested against its interpretation of the config. The operations team could have responded to a vendor schema change with a config deploy rather than a code deploy and a full release cycle.

The Service Bus topic-per-feed-type design has aged extremely well. Under peak load with five concurrent vendor feeds processing simultaneously, the isolation meant one feed's retry storm had no effect on the others. We'd make the same call again without hesitation.

The Cosmos DB partition key choice has not aged as well. We partitioned the catalog store by category code, which made sense when the largest vendor's catalog had a roughly even distribution across categories. Eighteen months in, one new vendor's catalog skewed heavily toward a single category — creating a hot partition that raised RU consumption by 30% and required a re-partitioning exercise. If designing this today, we'd partition by a compound key from the start, or take a harder look at Azure SQL with a regional read replica — which for this access pattern may be simpler and cheaper.

If You're Solving This Today

This was built on the Azure Functions v3 in-process model — the standard in 2021, using [FunctionName] attributes and DI wired directly through Startup.cs. In 2026 we'd start with the isolated worker model (.NET 8 default, [Function] attribute) — it gives cleaner dependency injection, proper middleware pipelines, and dramatically easier integration testing because the host no longer owns your DI container. We'd also evaluate Durable Functions for the orchestration layer rather than binding directly to Service Bus topics: the fan-out/fan-in pattern would have simplified the batch-completion signalling we implemented as a Redis counter. And we'd look at Azure Container Apps for the host — for workloads with predictable burst patterns, the per-replica pricing often beats the consumption plan once you factor in cold-start latency at scale.