Build a Medallion Lakehouse in Fabric

Build a bronze/silver/gold lakehouse in Microsoft Fabric using PySpark.

Introduction

30 Second Summary

Every business collects data from dozens of sources. Without a clear system for organizing it, raw data piles up and becomes impossible to trust or analyze.

In this project, you will build a three-layer medallion lakehouse in Microsoft Fabric that ingests raw sales data, cleans and transforms it with PySpark, and serves business-ready aggregations through a SQL endpoint.

What You'll Build

A complete medallion architecture lakehouse that moves sales data through bronze, silver, and gold layers using Delta Lake tables and PySpark notebooks.

By the end of this project, you'll have:

A Fabric workspace with a lakehouse containing bronze, silver, and gold Delta tables that each represent a different stage of data quality.
A PySpark notebook that performs the full ETL pipeline across all three layers.
Hands-on experience querying your lakehouse tables through the SQL analytics endpoint using T-SQL.
Secret Mission: Implement an incremental load using Delta Lake MERGE to upsert new records into your silver layer.

Are there any prerequisites?

You should be comfortable with SQL and Python. No prior PySpark or Microsoft Fabric experience is required.

You will need a work or school Microsoft account to activate the free 60-day Fabric trial. If you don't have one, Step 1 covers how to create a free Azure account and set one up.

Set Up Your Fabric Trial and Workspace

Before you can build your medallion lakehouse, you need somewhere to build it. Microsoft Fabric is a cloud-based analytics platform that brings together data engineering, data warehousing, and real-time analytics in one place.

In this step, you will activate a free 60-day Fabric trial, create a workspace to organize your project, and spin up a lakehouse to store your Delta Lake tables.

In this step, get ready to:

Activate your free 60-day Microsoft Fabric trial.
Create a workspace for your medallion lakehouse project.
Create a lakehouse to store your bronze, silver, and gold Delta tables.

Sign up for the Fabric trial

The Fabric trial gives you 60 days of free access with no credit card required. All the resources you create in this project run on this trial capacity.

Open your browser and navigate to app.fabric.microsoft.com.
Sign in with a work or school Microsoft account.

What if I only have a personal email?

Fabric requires a work or school Microsoft account. If you only have a personal email (Gmail, Outlook.com, Yahoo), you can create a free Azure account at azure.microsoft.com, then create a user in Microsoft Entra ID and sign into Fabric with that user.

Follow Microsoft's official guide for starting a Fabric trial with a personal email. Note that newly created Entra accounts may need up to 90 days before the trial can activate.

Select your profile icon in the upper-right corner to open the Account manager.
Select Start trial.
In the "Activate your 60-day free Fabric trial capacity" prompt, select your preferred region from the dropdown.
Select Activate.

You should see a confirmation message. You now have access to the Fabric Home Page.

Don't see the Start trial button?

If you see Free trial instead of Start trial, select that instead. Both lead to the same activation flow.

If neither option appears, your organization's Fabric administrator may have disabled trials. Contact your admin to request access.

Help me troubleshoot my Fabric trial activation.

Create a workspace

A workspace is a container that holds all the items for a project. You will create one workspace to house your lakehouse, notebooks, and Delta tables.

Select Workspaces from the left navigation bar.
Select + New workspace.
In the Name field, enter DP700-Medallion-Lab.
Expand the Advanced section.
Set Workspace type to Fabric Trial capacity.
Select Apply.

Your new workspace opens and appears in the workspace list.

Why the name DP700-Medallion-Lab?

This project practices the data engineering skills measured on the DP-700 Fabric Data Engineer Associate certification exam. Naming your workspace after the exam domain keeps things organized if you create more Fabric labs later.

If the name is already taken, add a few unique characters to the end (e.g. your initials).

Create a lakehouse

A lakehouse combines the flexibility of a data lake with the structure of a data warehouse. Every table you write to your lakehouse is stored as a Delta table and is automatically queryable via SQL.

Inside your DP700-Medallion-Lab workspace, select + New item.
Search for Lakehouse and select it.
In the Name field, enter sales_lakehouse.
Leave the Lakehouse schemas checkbox selected (this is the default).
Select Create.

The lakehouse opens in the Lakehouse Explorer pane. You should see empty Tables and Files sections.

What is the Lakehouse Explorer?

The Lakehouse Explorer is the left-hand pane that shows the structure of your lakehouse. The Tables section holds your Delta tables (which are also exposed via the SQL analytics endpoint). The Files section holds raw files like CSVs and Parquet files.

As you build your medallion architecture (bronze, silver, gold layers), you will see tables appear here.

Why use underscores in the lakehouse name?

Fabric lakehouse names must begin with a letter, and can only contain alphanumeric characters and underscores (max 123 characters). Spaces and hyphens are not allowed.

Seeing a naming error?

