Implementing the Medallion Architecture in Microsoft Fabric: Bronze, Silver, and Gold Layers

Introduction

Picture this: your team has spent months building a Fabric lakehouse. Data is flowing in from a dozen sources — an ERP system, a CRM, flat files from partners, and a real-time event stream. But somewhere along the way, it turned into a swamp. Raw files sit next to cleaned tables. Half-transformed staging data is getting queried by Power BI dashboards. Nobody is sure which version of the "customer" table is the right one. Reports are inconsistent, pipeline failures corrupt downstream data, and onboarding a new data engineer means a week of archaeology.

This is the problem medallion architecture was designed to solve. By organizing your lakehouse data into three distinct layers — Bronze (raw), Silver (cleaned), and Gold (business-ready) — you give every piece of data a clear contract, a defined purpose, and a predictable place to live. More importantly, you give your team a shared mental model. When everyone agrees on what "Bronze" means, you stop having arguments about whether a table is ready for reporting.

By the end of this lesson, you'll have a working, production-pattern medallion architecture deployed in Microsoft Fabric, built around a realistic retail scenario. You'll understand not just the structure but the reasoning behind each decision.

What you'll learn:

• What the Bronze, Silver, and Gold layers are and what guarantees each one provides
• How to organize multiple lakehouses (or a single one) to implement the pattern in Fabric
• How to ingest raw data into Bronze using Data Pipelines
• How to transform and clean data into Silver using Spark notebooks and PySpark
• How to build aggregated, business-ready Gold tables and expose them to Power BI via Direct Lake

---

Prerequisites

This lesson assumes you're comfortable with the Fabric fundamentals. Specifically, you should already know:

• How to create and navigate a Fabric workspace — if not, start with Fabric Capacities and Workspaces: F SKUs, Trials, and Setting Up Your First Workspace
• What a Lakehouse is and how it stores Delta tables — the Building Your First Lakehouse in Microsoft Fabric: Files, Tables, and the SQL Analytics Endpoint lesson covers this in detail
• Basic PySpark syntax for reading and writing DataFrames

You don't need to have Spark expertise. The code in this lesson is explained step by step.

---

The Logic Behind the Three Layers

Before building anything, you need to internalize why three layers, not two, not five.

Bronze is a landing zone, not a storage format. The prime directive of Bronze is: accept everything, transform nothing. When data arrives from a source system, you want an exact, timestamped copy of it as-is. This means raw JSON, CSV with bad rows, XML, or binary files. Why? Because source systems change, upstream bugs happen, and sometimes you need to re-process data from scratch. Bronze is your insurance policy. You should be able to delete everything downstream and rebuild from Bronze.

Silver is where trust is established. Silver data has been validated, deduplicated, typed, and conformed to your naming standards. It answers the question: "Is this data correct?" A Silver table for orders has a proper order_date timestamp, not the string "01/13/24". It has no duplicate records caused by a pipeline re-running twice. It follows your organization's business rules for things like NULL handling. Silver tables are structured as Delta tables in Parquet format — they're queryable by engineers, analysts, and other pipelines.

Gold is purpose-built for consumers. Gold tables are shaped for specific use cases: a dimensional model for the finance team, an aggregated summary for an executive dashboard, a denormalized wide table for a machine learning feature store. Gold tables change when business questions change, while Silver tables change when source systems change. That distinction is crucial.

---

Designing Your Fabric Architecture: One Lakehouse or Three?

In Microsoft Fabric, you have a choice about how to physically organize the three layers. Two main patterns exist:

Pattern A — Three separate Lakehouses: You create bronze_lakehouse, silver_lakehouse, and gold_lakehouse in the same workspace (or separate workspaces for larger teams). Each lakehouse is a distinct Fabric item with its own file system, Delta tables, and SQL Analytics Endpoint.

Pattern B — One Lakehouse with folder conventions: Everything lives in a single lakehouse. Bronze data sits in Files/bronze/, Silver tables are created with a silver_ prefix, and Gold tables use gold_. This works for small teams but becomes messy at scale.

For production work, three separate lakehouses is the better pattern, and that's what we'll use here. The reasons are practical:

1. You can set different security roles on each lakehouse. Analysts get read access to Silver and Gold. Only engineers touch Bronze.
2. Each lakehouse has its own SQL Analytics Endpoint, so you can give BI tools a Gold-only connection string and guarantee they never accidentally query a raw table.
3. You can apply different OneLake policies or lifecycle rules to each layer — for example, compressing Bronze files after 90 days.

