Implementing Medallion Architecture in a Microsoft Fabric Lakehouse: Opinionated Folder and Table Patterns

Why Medallion Architecture Still Matters in Fabric

Fabric gives you OneLake, shortcuts, and lakehouses, but none of that replaces a clear convention. Medallion architecture solves three recurring problems:

• Data trust – business users know Gold is curated and safe to report on.
• Change isolation – upstream schema chaos is absorbed in Bronze/Silver, not in your reports.
• Reusability – multiple Gold models can reuse the same standardized Silver layer.

In Fabric, the medallion pattern is mostly about how you structure your lakehouse (folders, table names) and how you move data between layers (Dataflows Gen2, Data Factory pipelines, or Notebook jobs).

Baseline Lakehouse Structure for Bronze, Silver, Gold

You can implement medallion layers in several ways:

1. Separate workspaces per layer (heavy governance, more overhead).
2. Separate lakehouses per layer (common in larger environments).
3. Single lakehouse with clear medallion zones (simple, good default).

For most teams, option 3 is a solid starting point.

Recommended Lakehouse Layout

Inside a single Fabric Lakehouse, use this folder structure:

/lakehouse
  /Files
    /bronze
      /raw
        /<source_system>
          /<entity>
            /yyyy
              /MM
                /dd
    /silver
      /staging
        /<domain>
          /<entity>
    /gold
      /mart
        /<subject_area>
          /dim
          /fact
  /Tables
    /bronze
      /<source_system>_<entity>
    /silver
      /<domain>_<entity>
    /gold
      /dim_<subject_area>_<entity>
      /fact_<subject_area>_<entity>

Key ideas:

• Files: where raw parquet/CSV/JSON land and intermediate files can live.
• Tables: Delta tables exposed to SQL Endpoint and Power BI.
• Each layer has both Files and Tables to keep ingestion flexible.

Bronze: Raw Ingestion Patterns

Bronze is about getting data in, with minimal transformation.

Bronze Folder and Table Pattern

• Files
  • /Files/bronze/raw/<source_system>/<entity>/yyyy/MM/dd/ for incremental loads.
• Tables
  • /Tables/bronze/<source_system>_<entity> as a Delta table.

Example for an ERP system erp and entity sales_order:

/Files/bronze/raw/erp/sales_order/2024/09/23/part-0001.parquet
/Tables/bronze/erp_sales_order

Typical Bronze Transformations

Keep it minimal:

• Schema inference or basic casting.
• Add ingestion metadata: load date, source file, batch id.
• No business rules, no joins.

Example PySpark notebook for Bronze ingestion:

from pyspark.sql import functions as F

source_path = "/lakehouse/Files/bronze/raw/erp/sales_order/2024/09/23"
bronze_table = "bronze.erp_sales_order"

raw_df = (
    spark.read
         .format("parquet")
         .load(source_path)
)

enriched_df = (
    raw_df
    .withColumn("_ingestion_date", F.current_timestamp())
    .withColumn("_source_path", F.input_file_name())
)

(enriched_df
 .write
 .format("delta")
 .mode("append")
 .saveAsTable(bronze_table)
)

Bronze Design Rules

• One Bronze table per source entity.
• Keep column names as close to source as possible.
• Never delete or overwrite history unless you have a strong compliance reason.

Silver: Standardized, Cleaned, and Join-Ready

Silver is where you make the data usable for multiple downstream consumers.

Silver Folder and Table Pattern

Organize by domain (e.g., sales, finance, hr):

• Files
  • /Files/silver/staging/<domain>/<entity>/.
• Tables
  • /Tables/silver/<domain>_<entity>.

Example:

/Files/silver/staging/sales/sales_order/
/Tables/silver/sales_sales_order

Typical Silver Transformations

Silver usually handles:

• Standardization
  • Rename columns to a consistent naming convention (snake_case or PascalCase).
  • Normalize data types (e.g., dates, decimals).
• Cleaning
  • Remove obvious duplicates.
  • Handle nulls and invalid values.
• Light modeling
  • Flatten nested JSON.
  • Split multi-use columns.

Example SQL to build a Silver table from Bronze:

CREATE OR REPLACE TABLE silver.sales_sales_order AS
SELECT
    CAST(order_id AS BIGINT)              AS order_id,
    CAST(customer_id AS BIGINT)           AS customer_id,
    CAST(order_date AS DATE)              AS order_date,
    CAST(order_status AS STRING)          AS order_status,
    CAST(total_amount AS DECIMAL(18,2))   AS total_amount,
    _ingestion_date,
    _source_path
FROM bronze.erp_sales_order;

For incremental updates, switch to INSERT INTO + watermark filters or use MERGE based on a business key.

Silver Design Rules

• One Silver table per logical entity, not necessarily per source.
• You can combine multiple Bronze sources if they represent the same concept.
• Silver should be join-ready, but not yet modeled as star schema.

Gold: Star-Schema Data Marts for Reporting

Gold is optimized for analytics and Power BI: facts and dimensions.