Make sure your lakehouse name starts with a letter, uses only letters, numbers, and underscores, and contains no spaces or hyphens. The exact name to enter is sales_lakehouse.

Help me fix my lakehouse naming error.

Your Fabric environment is ready. Next up, you will create a notebook, download sample sales data, and load it into your bronze layer.

Ingest Raw Data into the Bronze Layer

Your workspace and lakehouse are ready to go. Now it's time to land some actual data.

In a medallion architecture, the bronze layer stores raw, unprocessed data exactly as it arrives from the source. You will download a sample sales CSV file from GitHub, read it into a PySpark DataFrame, and write it as a Delta Lake table in your lakehouse.

In this step, get ready to:

Create a PySpark notebook attached to your lakehouse.
Download raw sales data and write it as a bronze Delta table.
Explore the bronze data to confirm it landed correctly.

Create a notebook and attach the lakehouse

Fabric notebooks let you write and run PySpark code directly against your lakehouse. You need to create one and confirm the lakehouse is attached so your code can read and write data.

In the lakehouse top navigation, select Open notebook.
Select New notebook.
A new notebook opens. Click the notebook name at the top of the page and rename it to medallion_pipeline.
Confirm sales_lakehouse appears in the Lakehouse explorer on the left side.

Don't see sales_lakehouse in the explorer?

If the lakehouse is not attached, select Add lakehouse in the left pane, choose Existing lakehouse, select sales_lakehouse from the Data hub, and select Add.

Help me attach my lakehouse to this notebook.

Download and load sample CSV data into the bronze layer

The bronze layer captures data in its original form. This cell downloads a sales CSV from a public GitHub repository, reads it into a DataFrame, and writes it as a Delta table.

Paste the following code into the first cell of your notebook, then run it by pressing Shift+Enter:

# Cell 1: Download sample data and create bronze table
import requests
import os

# Download sample sales data
data_url = "https://raw.githubusercontent.com/MicrosoftLearning/dp-data/main/sales.csv"
download_path = "/lakehouse/default/Files/raw"

if not os.path.exists("/lakehouse/default"):
    raise FileNotFoundError("Default lakehouse not found, please add a lakehouse and restart the session.")

os.makedirs(download_path, exist_ok=True)

file_path = f"{download_path}/sales.csv"
if not os.path.exists(file_path):
    r = requests.get(data_url, timeout=30)
    with open(file_path, "wb") as f:
        f.write(r.content)
    print("Downloaded sales.csv into lakehouse.")
else:
    print("File already exists.")

# Read CSV into DataFrame
df_raw = spark.read.format("csv").option("header", "true").option("inferSchema", "true").load(file_path)

# Write as bronze Delta table
df_raw.write.format("delta").mode("overwrite").save("Tables/bronze_sales")
print(f"Bronze table created with {df_raw.count()} rows.")

What does this code do?

The first section checks that a default lakehouse is attached, then downloads sales.csv from GitHub into the lakehouse's Files area. If the file already exists, it skips the download.
spark.read.format("csv") reads the CSV into a PySpark DataFrame with column headers and inferred data types.
df_raw.write.format("delta").save("Tables/bronze_sales") writes the DataFrame as a Delta table. Only tables saved to the Tables/ path appear in the lakehouse explorer and SQL analytics endpoint.

Wait for the cell to finish running. You should see output like Bronze table created with X rows. printed below the cell.
In the Lakehouse explorer on the left, click the refresh icon (circular arrows) next to Tables.
You should see bronze_sales appear as a table under Tables.

Seeing a FileNotFoundError?

This means no default lakehouse is attached to your notebook. Go back to the Lakehouse explorer on the left, select Add lakehouse, add sales_lakehouse, and restart the session before running the cell again.

If you see a timeout error, the GitHub download may have failed. Check your network connection and try running the cell again.

Help me fix my bronze table cell error.

Explore the bronze data

Before moving to the next layer, confirm the raw data looks correct by reading the Delta table back and inspecting its schema and rows.

Click + Code below your first cell to add a new cell.
Paste the following code into the new cell and run it with Shift+Enter:

# Cell 2: Explore bronze data
df_bronze = spark.read.format("delta").load("Tables/bronze_sales")
df_bronze.printSchema()
df_bronze.show(5)

What does this code do?

spark.read.format("delta").load("Tables/bronze_sales") reads the Delta table you just created back into a new DataFrame.
printSchema() shows the column names and data types so you can verify how Spark inferred the CSV structure.
show(5) displays the first 5 rows in a table format so you can eyeball the actual values.

You should see a schema printout followed by a table with columns including OrderDate, ProductKey, CustomerKey, Quantity, and UnitPrice.

Not seeing data in the output?

Make sure Cell 1 completed successfully first. If it showed an error, fix that cell before running Cell 2. The Delta table must exist before you can read it.

