Designing a Lakehouse in Microsoft Fabric for Excel and Power BI Users

1. Start with the end in mind: Excel and Power BI use cases

Before you create a single folder, decide what the Lakehouse must serve. For Excel and Power BI users, the usual patterns are:

• Self-service analysis
  • Excel users pulling data via Power Query or direct connection
  • Power BI analysts building semantic models and reports
• Standardized, governed datasets
  • Certified Power BI datasets reused across reports
  • Shared dimensions (calendar, customers, products)
• Operational reporting
  • Scheduled refreshes from source systems
  • Simple audit trail of what changed and when

Translate this into three high-level design goals:

1. Predictable structure: Users know where to find data and what it means.
2. Separation of concerns: Raw, cleaned, and report-ready data live in different places.
3. Governance hooks: Clear ownership, access rules, and auditability.

The medallion architecture (Bronze–Silver–Gold) is a convenient way to enforce this separation.

2. Medallion layers in Fabric Lakehouse

2.1 Medallion overview

In a Fabric Lakehouse, medallion layers map naturally to folders and tables:

• Bronze (Raw)

  • Near-direct copy of source systems
  • Minimal transformation (mostly type casting, basic normalization)
  • Used for replay, troubleshooting, and lineage

• Silver (Cleaned / Conformed)

  • Standardized schemas, cleaned values
  • Business keys, deduplication, basic enrichment
  • Shared across multiple Gold datasets

• Gold (Analytics / Semantic)

  • Star-schema facts and dimensions
  • Business logic embedded (calculated columns, measures)
  • Used by Excel and Power BI as the primary consumption layer

2.2 Mapping medallion layers to Lakehouse structure

At the Lakehouse level, create three top-level folders:

• bronze/
• silver/
• gold/

Under each, mirror your domain structure (e.g., sales, finance, hr). This gives you a consistent pattern:

lakehouse
├─ bronze
│  ├─ sales
│  │  ├─ orders
│  │  ├─ order_lines
│  │  └─ customers_raw
│  └─ finance
│     └─ gl_entries
├─ silver
│  ├─ sales
│  │  ├─ orders_clean
│  │  ├─ order_lines_clean
│  │  └─ customers_master
│  └─ finance
│     └─ gl_entries_clean
└─ gold
   ├─ sales
   │  ├─ fact_sales
   │  └─ dim_customer
   └─ finance
      ├─ fact_gl
      └─ dim_cost_center

Within Fabric, these folders map to Delta tables. You can create them via Dataflows Gen2, Notebooks, or pipelines.

3. Folder structures that Excel and Power BI users can live with

3.1 Core principles for folder design

Use these guidelines:

• Stable domains first
  • Group by business area (sales, marketing, finance) rather than by project.
• Tables as units of work
  • Each table gets its own folder; avoid dumping multiple unrelated tables into the same folder.
• Machine-friendly, human-readable names
  • Lowercase, underscores, no spaces: fact_sales, dim_customer.
• Versioning via metadata, not folder chaos
  • Avoid v1, v2 folders; track version in table metadata or Git instead.

3.2 Naming conventions

A simple, consistent naming pattern avoids confusion when Excel users browse the Lakehouse.

For tables:

• Bronze: src_<system>_<entity>
  • Example: src_erp_sales_order
• Silver: <domain>_<entity>_clean or <domain>_<entity>_master
  • Example: sales_orders_clean, customer_master
• Gold: fact_<subject> and dim_<subject>
  • Example: fact_sales, dim_date

For columns:

• Primary keys: <entity>_id (e.g., customer_id)
• Surrogate keys: sk_<entity> (e.g., sk_customer)
• Dates: suffix _date or _datetime consistently.

This matters for Excel users, because these names show up directly in Power Query and PivotTables.

4. Building medallion layers in Fabric with simple examples

4.1 Ingesting to Bronze with Dataflows Gen2

Assume you’re pulling orders from an ERP system into Bronze.

1. Create a Dataflow Gen2 in your Fabric workspace.
2. Connect to the source system (SQL, API, file share).
3. Load the raw table into the Lakehouse Bronze area.

A simple Power Query step to land data into bronze/sales/orders might look like:

let
    Source = Sql.Database("sql-prod", "ERP"),
    Orders = Source{[Schema="dbo",Item="SalesOrders"]}[Data],
    // Basic type cleanup only
    ChangedTypes = Table.TransformColumnTypes(
        Orders,
        {
            {"OrderID", Int64.Type},
            {"OrderDate", type datetime},
            {"CustomerID", Int64.Type},
            {"NetAmount", type number}
        }
    )
in
    ChangedTypes

Configure the Dataflow to output to the Lakehouse table bronze.sales.orders.

4.2 Transforming Bronze to Silver with Notebooks

From Bronze to Silver, you might standardize customer IDs and remove duplicates.

# Fabric Notebook (PySpark)
from pyspark.sql import functions as F

bronze_orders = spark.read.table("lakehouse.bronze.sales.orders")

