Case Study: Building a Real-Time Inventory Intelligence Platform for a Multi-Channel Retailer

14 February 2026·8 min read·Neo Analytica

The Problem

A UK-based retailer with 45 physical stores and a growing e-commerce channel was losing money in two directions at once.

Overstocking: £2.1 million in dead stock sitting in regional warehouses because buying decisions were based on spreadsheets updated weekly — sometimes fortnightly. By the time the data was available, the buying window had closed.

Stockouts: Their best-selling products regularly showed "out of stock" online while sitting on shelves in stores 30 miles away. The e-commerce team estimated they were losing £380,000 per quarter in missed online sales because inventory data was 24–48 hours stale.

The root cause was architectural. Their systems looked like this:

Point-of-sale (POS) data from 45 stores was batch-uploaded to an on-premise SQL Server every night at 2am
E-commerce orders from Shopify lived in a separate database with no connection to store inventory
Warehouse management ran on a legacy ERP with a proprietary API that could only be queried during off-peak hours
Buying decisions were made in Excel, based on exports that a single analyst spent two days per week compiling

There was no unified view of inventory across channels. The Head of Operations described it as "flying blind with a 48-hour delay."

Why They Were Hesitant

This client had specific concerns that are common in retail:

"Our POS systems can't be touched"

The 45-store POS infrastructure was mission-critical. Any solution that required changes to the POS software, network configuration, or store-level hardware was immediately ruled out. Tills going down during a Saturday afternoon would cost more than the entire data project.

"We've been quoted 6–9 months and £300,000+"

Two other consultancies had proposed large-scale data warehouse projects with long timelines and fixed-scope contracts. The CFO was not willing to commit that level of budget without seeing tangible results first.

"Our IT team is two people"

The entire internal IT function consisted of a systems administrator and a junior developer. They managed the POS network, Shopify, and the ERP. They had no data engineering experience and no bandwidth to take on a complex new system.

"What happens when you leave?"

Previous technology projects had created systems that only the implementing vendor could maintain. The client was explicit: they would not sign off on anything that required ongoing vendor dependency.

Our Approach

We designed a phased programme that addressed every concern directly:

Phase 1 (Weeks 1–3): Connect the data sources without touching source systems
Phase 2 (Weeks 4–6): Build the unified inventory model
Phase 3 (Weeks 7–8): Deploy dashboards and alerts
Phase 4 (Weeks 9–10): Knowledge transfer and documentation

Total timeline: 10 weeks. Total investment: a fraction of the £300,000 quotes they'd received.

Phase 1: Non-Invasive Data Ingestion

The key architectural decision was Change Data Capture (CDC) — reading database transaction logs rather than querying source systems directly. This meant:

POS data was captured by reading the SQL Server transaction log via Fivetran's CDC connector. No changes to the POS application, no additional load on the POS database, no store-level changes whatsoever.
Shopify data was ingested via Shopify's native API using Fivetran's pre-built connector. Orders, inventory levels, and product catalogue synced every 5 minutes.
Warehouse/ERP data was captured via a lightweight API polling service we deployed as a containerised application. It read inventory levels and purchase orders every 15 minutes — well within the API's rate limits.

All data landed in Snowflake, chosen for its ability to scale compute independently of storage, its near-zero maintenance overhead, and its excellent support for semi-structured data (critical for handling varying POS schemas across stores).

Phase 2: Unified Inventory Model

This was the core intellectual work of the engagement. We built a dimensional model in dbt that answered the question: "Right now, how much of product X do we have, where is it, and where should it be?"

The model architecture:

Bronze (raw ingestion):
  ├── stg_pos__transactions       (45 stores, CDC, ~2min latency)
  ├── stg_shopify__orders         (e-commerce, 5min sync)
  ├── stg_shopify__inventory      (online stock levels)
  ├── stg_erp__warehouse_stock    (3 warehouses, 15min sync)
  └── stg_erp__purchase_orders    (inbound shipments)

Silver (cleaned & joined):
  ├── int_inventory__unified      (single view across all locations)
  ├── int_sales__all_channels     (POS + e-commerce normalised)
  └── int_stock__movements        (transfers, receipts, adjustments)

