The Medallion Pipeline Architecture
Modern analytics architectures rely on the Medallion pattern to systematically improve data quality and schema reliability as information progresses from raw ingestion to consumer-ready models. In Microsoft Fabric, this architecture is implemented inside OneLake using Delta Lake Parquet tables.
[Source Systems] (REST APIs, SQL Databases, ERP Files)
│
▼ (Data Factory Copy Activity)
[Bronze Lakehouse] (Raw Delta Tables / Unmodified Event Logs)
│
▼ (PySpark Transformation Notebook)
[Silver Lakehouse] (Cleansed, Type-Casted, Deduplicated Star Schema)
│
▼ (Direct Lake Engine / Zero Copy)
[Gold Semantic Model] (Curated Measures, Hierarchies & Business Logic)
│
▼
[Power BI Executive Reports] (Interactive Visual Dashboards)Step 1: Silver Cleansing and Deduplication in PySpark
Once raw records land in the Bronze Lakehouse, a scheduled PySpark notebook processes incremental batches. The notebook enforces relational keys, parses timestamps, removes duplicate rows based on business identifiers, and handles missing dimension keys:
from pyspark.sql.functions import col, row_number, coalesce, lit
from pyspark.sql.window import Window
# Read incremental bronze data
raw_df = spark.read.format("delta").table("Bronze_Lakehouse.raw_sales")
# Deduplicate based on OrderId and latest modified timestamp
window_spec = Window.partitionBy("OrderId").orderBy(col("ModifiedDate").desc())
deduped_df = raw_df.withColumn("row_rank", row_number().over(window_spec)) \
.filter(col("row_rank") == 1) \
.drop("row_rank")
# Cleanse customer references and assign default unknown surrogate key
cleansed_df = deduped_df.withColumn(
"CustomerKey",
coalesce(col("CustomerId"), lit("UNKNOWN_CUST"))
).withColumn(
"NetSales",
col("GrossAmount") - col("DiscountAmount")
)
# Merge into Silver Delta Table
cleansed_df.write.format("delta") \
.mode("overwrite") \
.option("overwriteSchema", "true") \
.saveAsTable("Silver_Lakehouse.fact_sales")Step 2: Direct Lake Semantic Model Configuration
With data organized in the Silver Lakehouse, creating a Direct Lake semantic model requires zero data movement. In the Fabric Web Workspace, navigate to the Lakehouse, select New Semantic Model, and choose the cleansed tables. Define relationships using single-direction 1-to-many cardinality between dimension tables and fact tables.
Validate that all columns used in relationships are formatted with consistent data types (e.g., Integer to Integer or String to String), as type mismatches at relationship boundaries force the query engine to revert to DirectQuery.
Step 3: Automated Quality Reconciliation
Before publishing the semantic model to decision-makers, verify that the total revenue and row count in the Gold semantic model match the source system ledger exactly. Create a verification query running against the Lakehouse SQL Analytics Endpoint:
-- Verify row counts and revenue parity between Bronze and Silver
SELECT
'Bronze_Raw' AS Stage,
COUNT(*) AS TotalRows,
SUM(GrossAmount) AS TotalGross
FROM Bronze_Lakehouse.dbo.raw_sales
UNION ALL
SELECT
'Silver_Cleansed' AS Stage,
COUNT(*) AS TotalRows,
SUM(GrossAmount) AS TotalGross
FROM Silver_Lakehouse.dbo.fact_sales;Any discrepancy between stages indicates unhandled nulls or filtering bugs that must be resolved prior to report deployment.