Help me troubleshoot my bronze data exploration.

Why Delta Lake?

Delta Lake is the default table format in Microsoft Fabric. Every table in the lakehouse explorer is automatically a Delta table. This gives you built-in features like time travel, ACID transactions, and schema enforcement without any extra configuration.

✔️ Awesome, I've got everything!

Great. Make sure both cells ran without errors before moving on.

ⓧ I'd like to double check the full code

Here is the complete notebook code you should have so far in medallion_pipeline:

# Cell 1: Download sample data and create bronze table
import requests
import os

# Download sample sales data
data_url = "https://raw.githubusercontent.com/MicrosoftLearning/dp-data/main/sales.csv"
download_path = "/lakehouse/default/Files/raw"

if not os.path.exists("/lakehouse/default"):
    raise FileNotFoundError("Default lakehouse not found, please add a lakehouse and restart the session.")

os.makedirs(download_path, exist_ok=True)

file_path = f"{download_path}/sales.csv"
if not os.path.exists(file_path):
    r = requests.get(data_url, timeout=30)
    with open(file_path, "wb") as f:
        f.write(r.content)
    print("Downloaded sales.csv into lakehouse.")
else:
    print("File already exists.")

# Read CSV into DataFrame
df_raw = spark.read.format("csv").option("header", "true").option("inferSchema", "true").load(file_path)

# Write as bronze Delta table
df_raw.write.format("delta").mode("overwrite").save("Tables/bronze_sales")
print(f"Bronze table created with {df_raw.count()} rows.")

# Cell 2: Explore bronze data
df_bronze = spark.read.format("delta").load("Tables/bronze_sales")
df_bronze.printSchema()
df_bronze.show(5)

Your raw data is safely stored in the bronze layer. Next up, you will clean and transform this data into a polished silver layer with proper data types, derived columns, and filtered rows.

Transform Data into the Silver Layer

Your bronze layer is in place with raw sales data ready to go. Now it is time to clean, validate, and enrich that data into a silver layer.

The silver layer is where you enforce data quality. You will cast columns to proper types, filter out invalid rows, and add derived columns that make downstream analytics easier.

In this step, get ready to:

Configure the Spark session for optimized Delta writes.
Clean and transform bronze data into a silver Delta table.
Verify the silver layer schema and sample data.

Configure the Spark session for optimized writes

Before writing your silver table, you can enable two Fabric-specific optimizations that improve how Delta Lake files are written. V-Order and Optimize Write reduce small-file problems and speed up reads for downstream queries.

Add a new cell below your existing cells by hovering between cells and clicking + Code, or pressing B while in command mode.
Paste the following code into the new cell and run it by clicking the play button (or pressing Shift+Enter):

# Cell 3: Configure Spark session for optimized writes
spark.conf.set("spark.sql.parquet.vorder.enabled", "true")
spark.conf.set("spark.microsoft.delta.optimizeWrite.enabled", "true")
print("V-Order and Optimize Write enabled.")

What does this code do?

These two settings configure how Spark writes Delta files for the rest of your notebook session:

spark.sql.parquet.vorder.enabled applies V-Order sorting to Parquet files, which makes reads faster for Power BI and SQL queries.
spark.microsoft.delta.optimizeWrite.enabled automatically coalesces small partition files into larger ones during writes, reducing storage fragmentation.

Confirm the cell runs without errors and prints V-Order and Optimize Write enabled. in the output.

Cell not running?

Make sure your notebook session is still active. If the session expired, Fabric will start a new one automatically when you run the cell.
Confirm sales_lakehouse is still visible in the Lakehouse explorer on the left. If it disappeared, select Add lakehouse and re-attach it.

Why is my Spark session not connecting?

Clean and transform the data

The silver layer enforces data quality rules. You will filter out rows with missing or invalid values, cast columns to their correct types, and add calculated columns that downstream gold-layer aggregations depend on.

Add a new cell below Cell 3 and paste the following transformation code:

# Cell 4: Transform bronze to silver
from pyspark.sql.functions import col, year, month, when

# Read bronze
df_bronze = spark.read.format("delta").load("Tables/bronze_sales")

# Clean and transform
df_silver = (
    df_bronze
    .filter(col("Quantity").isNotNull() & col("UnitPrice").isNotNull())
    .filter(col("Quantity") > 0)
    .withColumn("Quantity", col("Quantity").cast("integer"))
    .withColumn("UnitPrice", col("UnitPrice").cast("double"))
    .withColumn("Year", year(col("OrderDate")))
    .withColumn("Month", month(col("OrderDate")))
    .withColumn("TotalAmount", col("Quantity") * col("UnitPrice"))
    .dropDuplicates()
)

