Case study 03 · Manufacturing

Microsoft Fabric Lakehouse Solution

A OneLake lakehouse with Direct Lake reporting that removed slow imports and duplicated datasets.

Data Engineer — lakehouse build, notebooks, semantic model 4 months

Report refresh: 45 min → seconds
Datasets merged: 9 → 1
Capacity cost: −28%

Executive summary

Implemented a Microsoft Fabric lakehouse that unifies ERP, production and spreadsheet-based planning data in OneLake using a bronze/silver/gold layout. Gold Delta tables feed a single Direct Lake semantic model, so reports read the lakehouse directly with no scheduled import refreshes.

Business problem

Nine Power BI import datasets each pulled the same ERP tables with their own Power Query logic. Refreshes took up to 45 minutes, frequently timed out, and margin figures differed between reports.

Duplicated transformation logic across nine datasets
Refresh failures every week during month-end
Planning data maintained in SharePoint Excel files with no validation
No reusable, queryable layer for analysts outside Power BI

Architecture design

A single Fabric workspace per environment with a lakehouse for bronze/silver, a warehouse for gold serving tables, and a Direct Lake semantic model on top.

L1
Ingestion
Fabric Data pipelines copy ERP and Azure SQL data; Dataflows Gen2 ingest SharePoint planning files.
L2
Bronze (Files)
Raw extracts in OneLake Files, partitioned by load date — immutable and replayable.
L3
Silver (Delta tables)
PySpark notebooks type, cleanse, deduplicate and MERGE into Delta tables.
L4
Gold (Warehouse)
T-SQL builds conformed dimensions and aggregated facts as Delta tables in the Fabric Warehouse.
L5
Semantic (Direct Lake)
One semantic model reads gold Delta tables directly — no import, no duplicate copies.

Source systems

System	Technology	Volume	Cadence
ERP	Azure SQL Database	~12M invoice lines	Every 2 hours
Production (MES)	SQL Server via gateway	~3M events / month	Nightly
Planning	SharePoint Excel	24 workbooks	On change
Reference data	CSV in OneLake	Currencies, plants, calendars	Weekly

Data flow

01Land
Pipelines copy changed records to Files/bronze/<source>/<load_date>/.
02Standardise
Notebooks apply explicit schemas, cast types and reject malformed rows to a quarantine table.
03Merge
Delta MERGE upserts into silver tables keyed on business keys.
04Model
Warehouse T-SQL builds gold dimensions and facts with CTAS.
05Serve
Direct Lake semantic model picks up new data on framing — no refresh window.

Data model

A gold star schema designed specifically for Direct Lake: narrow fact tables, integer keys and pre-aggregated profitability.

Facts

fact_sales
Grain: One row per invoice line
fact_customer_profitability
Grain: One row per customer per month
fact_production_output
Grain: One row per work order per day

Dimensions

dim_customer
SCD Type 2, current-row flag for reporting
dim_product
Product → family → category hierarchy
dim_plant
Plant, region and capacity attributes
dim_date
Fiscal calendar with working-day flags

ETL process

Notebook parameters

A parameter cell receives the load date from the pipeline, making every run replayable for any date.

Quarantine pattern

Rows failing schema or business rules are written to a quarantine table with a reason code rather than dropped.

Table maintenance

Weekly OPTIMIZE and V-Order keep file counts low for fast Direct Lake reads.

Deployment

Fabric Git integration and deployment pipelines promote dev → test → prod.

Tech stack

Platform: Microsoft FabricOneLakeLakehouseWarehouse
Processing: PySpark notebooksDelta LakeDataflows Gen2T-SQL
Orchestration: Fabric Data pipelinesOn-premises data gateway
Consumption: Direct Lake semantic modelPower BIDAX
DevOps: Fabric Git integrationDeployment pipelines

SQL example

Gold profitability fact with CTAS (Fabric Warehouse T-SQL)

sql

-- Materialised as a Delta table so the Direct Lake model reads it natively.
-- (Views would force a DirectQuery fallback.)
DROP TABLE IF EXISTS gold.fact_customer_profitability;

