Microsoft Fabric
Power BI

Unified Commerce Analytics

A Microsoft Fabric medallion build that turns a five-join transactional database into a Direct Lake star schema an executive can open at eight in the morning.

Role: Independent case study
Year: 2026
Stack: Microsoft Fabric · Data Factory · PySpark · Delta Lake · Power BI

North Meridian Retail Group runs its point-of-sale off a heavily normalised Azure SQL database, which is exactly what a transactional system should be and exactly wrong for reporting. Getting a basic revenue number out of it meant five-plus joins across atomic tables, and the region a customer belonged to lived in a CSV on one analyst’s desktop. The brief was a Fabric Lakehouse that gets analytics off that production database entirely, without ever touching the till.

The problem

The analytics team was spending eighty percent of its week hand-writing SQL joins across Sales, Orders, Products and Customers, leaving twenty percent for actual insight work. The regional hierarchy analysts needed for reporting wasn’t in the database at all, it was RegionMapping.csv, maintained by hand, with enough drifted copies in circulation that different regions reported conflicting numbers depending on which file they’d used. North Meridian’s Head of Sales put the frustration plainly: “I do not care about Third Normal Form. I care about what my top ten products are right now.”

The fix couldn’t touch the operational database. Point-of-sale writes had to stay exactly as reliable as they were, which meant analytics needed to become a fully decoupled, read-only consumer of a governed copy of the data, not a second workload competing for the same tables.

The architecture

A Bronze, Silver, Gold Lakehouse, built with the same principle behind every case study in this series: each layer protects a different stakeholder. Bronze captures the four Azure SQL tables and the RegionMapping CSV exactly as they arrive, so any transformation bug downstream can be replayed from source rather than patched over. Silver joins and standardises, deriving FullName, rounding prices, denormalising region into the customer record. Gold collapses individual transactions into a pre-aggregated star schema, three dimensions and one fact table, sized for Power BI’s in-memory engine rather than for a transaction log.

End-to-end data flow diagram: Sources (Azure SQL Database with Sales, Orders, Customers, Products; Reference Data as RegionMapping.csv) feed Ingestion via Fabric Data Factory (Batch Ingestion one-time, Incremental Ingestion daily by ID-based watermark, Dimension Refresh daily full load), into a Lakehouse with Bronze (raw ingestion), Silver (cleaned and conformed, via a Bronze-to-Silver PySpark notebook) and Gold (business-ready star schema, via a Silver-to-Gold PySpark notebook) layers, feeding a Power BI Semantic Model in Direct Lake mode and a Power BI report. A fifth band along the bottom covers Orchestration and Governance: Master Pipeline, Data Quality and Validation, Monitoring and SLA, Security and Governance, and Scheduling at 06:00 AM, looping back to the Master Pipeline. — The whole platform on one page: five stages, four Bronze tables plus the CSV, three-layer Lakehouse, and the governance band that ties a daily 6am run to the dashboard an executive actually opens.

Fabric workspace item list including AnCaseStudy06 semantic model, Bronze to Silver Notebook, CaseStudy06_Orders pipeline, Masterpipeline, Metadata_An06 notebook, Pipeline 3 - Incremental, Pipeline 4 - Dimensions, Pipeline_SalesBatch, SilverToGold notebook, UpdateWatermarkOrders_NB and UpdateWatermarkSales_NB notebooks. — Every pipeline and notebook the platform is built from, in one workspace.

Lakehouse schema browser showing three expandable schemas: CaseStudy06Bronze, CaseStudy06Gold, CaseStudy06Silver. — The three-layer split at the Lakehouse level, matching the medallion diagram above.

Bronze: land everything as-is, then make reruns safe

Two of the four source tables are large enough that a single unbounded Copy activity is the wrong tool. Orders gets a metadata-driven pattern instead: a control table holding OrderID ranges, a Lookup activity that reads the active ranges, and a ForEach loop that fires one Copy per range.

