Fabric Data Platform for QSR Product Performance
At a Glance
The Client asked our team to consolidate five disconnected data sources into a single product performance platform on Microsoft Fabric. We built a medallion lakehouse with a Direct Lake semantic model and applied AI accelerators across discovery, mapping, entity resolution, and documentation to compress the build without compromising engineering rigour.
Client Background
The Client operates several brands across hundreds of locations, with a mix of company-owned and franchised sites. Each function within the group had grown its own reporting estate. Operations relied on POS exports. Marketing worked off the loyalty platform. Finance maintained supplier cost files in SharePoint. Delivery aggregators were managed brand by brand, with each merchant portal reporting its own version of revenue.
There was no shared product master, no consolidated view of channel performance, and no consistent definition of margin across the business.
Project Objective
The menu team wanted to answer a question that no existing report could answer cleanly:
Which products perform once you account for channel mix, promo lift, food cost, and delivery commission?
A product that looks like a top seller on in-store POS can erode margin once a third of its volume comes through aggregators at a 30% commission. Decisions about menu rationalisation, regional pricing, and promotion design were being made without that view. The brief was to build a platform that made the answer accessible the next morning after a launch, not a week later, after a project.
Three measurable goals were set at the outset:
- Reduce time-to-insight on new product launches from 5 business days to next-morning.
- Establish a single product master reconciled across all five sources.
- Reduce manual analyst effort spent on reconciliation by at least 50%.
Our Approach and Architecture
We treated this as a standard medallion build on Microsoft Fabric, with AI augmentation woven into the lifecycle rather than bolted on as a feature. The architecture is conventional. The discipline is in how each layer is governed.
The diagram shows five source systems feeding through three ingestion patterns into the Bronze lakehouse in OneLake. Bronze flows into Silver (conformed, SCD2-managed dimensions, anchored by the resolved product master) and then into Gold (star schema by subject area). A Direct Lake semantic model sits on Gold and is surfaced in Power BI with row-level security by brand and region. Fabric Data Pipelines orchestrate the flow end to end. Git integration and Fabric deployment pipelines manage promotion across development, test, and production workspaces. AI accelerators are applied at discovery, mapping, entity resolution, documentation, and test generation.
Ingestion
Three patterns, one per source category. POS and inventory data are extracted via Fabric Data Pipelines through the on-premises data gateway against the source databases. Loads are incremental and watermark-driven, partitioned by transaction date.
Loyalty and delivery aggregator APIs are pulled by PySpark notebooks orchestrated from Data Pipelines. The pulls are paginated and retry-aware, with raw response bodies landed as JSON in the Bronze lakehouse before any parsing. Keeping raw payloads intact means re-deriving silver from a fixed source is a re-run, not a re-extract.
Supplier flat files are picked up from SharePoint into a staging area in OneLake. Schema is inferred at read time and validated against a checked-in expected schema. Drift raises a quality alert rather than failing the load silently.
Bronze, Silver, Gold
Bronze is raw, immutable, partitioned by source and ingestion date, with technical metadata only. Delta tables in OneLake.
Silver is conformed, deduplicated, and type-cast. SCD Type 2 history is maintained for menu item, recipe, and price dimensions. The product master, output of the entity resolution work, lives here as the spine that every downstream join passes through.
Gold is a star schema by subject area: Product Sales and Product Cost as fact subject areas, joined with conformed dimensions for product, location, brand, date, channel, and promotion. Margin is derived in the semantic layer rather than baked into a physical table, so finance can change the allocation rule without a rebuild.
Semantic and Consumption
A Direct Lake semantic model sits on Gold, surfaced through Power BI. No data copy, no scheduled refreshes; queries hit the lakehouse directly. Row-level security scopes brand and region. Two starter dashboards (Menu Performance and Channel Mix) were built with the menu and operations teams, and most of the ongoing value sits in the semantic model itself, which analysts query directly.
Orchestration and DataOps
Data Pipelines orchestrate ingestion and the bronze-to-silver step. Silver-to-gold transformations run in Spark notebooks called from the pipeline. Git integration ties the workspace to Azure DevOps. Development, test, and production workspaces are promoted through Fabric deployment pipelines. Secrets sit in Azure Key Vault. Data quality checks run after each layer load: schema conformance, freshness, row count tolerance, and a small set of business rules such as no negative product cost and no order line without a resolved product. Failures alert into Teams.
Project Plan
The build ran for 12 weeks of construction followed by 3 weeks of hypercare and minor enhancements, totalling 15 weeks. Eight phases, with planned overlap where parallelisation made sense.
Phase modules in detail:
Phase 1 — Discovery and Architecture (Weeks 1–2). AI-assisted source profiling across all five systems. Business requirements workshops. Target dimensional model. Data quality standards. Sprint plan and team RACI.
Phase 2 — Foundation and Security Setup (Weeks 2–3). Fabric workspace creation, OneLake structure, lakehouse and warehouse provisioning. Azure DevOps Git integration. On-premises data gateway. Azure Key Vault. Row-level security framework and brand/region scoping rules.
Phase 3 — Source Ingestion Build (Weeks 3–7). Gateway-based pipelines for POS and inventory. Notebook-driven API pulls for loyalty and the two delivery aggregators, including pagination and retry handling. SharePoint pickup for supplier files with schema validation.
Phase 4 — Silver Layer and Product Master (Weeks 5–9). SCD Type 2 dimensions for menu item, recipe, and price. Deduplication and type-casting. Cross-source entity resolution to produce the product master that anchors every downstream join.
Phase 5 — Gold Layer and Semantic Model (Weeks 8–11). Star schema implementation with conformed dimensions for product, location, brand, date, channel, and promotion. Direct Lake semantic model with margin and commission measures.
Phase 6 —BI Layer and Dashboards (Weeks 9–12). Power BI dashboards for Menu Performance and Channel Mix. Row-level security testing across brands and regions. User training for analysts and adoption work with the menu team.
Phase 7 — UAT, Cutover and Go-Live (Weeks 11–12). End-to-end validation. Parallel run against the legacy reporting estate. Business sign-off. Production cutover and decommissioning of retired reports.
Phase 8 — Hypercare and Minor Enhancements (Weeks 13–15). Defect triage and fixes. Performance tuning of the semantic model. Minor enhancements raised during early production use. Final knowledge transfer and runbook handover.
AI Acceleration Across the Build Lifecycle
This is where the engagement differs from a conventional Fabric build. AI accelerators were applied stage by stage during development, with engineers in front at every decision point. The resulting platform is standard Microsoft Fabric. The acceleration sits in how it was built, not in what it became.
Discovery and profiling. Our accelerator scanned each source, generated schema documentation, surfaced suspected primary and foreign keys, and flagged columns with unexpected distributions. What is usually two weeks of "what do we actually have here" compressed into four days of review.
Source-to-target mapping. Given the target dimensional model, the accelerator proposed mappings and PySpark transformations for the bronze-to-silver step. Engineers reviewed, corrected, and hardened the output. Around 70% of the boilerplate was generated. The remaining work is where engineering judgement still lives.
Entity resolution. The product identity problem was the hardest part of the build. The same chicken sandwich appeared as one ID in POS, another in the recipe system, a marketing name on loyalty, and two distinct aggregator listings. We combined deterministic rules with an AI-assisted matcher tuned on labelled pairs during development. Low-confidence matches were reviewed in batches.
Test generation. Unit and integration test scaffolds for the ingestion, silver, and gold transformations were generated against the source-to-target mappings, then completed by engineers. This improved coverage without adding weeks of test authoring.
Documentation. Lineage, column-level descriptions, and test summaries were generated and refreshed on every deployment. The Client now reads the docs instead of asking us about them.
Where we kept humans firmly in front: the target data model, business logic for revenue recognition, promo allocation, delivery commission handling, the product master itself, the data quality rules, and anything finance was going to use to make a real decision.
Outcome
- Time from new product launch to first read on channel-level performance: down from 5 business days to next-morning.
- Manual reconciliation work previously absorbed by two analysts: reduced by around 60%. Both have shifted to analysis rather than spreadsheet plumbing.
- Menu rationalisation: 14 items retired after seeing they were margin-dilutive once delivery commission was accounted for. One regional promotion retargeted after channel-mix analysis showed it cannibalising in-store traffic at no incremental volume.
- Disconnected reports retired: 23.
- Build acceleration through AI augmentation: estimated 35% reduction in time to first usable Gold dataset compared to a non-augmented baseline of similar scope.
Tech Stack
Microsoft Fabric: OneLake, Lakehouse, Data Pipelines, PySpark Notebooks, Direct Lake semantic model, Power BI.
Microsoft Azure: On-premises data gateway, Azure Key Vault, Azure DevOps.
FabricDataOps: Git integration, Fabric deployment pipelines.
AI accelerators: Source Profiling Accelerator, Transformation Scaffolding Accelerator, Entity Resolution Accelerator, Documentation Generator.
Reflection
Two things were truer at the end of this engagement than we expected at the start.
First, AI acceleration did not change which problems were hard. It changed how quickly we got to them. Product identity was always going to be the question that mattered, and it still required engineering judgement and business input to settle. The accelerators took schema archaeology and transformation boilerplate off the critical path, so we could spend our weeks on the work that needed actual thinking.
Second, documentation that stays current turned out to matter more than we usually admit. When lineage and column descriptions update on every deployment, the conversations with finance and the menu team shift. They stop asking where a number comes from and start asking whether it should. That is the conversation a platform like this is meant to enable.