CREATE TABLE gold.fact_customer_profitability
AS
SELECT  c.customer_key,
        CAST(FORMAT(s.invoice_date, 'yyyyMM') AS INT)   AS month_key,
        SUM(s.net_revenue)                              AS net_revenue,
        SUM(s.cost_of_goods)                            AS cost_of_goods,
        SUM(s.net_revenue) - SUM(s.cost_of_goods)       AS gross_margin,
        CAST((SUM(s.net_revenue) - SUM(s.cost_of_goods))
             / NULLIF(SUM(s.net_revenue), 0) AS DECIMAL(9, 4)) AS gross_margin_pct,
        COUNT(DISTINCT s.invoice_id)                    AS invoices
FROM    lh_silver.dbo.sales_invoice_lines AS s
JOIN    gold.dim_customer                 AS c
        ON  c.customer_id = s.customer_id
        AND s.invoice_date >= c.valid_from
        AND s.invoice_date <  c.valid_to
GROUP BY c.customer_key,
         CAST(FORMAT(s.invoice_date, 'yyyyMM') AS INT);

Python example

Bronze → silver MERGE in a Fabric PySpark notebook

python

# Parameter cell — overridden by the Fabric pipeline at run time
load_date = "2026-10-01"

from delta.tables import DeltaTable
from pyspark.sql import functions as F, types as T

schema = T.StructType([
    T.StructField("InvoiceId", T.LongType()),
    T.StructField("LineNo", T.IntegerType()),
    T.StructField("CustomerId", T.IntegerType()),
    T.StructField("InvoiceDate", T.DateType()),
    T.StructField("NetRevenue", T.DecimalType(18, 2)),
    T.StructField("CostOfGoods", T.DecimalType(18, 2)),
])

raw = (spark.read
       .option("header", True)
       .option("dateFormat", "yyyy-MM-dd")
       .schema(schema)
       .csv(f"Files/bronze/erp/invoice_lines/{load_date}/"))

is_valid = (F.col("InvoiceId").isNotNull()
            & F.col("InvoiceDate").isNotNull()
            & (F.col("NetRevenue") >= 0))

valid = (raw.where(is_valid)
            .dropDuplicates(["InvoiceId", "LineNo"])
            .withColumn("_load_date", F.lit(load_date).cast("date"))
            .withColumn("_ingested_at", F.current_timestamp()))

(raw.where(~is_valid)
    .withColumn("_reason", F.lit("failed_validation"))
    .withColumn("_load_date", F.lit(load_date).cast("date"))
    .write.mode("append").saveAsTable("quarantine_invoice_lines"))

(DeltaTable.forName(spark, "sales_invoice_lines").alias("t")
    .merge(valid.alias("s"), "t.InvoiceId = s.InvoiceId AND t.LineNo = s.LineNo")
    .whenMatchedUpdateAll()
    .whenNotMatchedInsertAll()
    .execute())

print(f"Merged {valid.count():,} rows for {load_date}")

Challenges

Challenge

The first gold layer used SQL views, and Direct Lake fell back to DirectQuery.

Solution

Materialised gold as Delta tables with CTAS so the semantic model stays in Direct Lake mode.

Challenge

Excel planning files changed structure without notice.

Solution

Dataflows Gen2 with explicit column mapping plus a schema-check notebook that fails fast with a clear message.

Challenge

Many small files from frequent loads slowed queries.

Solution

Scheduled OPTIMIZE with V-Order and tuned load frequency to produce fewer, larger files.

Performance improvements

Metric	Before	After	Change
Report data latency	45 min refresh	Seconds (Direct Lake)	No refresh window
Semantic models	9	1	−89%
Silver notebook runtime	18 min	6 min	−67%
Fabric capacity usage	Baseline	−28%	−28%

Business impact

One margin definition used by every report, ending month-end disputes
Planning data validated and joined to actuals for the first time
Analysts can query gold tables with SQL — not only through Power BI
Month-end refresh failures eliminated

Lessons learned

Design gold for the consumer: Direct Lake rewards narrow tables and materialised outputs.
Quarantine, don't drop — rejected rows are evidence for the data owner.
Fabric Git integration from day one avoids painful manual promotion later.

Success metrics

Refresh failures / month: 0Down from 4–6
Report users onboarded: 95Across 3 plants
Quarantined rows: < 0.2%Each with a reason code
Time to new KPI: 1 dayPreviously 1–2 weeks

Next case studyDatabricks Medallion Architecture