# Write as silver Delta table
df_silver.write.format("delta").mode("overwrite").save("Tables/silver_sales")
print(f"Silver table created with {df_silver.count()} rows.")

What does this code do?

This cell reads from your bronze table, applies a series of cleaning and enrichment steps, then writes the result as a new Delta table:

.filter() removes rows where Quantity or UnitPrice is null, and removes rows where Quantity is zero or negative.
.withColumn() casts Quantity to integer and UnitPrice to double, ensuring proper numeric types for calculations.
Three derived columns are added: Year and Month (extracted from OrderDate) and TotalAmount (Quantity multiplied by UnitPrice).
.dropDuplicates() removes any repeated rows that might have been ingested multiple times.

Why clean data in a separate layer?

The medallion architecture keeps raw data untouched in bronze so you can always re-derive silver from the original source. This separation makes debugging easier and lets you evolve transformation logic without re-ingesting data.

Run the cell by pressing Shift+Enter.
After the cell finishes, refresh the Lakehouse explorer by clicking the three-dot menu (...) at the top of the explorer pane and selecting Refresh.
You should see a silver_sales table appear under Tables in the explorer.

Don't see silver_sales in the explorer?

Make sure the cell finished running. Look for the green checkmark and the print output showing the row count.
Try refreshing the explorer again. Table metadata can take a few seconds to sync.
Check the cell output for errors. A common issue is a typo in Tables/bronze_sales (the path is case-sensitive).

Help me debug why my silver table is not appearing.

Verify the silver layer

A quick schema check confirms your type casts and derived columns landed correctly.

Add a new cell below Cell 4 and paste this verification code:

# Cell 5: Verify silver layer
df_silver_check = spark.read.format("delta").load("Tables/silver_sales")
df_silver_check.printSchema()
df_silver_check.show(5)

What does this code do?

This cell reads the silver table back from disk and displays two things:

printSchema() shows column names and data types, letting you confirm the casts worked.
show(5) displays 5 sample rows so you can visually check that Year, Month, and TotalAmount have the values you expect.

Run the cell.
In the schema output, confirm that Quantity is integer, UnitPrice is double, and the Year, Month, and TotalAmount columns appear.
The sample rows should show positive quantities and calculated total amounts.

Schema shows wrong types?

If Quantity still shows as string, make sure your Cell 4 includes the .cast("integer") call and that you re-ran Cell 4 after making changes.
If TotalAmount is missing, confirm the .withColumn("TotalAmount", ...) line is present in your transformation chain.

Help me fix my silver table schema.

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook (Ctrl+S or Cmd+S) and confirmed the schema output looks correct.

ⓧ I'd like to double check the full code

Here is the complete notebook up to this point. Compare each cell against yours:

# Cell 1: Download sample data and create bronze table
import requests
import os

data_url = "https://raw.githubusercontent.com/MicrosoftLearning/dp-data/main/sales.csv"
download_path = "/lakehouse/default/Files/raw"

if not os.path.exists("/lakehouse/default"):
    raise FileNotFoundError("Default lakehouse not found, please add a lakehouse and restart the session.")

os.makedirs(download_path, exist_ok=True)

file_path = f"{download_path}/sales.csv"
if not os.path.exists(file_path):
    r = requests.get(data_url, timeout=30)
    with open(file_path, "wb") as f:
        f.write(r.content)
    print("Downloaded sales.csv into lakehouse.")
else:
    print("File already exists.")

df_raw = spark.read.format("csv").option("header", "true").option("inferSchema", "true").load(file_path)
df_raw.write.format("delta").mode("overwrite").save("Tables/bronze_sales")
print(f"Bronze table created with {df_raw.count()} rows.")

# Cell 2: Explore bronze data
df_bronze = spark.read.format("delta").load("Tables/bronze_sales")
df_bronze.printSchema()
df_bronze.show(5)

# Cell 3: Configure Spark session for optimized writes
spark.conf.set("spark.sql.parquet.vorder.enabled", "true")
spark.conf.set("spark.microsoft.delta.optimizeWrite.enabled", "true")
print("V-Order and Optimize Write enabled.")

# Cell 4: Transform bronze to silver
from pyspark.sql.functions import col, year, month, when

# Read bronze
df_bronze = spark.read.format("delta").load("Tables/bronze_sales")

# Clean and transform
df_silver = (
    df_bronze
    .filter(col("Quantity").isNotNull() & col("UnitPrice").isNotNull())
    .filter(col("Quantity") > 0)
    .withColumn("Quantity", col("Quantity").cast("integer"))
    .withColumn("UnitPrice", col("UnitPrice").cast("double"))
    .withColumn("Year", year(col("OrderDate")))
    .withColumn("Month", month(col("OrderDate")))
    .withColumn("TotalAmount", col("Quantity") * col("UnitPrice"))
    .dropDuplicates()
)