silver_orders = (
    bronze_orders
    .dropDuplicates(["OrderID"])
    .withColumn("order_date", F.to_date("OrderDate"))
    .withColumn("load_timestamp", F.current_timestamp())
)

silver_orders.write.mode("overwrite").saveAsTable("lakehouse.silver.sales.orders_clean")

4.3 Silver to Gold: building a star schema

Now create a fact_sales table and a dim_date table in Gold.

-- dim_date from silver orders
CREATE OR REPLACE TABLE lakehouse.gold.shared.dim_date AS
SELECT DISTINCT
    order_date AS date,
    YEAR(order_date) AS year,
    MONTH(order_date) AS month,
    FORMAT(order_date, 'yyyy-MM') AS year_month
FROM lakehouse.silver.sales.orders_clean
WHERE order_date IS NOT NULL;

-- fact_sales
CREATE OR REPLACE TABLE lakehouse.gold.sales.fact_sales AS
SELECT
    o.OrderID AS sales_id,
    o.order_date,
    o.CustomerID AS customer_id,
    o.NetAmount AS net_amount
FROM lakehouse.silver.sales.orders_clean o;

This Gold layer is what Excel and Power BI should connect to.

5. Connecting Excel and Power BI to the Lakehouse

5.1 Power BI: semantic model on top of Gold

In Power BI Desktop:

1. Get Data → Power BI data hub and select your Lakehouse.
2. Choose the gold tables you want (e.g., fact_sales, dim_customer, dim_date).
3. Build relationships:
   • fact_sales[customer_id] → dim_customer[customer_id]
   • fact_sales[order_date] → dim_date[date]
4. Save and publish the dataset.

Add a basic measure:

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

Certify this dataset so Excel users can safely reuse it.

5.2 Excel: connecting to Fabric data

Excel users have two main options:

1. Power BI dataset connection (recommended)

   • Data → Get Data → From Power BI.
   • Select the certified dataset you created.
   • Build PivotTables on top of the semantic model.

2. Direct Lakehouse connection via Power Query

   • Data → Get Data → From Power Platform → From Power BI (Fabric) Lakehouse.
   • Select gold.sales.fact_sales and relevant dimensions.
   • Load to Data Model or Table.

Encourage users to prefer the dataset route for governed, consistent measures and calculations.

6. Governance: roles, policies, and auditability

6.1 Ownership and roles

Define clear roles per layer:

• Bronze
  • Owner: Data engineering / integration team.
  • Access: restricted; mostly technical users.
• Silver
  • Owner: Data engineering + domain data stewards.
  • Access: analysts and developers.
• Gold
  • Owner: BI team and business owners.
  • Access: broad; Excel and Power BI users.

Document ownership per table, e.g., in a simple metadata table in the Lakehouse:

CREATE OR REPLACE TABLE governance.table_owners (
    table_name STRING,
    layer STRING,
    owner_upn STRING,
    domain STRING
);

6.2 Security patterns

Use these patterns to keep things manageable:

• Workspace-based access
  • Restrict edit rights to engineering and BI teams.
  • Give read rights to business users for Gold datasets only.
• Row-Level Security (RLS) in Power BI
  • Implement RLS in the semantic model, not in the Lakehouse tables.
  • Example: restrict fact_sales to specific regions.

[Region Filter] :=
VAR UserRegion = LOOKUPVALUE(
    'user_region'[region],
    'user_region'[user_principal_name],
    USERPRINCIPALNAME()
)
RETURN
    'dim_customer'[region] = UserRegion

6.3 Change management

To avoid breaking Excel and Power BI reports:

• No breaking changes in Gold without deprecation
  • Add columns instead of renaming/removing.
  • If you must change a column, deprecate it with a warning period.
• Schema evolution in Bronze and Silver
  • Allow more flexibility here; Gold should be stable.
• Refresh orchestration
  • Schedule Bronze → Silver → Gold in the right order.
  • Use Fabric pipelines to ensure dependencies are respected.

7. Practical patterns for Swiss-style governance (and beyond)

Many organizations need to reconcile strict governance with flexible analysis. A few practical patterns:

• Certified vs. uncertified Gold tables
  • Mark some Gold tables as “sandbox” for experimentation.
  • Only certified ones feed official reports.
• Documentation as data
  • Store table descriptions and column definitions in a governance.data_dictionary table.
  • Surface this in Power BI as a simple report.
• Audit-friendly logs
  • Capture load timestamps and source system references in every table.

ALTER TABLE lakehouse.silver.sales.orders_clean
ADD COLUMN load_timestamp datetime;

-- During writes, always populate load_timestamp

This makes it easy to answer “what data did this report use on a given day?” without digging through pipelines.

8. One concrete next step

Create a single Lakehouse with one simple domain (e.g., Sales), implement Bronze–Silver–Gold with the folder and naming conventions above, and connect one Power BI report and one Excel workbook to the Gold layer. Once that small slice works end-to-end, reuse the same pattern for every new domain instead of inventing a new structure each time.