Metadata ingestion notebook cell, entirely commented out, defining CREATE SCHEMA CaseStudy06, CREATE TABLE OrderConfigTable with TaskId, SourceTable, FilterQuery, DestinationTable and CopyFlag columns, and three INSERT INTO statements seeding it with OrderID ranges 70000 to 71000, 71001 to 72000, and 72001 to 73000. — The control table's schema and seed data. Commented out because it only needed to run once, to create OrderConfigTable, not on every notebook execution.

Challenge 01

Make a failed batch resumable, not restart-from-zero

Orders is too large to copy in one unbounded query, and a single monolithic Copy activity that fails partway through leaves no way to know which rows made it across without a full reload. The pipeline needs to isolate failure to one slice of the table, and let that one slice be rerun without touching the rest.

OrderConfigTable holds one row per OrderID range, each with a FilterQuery, a DestinationTable, and a CopyFlag. A Lookup activity reads the active rows, and a ForEach loop fires one Copy per row, with the source query and destination table both resolved dynamically from that row rather than hardcoded per activity.

A Lookup activity named Lookup_config_order feeding a ForEach activity, Settings tab, Sequential checked, Items set to @activity('Lookup_config_order').out... (truncated). — The Lookup feeds the ForEach's item list directly. Nothing about which ranges exist is hardcoded into the loop.

Pipeline expression builder showing the full expression: @activity('Lookup_config_order').output.value. — The full Items expression: every row the Lookup returned becomes one loop iteration.

Copy data activity Source tab, Azure SQL Database connection, Query mode, Query set to @item().FilterQuery. — Each iteration's source query comes from that row's FilterQuery, not from a query typed into the activity itself.

Copy data activity Destination tab, connection AnjaliLakehouse, Root folder Tables, Table set to CaseStudy06 . @item().DestinationTable with Enter manually checked, Table action Append. — The destination table is dynamic too. If a range's CopyFlag were false, that row simply wouldn't appear in the Lookup's output, and rerunning only ever touches the ranges still flagged for copy.

That’s the operational payoff: a range that fails halfway through gets its CopyFlag addressed on its own, and a rerun of the pipeline picks up exactly that range, not the two that already succeeded. A single big Copy activity can’t offer that, because a partial failure inside one unbounded query gives you no row-level boundary to resume from.

The watermark tables that make the daily incremental loads possible get seeded the same way, via commented-out scaffolding kept as a record of the one-time setup, with the live logic below it updating each watermark from the newly landed Bronze data.

Metadata notebook, mostly commented out: an idempotent MERGE INTO OrderConfigTable pattern, a row-count reconciliation query comparing Orders and Sales counts, and CREATE TABLE statements for WatermarkOrders and WatermarkSales. Below the comments, live UPDATE statements set WatermarkOrders and WatermarkSales to 0, followed by a live UPDATE setting WatermarkSales.LastMaxId from MAX(SalesID) in CaseStudy06.Sales. — The reconciliation query and the idempotent MERGE pattern for the control table, both commented out as documentation of the one-off setup, sitting above the watermark tables' live initialisation.

PySpark notebook cell running spark.sql to UPDATE CaseStudy06.WatermarkOrders, setting LastMaxId to the MAX of OrderID from CaseStudy06.Orders, executed successfully. — After the initial Orders batch lands, the watermark table records the highest OrderID copied so far, the value the incremental pipeline will compare against tomorrow.

Pipeline 1 handles Sales the simple way, a single Copy activity, since a full initial load of one table doesn’t need the range-slicing machinery Orders does.

Copy data activity named SalesBatch, Source tab, Azure SQL Database connection, Table mode, Table set to NorthMeridianRetail.Sales. — Pipeline 1: one Copy activity, the full Sales table, no control table needed for a single unbounded initial load.

SalesBatch Copy activity Destination tab, connection AnjaliLakehouse, Table CaseStudy06.Sales, Append write behaviour. — Landed as CaseStudy06.Sales, appended rather than overwritten, since this is the one-time initial load.