# Write as silver Delta table
df_silver.write.format("delta").mode("overwrite").save("Tables/silver_sales")
print(f"Silver table created with {df_silver.count()} rows.")

# Cell 5: Verify silver layer
df_silver_check = spark.read.format("delta").load("Tables/silver_sales")
df_silver_check.printSchema()
df_silver_check.show(5)

Your silver layer is locked in with clean, typed, and enriched data. Next up, you will build gold-layer aggregation tables that answer real business questions like monthly revenue trends and top-performing products.

Build Gold Layer Aggregations

Your silver layer has clean, properly typed data with derived columns ready for analysis. The final step in the medallion architecture is the gold layer, where you aggregate data into business-ready tables.

In this step, you will build two gold tables: one summarizing monthly revenue trends, and another ranking product performance. These are the tables that analysts and dashboards would query directly.

In this step, get ready to:

Create a monthly revenue aggregation table.
Build a product performance table.
Confirm all lakehouse layers are populated.

Create a gold monthly sales aggregation

The gold layer answers specific business questions. Your first gold table will answer: how much revenue did we generate each month, and how many orders contributed to it?

Add a new cell below Cell 5 in your notebook by hovering between cells and selecting + Code.
Paste the following code into Cell 6 and run it:

# Cell 6: Build gold monthly sales aggregation
from pyspark.sql.functions import sum, count, avg

df_silver = spark.read.format("delta").load("Tables/silver_sales")

df_gold_monthly = (
    df_silver
    .groupBy("Year", "Month")
    .agg(
        sum("TotalAmount").alias("TotalRevenue"),
        count("*").alias("TotalOrders"),
        avg("TotalAmount").alias("AvgOrderValue")
    )
    .orderBy("Year", "Month")
)

df_gold_monthly.write.format("delta").mode("overwrite").save("Tables/gold_monthly_sales")
print(f"Gold monthly sales table created with {df_gold_monthly.count()} rows.")

What does this code do?

This cell reads the silver table and groups all orders by Year and Month. For each group, it calculates three aggregates.

TotalRevenue sums all TotalAmount values for that month.
TotalOrders counts how many rows (orders) exist in that month.
AvgOrderValue calculates the average order size for that month.

The result is ordered chronologically and written as a Delta Lake table named gold_monthly_sales.

After the cell finishes, refresh the Lakehouse explorer by right-clicking Tables and selecting Refresh.
You should see gold_monthly_sales appear under Tables in the explorer pane.

Don't see gold_monthly_sales?

Make sure the cell finished running without errors. Check the output below the cell for a print statement confirming the row count.
Confirm you ran Cell 4 earlier in this session. Cell 6 reads from Tables/silver_sales which must already exist.
Try clicking the refresh icon next to Tables in the explorer. New tables sometimes take a moment to appear.

Why isn't my gold table showing in the lakehouse explorer?

Create a gold table for product performance

Your second gold table answers a different question: which products sell the most, and which generate the most revenue?

Add another new cell below Cell 6.
Paste the following code into Cell 7 and run it:

# Cell 7: Build gold product performance aggregation
df_gold_product = (
    df_silver
    .groupBy("ProductKey")
    .agg(
        sum("Quantity").alias("TotalQuantitySold"),
        sum("TotalAmount").alias("TotalRevenue")
    )
    .orderBy(col("TotalRevenue").desc())
)

df_gold_product.write.format("delta").mode("overwrite").save("Tables/gold_product_performance")
print(f"Gold product performance table created with {df_gold_product.count()} rows.")

What does this code do?

This groups all orders by ProductKey and calculates two metrics per product: total units sold and total revenue generated.

The results are ordered by TotalRevenue descending so the highest-earning products appear first. Notice this cell reuses the df_silver DataFrame that was loaded in Cell 6. PySpark keeps it in memory for the session.

Refresh the Lakehouse explorer again.
You should see gold_product_performance appear as a new table under Tables.

Seeing a NameError for col?

The col function was imported in Cell 4. If you restarted your session since then, re-run Cell 4 first to restore the import.
Check that Cell 6 ran successfully before Cell 7. Cell 7 relies on df_silver which is loaded in Cell 6.

Help me fix a NameError in my gold product performance cell.

Confirm all layers are populated

With all three layers built, you can verify the medallion pattern is working correctly. Each layer should have progressively fewer rows as data moves from raw to aggregated.

Add a new cell below Cell 7.
Paste the following code into Cell 8 and run it:

# Cell 8: Confirm all layers populated
bronze_count = spark.read.format("delta").load("Tables/bronze_sales").count()
silver_count = spark.read.format("delta").load("Tables/silver_sales").count()
gold_monthly_count = spark.read.format("delta").load("Tables/gold_monthly_sales").count()
gold_product_count = spark.read.format("delta").load("Tables/gold_product_performance").count()