For this lesson, our scenario is a mid-sized retail company. Data sources include:

• A daily CSV export of sales transactions from a legacy POS system
• A REST API delivering product catalog data as JSON
• A partner flat file of store metadata delivered via SFTP

---

Setting Up the Three Lakehouses

In your Fabric workspace, create three lakehouses:

1. Navigate to your workspace and select New > Lakehouse
2. Name the first one RetailBronze
3. Repeat to create RetailSilver and RetailGold

Inside each lakehouse, you'll see the familiar two-pane view: Files on the left and Tables on the right. Bronze will use the Files section heavily (raw files land there). Silver and Gold will live primarily in the Tables section as managed Delta tables.

In RetailBronze, create a folder structure under Files to organize by source system. Using the lakehouse Files explorer, create folders:

• Files/pos_transactions/
• Files/product_catalog/
• Files/store_metadata/

Within each source folder, add date-partitioned subfolders following the pattern YYYY/MM/DD/. You won't create these manually — your pipeline will create them dynamically — but understanding the intended structure helps.

---

Building the Bronze Layer: Land Everything, Transform Nothing

The Bronze ingestion layer is built with Data Pipelines. Pipelines are the right tool here because they handle scheduling, retry logic, and parameter-driven paths without requiring you to write orchestration code. If you haven't built a pipeline before, Orchestrating Loads with Fabric Data Pipelines: Copy Activities, Parameters, and Schedules walks through the mechanics.

For our POS transaction CSV, here's the pipeline pattern:

Create a new Data Pipeline in your workspace called pl_bronze_pos_transactions.

Add a Copy Data activity with these settings:

• Source: Your SFTP or Azure Blob connection pointing to the raw CSV files
• Sink: RetailBronze lakehouse, Files section
• Sink file path: Use dynamic content to build the date-partitioned path:

Files/pos_transactions/@{formatDateTime(utcNow(), 'yyyy')}/@{formatDateTime(utcNow(), 'MM')}/@{formatDateTime(utcNow(), 'dd')}/

• Copy behavior: Preserve hierarchy
• Sink format: Keep as-is (do not transform to Parquet at this stage — land the CSV exactly as-is)

Add a second Copy Data activity for the product catalog JSON from the REST API:

• Source: REST connector with your API endpoint and auth headers
• Sink path: Files/product_catalog/@{formatDateTime(utcNow(), 'yyyy')}/@{formatDateTime(utcNow(), 'MM')}/@{formatDateTime(utcNow(), 'dd')}/catalog.json
• File format: JSON (write as-is)

Schedule the pipeline to run at 2:00 AM daily. Add a pipeline-level timeout of 30 minutes and configure the activity retry count to 3 with a 5-minute interval. This means a transient network issue won't cause a data gap.

By the end of this setup, your Bronze lakehouse accumulates raw files in a predictable structure every day. Anyone on the team can go find exactly what the source system sent on any given date. That's the Bronze contract.

---

Building the Silver Layer: Establish Trust with PySpark

The Silver transformation is where the real engineering happens. We'll use Spark notebooks for this layer because the transformations are complex enough to need code — type casting, deduplication logic, business rule validation — and Spark's distributed processing handles the scale.

Create a new Spark notebook in your workspace called nb_silver_pos_transactions. Attach it to a Spark environment (or use the workspace default). Then add RetailBronze and RetailSilver as lakehouse references in the notebook's lakehouse pane — you'll see an option to add a lakehouse when editing the notebook. Add RetailBronze as a secondary lakehouse so you can read from it, and RetailSilver as the primary (default) so writes go there.

Here's the full Silver transformation notebook for POS transactions:

from pyspark.sql import SparkSession
from pyspark.sql import functions as F
from pyspark.sql.types import (
    StructType, StructField, StringType, 
    DoubleType, IntegerType, TimestampType
)
from delta.tables import DeltaTable
from datetime import datetime, timedelta

spark = SparkSession.builder.getOrCreate()

# ── 1. Read yesterday's Bronze files ────────────────────────────────────────
yesterday = (datetime.utcnow() - timedelta(days=1)).strftime("%Y/%m/%d")
bronze_path = f"abfss://RetailBronze@onelake.dfs.fabric.microsoft.com/<workspace-id>/Files/pos_transactions/{yesterday}/"

raw_df = (
    spark.read
    .option("header", "true")
    .option("inferSchema", "false")   # Never infer schema in production
    .csv(bronze_path)
)

print(f"Raw record count: {raw_df.count()}")
raw_df.printSchema()

