Building a Data Lakehouse for FMCG Sales, Demand and Distribution Analytics

Most FMCG companies can tell you exactly what they dispatched last month. Very few can tell you what actually sold.

Primary sales — what left your plant or depot onto a distributor's invoice — is clean, audited and sitting in SAP or Dynamics 365 within the hour. Secondary sales — what the distributor sold onward to the retailer — arrives as a monthly upload from a DMS, sometimes complete, sometimes three weeks late, sometimes in a spreadsheet a distributor's accountant built by hand. Tertiary sales — what the shopper actually bought off the shelf — mostly does not arrive at all outside modern trade and e-commerce.

That gap is where the money goes. It is why a brand can be shipping to plan and still be losing shelf space. It is why trade spend gets approved against a scheme whose real return nobody can calculate for another six weeks. And it is why the forecast that drives production planning is built on the one number least connected to consumer demand.

A lakehouse does not fix distributor behaviour. What it fixes is the part that is genuinely a technology problem: holding primary, secondary, stock and outlet data in one governed model, at one grain, with one definition of an SKU and one definition of an outlet — so that "which of my top 200 SKUs is losing weighted distribution in the west region" has an answer that takes seconds instead of a week of reconciliation.

A data lakehouse for FMCG is a single governed storage layer — in the Microsoft stack, OneLake holding Delta Parquet tables — where primary sales from ERP, secondary sales from distributor management systems, stock and claims data, outlet masters and third-party retail audit data are landed, standardised and modelled together. It replaces the pattern of separate extracts feeding separate Power BI datasets for sales, supply chain and trade marketing.

The distinction that matters commercially is not warehouse versus lake. It is this: in a lakehouse, the sales team's number and the supply chain team's number come from the same physical table — not from two pipelines built eighteen months apart by two different vendors and reconciled quarterly by the one analyst who understands both.

In a mid-market FMCG estate, that usually means collapsing four or five reporting stacks into one:

• A primary sales dashboard built off an ERP extract
• A separate secondary sales report built off a monthly DMS file
• A supply chain planning spreadsheet with its own SKU mapping
• A trade spend tracker in Excel, owned by finance
• A retail audit deck from Nielsen or a similar provider that only three people ever open

Each is defensible on its own. Together they make it impossible to answer a cross-cutting question — which is exactly the kind of question a Sales Director or Head of Supply Chain asks.

Anyone can draw a medallion architecture. The reason FMCG lakehouse builds run long is rarely the architecture — it is these four.

1. The SKU master does not match across systems. Your ERP has a material code. The distributor's DMS has its own item code, created by the distributor's operator when the product was first billed. There is often no reliable mapping, and where one exists it breaks whenever a pack size changes or a variant launches. Promotions make it worse — a promotional pack frequently gets a separate code nobody maps back to the base SKU. Until this mapping exists as a governed, versioned table — not a spreadsheet on someone's laptop — every secondary sales number is approximate.

2. The outlet master is duplicated and dirty. The same kirana store, grocery or pharmacy appears three or four times across distributor systems: once from the salesperson who created it on a mobile app, once from the billing clerk, once with a spelling variation, once with a different GST or trade-licence number. Numeric distribution — the count of outlets billing your product — is a headline board metric, and it is arithmetically wrong if the outlet master is duplicated. This needs deduplication logic (fuzzy matching on name, address, geo-coordinates and tax identifier) running as a maintained pipeline, plus a data steward who owns the survivorship rules. It is not a one-off cleanup.

3. Units of measure and free goods. Cases, pieces, kilograms, litres, and the conversion factors between them. Free goods issued under a scheme that show as zero-value lines. Damaged and expired returns that reverse against a different period than the original sale. Get the UOM conversion layer wrong and your fill rate, your days of inventory and your forecast are all wrong in the same direction, quietly.

4. Data arrives late, incomplete, or not at all. Van-sales apps go offline in low-connectivity markets and sync a day later. A DMS upload fails silently. A sub-stockist in a multi-company billing setup reports under a different entity. Any FMCG lakehouse needs upload compliance as a first-class metric — a table showing, by distributor, by day, what was expected and what arrived. Most estates do not have this, which means nobody knows whether last week's dip in secondary sales was a market signal or three distributors who simply did not upload.

Here is the shape of an FMCG lakehouse on the Microsoft stack, starting with how data lands.

Primary sales, stock, pricing and master data from ERP. For SAP, Oracle or SQL Server sources, Fabric mirroring replicates the database into OneLake as Delta tables with continuous change capture — no hand-built extraction job to maintain. For Dynamics 365 and Business Central, the native Fabric link serves the same purpose.

Secondary sales from DMS platforms. Most distributor management systems expose either an API or a scheduled file drop. Azure Data Factory pipelines land these into Bronze exactly as received, with the source file preserved. That matters — when a distributor disputes a claim six months later, you need the version they sent, not the version you cleaned.

