The problem
The client, a regional retail chain, came to us wanting a demand-forecasting model to reduce overstock and stockouts across their stores. This is a very common starting request, and it's usually not the first problem to solve. Their product, sales, and inventory data lived across seven systems (a point-of-sale platform, a separate e-commerce backend, a legacy inventory management tool acquired years earlier, a spreadsheet-based promotions calendar, and three more) with no consistent product identifiers between them. Before any forecasting model could be useful, the data itself needed to actually be trustworthy and unified.
We were direct about this in the first scoping call. A forecasting model built on seven inconsistent, unreconciled data sources would look sophisticated and perform unreliably, and the data quality problem would show up as model error that looked like a modeling failure rather than what it was: a data foundation failure.
Why we scoped this as a data engineering project first, with the forecasting model explicitly phase two
We proposed, and the client agreed to, a six-week data foundation engagement before any forecasting work, with a clear deliverable: one reliable, unified view of product, sales, and inventory data that a forecasting model (built by us or anyone else) could be trusted to train on. It was a harder sell than it sounds, because "build us a data pipeline" is less exciting than "build us a forecasting model," but it was the right sequencing.
Architecture
Source-by-source audit and reconciliation plan
We audited each of the seven systems individually: what data it held, how often it updated, and, critically, how its product identifiers related (or didn't) to the other six. The legacy inventory tool used its own SKU scheme, entirely disconnected from the POS system's product codes, so we had to build an explicit mapping table (a meaningful piece of work in itself) before any pipeline could reconcile the two.
ETL pipeline design
We built extraction and transformation pipelines pulling from each source on an appropriate schedule (real-time for POS transactions, daily batches for slower-moving inventory and promotions data) and normalizing everything into a consistent product and transaction schema before loading it into a central warehouse. The schema design was significant work, done with the client's business team to capture the fields a forecasting model would actually need, like promotion status and regional store groupings, not just what was easiest to extract.
Data quality validation, not just movement
Moving data into one place doesn't make it trustworthy. We built validation rules at each pipeline stage. Referential checks flag a sale that references a product ID that doesn't exist anywhere, instead of silently loading it. Range checks flag a zero unit price or a negative quantity for review. Freshness monitoring raises an alert when a source hasn't updated in its expected window, instead of silently serving stale data as current.
Schema designed for the actual downstream use case
Because the eventual goal was demand forecasting, the schema was designed to support it: a consistent time-series structure per product per store; promotion and seasonality flags as first-class fields rather than something a model would have to reconstruct; and a clean separation between actual sales and out-of-stock periods. Those look similar in naive sales data but mean very different things for forecasting, because a stockout suppresses recorded sales rather than reflecting real demand.
Challenges and tradeoffs
- The SKU mapping between the legacy inventory tool and the POS system was the single hardest piece of work. No clean mapping existed. We built a matching process combining product-name similarity, category, and manual review for ambiguous cases, and it took nearly two of the six weeks on its own, longer than any other component.
- Distinguishing stockouts from genuine low demand required new data the client wasn't currently capturing consistently. Some store managers tracked stockout events informally and others didn't. We worked with operations to establish a consistent stockout-logging practice, because without it a forecasting model would systematically underestimate true demand for frequently out-of-stock products.
- Validation rules needed tuning to avoid flagging too much as suspicious. Our initial value-range checks were too aggressive and flagged a meaningful share of legitimate clearance-sale transactions as anomalous low prices. We adjusted the rules with input from the merchandising team to distinguish genuine data errors from real, if unusual, business events.
Results
The unified pipeline achieved a 99.2% schema validation pass rate across three years of reconciled history and ongoing daily loads; the remaining 0.8% is flagged for manual review rather than silently loaded as clean. Seven previously disconnected systems now feed one consistent source of truth for product, sales, and inventory data. The client's business intelligence team started using it for reporting dashboards even before forecasting work began, an unplanned but welcome benefit of doing the foundation properly.
The forecasting model built on this foundation in the next phase hit its accuracy targets on the first evaluation pass. The client's data lead attributed that to the data quality work, which removed what would otherwise have looked like unexplained model error needing debugging.
What we'd do differently
We'd establish the consistent stockout-logging practice with store operations in week one rather than discovering the gap partway through schema design. It was a genuine blind spot in our initial audit: it showed up as a completeness issue rather than a quality issue, and those two categories got different levels of scrutiny in our early review.