Gold (business-ready):
  ├── fct_inventory_snapshot      (current stock by product × location)
  ├── fct_daily_sell_through      (units sold / units available)
  ├── dim_products                (product master with categories)
  ├── dim_locations               (stores + warehouses + online)
  ├── rpt_stockout_risk           (products at risk of selling out)
  └── rpt_overstock_candidates    (slow-moving inventory by location)

The Hardest Problem: Inventory Reconciliation

The single most complex challenge was reconciling inventory counts across systems that disagreed.

The POS system tracked sales but not real-time stock levels — it only knew what had been sold since the last stock take. The ERP tracked warehouse stock but had a 15-minute delay. Shopify showed "available to sell" quantities that were manually set by the e-commerce team.

We built a reconciliation model that:

Started with the last known physical stock count for each product × location
Subtracted confirmed sales from POS and Shopify in real time
Added confirmed receipts from the ERP purchase order system
Applied adjustment factors for known shrinkage rates by store and category
Flagged discrepancies when the calculated stock differed from the ERP's reported stock by more than a configurable threshold

This gave the operations team a "confidence score" for each inventory figure — high confidence meant all systems agreed, low confidence meant a physical count was recommended.

Phase 3: Dashboards and Alerts

We deployed three dashboards in Metabase, connected directly to the Gold layer in Snowflake:

Inventory Command Centre — A real-time map showing stock levels across all 45 stores and 3 warehouses. Colour-coded: green (healthy), amber (below reorder point), red (stockout or critical).

Buying Intelligence — Sell-through rates by product and category, with 7-day and 30-day trends. The buying team could see which products were accelerating and which were stalling — with data that was hours old, not weeks old.

Stockout & Overstock Alerts — Automated alerts sent via Slack when:

A top-100 SKU dropped below the reorder threshold at any location
A product had zero sales across all channels for 14+ days (overstock candidate)
Online inventory showed "out of stock" while store inventory exceeded 50 units (missed fulfilment opportunity)

Phase 4: Knowledge Transfer

We spent the final two weeks ensuring the client's team could operate and extend the system independently:

Runbook documentation covering every pipeline, every model, and every alert
Pair programming sessions with their junior developer on writing and testing dbt models
Architecture decision records explaining why each technology choice was made
Incident response playbook for the most common failure scenarios
Recorded walkthroughs of the entire system for future team members

Results

Ten weeks after kickoff:

Metric	Before	After	Change
Inventory data latency	24–48 hours	< 15 minutes	-99%
Stockout incidents (online)	~12/week	~2/week	-83%
Overstock value	£2.1M	£1.4M	-32%
Time to produce buying report	2 days	Real-time dashboard	Eliminated
Manual data compilation	16 hours/week	0	-100%
Cross-channel fulfilment decisions	Next day	Same day	Real-time

The Unexpected Win

Three months after go-live, the client used the platform to launch ship-from-store for their e-commerce channel. Because they now had real-time visibility into store-level inventory, they could fulfil online orders from the nearest store with available stock — reducing delivery times and shipping costs simultaneously.

This capability was not in the original scope. It was only possible because the data foundation was in place.

Key Takeaways

CDC is your friend in retail. Change Data Capture lets you stream data from POS and ERP systems without touching them. This removes the biggest objection from IT and operations teams.
Inventory reconciliation is a data engineering problem, not a warehouse management problem. Most retailers treat discrepancies as an operational issue. In reality, it's a data integration challenge — and solving it at the data layer saves thousands of hours of manual stock counts.
Start with the buying team's questions, not the data model. We designed the Gold layer around the decisions the buying team actually makes: "What should I order? How much? Where should it go?" The model serves the decisions, not the other way around.
10 weeks is enough if you scope correctly. We didn't try to replace the ERP or rebuild the POS system. We built a read-only intelligence layer on top of existing systems. This kept the scope manageable and the risk low.
Knowledge transfer is a deliverable, not an afterthought. We allocated 20% of the project timeline to documentation and training. Six months later, the client's junior developer has added two new data sources independently — exactly as planned.

The Head of Operations told us: "We used to argue about numbers in every meeting. Now we argue about strategy. That's a much better use of everyone's time."