Retail audit and e-commerce data. Nielsen, Kantar or marketplace exports land as flat files. Quick-commerce partners increasingly provide daily SKU-level offtake, which is the closest thing to true tertiary data most brands will get.

Existing lake investments. If you already have data in ADLS Gen2, Databricks or Snowflake, OneLake shortcuts reference it in place rather than copying it again. This is the single most under-used feature in Fabric migrations — teams re-ingest data they already own because a consultant proposed a greenfield pipeline.

Bronze holds what arrived, untouched, partitioned by source and load date.

Silver is where the FMCG-specific work happens: SKU harmonisation against the governed mapping table, outlet deduplication and survivorship, UOM conversion to a standard base unit, scheme and free-goods classification, and returns matched back to originating invoices. Materialized Lake Views in Microsoft Fabric suit this layer — you declare the transformation in Spark SQL with data-quality constraints attached (for example, rejecting rows where billed quantity is negative and no return reference exists), and Fabric handles refresh order and lineage. Incremental refresh needs Delta Change Data Feed enabled on the source tables; without it you run full refreshes, which becomes expensive at national distributor scale.

Gold is the business model: a conformed sales fact at invoice-line grain, a daily stock-position fact, and dimensions for SKU, outlet, distributor, geography, calendar and scheme. This is where numeric distribution, weighted distribution, must-sell-list compliance, lines per call, productive call rate, fill rate and days of inventory are calculated once — not five times in five reports.

Serve. A Direct Lake semantic model in Power BI reads the Gold Delta tables without an import-and-refresh cycle. Worth knowing before you design it: Direct Lake has capacity guardrails on row counts, Parquet file counts and model size that vary by F-SKU, and Direct Lake on SQL falls back to DirectQuery when you query non-materialised SQL views or when SQL-based row-level security applies. If your distributor-facing reports need each distributor to see only their own rows, design that RLS approach deliberately — it is a common cause of "the report got slow after go-live".

Predict. Once secondary sales sit at SKU–outlet–week grain with a clean calendar and scheme history, demand forecasting stops being an Excel exercise. Realistic first models: a baseline statistical forecast per SKU–region, promotional uplift modelled from historical scheme performance, and anomaly detection on distributor stock positions to flag likely stock-outs before the order comes in. Set accuracy expectations honestly — SKU-level weekly forecast error in FMCG is materially higher than category-level monthly error, and any consultant quoting a single accuracy figure without asking about your promotional intensity is guessing.

Act. This is where most BI programmes stop and should not. Power Automate handles the routine loop: a distributor whose upload did not arrive by 10:00 gets an automated chase; a claim that fails validation routes to the regional sales manager rather than sitting in a queue; a stock-out risk flagged by the model creates a task for the territory salesperson in the field app. Fabric's Data Activator watches the Gold tables and triggers those flows on threshold breach. A Fabric Data Agent or Copilot Studio agent can then answer "what's my numeric distribution in Sharjah this month versus last" in Teams, grounded on the governed model rather than on whatever a model hallucinates.

The honest section, because you will meet all of these.

A lakehouse cannot make a distributor send data. If 30% of your distributors upload late or not at all, you now have a very well-architected view of an incomplete market. Upload compliance is a commercial conversation — usually tied to scheme payouts — before it is a technical one.

Fabric runs on a capacity model. F-SKUs are billed on compute units, and an unoptimised Spark job over national secondary sales history will burn through capacity fast. Budget for capacity monitoring from week one, not month three.

Outlet mastering never finishes. New outlets are created daily by field staff. Deduplication is a running process with a named owner. Treating it as a one-off migration task has seen numeric distribution accuracy decay within a couple of quarters.

Mirrored data is read-only in Fabric. Corrections have to happen at source or in a deliberate override table with its own audit trail. Teams who plan to "just fix it in the lakehouse" discover this late.

One version of the truth requires retiring the old version. If the legacy secondary sales report stays live after go-live, you have two numbers and a monthly argument. Somebody senior has to switch the old one off.

Do not start with a platform migration. Start with a diagnostic that answers four questions:

• What percentage of your secondary sales data arrives complete and on time, by distributor, over the last six months?
• How many outlets are in your combined distributor masters, and what is the realistic duplicate rate?
• Can you map every DMS item code to an ERP material code today — and who owns that mapping?
• Which single decision would change if the answer arrived weekly instead of monthly?

That fourth question is the one that decides whether this is worth building. If nothing changes, the platform is an expensive report. If the answer is "we'd reallocate trade spend mid-cycle instead of after the quarter", the business case writes itself.

We build these on Microsoft Fabric, OneLake and Power BI, with Power Automate and Copilot Studio closing the loop from insight to action. First value in six weeks — a working slice of one region's secondary sales, modelled properly, not a slide about what could be built.