How a Fortune-scale consumer packaged goods company consolidated five external data providers into a governed medallion Lakehouse and served it to the business through Direct Lake powered Power BI, with zero data duplication.

By Prathamesh Sable, Head of CoE, Santor Technologies

Every consumer-packaged goods company knows the feeling: the data exists, but the answer doesn’t. Sell-in numbers live with one syndicated provider, store-universe data with another, distribution and velocity metrics with a third, and field-collected shelf compliance with a fourth-each arriving on its own cadence, in its own format, through its own channel. The convenience store channel, with its tens of thousands of independent and chain outlets, magnifies the problem.

This is the story of how one of the world’s largest CPG companies, a household name in the confectionery and snacking category transformed that fragmentation into a single, governed decision platform for its U.S. convenience store (C-Store) business. The engagement paired Azure Databricks as the engineering and transformation engine with Microsoft Fabric as the semantic and consumption layer, delivering interactive Power BI dashboards over Direct Lake with natural-language querying through Copilot and a Fabric Data Agent.

It is also a blueprint. What follows is the architecture, the design decisions behind it, and why the Databricks-plus-Fabric pattern is emerging as the reference architecture for enterprise CPG analytics.

The Challenge: Five Providers, Five Formats, Zero Shared Truth

The client’s C-Store performance picture depended on five distinct data streams, none of which spoke to each other:

Each source used different store identifiers, different product keys, and different time grains. KPI definitions varied by team. Reconciling a single question: “How is our priority SKU distribution trending in covered versus non-covered stores?” which required weeks of manual stitching in spreadsheets.

Figure 1 — Source systems → ADF → ADLS Raw → Databricks Bronze / Silver / Gold → One Lake shortcut → Fabric Warehouse → Direct Lake semantic model → Power BI, Copilot & Fabric Data Agent.

Inside the Medallion: What Each Layer Actually Does

Raw – the immutable landing zone

Every vendor file lands exactly as delivered, held on Unity Catalog volumes with no transformation applied. Each delivery is a full snapshot; once consumed, files are archived with a timestamp. This preserves complete lineage and makes any historical run reproducible – a non-negotiable for audit and reprocessing in a regulated enterprise.

Bronze – faithful, auditable Delta

Bronze lands the source into Delta with no type casting: every column is retained as a string, column names are standardized, and audit columns (load timestamp, pipeline run ID, source-file label) are appended. Row-level primary-key checks run here; depending on each entity’s quality mode, failing rows are quarantined to a dedicated error volume or the whole file is rejected. Nothing bad silently flows downstream.

Silver – cleansed, typed, historized

Silver applies typing, trimming, date parsing, deduplication, and business derivations, then integrates sources against common keys – store universe outlet IDs and product UPC/GTIN. Master and dimension entities are historized with SCD Type 2, using an MD5 record hash to detect change, so re-running the same snapshot never creates duplicate versions. Every load is safely re-runnable by design.

Gold – the business-ready star schema

Gold publishes dimensional models grouped into subject-area schemas: product, store, distributor, and sell-out. Conformed dimensions are built here – a weekly-grain calendar aligned to the client’s September-August fiscal year, and a state-to-region mapping derived from the store master – alongside fact and KPI tables optimized with OPTIMIZE and Z-ORDER. This is the single physical source that both Databricks consumers and the Fabric semantic model read.

The Benefits of Pairing Microsoft Fabric with Databricks

A common misconception is that Databricks and Fabric compete for the same seat. In practice, they are complementary – and this engagement demonstrates exactly how. Databricks excels at large-scale, code-first data engineering; Fabric excels at governed, business-facing consumption. Both read and write open Delta Lake format, which is what makes the pairing seamless.

• Zero-copy interoperability through OneLake shortcuts. Gold Delta tables produced in Databricks appear inside the Fabric Warehouse without ETL, duplication, or synchronization jobs – one physical dataset, two engines, no drift.

• Best-of-breed engineering meets best-of-breed BI. Spark-based transformation, SCD2 historization, and data quality frameworks run where they belong (Databricks), while semantic modelling, Power BI, and Copilot live where the business already works (Fabric).