PySpark notebook cell running spark.sql to UPDATE CaseStudy06.WatermarkSales, setting LastMaxId to the MAX of SalesID from CaseStudy06.Sales. — Sales gets the same watermark treatment as Orders, recording the highest SalesID after the initial batch.

From here, the daily pipeline takes over. Pipeline 3 reads both watermark tables, pulls only the rows with a higher ID than what’s already landed, and updates the watermark again once the copy succeeds.

Pipeline 3 canvas: a LookupOrders activity feeding a CopyOrdersIncremental Copy data activity feeding an UpdateWatermarkOrders notebook, and in parallel a LookupSales activity feeding a CopySalesIncremental Copy data activity feeding an UpdateWatermarkSales notebook. — Two independent chains, Orders and Sales, each Lookup, Copy, then a notebook that advances that table's watermark.

CopyOrdersIncremental Source tab, Query mode, Query field reading SELECT * FROM NorthMeridianRetail... (truncated). — The Orders incremental query, filtered against the watermark rather than a full table scan.

Full query text: SELECT * FROM NorthMeridianRetail.Orders WHERE OrderID > @{activity('LookupOrders').output.value[0].LastMaxId}. — Only OrderIDs past the last recorded watermark come across. Everything already loaded is never touched again.

CopySalesIncremental Source tab, Query mode, Query field reading SELECT * FROM NorthMeridianRetail... (truncated). — The equivalent query for Sales.

Full query text: SELECT * FROM NorthMeridianRetail.Sales WHERE SalesID > @{activity('LookupSales').output.firstRow.LastMaxId}. — Same logic, though written with Fabric's firstRow shorthand rather than an explicit array index, two valid ways of reaching the same value that happened to land in two different pipelines.

Customers and Products are small enough that daily full-refresh is simpler than tracking a watermark for them, so Pipeline 4 just re-copies both in full every run.

Pipeline 4 canvas: a CopyCustomers Copy data activity feeding a CopyProduct Copy data activity in sequence. — Pipeline 4: two Copy activities, no watermark logic, because a full daily refresh of two small dimension tables costs less than the bookkeeping incremental loading would add.

By the time the initial loads and the first incremental run have both landed, Bronze holds five governed datasets, four from Azure SQL and the region CSV, none of them transformed yet.

Bronze Customer table sample: CustomerID, FirstName, LastName, Email, City, StateCode columns, rows for customers including Dylan Oliver in Castrofort, TX and Jason Erickson in Christopher, MA. — Bronze Customer, untouched, PII columns and all, exactly as it read from Azure SQL.

Bronze Orders table sample: OrderID, SalesID, Status, ShipDate columns, rows with statuses Cancelled, Delivered and Pending. — Bronze Orders, including ShipDate, a column the brief explicitly says is retained for lineage but never used downstream.

Bronze Products table sample: ProductID, ProductName, Category, UnitPrice columns, six rows spanning Sports, Toys, Books, Electronics, Grocery and Beauty categories. — Bronze Products, prices still at full source precision, before Silver rounds them.

Bronze Sales table sample: SalesID, CustomerID, ProductID, Quantity, SalesDate columns, six rows of raw transactions. — Bronze Sales in its rawest form, five columns, no joins, no derived amount yet.

Bronze RegionMapping table sample: StateCode and RegionName columns, rows including NY to Northeast, CA to West, TX to South, FL to South, IL to Midwest, PA to Northeast, OH to Midwest. — The CSV that used to live on one analyst's desktop, now a governed Bronze table with the same audit trail as everything else.

Silver: join, standardise, and denormalise region

One PySpark notebook produces all three Silver tables: Customers joined to the region mapping with a derived FullName, Products with UnitPrice rounded to two decimal places, and Sales joined to both Orders and Products with a computed SalesAmount.