# ── 2. Define and enforce the target schema ──────────────────────────────────
# Cast all columns explicitly — never trust inferSchema on Bronze data
typed_df = (
    raw_df
    .select(
        F.col("transaction_id").cast(StringType()),
        F.col("store_id").cast(StringType()),
        F.col("product_id").cast(StringType()),
        F.col("quantity").cast(IntegerType()),
        F.col("unit_price").cast(DoubleType()),
        F.col("transaction_timestamp").cast(TimestampType()),
        F.col("payment_method").cast(StringType()),
        F.col("cashier_id").cast(StringType()),
    )
    .withColumn("_ingestion_date", F.lit(yesterday))
    .withColumn("_source_file", F.input_file_name())
)

# ── 3. Validate: identify and quarantine bad rows ────────────────────────────
# Good rows: required fields not null, quantity > 0, price >= 0
valid_df = typed_df.filter(
    F.col("transaction_id").isNotNull()
    & F.col("store_id").isNotNull()
    & F.col("quantity").isNotNull()
    & (F.col("quantity") > 0)
    & F.col("unit_price").isNotNull()
    & (F.col("unit_price") >= 0)
    & F.col("transaction_timestamp").isNotNull()
)

invalid_df = typed_df.subtract(valid_df)

invalid_count = invalid_df.count()
valid_count = valid_df.count()
print(f"Valid rows: {valid_count} | Invalid rows: {invalid_count}")

# Write quarantined rows to a separate Silver table for investigation
if invalid_count > 0:
    (
        invalid_df
        .write
        .format("delta")
        .mode("append")
        .partitionBy("_ingestion_date")
        .saveAsTable("silver_pos_transactions_quarantine")
    )

# ── 4. Deduplicate ───────────────────────────────────────────────────────────
# Source system occasionally re-sends records. Keep the latest version
# of each transaction_id based on transaction_timestamp.
deduped_df = (
    valid_df
    .withColumn(
        "_row_num",
        F.row_number().over(
            Window.partitionBy("transaction_id")
            .orderBy(F.col("transaction_timestamp").desc())
        )
    )
    .filter(F.col("_row_num") == 1)
    .drop("_row_num")
)

print(f"After dedup: {deduped_df.count()} rows")

# ── 5. Add derived columns used across many downstream Gold tables ────────────
enriched_df = (
    deduped_df
    .withColumn("revenue", F.round(F.col("quantity") * F.col("unit_price"), 2))
    .withColumn("transaction_date", F.to_date("transaction_timestamp"))
    .withColumn("transaction_hour", F.hour("transaction_timestamp"))
)

# ── 6. Upsert into the Silver Delta table (MERGE pattern) ────────────────────
# Upsert handles pipeline reruns gracefully — same transaction_id won't
# create duplicate rows if this notebook runs twice for the same day.
silver_table_name = "silver_pos_transactions"

if spark.catalog.tableExists(silver_table_name):
    silver_table = DeltaTable.forName(spark, silver_table_name)
    (
        silver_table.alias("existing")
        .merge(
            enriched_df.alias("incoming"),
            "existing.transaction_id = incoming.transaction_id"
        )
        .whenMatchedUpdateAll()
        .whenNotMatchedInsertAll()
        .execute()
    )
else:
    # First run — create the table
    (
        enriched_df
        .write
        .format("delta")
        .mode("overwrite")
        .partitionBy("transaction_date")
        .saveAsTable(silver_table_name)
    )

print(f"Silver table updated: {silver_table_name}")

This notebook does five important things that distinguish a production Silver layer from a quick transformation script:

1. Explicit schema casting — you define what the data should be, not what it happens to be today
2. Quarantine pattern — bad rows don't fail the pipeline; they're isolated for investigation
3. Idempotent deduplication — running the pipeline twice doesn't double-count records
4. MERGE/upsert — the Silver table can be safely re-processed without full rebuilds
5. Derived columns computed once — revenue and transaction_date are calculated here so every Gold table downstream gets a consistent definition

---

Building the Gold Layer: Purpose-Built for Business

Gold tables are different in character from Silver. Where Silver has one table per source entity (one for transactions, one for products, one for stores), Gold has one table per business question. Your retail stakeholders have asked for:

1. A daily sales summary by store and product category
2. A slowly changing fact table for Power BI's Direct Lake mode

Let's build the first one.

Create a new notebook called nb_gold_daily_sales_summary. Add RetailSilver as a secondary lakehouse and RetailGold as the primary.

from pyspark.sql import functions as F
from pyspark.sql import SparkSession

