A lakehouse inthree layers.

Bronze is the data as it arrived. Nothing is cleaned or dropped here, so when a number looks wrong later, you can always go back to what the source really sent.

#The lakehouse

In a workspace on a Fabric capacity (a trial works), select New item, then Lakehouse, and name it sales. A lakehouse has two parts: Files, for files of any kind, and Tables, for Delta tables that every Fabric engine can read: Spark, the SQL analytics endpoint and Power BI.

#The file

The lessons use a CSV of orders with these columns:

OrderID,OrderDate,CustomerID,CustomerName,Country,Product,Quantity,Amount
10241,2026-03-02,C-118,Muster Bau,Austria,Cable 3m,4,59.60
10242,2026-03-02,C-204,Beispiel GmbH,Germany,Switch 24p,1,389.00

Any export of orders with these columns works. In the lakehouse, open Files, make a folder raw/orders and upload the file into it.

#The notebook

Select New notebook from the lakehouse, so it opens with the lakehouse attached, and run:

from pyspark.sql import functions as F

raw = (spark.read
       .option("header", True)
       .csv("Files/raw/orders/"))

bronze = (raw
          .withColumn("_loaded_at", F.current_timestamp())
          .withColumn("_source_file", F.input_file_name()))

bronze.write.mode("append").saveAsTable("bronze_orders")

Three choices in those lines are on purpose:

• No schema inference. Every column stays text in bronze. A date the source wrote badly is kept as it was, instead of turning into an empty value nobody can explain.
• Two columns of our own. _loaded_at and _source_file say when each row came and from which file. The underscore marks them as added by the platform.
• Append. Each run adds what arrived. Loading the same file twice gives duplicates in bronze, and that is fine: silver takes care of them.

#Look at it

display(spark.sql("""
    SELECT _source_file, COUNT(*) AS row_count, MAX(_loaded_at) AS loaded
    FROM bronze_orders
    GROUP BY _source_file
"""))

The table also shows under Tables in the lakehouse, and the SQL analytics endpoint can query it straight away.

Silver is where the data becomes right: real types, trimmed text, one row per order. And where a row cannot be made right, it is set aside with the reason, so it is never counted and never lost.

#Types and text

In a new notebook cell, read bronze and give every column its type:

from pyspark.sql import functions as F, Window

b = spark.read.table("bronze_orders")

typed = b.select(
    F.trim("OrderID").alias("order_id"),
    F.to_date("OrderDate", "yyyy-MM-dd").alias("order_date"),
    F.trim("CustomerID").alias("customer_id"),
    F.trim("CustomerName").alias("customer_name"),
    F.trim("Country").alias("country"),
    F.trim("Product").alias("product"),
    F.col("Quantity").cast("int").alias("quantity"),
    F.col("Amount").cast("decimal(18,2)").alias("amount"),
    "_loaded_at",
)

A value that does not fit its type becomes empty (null) instead of stopping the run. That is what the checks catch next.

#The checks, each with a name

reason = (F.when(F.col("order_id").isNull() | (F.col("order_id") == ""), "missing order id")
           .when(F.col("order_date").isNull(), "date not readable")
           .when(F.col("amount").isNull(), "amount not a number")
           .when(F.col("amount") < 0, "negative amount"))

checked = typed.withColumn("_reason", reason)
bad = checked.filter("_reason IS NOT NULL")
good = checked.filter("_reason IS NULL").drop("_reason")

bad.write.mode("append").saveAsTable("quarantine_orders")

Every row that fails lands in quarantine_orders with the first rule it broke. Anyone can see what was left out and why, and fix it at the source.

#One row per order

Bronze may hold the same order more than once. Keep the newest:

newest = Window.partitionBy("order_id").orderBy(F.col("_loaded_at").desc())

good = (good.withColumn("_n", F.row_number().over(newest))
            .filter("_n = 1")
            .drop("_n"))

#Merge into silver

The first run creates the table; every run after it updates the orders that changed and inserts the new ones:

from delta.tables import DeltaTable

if not spark.catalog.tableExists("silver_orders"):
    good.write.saveAsTable("silver_orders")
else:
    (DeltaTable.forName(spark, "silver_orders").alias("t")
        .merge(good.alias("s"), "t.order_id = s.order_id")
        .whenMatchedUpdateAll()
        .whenNotMatchedInsertAll()
        .execute())

print(f"{good.count()} rows to silver, {bad.count()} set aside")

Run the whole notebook twice: silver has the same number of rows both times. That is the point of a merge by key.

Gold is shaped for the people who read the numbers: a star schema, with one table of facts in the middle and the things you filter by around it. Power BI is built for exactly that shape.

#The tables

Spark SQL builds the gold tables from silver, one statement per notebook cell. CREATE OR REPLACE rebuilds them on every run:

%%sql
CREATE OR REPLACE TABLE gold_fact_orders AS
SELECT order_id, order_date, customer_id, product, quantity, amount
FROM silver_orders;

%%sql
CREATE OR REPLACE TABLE gold_dim_customer AS
SELECT customer_id,
       MAX(customer_name) AS customer_name,
       MAX(country)       AS country
FROM silver_orders
GROUP BY customer_id;

The fact table keeps the numbers and the keys. The customer table has one row per customer, with what people filter and group by.

#A date table

Reports compare months and years, and that needs a table with every day in it, not only the days that had orders:

%%sql
CREATE OR REPLACE TABLE gold_dim_date AS
SELECT d                    AS calendar_date,
       year(d)              AS year,
       month(d)             AS month_number,
       date_format(d, 'MMM') AS month
FROM (SELECT explode(sequence(to_date('2020-01-01'), to_date('2030-12-31'), interval 1 day)) AS d);

#The semantic model

In the lakehouse, select New semantic model, name it Sales, and pick the three gold tables. The model reads them in Direct Lake mode: straight from the Delta tables in OneLake, with no import to refresh.

Open the model and add two relationships, both one to many:

• gold_dim_customer[customer_id] to gold_fact_orders[customer_id]
• gold_dim_date[calendar_date] to gold_fact_orders[order_date]

Then mark gold_dim_date as the date table, on calendar_date, and add the first measure:

Total Sales = SUM ( gold_fact_orders[amount] )

A report on this model now shows sales by month, by country and by customer, and the numbers follow silver every time the notebook runs.

#Run it every night

Put the notebooks in a data pipeline, in order: bronze, silver, gold, and schedule it. Each run lands the new files, cleans and merges them, sets aside what breaks a rule and rebuilds gold.