Bronze-to-Silver PySpark notebook: reads all five Bronze tables, then builds df_silver_customer via withColumn FullName as a concat of FirstName and LastName, a left join to df_regions on StateCode, a drop of FirstName, LastName and StateCode, then, in a second block, the same withColumn FullName and left join to df_regions repeated a second time before the final select and write. Also builds df_silver_products by rounding UnitPrice, and df_silver_sales by joining Sales to Products and Orders and computing SalesAmount as UnitPrice times Quantity. — The FullName-and-region-join step appears twice in a row in the same chain, an artifact of iterating on the notebook rather than a deliberate step. It's redundant, though it didn't stop the correct FullName and RegionName values from reaching the table below.

Silver_Customer result table: CustomerID, FullName, Email, City, RegionName columns, rows including Jody Wood in West Amy, Northeast and Jennifer Parker in West Leslie, South. — FullName derived, RegionName joined in from the CSV, StateCode and the separate name fields gone. This is the table that ends the RegionMapping version-drift problem for good.

Silver_Products result table: ProductID, ProductName, Category, UnitPrice columns, UnitPrice values now rounded to two decimal places such as 119.37 and 422.45. — Same rows as Bronze, UnitPrice now rounded rather than carrying full source precision.

Silver_sales result table with 12 columns and 1000 rows: SalesID, ProductID, CustomerID, Quantity, SalesDate, ProductName, Category, UnitPrice, SalesAmount, OrderID, plus Status and ShipDate from the Orders join. — Every transaction now carries its product details, its computed SalesAmount, and its order status in one row. 1,000 rows in this initial load, matching the Products table's full coverage the brief promised.

Gold: collapse into a star schema

The Silver-to-Gold notebook generates surrogate keys with monotonically_increasing_id(), builds Dim_Date from the distinct dates in the data, and groups Sales down to the (DateKey, CustomerKey, ProductKey, Status) grain the brief specified.

Silver-to-Gold PySpark notebook: reads Silver_customer, adds CustomerKey via monotonically_increasing_id(), selects the Gold columns and writes Dim_Customer. Repeats the pattern for Dim_Product. Then reads Silver_sales, adds DateKey via date_format cast to int, joins to Dim_Customer and Dim_Product, groups by DateKey, CustomerKey, ProductKey and Status, and aggregates sum of SalesAmount as TotalSalesAmount, sum of Quantity as TotalQuantity, and a count as OrderCount, writing Fact_Sales. — Surrogate keys generated in the same pass that builds the dimension, then the fact table's groupBy collapses a thousand individual transactions down to one row per unique combination of date, customer, product and status.

Dim_Date generation notebook, part one: building df_gold_dates from a distinct-dates DataFrame, deriving DateKey as a primary key, Year, YearMonth, YearQuarter, Quarter, MonthNumber, MonthName, MonthShort, WeekOfYear, DayOfMonth, DayOfWeek, DayName, DayShort, DayOfYear and an IsWeekend flag. — The brief's Dim_Date spec asked for three columns: DateKey, Year, Quarter. This notebook builds fourteen, calendar attributes a real Power BI report would eventually want.

Dim_Date generation notebook, part two: FirstDayOfMonth and LastDayOfMonth via trunc and last_day, an IsLeapYear flag computed from the standard leap-year rule, then display and a write to AnjaliLakehouse.CaseStudy06Gold.Dim_Date. — Even a leap-year flag, computed with the actual four-hundred-year rule rather than the simplified divisible-by-four shortcut that gets 2000 and 2100 wrong.

Dim_Customer Gold result: CustomerKey, CustomerID, FullName, Email, City, RegionName columns, CustomerKey values starting at 0 and incrementing sequentially. — The dimension a Power BI report actually joins against, CustomerKey standing in for CustomerID everywhere downstream.

Dim_Product Gold result: ProductKey, ProductID, ProductName, Category, UnitPrice columns. — Same pattern for Products, ProductKey generated fresh rather than reused from the source system.