spark = SparkSession.builder.getOrCreate()

# ── Read from Silver tables (joins across entities) ──────────────────────────
transactions = spark.table("RetailSilver.silver_pos_transactions")
products = spark.table("RetailSilver.silver_products")
stores = spark.table("RetailSilver.silver_stores")

# ── Build the Gold aggregation ────────────────────────────────────────────────
daily_summary = (
    transactions
    .join(products, on="product_id", how="left")
    .join(stores, on="store_id", how="left")
    .groupBy(
        "transaction_date",
        "store_id",
        F.col("store_name"),
        F.col("store_region"),
        F.col("product_category"),
    )
    .agg(
        F.sum("revenue").alias("total_revenue"),
        F.sum("quantity").alias("total_units_sold"),
        F.countDistinct("transaction_id").alias("transaction_count"),
        F.avg("unit_price").alias("avg_unit_price"),
        F.countDistinct("cashier_id").alias("unique_cashiers"),
    )
    .withColumn("revenue_per_transaction",
        F.round(F.col("total_revenue") / F.col("transaction_count"), 2))
    .withColumn("_gold_updated_at", F.current_timestamp())
)

# ── Write to Gold — full refresh pattern for aggregations ────────────────────
# For aggregate tables, a full refresh is often simpler and more reliable
# than a MERGE. The table is rebuilt each run from Silver truth.
(
    daily_summary
    .write
    .format("delta")
    .mode("overwrite")
    .option("overwriteSchema", "true")
    .partitionBy("transaction_date")
    .saveAsTable("gold_daily_sales_summary")
)

print("Gold table refreshed: gold_daily_sales_summary")
spark.sql("SELECT COUNT(*) as row_count FROM gold_daily_sales_summary").show()

Notice the design choice at the bottom: this Gold table uses a full overwrite rather than MERGE. That's intentional. For aggregate tables, a full overwrite is simpler, faster, and avoids subtle bugs where a MERGE might leave stale aggregations from a day that had late-arriving corrections in Silver. The Silver MERGE pattern protects you from duplication; the Gold overwrite guarantees freshness.

For the Power BI team to get maximum performance using Direct Lake mode, your Gold Delta tables need to be optimized. Add this at the end of your Gold notebook:

# ── Optimize Delta table for Direct Lake ─────────────────────────────────────
# OPTIMIZE compacts small files into larger ones — critical for Direct Lake
# V-Order encoding is applied automatically in Fabric Spark environments

spark.sql("OPTIMIZE gold_daily_sales_summary ZORDER BY (transaction_date, store_region)")
spark.sql("VACUUM gold_daily_sales_summary RETAIN 168 HOURS")

ZORDER BY co-locates rows with similar values for your filter columns in the same files. When Power BI renders a report filtered by transaction_date and store_region, Delta can skip entire files rather than scanning the whole table. This makes a measurable difference at scale.

---

Orchestrating the Full Pipeline

The three notebooks (Bronze ingestion, Silver transformation, Gold aggregation) need to run in sequence every morning. Build this as a single orchestrating Data Pipeline called pl_medallion_full_refresh.

The pipeline structure is:

1. Activity 1: Copy Data — Bronze ingestion (POS transactions CSV)
2. Activity 2: Copy Data — Bronze ingestion (product catalog JSON)
3. Activity 3: Notebook activity — run nb_silver_pos_transactions
4. Activity 4: Notebook activity — run nb_silver_products
5. Activity 5: Notebook activity — run nb_gold_daily_sales_summary

Chain Activities 1 and 2 to run in parallel (connect them both to Activity 3 using "on success" connectors). Activities 3 and 4 run in parallel. Activity 5 depends on both 3 and 4 completing.

Set the pipeline schedule to trigger at 2:30 AM, giving the upstream source systems time to export their files. Set activity-level timeout on each Notebook activity to 45 minutes — a notebook that runs indefinitely usually means it's stuck on a shuffle operation or a network issue.

---

Hands-On Exercise

Now that you understand the pattern end-to-end, build this yourself using a realistic dataset.

Your scenario: You're implementing a medallion lakehouse for a fictional e-commerce company called Northfield Retail. Use the NYC Yellow Taxi Trip dataset as a stand-in (it's free, large, and has all the messiness of real data).

Step 1: Set up Bronze

• Create three lakehouses: NorthfieldBronze, NorthfieldSilver, NorthfieldGold
• Build a pipeline that downloads a monthly taxi CSV from the public URL and lands it in NorthfieldBronze/Files/taxi_trips/YYYY/MM/ without transformation