Gold Folder and Table Pattern

Organize by subject area (e.g., sales, inventory, finance_reporting):

• Files
  • /Files/gold/mart/<subject_area>/dim/.
  • /Files/gold/mart/<subject_area>/fact/.
• Tables
  • /Tables/gold/dim_<subject_area>_<entity>.
  • /Tables/gold/fact_<subject_area>_<entity>.

Example for sales reporting:

/Tables/gold/dim_sales_customer
/Tables/gold/dim_sales_date
/Tables/gold/fact_sales_order

Building Gold Dimensions

Dimensions should be:

• Surrogate-keyed (customer_key, product_key).
• Slowly changing (if needed) with valid_from, valid_to, is_current.

Example SQL for a simple Type 1 dimension:

CREATE OR REPLACE TABLE gold.dim_sales_customer AS
SELECT DISTINCT
    CAST(customer_id AS BIGINT) AS customer_key,
    customer_name,
    customer_segment,
    country,
    city,
    postal_code
FROM silver.sales_customer;

Building Gold Fact Tables

Facts should:

• Use surrogate keys from dimensions.
• Store additive and semi-additive measures.
• Be grain-consistent (e.g., one row per order line).

Example SQL for a fact table:

CREATE OR REPLACE TABLE gold.fact_sales_order AS
SELECT
    so.order_id,
    so.order_line_id,
    dc.customer_key,
    dd.date_key        AS order_date_key,
    so.product_id,
    so.quantity,
    so.unit_price,
    so.discount_amount,
    so.total_amount
FROM silver.sales_sales_order_line AS so
LEFT JOIN gold.dim_sales_customer AS dc
    ON so.customer_id = dc.customer_key
LEFT JOIN gold.dim_sales_date AS dd
    ON so.order_date = dd.date;

Gold Design Rules

• Gold is report-facing; keep it stable and versioned.
• Avoid exposing raw technical columns (_ingestion_date, _source_path).
• Keep naming consistent with your Power BI semantic model.

Orchestrating Bronze → Silver → Gold in Fabric

You can implement the flows in several ways. A common pattern:

1. Ingestion (Bronze)
   • Data Factory pipeline or Dataflow Gen2 loads into /Files/bronze/raw/....
   • Notebook or Dataflow writes to Tables/bronze/....
2. Refinement (Silver)
   • Notebook or Dataflow reads Bronze tables.
   • Applies standardization, writes to Tables/silver/....
3. Modeling (Gold)
   • SQL scripts or notebooks build facts/dims in Tables/gold/....

Example: Simple Notebook-Driven Orchestration

In a Fabric Notebook, you can chain the layers:

# 1. Bronze load already completed by another pipeline

# 2. Build Silver from Bronze
spark.sql("""
CREATE OR REPLACE TABLE silver.sales_sales_order AS
SELECT
    CAST(order_id AS BIGINT) AS order_id,
    CAST(customer_id AS BIGINT) AS customer_id,
    CAST(order_date AS DATE) AS order_date,
    total_amount
FROM bronze.erp_sales_order
""")

# 3. Build Gold from Silver
spark.sql("""
CREATE OR REPLACE TABLE gold.fact_sales_order AS
SELECT
    order_id,
    customer_id,
    order_date,
    total_amount
FROM silver.sales_sales_order
""")

Then schedule the notebook inside a Data Factory pipeline with:

• Triggers (time-based or event-based).
• Dependencies (Bronze notebook must succeed before Silver, etc.).

Naming Conventions That Save You Pain Later

Agree on conventions early. A simple, workable set:

• Schemas (logical)
  • bronze, silver, gold.
• Tables
  • Bronze: <source>_<entity> → erp_sales_order.
  • Silver: <domain>_<entity> → sales_sales_order.
  • Gold dimensions: dim_<subject>_<entity> → dim_sales_customer.
  • Gold facts: fact_<subject>_<entity> → fact_sales_order.
• Columns
  • Keys: *_id for natural keys, *_key for surrogate keys.
  • Dates: *_date as DATE, *_datetime as TIMESTAMP.

Stick to one pattern and document it in your repo or Confluence. The value is not in the specific pattern, but in its consistency.

How This Plays with Power BI

Because Lakehouse tables are Delta, the SQL Endpoint and Power BI connect cleanly to Gold.

Recommended pattern:

1. Build a Power BI semantic model on top of gold.* tables only.
2. Hide Bronze and Silver from ad-hoc users.
3. Use Direct Lake mode when possible for performance.

Example DAX measure on top of gold.fact_sales_order:

Total Sales :=
SUM ( 'fact_sales_order'[total_amount] )

Because the medallion layers keep schemas stable, you can refactor Bronze and Silver without constantly breaking your Power BI model.

One Practical Takeaway

Before you build your next Fabric pipeline, create a single example subject area (e.g., Sales) and implement Bronze, Silver, and Gold exactly with the folder and table patterns above. Once that path works end-to-end, copy the pattern to new domains instead of inventing a new structure for every project.