Building a Lakehouse for BI in Microsoft Fabric: Practical Patterns for Bronze, Silver, Gold

Core Principles Before You Touch Fabric

Before you start creating Lakehouses and Pipelines, lock in three decisions:

1. Single source of truth per domain

   • One Lakehouse per domain (e.g. Sales, Finance, Operations) is usually enough.
   • Avoid a Lakehouse per report; that multiplies maintenance.

2. Layered model is non‑negotiable

   • Bronze = raw, immutable.
   • Silver = cleaned, conformed.
   • Gold = analytics‑ready, star schema.

3. Schema and naming discipline

   • Tables and columns must be predictable: sales_order_header, order_date, customer_key.
   • If people need a wiki to guess column meaning, your model is not ready for Gold.

Lakehouse Structure: A Concrete Pattern

A simple, repeatable pattern for one domain:

• Workspace: wh-sales-analytics
• Lakehouse: lh-sales
• Folders / Tables (in the Lakehouse):
  • /Files/bronze/<source_system>/<entity> (optional if you stage files first)
  • /Tables/bronze_<entity>
  • /Tables/silver_<entity>
  • /Tables/gold_<mart_name>_<table>

Example for Sales:

• bronze_orders_raw
• silver_orders_header, silver_orders_line
• gold_sales_fct_sales, gold_sales_dim_customer, gold_sales_dim_date

This keeps three things obvious at a glance:

• Where data comes from (source‑oriented Bronze).
• Where it is cleaned and conformed (entity‑oriented Silver).
• What is ready for BI and Power BI models (subject‑oriented Gold).

Bronze Layer: Land Raw, Land Fast

Bronze is about ingesting and preserving source data with minimal logic.

What Goes Into Bronze

• Raw data from:
  • Databases (SQL, Oracle, SAP, etc.)
  • Line‑of‑business APIs
  • CSV/Excel drops
  • Event streams
• Only light transformations:
  • Add load metadata (_load_date, _source_file, _batch_id).
  • Normalize basic types (e.g. parse dates, integers).

Design Rules for Bronze

1. Immutable

   • No updates for business corrections; only re‑loads or new versions.
   • Keep history where possible.

2. Append‑only pattern

   • Land each batch as new rows in a Delta table.
   • Use partitioning on natural load keys (e.g. ingestion_date).

3. One table per source object

   • bronze_erp_sales_order_header
   • bronze_erp_sales_order_line
   • bronze_crm_account

Example: Ingest to Bronze with a Dataflow Gen2

A typical pattern when landing from SQL Server into bronze_orders_raw:

let
    Source = Sql.Database("sql-prod", "ERP"),
    Orders = Source{[Schema="dbo",Item="SalesOrders"]}[Data],
    KeepColumns = Table.SelectColumns(Orders,
        {"OrderID", "CustomerID", "OrderDate", "Status", "TotalAmount"}),
    AddLoadDate = Table.AddColumn(KeepColumns, "_load_date", each DateTimeZone.FixedUtcNow(), type datetimezone),
    AddSource = Table.AddColumn(AddLoadDate, "_source_system", each "ERP", type text)
in
    AddSource

Sink this into the Lakehouse as bronze_erp_sales_order_header (Delta table). The only logic is metadata and column selection.

Silver Layer: Clean, Conform, and Model Entities

Silver is where you fix data and align semantics across sources.

What Silver Should Do

• Standardise:
  • Data types and formats (dates, currencies, codes).
  • Reference values (status codes, country names, etc.).
• De‑duplicate and filter:
  • Remove technical duplicates.
  • Filter out obviously invalid records.
• Conform keys:
  • Map source IDs to conformed business keys (e.g. customer_business_key).

Typical Silver Patterns

Use Fabric Notebooks (PySpark) or Dataflows Gen2 for these transformations.

1. Surrogate Keys and Natural Keys

You often keep both:

• Natural key: customer_number (from ERP).
• Surrogate key: customer_key (generated in Silver or Gold).

If you want to generate keys in Silver using PySpark:

from pyspark.sql import functions as F
from pyspark.sql.window import Window

bronze_customers = spark.read.table("lh_sales.bronze_erp_customer")

# Basic cleaning
clean_customers = (
    bronze_customers
    .dropDuplicates(["CustomerNumber"])
    .withColumn("CustomerName", F.initcap("CustomerName"))
    .withColumn("IsActive", (F.col("Status") == F.lit("Active")).cast("boolean"))
)

# Generate stable surrogate key
w = Window.orderBy("CustomerNumber")
silver_customers = clean_customers.withColumn("customer_key", F.row_number().over(w))

silver_customers.write.format("delta").mode("overwrite").saveAsTable("lh_sales.silver_customer")

2. Conformed Dimensions Across Sources

If you have customers in ERP and CRM:

• Bronze: bronze_erp_customer, bronze_crm_account.
• Silver: silver_customer that merges both.

Pattern:

1. Standardise column names.
2. Union and resolve conflicts (e.g. CRM overrides ERP for email).
3. Keep source lineage columns (_source_system, _source_id).

Gold Layer: BI‑Ready Marts and Star Schemas

Gold is what your BI tools connect to. It should be predictable, simple, and optimised for analytics.

Gold Design Goals

