Define the reporting outcome first

Before creating Lakehouses or pipelines, define what the Gold layer must eventually support.

In this example, management needs:

• Total Revenue
• Gross Profit
• Revenue by Customer
• Revenue by Product Category
• Revenue by Region
• Monthly trend

That tells us the Gold layer requires at least:

• FactSales
• DimCustomer
• DimProduct
• DimDate

Create the three Fabric data layers

Create separate governed areas for the three processing stages.

A simple naming pattern is:

lh_sales_bronze
lh_sales_silver
lh_sales_gold

The exact design can vary. Some organisations separate layers into different Lakehouses, while others structure them according to broader domain and governance requirements.

Land source data into Bronze

Create a Fabric Pipeline and add Copy Data activities for each source.

For the example business:

• Copy Sales.csv into Bronze
• Copy Customers.csv into Bronze
• Copy Products.csv into Bronze

Keep the Bronze copy close to the original representation.

A useful folder pattern is:

Files/
    sales/
        2026/
            08/
                29/
                    Sales.csv

    customers/
        2026/
            08/
                29/
                    Customers.csv

    products/
        2026/
            08/
                29/
                    Products.csv

A dated landing structure provides a clear record of what arrived on each processing date.

Clean the data into Silver with Dataflow Gen2

Create a Dataflow Gen2 that reads the Bronze data and applies reusable transformation rules.

For Sales:

• Set the correct data types
• Trim customer and product codes
• Remove invalid blank transaction IDs
• Remove duplicate transactions
• Reject or flag invalid quantities

Example Power Query M:

let
    Source = BronzeSales,

    ChangedTypes =
        Table.TransformColumnTypes(
            Source,
            {
                {"TransactionID", type text},
                {"SaleDate", type date},
                {"CustomerID", type text},
                {"ProductID", type text},
                {"Quantity", Int64.Type},
                {"UnitPrice", Currency.Type}
            }
        ),

    CleanCodes =
        Table.TransformColumns(
            ChangedTypes,
            {
                {
                    "CustomerID",
                    each Text.Upper(Text.Trim(_)),
                    type text
                },
                {
                    "ProductID",
                    each Text.Upper(Text.Trim(_)),
                    type text
                }
            }
        ),

    ValidRows =
        Table.SelectRows(
            CleanCodes,
            each
                [TransactionID] <> null
                and [Quantity] > 0
        ),

    Deduplicated =
        Table.Distinct(
            ValidRows,
            {"TransactionID"}
        )

in
    Deduplicated

Send the result to a Silver Lakehouse table such as:

SilverSales

Build business-ready Gold tables

Gold should organise the data around how the business analyses it.

For the sales fact table, add a reusable sales value:

let
    Source = SilverSales,

    AddedSalesValue =
        Table.AddColumn(
            Source,
            "SalesValue",
            each [Quantity] * [UnitPrice],
            Currency.Type
        )

in
    AddedSalesValue

Publish the result as:

FactSales

Build separate curated dimensions:

DimCustomer
DimProduct
DimDate

Gold is also the appropriate place for curated summary tables where there is a genuine analytical requirement.

Orchestrate Bronze → Silver → Gold

Return to the Fabric Pipeline and create an explicit dependency chain.

Conceptually:

Copy Source Data
        ↓
Bronze Load Complete
        ↓
Run Silver Dataflow
        ↓
Silver Validation Complete
        ↓
Run Gold Dataflow
        ↓
Gold Tables Ready
        ↓
Reporting Process

Configure downstream activities to run only after the previous critical activity succeeds.

This prevents Gold from being rebuilt from incomplete Silver data.

Build Power BI from Gold — not Bronze

Your Power BI reporting layer should normally consume the curated structures designed for analytics rather than directly connecting every report to raw operational extracts.

Build relationships around:

• FactSales
• DimCustomer
• DimProduct
• DimDate

Then create measures such as:

Total Revenue =
SUM(
    FactSales[SalesValue]
)

Total Quantity =
SUM(
    FactSales[Quantity]
)

Average Selling Price =
DIVIDE(
    [Total Revenue],
    [Total Quantity]
)

The important difference is architectural: Power BI is now responsible primarily for semantic modelling and analysis rather than repeatedly cleaning source files.