Step 2: Build Silver

• Write a notebook that reads the raw CSV from Bronze
• Cast all columns to their correct types (watch for the tpep_pickup_datetime string format)
• Filter out rows where passenger_count is null or zero, trip_distance is negative, or fare_amount is negative
• Write invalid rows to silver_taxi_trips_quarantine
• MERGE valid rows into silver_taxi_trips partitioned by trip date

Step 3: Build Gold

• Create gold_daily_trip_summary aggregating by pickup_date and payment_type
• Include: total_fare, avg_trip_distance, total_trips, avg_tip_pct (tip / fare)
• Run OPTIMIZE with ZORDER on pickup_date

Stretch goal: Add a second Gold table called gold_hourly_demand that shows trip count by hour of day and day of week. Think about what filter columns to ZORDER on for this one.

---

Common Mistakes & Troubleshooting

Mistake 1: Schema drift breaking Silver notebooks

Source systems change their schemas without notice. A new column appears in the CSV, or a column is renamed. Your Silver notebook fails with a AnalysisException: cannot resolve column name.

Fix: Explicitly select only the columns you need in your Silver notebook (as shown above). Unknown new columns in Bronze are silently ignored. If a column disappears, your explicit cast will fail with a clear error rather than producing silently wrong data.

Mistake 2: Silver tables getting too many small files

If your pipeline runs every 15 minutes and appends to Silver, you'll accumulate thousands of tiny Delta files over time. Queries slow to a crawl.

Fix: Run OPTIMIZE on Silver tables weekly (not just Gold). Add it as a scheduled notebook that runs Sunday night. Also consider increasing the Spark shuffle partition count for small datasets: spark.conf.set("spark.sql.shuffle.partitions", "8") — the default of 200 is designed for cluster-scale data.

Mistake 3: Reading from the wrong layer in Gold

A new engineer writes a Gold notebook that reads directly from a Bronze JSON file because "it had the field I needed." Three months later, the Bronze file format changes and the Gold notebook silently starts returning wrong data.

Fix: Enforce layer isolation through lakehouse permissions. Give the Gold notebooks' service principal read access to Silver only, not Bronze. Let the security model enforce the architectural rule.

Mistake 4: Not adding _ingestion_date and _source_file to Bronze records

When a data quality issue is found months later, engineers need to trace a bad record back to its source file. Without metadata columns like _ingestion_date and _source_file, this becomes a needle-in-a-haystack problem.

Fix: Always add F.lit(ingestion_date) and F.input_file_name() as system columns in your Silver notebook when reading Bronze files. These columns carry through to quarantine tables where they're especially valuable.

Mistake 5: Gold tables designed for one team, broken by another

The finance team needs daily revenue totals. The marketing team adds a requirement to the same Gold table for email-opened attribution. The two requirements conflict and the table becomes messy and slow.

Fix: Gold tables should be purpose-built and team-specific. It's fine — good, actually — to have gold_finance_daily_revenue and gold_marketing_campaign_performance as separate tables even if they share some Silver source data. Gold is cheap to rebuild; confusion is expensive.

---

Summary & Next Steps

You've now built a medallion architecture that does what the pattern promises: raw data lands safely in Bronze, trust is established in Silver, and business-ready aggregations are served from Gold. More importantly, you understand why each layer is designed the way it is — which means you can adapt the pattern when reality doesn't match the textbook.

The key decisions to remember:

• Three separate lakehouses provide security isolation and clean SQL endpoints for each layer
• Bronze is append-only and format-preserving — never transform here
• Silver uses MERGE semantics for idempotency and quarantine for bad data
• Gold uses the write pattern that matches the business logic — overwrite for aggregations, MERGE for slowly changing entities
• OPTIMIZE and ZORDER on Gold tables makes Direct Lake reporting fast

From here, the natural next steps are:

• Connect your Gold lakehouse to Power BI using Direct Lake mode — this gives report consumers a live view of your Delta tables without import delays
• Explore how OneLake shortcuts can let you bring external data sources (like Azure Data Lake Gen2 or Amazon S3) into your Bronze layer without copying files
• If your team has analysts who prefer SQL over PySpark, review Fabric Lakehouse vs Warehouse: Choosing the Right Store for Your Workload to understand when a Fabric Warehouse might complement your Gold layer for SQL-heavy workloads

The medallion pattern isn't just a folder structure — it's a set of contracts between your team members about data quality and data lineage. The architecture documents your decisions. When something breaks at 3 AM, that documentation is what gets you back to sleep by 4.