• Star schema:
  • Fact tables: fct_sales, fct_invoice, fct_inventory.
  • Dimension tables: dim_customer, dim_product, dim_date.
• Surrogate keys on dimensions, foreign keys in facts.
• No technical columns exposed to end users (hide them in the model).

Example: Build a Gold Fact Table in PySpark

From Silver orders header and line to gold_sales_fct_sales:

from pyspark.sql import functions as F

orders_header = spark.read.table("lh_sales.silver_orders_header")
orders_line = spark.read.table("lh_sales.silver_orders_line")
customer_dim = spark.read.table("lh_sales.gold_sales_dim_customer")
product_dim = spark.read.table("lh_sales.gold_sales_dim_product")

# Join header and line
sales = (
    orders_line.alias("l")
    .join(orders_header.alias("h"), "OrderID", "inner")
    .join(customer_dim.alias("c"), "CustomerBusinessKey", "left")
    .join(product_dim.alias("p"), "ProductCode", "left")
    .select(
        F.col("l.OrderLineID").alias("sales_key"),
        F.col("h.OrderDate").alias("order_date"),
        F.col("c.customer_key"),
        F.col("p.product_key"),
        F.col("l.Quantity"),
        F.col("l.NetAmount").alias("net_amount"),
        F.col("h.CurrencyCode").alias("currency_code")
    )
)

sales.write.format("delta").mode("overwrite").saveAsTable("lh_sales.gold_sales_fct_sales")

Gold Table Naming Pattern

• Dimensions: gold_<subject>_dim_<name>
  • gold_sales_dim_customer
  • gold_sales_dim_product
• Facts: gold_<subject>_fct_<name>
  • gold_sales_fct_sales
  • gold_sales_fct_returns

This keeps multiple subject areas manageable inside one Lakehouse.

Power BI Model: Connect to Gold Only

In Microsoft Fabric, you can create a semantic model directly on top of Lakehouse tables.

For clean governance:

1. Limit model sources to Gold tables

   • Avoid pulling Bronze/Silver into the same model unless you have a very specific reason.

2. Hide technical columns

   • Hide surrogate keys (*_key) and metadata columns (_load_date) from report authors.

3. Create measures, not calculated columns, where possible

Example DAX measures on gold_sales_fct_sales:

Total Sales := 
SUM ( 'gold_sales_fct_sales'[net_amount] )

Total Quantity := 
SUM ( 'gold_sales_fct_sales'[Quantity] )

Sales LY := 
CALCULATE ( 
    [Total Sales], 
    DATEADD ( 'gold_sales_dim_date'[Date], -1, YEAR )
)

Customer Count := 
DISTINCTCOUNT ( 'gold_sales_dim_customer'[customer_key] )

These measures live in the semantic model; the Lakehouse remains focused on data, not business calculations.

Orchestration: How Data Flows Between Layers

You have three main options in Fabric:

1. Data Pipelines (copy + notebooks)

   • Good for scheduled batch loads.
   • Typical pattern:
     1. Copy activity from source to Bronze.
     2. Notebook activity Bronze → Silver.
     3. Notebook activity Silver → Gold.

2. Dataflows Gen2

   • Good for GUI‑driven transformations, especially for business‑friendly Silver logic.

3. Notebooks (PySpark)

   • Best for heavy transformations, joins, and advanced logic.

A simple daily pattern:

1. Trigger: Time‑based schedule (e.g. 06:00 CET).
2. Bronze load: Copy or Dataflow from source.
3. Silver refresh: Notebook that reads changed Bronze partitions.
4. Gold refresh: Notebook that rebuilds marts or uses MERGE for incremental updates.

Incremental Pattern with Delta MERGE

For updating a Silver table from Bronze incrementally:

MERGE INTO lh_sales.silver_orders_header AS tgt
USING (
    SELECT *
    FROM lh_sales.bronze_erp_sales_order_header
    WHERE _load_date >= DATEADD(day, -1, CURRENT_TIMESTAMP)
) AS src
ON tgt.OrderID = src.OrderID
WHEN MATCHED THEN
    UPDATE SET
        tgt.OrderDate = src.OrderDate,
        tgt.Status = src.Status,
        tgt.TotalAmount = src.TotalAmount
WHEN NOT MATCHED THEN
    INSERT (OrderID, OrderDate, Status, TotalAmount)
    VALUES (src.OrderID, src.OrderDate, src.Status, src.TotalAmount);

Same idea applies Silver → Gold for facts and dimensions.

Governance and Reuse: Avoid One‑Off Gold Marts

Common failure mode: every team gets its own Gold tables, and definitions diverge.

A more sustainable pattern:

1. Shared conformed layer

   • One set of gold_*_dim_* tables per domain.
   • Facts built from these shared dimensions.

2. Thin report‑specific models

   • If a report needs special logic, implement it in the semantic model (DAX), not by cloning the Gold tables.

3. Data contracts for Gold

   • Document what each Gold table guarantees: grain, refresh frequency, and key relationships.

One Practical Takeaway

Before you build anything, sketch your Bronze/Silver/Gold pattern for a single domain on one page: source objects → Bronze tables → Silver entities → Gold facts/dimensions → Power BI model. If you can’t draw that flow clearly, fix the design on paper first; it’s far cheaper than refactoring a messy Lakehouse later.