print(f"Bronze: {bronze_count} rows")
print(f"Silver: {silver_count} rows")
print(f"Gold Monthly Sales: {gold_monthly_count} rows")
print(f"Gold Product Performance: {gold_product_count} rows")

What should I see?

The output prints row counts for all four Delta tables. Bronze has the most rows (raw data). Silver has slightly fewer (null and duplicate rows were removed). The gold tables have the fewest rows because they are aggregations.

This pattern confirms the medallion architecture is working: data quality increases as you move from bronze to gold, while row counts decrease through filtering and grouping.

Confirm the output shows all four tables with non-zero row counts.
Verify that the bronze count is greater than or equal to silver, and both gold tables have fewer rows than silver.

Seeing zero rows for a table?

Make sure you ran all previous cells (1 through 7) in order during this session. If you restarted the kernel, re-run from Cell 1.
Check that each earlier cell printed a confirmation message with a row count. If any cell failed silently, its table may be empty.

Help me figure out why one of my Delta tables has zero rows.

✔️ Awesome, I've got everything!

Great. Make sure your notebook is saved by pressing Ctrl+S or selecting the save icon in the toolbar.

ⓧ I'd like to double check the full code

Here is the complete notebook code from Cell 1 through Cell 8. Compare your cells against this:

# Cell 1: Download sample data and create bronze table
import requests
import os

data_url = "https://raw.githubusercontent.com/MicrosoftLearning/dp-data/main/sales.csv"
download_path = "/lakehouse/default/Files/raw"

if not os.path.exists("/lakehouse/default"):
    raise FileNotFoundError("Default lakehouse not found, please add a lakehouse and restart the session.")

os.makedirs(download_path, exist_ok=True)

file_path = f"{download_path}/sales.csv"
if not os.path.exists(file_path):
    r = requests.get(data_url, timeout=30)
    with open(file_path, "wb") as f:
        f.write(r.content)
    print("Downloaded sales.csv into lakehouse.")
else:
    print("File already exists.")

df_raw = spark.read.format("csv").option("header", "true").option("inferSchema", "true").load(file_path)
df_raw.write.format("delta").mode("overwrite").save("Tables/bronze_sales")
print(f"Bronze table created with {df_raw.count()} rows.")

# Cell 2: Explore bronze data
df_bronze = spark.read.format("delta").load("Tables/bronze_sales")
df_bronze.printSchema()
df_bronze.show(5)

# Cell 3: Configure Spark session for optimized writes
spark.conf.set("spark.sql.parquet.vorder.enabled", "true")
spark.conf.set("spark.microsoft.delta.optimizeWrite.enabled", "true")
print("V-Order and Optimize Write enabled.")

# Cell 4: Transform bronze to silver
from pyspark.sql.functions import col, year, month, when

df_bronze = spark.read.format("delta").load("Tables/bronze_sales")

df_silver = (
    df_bronze
    .filter(col("Quantity").isNotNull() & col("UnitPrice").isNotNull())
    .filter(col("Quantity") > 0)
    .withColumn("Quantity", col("Quantity").cast("integer"))
    .withColumn("UnitPrice", col("UnitPrice").cast("double"))
    .withColumn("Year", year(col("OrderDate")))
    .withColumn("Month", month(col("OrderDate")))
    .withColumn("TotalAmount", col("Quantity") * col("UnitPrice"))
    .dropDuplicates()
)

df_silver.write.format("delta").mode("overwrite").save("Tables/silver_sales")
print(f"Silver table created with {df_silver.count()} rows.")

# Cell 5: Verify silver layer
df_silver_check = spark.read.format("delta").load("Tables/silver_sales")
df_silver_check.printSchema()
df_silver_check.show(5)

# Cell 6: Build gold monthly sales aggregation
from pyspark.sql.functions import sum, count, avg

df_silver = spark.read.format("delta").load("Tables/silver_sales")

df_gold_monthly = (
    df_silver
    .groupBy("Year", "Month")
    .agg(
        sum("TotalAmount").alias("TotalRevenue"),
        count("*").alias("TotalOrders"),
        avg("TotalAmount").alias("AvgOrderValue")
    )
    .orderBy("Year", "Month")
)

df_gold_monthly.write.format("delta").mode("overwrite").save("Tables/gold_monthly_sales")
print(f"Gold monthly sales table created with {df_gold_monthly.count()} rows.")

# Cell 7: Build gold product performance aggregation
df_gold_product = (
    df_silver
    .groupBy("ProductKey")
    .agg(
        sum("Quantity").alias("TotalQuantitySold"),
        sum("TotalAmount").alias("TotalRevenue")
    )
    .orderBy(col("TotalRevenue").desc())
)

df_gold_product.write.format("delta").mode("overwrite").save("Tables/gold_product_performance")
print(f"Gold product performance table created with {df_gold_product.count()} rows.")