Fact_Sales Gold result: DateKey, CustomerKey, ProductKey, Status, TotalSalesAmount, TotalQuantity, OrderCount columns, rows showing pre-aggregated totals such as a Pending order on 20250701 totalling 438.28 across 2 units. — This is the pre-aggregated grain the brief asked for: one row per date, customer, product and status combination, not one row per transaction.

Dim_Date Gold result: Date, DateKey, Year, YearMonth, YearQuarter, Quarter, MonthNumber, MonthName, MonthShort, WeekOfYear columns, rows for early January 2023. — The finished date dimension, ready to slice Fact_Sales by quarter, month or week without a single date function running inside Power BI itself.

The semantic model and report

The Power BI Semantic Model connects to Gold via Direct Lake, reading the Delta tables directly rather than importing a copy, with one-to-many relationships from each dimension into Fact_Sales.

Fabric semantic model editing window showing four table cards: dim_customer (City, CustomerID, CustomerKey, Email, FullName, RegionName), fact_sales (CustomerKey, DateKey, OrderCount, ProductKey, Status, TotalQuantity, TotalSalesAmount), dim_product (Category, ProductID, ProductKey, ProductName, UnitPrice), and dim_date (Date, DateKey, DayName, DayOfMonth, DayOfWeek, DayOfYear, FirstDayOfMonth, IsLeapYear). — All four Gold tables in the semantic model editor, positioned but not yet wired together.

Semantic model relationship diagram: CaseStudy06Gold dim_product connects one-to-many into CaseStudy06Gold fact_sales, CaseStudy06Gold dim_date connects one-to-many into fact_sales with a single-direction cross-filter arrow, and CaseStudy06Gold dim_customer connects one-to-many into fact_sales. — Three one-to-many relationships, each single-direction, exactly the star schema shape the brief specified and nothing more complex than that.

Power BI Sales Performance Dashboard for North Meridian Retail Group. KPI tiles read Total Sales $3,137K (up 179.8% versus last year), Total Orders 4000, Total Unit 12103. Filters for date range, status, region and city sit on the left. A Total Sale by Date line chart runs from January 2023 to mid-2025 with a peak of $11,986.9. A Total Sale by Category bar chart ranks Sports at $602.51K down to Grocery at $250.86K. A Top 5 Product bar chart lists five products each around $35K to $39K. — The report the brief asked for was two visuals, a trend line and a top-five list. This one shipped with both, plus KPI tiles and four filters, built on the same Fact_Sales grain the whole pipeline exists to produce.

Orchestration

A Master Pipeline chains the daily steps in dependency order, incremental load first, then dimension refresh, then the two PySpark notebooks, then a semantic model refresh so the report never shows stale Gold data next to a fresh Bronze load.

Master Pipeline canvas: Run_Pipeline_3 (Invoke Pipeline) feeding Run_Pipeline_4 (Invoke Pipeline) feeding a BronzeToSilver Notebook activity feeding a SilverToGold Notebook activity feeding a Semantic model refresh activity, all connected in sequence with success dependencies. — Five steps, each gated on the previous one succeeding: incremental ingestion, dimension refresh, Bronze to Silver, Silver to Gold, then the semantic model refresh that makes the new data visible in the report.

Masterpipeline Schedule settings: an Every day schedule, Time of day 06:00 AM, Time zone UTC+05:30 Chennai, Kolkata, Mumbai, New Delhi, toggled On, with Last successful update reading Not available. — The daily trigger, set for 06:00 and switched on. Last successful update still reads Not available here, this schedule was armed but hadn't completed its first live run at the time of this screenshot.

What this proves

A five-way-join transactional database and a star schema an executive can read at 8am aren’t reconciled by picking one over the other, they’re reconciled by putting a Lakehouse between them and letting each layer do one job. Bronze makes replay possible. Silver makes the RegionMapping CSV’s version drift someone else’s problem, permanently. Gold makes the fact table small enough that Direct Lake and VertiPaq can do their job. None of that required touching the point-of-sale database once.