• A single open format underneath everything. Delta Lake’s ACID transactions, schema enforcement, and time travel protect every layer, and because the format is open, the estate is never locked to one compute engine.

• Layered governance that actually composes. Unity Catalog governs lineage, access, and discovery on the engineering side; Fabric workspace roles and semantic-model security govern consumption – with Azure Key Vault securing every credential in between.

• Cost efficiency by construction. Eliminating the duplicate “reporting copy” of data removes redundant storage, redundant refresh compute, and the operational burden of keeping two estates reconciled.

• AI-readiness out of the box. Because the semantic model sits directly on governed Gold data, Copilot in Power BI and the Fabric Data Agent answer natural-language questions against the same single source of truth the dashboards use.

• Independent scalability. Engineering workloads scale on Databricks job clusters; consumption workloads scale on Fabric capacity – each tuned and paid for independently, with no contention between them.

Why Direct Lake Changes the Power BI Equation

The semantic model connects to the Fabric Warehouse in Direct Lake mode – the connectivity option that reads Delta/Parquet files natively into the VertiPaq engine, combining the two things BI teams have always had to trade off:

• Import-mode speed without the import. Direct Lake loads column data straight from Delta files into memory on demand, delivering interactive, in-memory query performance without a scheduled dataset refresh pipeline.

• Direct Query freshness without latency. The moment the Gold notebook finishes writing, the new data is queryable – no waiting for an overnight refresh window, and no per-visual round trips to a SQL endpoint.

• No dataset size ceiling. Import-mode models are constrained by memory and capacity limits; Direct Lake reads from the lake itself, so the model scales with the data rather than against it.

• One copy of data, everywhere. The report, the semantic model, the warehouse, and the Databricks Gold tables are all views over the same Delta files – eliminating the version-drift problems that plague import-based estates.

• Lower total cost of refresh. Removing large scheduled imports frees capacity, shrinks refresh failure surface area, and simplifies operations to a single event: “Gold has landed.”

• Governed DAX stays fully intact. Measures, hierarchies, KPI logic, and row-level security all live in the semantic model exactly as they would in import mode – the storage mode changes, the governance doesn’t.

From Pipeline to Decision: The C-Store Dashboard

The consumption layer is a multi-page Power BI application built on the governed semantic model, giving sales, category, and distributor-management teams a self-serve view of the channel. A high-level Scorecard tracks total sales, store counts, distributor coverage, and per-store efficiency against year-ago. Sales Insights and Distributions pages break performance down by covered versus non-covered stores, geography, brand, and store classification. An Inventory page tracks stock value, units, and weeks of supply by SKU, while Growth Opportunities quantifies untapped revenue – identifying opportunity stores, distribution gaps, and the estimated upside of closing them, with drill-through to a store-level action list field teams can execute against. Rolling 4 / 12 / 26 / 52-week comparisons make momentum visible at a glance, and a built-in glossary anchors every KPI to a single published definition and formula.

Layered on top, Copilot in Power BI and a Fabric Data Agent let business users simply ask – “Which distributors had the biggest sales decline?” or “What’s the weeks of supply for the candy category?” – and receive answers computed from the same governed measures the dashboards use. Self-service analytics stops being a training problem and becomes a conversation.

Engineering Discipline: The Part You Don’t See

What makes this platform enterprise-grade is less visible than the dashboard. Each source runs an independent, parameterized ADF pipeline, so a late file from one vendor never blocks the others. Notebook activities are chained on success dependencies – Silver runs only if Bronze succeeds, Gold only if Silver succeeds – and every run carries a pipeline run ID for end-to-end traceability. Loads are idempotent: full-snapshot feeds, overwrite semantics for facts, and hash-based SCD2 for masters mean a re-run can never create duplicates. Data quality is QA-mode aware, quarantining bad rows where the business tolerates partial loads and failing the file where it doesn’t. Testing spanned unit, system integration, formal UAT with business SMEs, and performance validation against explicit SLAs – Gold refreshed within four hours of file arrival, report pages loading in under three seconds.

Digital transformation gave enterprises data. Intelligence transformation is about making that data answer back – governed, current, and conversational. This engagement shows the pattern is not aspirational; it is running in production today for one of the world’s most recognizable CPG brands.