# Cell 8: Confirm all layers populated
bronze_count = spark.read.format("delta").load("Tables/bronze_sales").count()
silver_count = spark.read.format("delta").load("Tables/silver_sales").count()
gold_monthly_count = spark.read.format("delta").load("Tables/gold_monthly_sales").count()
gold_product_count = spark.read.format("delta").load("Tables/gold_product_performance").count()

print(f"Bronze: {bronze_count} rows")
print(f"Silver: {silver_count} rows")
print(f"Gold Monthly Sales: {gold_monthly_count} rows")
print(f"Gold Product Performance: {gold_product_count} rows")

Your medallion lakehouse is fully built. You have raw data in bronze, clean data in silver, and business-ready aggregations in gold. Next up, you will query these gold tables using the SQL analytics endpoint to prove the data is accessible via T-SQL.

Query with the SQL Analytics Endpoint

Your medallion lakehouse now has clean, aggregated data ready for analysis. The final piece is proving that this data is accessible to SQL-based tools and analysts.

Every Fabric lakehouse automatically provisions a SQL analytics endpoint. This gives you a read-only T-SQL interface over your Delta Lake tables without any extra configuration.

In this step, get ready to:

Switch from the lakehouse view to the SQL analytics endpoint.
Run T-SQL queries against your gold tables to see revenue trends and top products.
Create an analytical view with a window function for running totals.

Switch to the SQL analytics endpoint

The SQL analytics endpoint exposes your Delta tables as standard SQL tables. Only tables written to the /Tables path appear here.

Return to your lakehouse view. If you are still in the notebook, click sales_lakehouse in the left explorer pane to open the lakehouse.
In the top-right area of the ribbon, locate the dropdown that currently says Lakehouse.
Click the dropdown and select SQL analytics endpoint.

The view changes to show your Delta tables as SQL tables in the explorer under the dbo schema.

What is the SQL analytics endpoint?

It is a read-only SQL interface that Fabric auto-provisions for every lakehouse. You can run SELECT queries and create views, but you cannot INSERT, UPDATE, or DELETE data through it.

To modify data, you go back to PySpark in the lakehouse. This separation keeps your analytical queries from interfering with data engineering workloads.

Don't see your tables in the explorer?

Tables may take a moment to sync after being created. Try clicking the refresh icon in the explorer pane. If you still see no tables, confirm you wrote your Delta tables to the Tables/ path (not Files/) in your notebook.

help me find my tables in the SQL analytics endpoint

Run T-SQL queries against your gold tables

With the SQL analytics endpoint open, you can write T-SQL queries just like you would in SQL Server Management Studio. This proves your gold layer data is ready for downstream reporting tools.

Select New SQL query from the ribbon to open a query editor tab.

Query the monthly revenue trends by pasting and running this SQL:

-- Query 1: Monthly revenue trends
SELECT Year, Month, TotalRevenue, TotalOrders, AvgOrderValue
FROM dbo.gold_monthly_sales
ORDER BY Year, Month;

What does this query do?

This pulls every row from your gold_monthly_sales table, ordered chronologically. Each row represents one month's aggregated sales metrics: total revenue, order count, and average order value.

Click the Run button in the query toolbar (or press Shift+Enter).
You should see a results grid with rows showing Year, Month, TotalRevenue, TotalOrders, and AvgOrderValue.

Getting an 'Invalid object name' error?

Make sure the table name matches exactly: dbo.gold_monthly_sales. The SQL analytics endpoint is case-sensitive by default in Fabric. Also confirm you are in the SQL analytics endpoint view, not the Lakehouse view.

help me fix my SQL query error in the Fabric SQL analytics endpoint

Find the top 5 products by revenue by pasting and running this query:

-- Query 2: Top 5 products by revenue
SELECT TOP 5 ProductKey, TotalQuantitySold, TotalRevenue
FROM dbo.gold_product_performance
ORDER BY TotalRevenue DESC;

What does this query do?

This retrieves only the five highest-revenue products from gold_product_performance. The TOP 5 clause combined with ORDER BY TotalRevenue DESC sorts descending and returns the leaders.

Click Run. You should see 5 rows with ProductKey, TotalQuantitySold, and TotalRevenue columns.

Getting zero results?

If the query runs but returns no rows, your gold table may not have been written successfully. Switch back to Lakehouse mode and re-run Cell 7 in your notebook. Then return to the SQL analytics endpoint and refresh the explorer.

help me troubleshoot empty query results in Fabric

Create an analytical view

Views let you save reusable query logic directly in the SQL analytics endpoint. You will create a view that adds a running revenue total using a window function.

Select New SQL query again to open a fresh query tab.
Create the analytical view by pasting and running this SQL:

-- Query 3: Create an analytical view
CREATE VIEW dbo.vw_monthly_revenue_summary AS
SELECT
    Year,
    Month,
    TotalRevenue,
    TotalOrders,
    AvgOrderValue,
    SUM(TotalRevenue) OVER (PARTITION BY Year ORDER BY Month) AS RunningTotal
FROM dbo.gold_monthly_sales;

What does this view do?

The SUM(TotalRevenue) OVER (PARTITION BY Year ORDER BY Month) is a window function. It calculates a cumulative revenue total that resets at the start of each year.

Saving this as a view means any analyst or reporting tool can query dbo.vw_monthly_revenue_summary without rewriting the window logic each time.

Click Run. You should see a "Commands completed successfully" message.
Confirm the view appears in the explorer pane under Views. You may need to click the refresh icon.
Query the view to verify it works by pasting and running:

SELECT * FROM dbo.vw_monthly_revenue_summary
ORDER BY Year, Month;

Click Run. You should see all monthly rows with a RunningTotal column that accumulates within each year.

Seeing 'There is already an object named' error?

This means the view already exists from a previous run. Drop it first, then recreate:

DROP VIEW dbo.vw_monthly_revenue_summary;

Then re-run the CREATE VIEW statement.

help me fix my CREATE VIEW error in Fabric

✔️ Awesome, I've got everything!

Great. You have proven your entire medallion pipeline works end-to-end, from raw CSV to queryable SQL views.

ⓧ I'd like to double check the full SQL

Here are all three SQL queries together for reference:

-- Query 1: Monthly revenue trends
SELECT Year, Month, TotalRevenue, TotalOrders, AvgOrderValue
FROM dbo.gold_monthly_sales
ORDER BY Year, Month;

-- Query 2: Top 5 products by revenue
SELECT TOP 5 ProductKey, TotalQuantitySold, TotalRevenue
FROM dbo.gold_product_performance
ORDER BY TotalRevenue DESC;

-- Query 3: Create an analytical view
CREATE VIEW dbo.vw_monthly_revenue_summary AS
SELECT
    Year,
    Month,
    TotalRevenue,
    TotalOrders,
    AvgOrderValue,
    SUM(TotalRevenue) OVER (PARTITION BY Year ORDER BY Month) AS RunningTotal
FROM dbo.gold_monthly_sales;

Secret mission

Implement Incremental Loading with Delta MERGE

Your pipeline currently does a full overwrite every time it runs. In production, data arrives continuously. Can you implement the incremental loading pattern that the DP-700 exam tests heavily?

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs entirely on the free 60-day Fabric trial capacity, so there are no ongoing costs.

Resources you used:

Fabric workspace DP700-Medallion-Lab (contains the sales_lakehouse lakehouse, two notebooks, and all Delta tables).
SQL analytics endpoint with the dbo.vw_monthly_revenue_summary view (automatically provisioned with the lakehouse).

Keep everything running

No action needed. Choose this if you are still actively building or want to continue exploring your medallion lakehouse.

Your 60-day Fabric trial remains active with 64 CU capacity and up to 1 TB of OneLake storage.
You can create additional notebooks, add new tables, or experiment with more PySpark transformations at any time.
The trial expires automatically after 60 days if you do not purchase paid capacity.

Pause - I'll come back to this later

Nothing to pause. Your Fabric trial capacity is always available until it expires. There are no running processes or services that consume resources while idle.

Your workspace, lakehouse, notebooks, and Delta tables remain exactly as you left them.
No charges accrue while the trial is active, regardless of whether you are using it or not.
Simply return to app.fabric.microsoft.com and sign in to pick up where you left off.

Delete - I don't want to use this again

Remove all project resources and start fresh. Deleting the workspace permanently removes the lakehouse, all Delta tables, both notebooks, and the SQL analytics endpoint.

Select Workspaces from the left navigation bar.
Find DP700-Medallion-Lab in the workspace list.
Select the ellipsis (...) menu next to the workspace name.
Select Delete.
Confirm the deletion when prompted. This permanently removes all items inside the workspace, including sales_lakehouse, the medallion_pipeline notebook, the incremental_merge notebook, and all Delta tables.

Nice Work!

You just built a production-ready medallion architecture lakehouse in Microsoft Fabric. Your data flows from raw CSV all the way through bronze, silver, and gold layers, and you can query it with plain T-SQL.

You've learned how to:

Build a complete medallion architecture lakehouse (bronze, silver, gold) using Delta Lake tables in Microsoft Fabric.
Write PySpark notebook code to ingest CSV data, transform it with DataFrame operations, and produce aggregated gold tables.
Query your lakehouse Delta tables via the SQL analytics endpoint using T-SQL.
Secret Mission: Implemented an incremental load using Delta Lake MERGE to upsert records into your silver layer.

Ready to quiz yourself?