Financial Transactions Medallion Pipeline

Build a Bronze-Silver-Gold data pipeline in Microsoft Fabric with PySpark.

Introduction

30 Second Summary

Every company has messy financial data flowing in from multiple sources, full of duplicates, missing values, and inconsistent formats. Before anyone can build dashboards or make decisions, someone has to clean that data up and organize it into layers that business teams can trust.

In this project, you will build a complete data pipeline that takes raw transaction data, cleanses it, and produces business-ready aggregation tables using the Medallion Architecture pattern in Microsoft Fabric.

What You'll Build

A production-style Medallion Architecture data pipeline that ingests a sample financial transactions CSV, transforms it with PySpark into clean Delta Lake tables, and creates Gold aggregation tables ready for BI consumption.

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

A Dataflow Gen2 that ingests raw CSV data into your Bronze Lakehouse and a Data Pipeline that orchestrates the full flow.
A PySpark Notebook that transforms Bronze data into clean Silver Delta tables using deduplication, null handling, and data type enforcement.
A Spark SQL Notebook that creates Gold aggregation tables for daily transaction volumes, category spending, and account balance summaries.
Secret Mission: Add an incremental load pattern using Delta Lake MERGE to handle daily transaction updates without reprocessing the entire dataset.

Are there any prerequisites?

This project assumes you have access to a Microsoft Fabric Dev workspace with Bronze, Silver, and Gold Lakehouses already provisioned. You also need basic Python and pandas knowledge, along with familiarity with SQL.

No prior PySpark or Fabric notebook experience is required. This project is designed to be your first hands-on encounter with both.

Before We Start

Before We Start...

Prepare Your Sample Data and Bronze Lakehouse

Every data pipeline starts with raw source data. In production, that data rarely arrives clean. It comes with duplicates, missing values, and inconsistent formatting that your pipeline needs to handle.

In this step, you'll create a sample CSV file that mimics these real-world data quality issues. Then you'll upload it to your Bronze Lakehouse, where it becomes the starting point for your entire pipeline.

In this step, get ready to:

Create a sample transactions CSV with intentional data quality issues.
Upload the CSV to your Bronze Lakehouse.
Verify the file landed in the correct location.

Create your sample transactions CSV

Your pipeline needs source data to process. You'll create a CSV file with realistic banking transactions that contains intentional quality problems. These problems simulate what real source systems produce every day.

Open any text editor on your computer (Notepad, TextEdit, or VS Code).
Create a new file and paste the following CSV content into it:

transaction_id,account_id,transaction_date,amount,currency,category,merchant,status,description
TXN001,ACC100,2024-01-15,250.00,USD,groceries,FreshMart,completed,Weekly grocery shopping
TXN002,ACC100,2024-01-15,250.00,USD,groceries,FreshMart,completed,Weekly grocery shopping
TXN003,ACC101,2024-01-16,89.99,USD,Electronics,TechStore,completed,Headphones purchase
TXN004,ACC102,2024-01-16,,USD,dining,CafeDeluxe,completed,Lunch meeting
TXN005,ACC100,2024-01-17,1200.00,USD,RENT,PropertyMgmt,completed,Monthly rent payment
TXN006,ACC103,2024-01-17,45.50,USD,Transport,UberRides,pending,Airport transfer
TXN007,ACC101,2024-01-18,320.00,USD,  Groceries  ,WholeFoods,completed,Organic shopping
TXN008,ACC102,2024-01-18,67.80,USD,dining,Sushi Palace,completed,Team dinner
TXN009,ACC100,2024-01-19,150.00,USD,utilities,PowerCo,completed,Electricity bill
TXN010,ACC103,2024-01-19,2500.00,USD,transfer,BankTransfer,completed,Savings transfer
TXN011,ACC101,2024-01-20,34.99,USD,entertainment,Netflix,completed,Monthly subscription
TXN012,ACC102,2024-01-20,890.00,USD,Travel,AirlineXYZ,pending,Flight booking
TXN013,ACC100,2024-01-21,55.00,USD,Dining,CafeDeluxe,completed,Brunch
TXN014,ACC103,2024-01-21,120.00,USD,groceries,FreshMart,completed,Weekly groceries
TXN015,ACC101,2024-01-22,430.00,USD,electronics,BestBuy,completed,Monitor purchase
TXN016,ACC102,2024-01-22,28.50,USD,transport,MetroCard,completed,Weekly transit pass
TXN017,ACC100,2024-01-23,75.00,USD,ENTERTAINMENT,MovieTheater,completed,Family movie night
TXN018,ACC103,2024-01-23,,USD,groceries,TraderJoes,failed,Payment declined
TXN019,ACC101,2024-01-24,1500.00,USD,rent,PropertyMgmt,completed,Monthly rent
TXN020,ACC102,2024-01-24,95.00,USD,Utilities,WaterCorp,completed,Water bill

What quality issues are hiding in this data?

This file is intentionally messy, just like real source data. Your PySpark transformations will fix these issues in a later step.

Duplicate rows: TXN001 and TXN002 are identical transactions.
Null amounts: TXN004 and TXN018 have empty amount fields.
Mixed-case categories: groceries, Electronics, RENT, ENTERTAINMENT all refer to similar concepts but with inconsistent casing.
Whitespace padding: TXN007 has leading and trailing spaces in its category value.

Save the file as financial_transactions.csv to your Desktop or a location you can easily find.

Upload the CSV to your Bronze Lakehouse

The Bronze layer in a Medallion Architecture stores data exactly as it arrives from the source. No transformations happen here. You'll upload your CSV into the Bronze Lakehouse's Files section, which acts as a landing zone for raw data.

Open your browser and navigate to https://app.fabric.microsoft.com.
Sign in with your organizational account.
Select Workspaces in the left navigation pane.
Select your Dev workspace from the list.
Find and select your Bronze Lakehouse to open it.

You should now see the Lakehouse explorer with a Tables section and a Files section in the left pane.

In the explorer pane on the left, hover over Files and select the ellipsis ... that appears.
Select New subfolder.
Enter raw_transactions as the folder name and select Create.
Hover over the new raw_transactions folder and select the ellipsis ... that appears.
Select Upload > Upload files.
Browse to the financial_transactions.csv file you saved earlier and select it.
Select Upload to confirm.

Why upload to Files instead of Tables?

The Files section is the unmanaged area of your Lakehouse. It stores raw files in their original format without converting them to Delta Lake tables. This is exactly what a Bronze layer needs. You keep the data as-is and let downstream processes handle the transformation.

Don't see your Bronze Lakehouse?

Make sure you're in the correct Dev workspace. Select Workspaces in the left navigation pane again and confirm you've selected the right workspace.

If your Lakehouses still don't appear, confirm with your workspace administrator that the Bronze, Silver, and Gold Lakehouses have been provisioned.

Help me find my Bronze Lakehouse in Microsoft Fabric

Verify the file landed

In the Lakehouse explorer on the left, expand the Files section.
Select the raw_transactions folder to open it in the main view.

You should see financial_transactions.csv listed in the main view pane on the right.

File not showing up?

Try selecting the ellipsis ... next to Files and choosing Refresh. The explorer sometimes needs a manual refresh after an upload.

My uploaded file isn't appearing in the Lakehouse explorer

Your raw data is now sitting in the Bronze Lakehouse, ready for ingestion. Next up, you'll use Dataflow Gen2 to convert this raw file into a Delta table that Fabric can query and process.

Ingest Data with Dataflow Gen2

Your CSV file is sitting in the Bronze Lakehouse, but it is still just a raw file. For PySpark notebooks to query it efficiently in later steps, you need to convert it into a Delta table.

Dataflow Gen2 is Fabric's visual ingestion tool built on the Power Query engine you already know from Power BI. In this step, you will use it to read your CSV and write it as a Delta table in the Bronze Lakehouse.

In this step, get ready to:

Create a Dataflow Gen2 to connect to your CSV file.
Configure the data destination to write a Delta table.
Validate the ingestion via the SQL analytics endpoint.

Create a Dataflow Gen2

When you create a Dataflow Gen2 directly from a Lakehouse, Fabric automatically sets that Lakehouse as the data destination. This saves you configuration time.

In your Dev workspace, open your Bronze Lakehouse.
Select Get data in the ribbon, then select New Dataflow Gen2.
In the New Dataflow Gen2 pane, enter Ingest Financial Transactions in the Name field.
Select Create.

The Power Query editor opens. This is where you connect to your source data.

On the dataflow Home tab, select the Import from a Text/CSV file tile.
On the Connect to data source screen, select the Upload file radio button.
Browse to and select your financial_transactions.csv file from your local machine.
After the upload completes, select Next.
On the Preview file data page, confirm that columns like transaction_id, amount, and category are detected correctly.
Select Create to return to the dataflow canvas.

What is Dataflow Gen2?

Dataflow Gen2 uses the same Power Query interface you use in Power BI Desktop. It is a low-code tool for connecting to data sources, applying transformations, and loading data into Fabric destinations like Lakehouse tables.

For this project, you are using it purely for ingestion (no transformations). The heavy transformation work happens in PySpark notebooks in the next step.

Why use Dataflow Gen2 for ingestion?

You already know Power Query from your BI Analyst work. Using Dataflow Gen2 for ingestion leverages that existing skill while reserving PySpark for the transformation logic you need to learn.

This mirrors production patterns where Dataflow Gen2 handles ingestion and Notebooks handle complex transformations.

Configure the data destination and run the dataflow

Because you created the dataflow from within the Bronze Lakehouse, the data destination is already pointed at that Lakehouse. You just need to confirm the table name.

In the Query settings pane on the right side of the editor, find the Name field.
Set the name to raw_transactions. This becomes the Delta table name in your Lakehouse.
Verify that the data destination below shows your Bronze Lakehouse under Tables.
Select Publish in the lower-right corner to save and run the dataflow.

Fabric begins running the dataflow. You are returned to the workspace while it processes.

What are the DataflowsStaging items?

When the first Dataflow Gen2 is created in a workspace, Fabric automatically provisions Lakehouse and Warehouse items with the prefix DataflowsStaging. These are shared by all dataflows in the workspace and required for operation.

Do not delete them. They are internal infrastructure, not something you interact with directly.

Dataflow stuck or failing?

If the dataflow shows a failure status, open it and check that the file upload completed successfully. Re-upload the CSV if the file column shows an error.

Also confirm your data destination still points to the Bronze Lakehouse under Tables, not Files.

help me troubleshoot my Dataflow Gen2 run failure in Microsoft Fabric

Validate the ingestion

Once the dataflow run completes, the CSV data is now stored as a Delta table. You can verify this in two ways: through the Lakehouse explorer and through the SQL analytics endpoint.

Navigate back to your Bronze Lakehouse from the Dev workspace.
In the Lakehouse explorer, expand the Tables section.
Confirm that raw_transactions appears as a Delta table. Select it to preview the data.

You should see all 20 rows from your CSV, including the messy data (duplicates, nulls, mixed-case categories). This is expected. The Bronze layer stores data exactly as it arrives.

Switch to the SQL analytics endpoint by using the dropdown in the top-right area of the Lakehouse ribbon.
Select New SQL query from the toolbar.
Run the following query to confirm the row count:

SELECT COUNT(*) FROM raw_transactions

You should see a count of 20 returned in the results pane.

What is the SQL analytics endpoint?

Every Lakehouse automatically provisions a SQL analytics endpoint. It gives you a read-only T-SQL query surface over your Delta tables without any extra setup.

This is how your analyst colleagues (and Power BI) will eventually query the Gold tables you build later in this project.

Table not showing up?

The SQL analytics endpoint auto-syncs when new Delta tables are created. If you do not see raw_transactions, wait 30 seconds and select the Refresh button in the Explorer toolbar.

If the table still does not appear, confirm that the Dataflow Gen2 run completed successfully in your workspace (look for a green check next to the dataflow name).

help me figure out why my raw_transactions table is not showing in the SQL analytics endpoint

Your Bronze layer is loaded. The raw data is stored exactly as it arrived, quality issues and all. Next up, you will write PySpark code to cleanse this data and produce a clean Silver table.

Transform Bronze to Silver with PySpark

Your Bronze Lakehouse now has raw transaction data sitting in a Delta Lake table. The data is in, but it is messy.

Duplicate records, null values, and inconsistent formatting all need to be fixed before any analyst can trust this data. In this step, you will write your first PySpark notebook to cleanse the Bronze data and write it into a clean Silver Delta table.

In this step, get ready to:

Read raw Bronze data into a PySpark DataFrame.
Apply data cleansing transformations to remove duplicates, nulls, and inconsistencies.
Write the cleansed data as a Silver Delta table.

Create a notebook and read Bronze data

To transform data in Microsoft Fabric, you write code in notebooks. Your notebook needs to connect to your Silver Lakehouse (where you will write the output) and read from your Bronze Lakehouse (where the raw data lives).

Return to your Dev workspace in the Fabric portal.
Select + New item in the toolbar, then choose Notebook from the panel that opens.
A new notebook opens with a blank code cell. Rename it by clicking the default name at the top and typing Bronze_to_Silver_Transactions.

What is a Fabric Notebook?

A Fabric Notebook is a web-based coding surface that runs on Apache Spark. You write code in cells and execute them one at a time. Each cell runs on a remote Spark cluster managed by Fabric.

In the Lakehouse explorer on the left side of the notebook, select Add lakehouse.
Choose Existing lakehouse and select your Silver Lakehouse.
Pin your Silver Lakehouse so it becomes the default (the pin icon appears next to its name).

Why pin Silver as the default?

The default lakehouse determines where saveAsTable writes data. Since you want the cleansed output to land in Silver, it should be your default. You will still read from Bronze using its full ABFS path.

Now you need the ABFS path for the Bronze raw_transactions table. This path lets your notebook reach across to the Bronze Lakehouse.

Open a new browser tab and navigate to your Bronze Lakehouse.
Expand Tables in the Lakehouse explorer.
Right-click the raw_transactions table and select Copy ABFS path.
Switch back to your notebook tab. You will paste this path into the code below.

Paste the following code into the first cell of your notebook, replacing the ABFS path placeholder with the path you just copied:

# Cell 1: Read Bronze data
from pyspark.sql.functions import col, lower, trim, to_date, current_timestamp, lit

# Read raw transactions from Bronze Lakehouse
# Replace with your actual ABFS path (right-click table in Bronze Lakehouse > Copy ABFS path)
df_bronze = spark.read.format("delta").load("[[BRONZE_ABFS_PATH=\"your Bronze raw_transactions ABFS path\"]]")

print(f"Bronze row count: {df_bronze.count()}")
display(df_bronze)

What does this code do?

The import line brings in PySpark functions you will use for transformations later.

The spark.read.format("delta").load(...) line reads the Bronze Delta table into a DataFrame called df_bronze. A DataFrame is a structured table of data you can manipulate with PySpark. The display() call renders the data visually so you can inspect it.

Run the cell by clicking the Run cell button (or press Shift+Enter).

First run may take a moment

Every Fabric workspace includes a Spark starter pool. The first time you run a cell, Spark session initialization typically takes 5 to 10 seconds. Subsequent cells run faster.

Confirm you see a row count printed (20 rows) and a table preview showing all the raw data, including duplicates, null amounts, and inconsistent category names.

Not seeing any data?

Make sure you pasted the correct ABFS path. It should start with abfss:// and end with /Tables/raw_transactions.

If you see a path error, go back to the Bronze Lakehouse, right-click the table again, and verify you are copying the ABFS path (not the relative path).

Why can't my notebook find the Bronze table?

Write PySpark transformations to cleanse the data

This is the core data engineering work. You will chain together multiple DataFrame operations to fix every quality issue in the raw data.

Add a new code cell below Cell 1 by hovering between cells and selecting + Code.
Paste the following transformation code into Cell 2:

# Cell 2: Data cleansing transformations
# Remove duplicate transactions (keeps first occurrence)
df_deduped = df_bronze.dropDuplicates(["transaction_id"])
print(f"After deduplication: {df_deduped.count()} rows")

# Remove records with null amounts (cannot process transactions without amounts)
df_no_nulls = df_deduped.filter(col("amount").isNotNull())
print(f"After removing null amounts: {df_no_nulls.count()} rows")

# Standardize category names (lowercase, trimmed)
df_standardized = df_no_nulls.withColumn("category", lower(trim(col("category"))))

# Cast amount to proper decimal type
df_typed = df_standardized.withColumn("amount", col("amount").cast("decimal(10,2)"))

# Parse transaction_date to date type
df_dated = df_typed.withColumn("transaction_date", to_date(col("transaction_date"), "yyyy-MM-dd"))

# Add audit column for data lineage
df_silver = df_dated.withColumn("ingested_at", current_timestamp())

print(f"Silver row count: {df_silver.count()}")
display(df_silver)

What does this code do?

dropDuplicates(["transaction_id"]) removes rows that share the same transaction_id. Your raw data had duplicate TXN001/TXN002 entries.
filter(col("amount").isNotNull()) drops rows where the amount is missing. Transactions without an amount cannot be processed.
lower(trim(col("category"))) standardizes categories like " Groceries " and "RENT" into consistent lowercase values.
cast("decimal(10,2)") ensures the amount column is a precise numeric type for financial calculations.
to_date(...) parses the date string into a proper date type that supports date operations.
current_timestamp() adds an audit column recording when each row was processed. This supports data lineage tracking.

Run Cell 2 by pressing Shift+Enter.
Confirm the print statements show a decreasing row count (duplicates and nulls removed) and that the displayed table shows lowercase categories and a new ingested_at column.

Seeing a NameError for df_bronze?

This means Cell 1 did not run successfully before Cell 2. Go back to Cell 1 and run it first. Each cell depends on the variables created by previous cells.

Help me fix my PySpark NameError in Fabric

Write the cleansed DataFrame as a Silver Delta table

Your transformations are done. The final step is writing the cleansed DataFrame to the Silver Lakehouse as a Delta table so it is available for downstream consumption.

Add a new code cell below Cell 2.
Paste the following code into Cell 3:

# Cell 3: Write to Silver Lakehouse as Delta table
df_silver.write.mode("overwrite").format("delta").saveAsTable("silver_transactions")

print("Silver table written successfully!")
print(f"Records dropped during cleansing: {df_bronze.count() - df_silver.count()}")

What does this code do?

The write.mode("overwrite").format("delta").saveAsTable("silver_transactions") line writes your cleaned DataFrame as a Delta table named silver_transactions in the default Lakehouse (your pinned Silver Lakehouse).

Using saveAsTable automatically registers the table in the metastore, making it visible in the Lakehouse explorer and queryable via the SQL analytics endpoint.

Run Cell 3 by pressing Shift+Enter.
Confirm you see "Silver table written successfully!" and a count of records dropped.
In the Lakehouse explorer on the left, click the refresh icon next to your Silver Lakehouse.
Expand Tables and confirm the silver_transactions table now appears.

Table not appearing in the explorer?

Click the refresh icon in the Lakehouse explorer. Fabric does not auto-refresh the table list after a write operation.

If Cell 3 threw an error, make sure your Silver Lakehouse is pinned as the default. The saveAsTable call writes to whichever lakehouse is pinned.

Help me fix my Silver table write in Fabric

✔️ Awesome, I've got everything!

Great. Double check you have saved your notebook and that silver_transactions appears under Tables in your Silver Lakehouse explorer.

ⓧ I'd like to double check the full code

Here is the complete code across all three cells:

# Cell 1: Read Bronze data
from pyspark.sql.functions import col, lower, trim, to_date, current_timestamp, lit

# Read raw transactions from Bronze Lakehouse
# Replace with your actual ABFS path (right-click table in Bronze Lakehouse > Copy ABFS path)
df_bronze = spark.read.format("delta").load("[[BRONZE_ABFS_PATH=\"your Bronze raw_transactions ABFS path\"]]")

print(f"Bronze row count: {df_bronze.count()}")
display(df_bronze)

# Cell 2: Data cleansing transformations
# Remove duplicate transactions (keeps first occurrence)
df_deduped = df_bronze.dropDuplicates(["transaction_id"])
print(f"After deduplication: {df_deduped.count()} rows")

# Remove records with null amounts (cannot process transactions without amounts)
df_no_nulls = df_deduped.filter(col("amount").isNotNull())
print(f"After removing null amounts: {df_no_nulls.count()} rows")

# Standardize category names (lowercase, trimmed)
df_standardized = df_no_nulls.withColumn("category", lower(trim(col("category"))))

# Cast amount to proper decimal type
df_typed = df_standardized.withColumn("amount", col("amount").cast("decimal(10,2)"))

# Parse transaction_date to date type
df_dated = df_typed.withColumn("transaction_date", to_date(col("transaction_date"), "yyyy-MM-dd"))

# Add audit column for data lineage
df_silver = df_dated.withColumn("ingested_at", current_timestamp())

print(f"Silver row count: {df_silver.count()}")
display(df_silver)

# Cell 3: Write to Silver Lakehouse as Delta table
df_silver.write.mode("overwrite").format("delta").saveAsTable("silver_transactions")

print("Silver table written successfully!")
print(f"Records dropped during cleansing: {df_bronze.count() - df_silver.count()}")

Your Silver layer is live. You now have clean, deduplicated, strongly-typed financial transaction data sitting in a Delta table. Next up, you will create Gold aggregation tables that turn this data into business-ready insights.

Create Gold Aggregations with Spark SQL

Your Silver layer is clean, deduplicated, and properly typed. Now it is time to build the Gold layer where data becomes directly useful for business decisions.

The Gold layer contains pre-aggregated tables that answer specific business questions. You will write Spark SQL queries to summarize your Silver transactions into daily spending summaries, account balance snapshots, and merchant spending analysis.

In this step, get ready to:

Create a new notebook and read Silver data via ABFS path.
Write Spark SQL aggregation queries to produce three Gold Delta tables.
Validate the Gold layer through the SQL analytics endpoint.

Create the Silver_to_Gold_Aggregations notebook

Since this notebook writes to your Gold Lakehouse, you will attach the Gold Lakehouse as the default. You will also read from the Silver Lakehouse using its ABFS path.

Navigate to your Dev workspace.
Select + New Item and choose Notebook.
Name the notebook Silver_to_Gold_Aggregations.
In the Lakehouse explorer on the left, select Add lakehouse.
Add your Gold Lakehouse and pin it as the default by selecting the pin icon next to its name.

Why pin the Gold Lakehouse as default?

The default lakehouse determines which Hive metastore the notebook uses when running Spark SQL. Since your Gold aggregation tables need to land in the Gold Lakehouse, pinning it as default means saveAsTable writes directly to the Gold metastore.

Now you need the ABFS path for your Silver Lakehouse's silver_transactions table so you can read it from this notebook.

Open your Silver Lakehouse in a separate browser tab.
In the Lakehouse explorer, expand Tables.
Right-click silver_transactions and select Copy ABFS path.
Switch back to your Silver_to_Gold_Aggregations notebook tab.

Paste the following code into Cell 1, replacing the ABFS path placeholder with the path you just copied:

# Cell 1: Read Silver data from Silver Lakehouse
# Replace with your actual ABFS path (right-click table in Silver Lakehouse > Copy ABFS path)
df_silver = spark.read.format("delta").load("abfss://<YourWorkspace>@onelake.dfs.fabric.microsoft.com/<SilverLakehouse>.Lakehouse/Tables/silver_transactions")

df_silver.createOrReplaceTempView("silver_transactions")
print(f"Silver transactions loaded: {df_silver.count()} rows")

What does this code do?

spark.read.format("delta").load(...) reads the Silver Delta Lake table from the Silver Lakehouse using the full ABFS path.
createOrReplaceTempView("silver_transactions") registers the DataFrame as a temporary SQL view so you can query it with Spark SQL in later cells.
The print statement confirms how many cleansed rows are available for aggregation.

Run Cell 1 by clicking the play button on the cell.

You should see a row count printed (around 18 rows after the Silver cleansing removed duplicates and null amounts).

Seeing a path not found error?

Double-check that you replaced the full placeholder path with the ABFS path you copied from your Silver Lakehouse.
Make sure the path ends with /Tables/silver_transactions and not /Files/....
Confirm that you ran the Bronze_to_Silver notebook successfully in the previous step. The Silver table must exist before you can read it.

Why can't my notebook find the Silver table?

Write Spark SQL aggregation queries

Each Gold table answers a specific business question. You will create three aggregation tables using Spark SQL, which leverages your existing SQL skills inside the Spark engine.

Add a new cell below Cell 1 and paste the following code for the daily transaction summary:

# Cell 2: Gold Table 1 - Daily Transaction Summary by Category
gold_daily_summary = spark.sql("""
    SELECT 
        transaction_date,
        category,
        COUNT(*) AS transaction_count,
        SUM(amount) AS total_amount,
        ROUND(AVG(amount), 2) AS avg_amount
    FROM silver_transactions
    WHERE status = 'completed'
    GROUP BY transaction_date, category
    ORDER BY transaction_date, category
""")

gold_daily_summary.write.mode("overwrite").format("delta").saveAsTable("gold_daily_transaction_summary")
print("Gold daily summary written!")
display(gold_daily_summary)

What does this query do?

Filters to only completed transactions so pending and failed ones do not skew business metrics.
Groups by transaction_date and category to show daily spending patterns per category.
Produces transaction count, total spend, and average spend per group. These are the exact metrics a BI analyst would need for a daily spending dashboard.

Run Cell 2.

You should see a table output showing daily totals broken down by category.

Add a new cell and paste the following code for the account balance snapshot:

# Cell 3: Gold Table 2 - Account Balance Snapshot
gold_account_balance = spark.sql("""
    SELECT 
        account_id,
        COUNT(*) AS total_transactions,
        SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) AS total_debits,
        SUM(amount) AS net_total,
        MIN(transaction_date) AS first_transaction,
        MAX(transaction_date) AS last_transaction
    FROM silver_transactions
    WHERE status = 'completed'
    GROUP BY account_id
    ORDER BY account_id
""")

gold_account_balance.write.mode("overwrite").format("delta").saveAsTable("gold_account_balance_snapshot")
print("Gold account balance snapshot written!")
display(gold_account_balance)

What does this query do?

Summarizes each account's activity: total transactions, total debits, and net total.
Uses MIN and MAX on transaction_date to show the time range of each account's activity.
This table lets a risk analyst quickly identify high-volume accounts or spot unusual activity patterns.

Run Cell 3.

You should see one row per account ID with their aggregated totals.

Add a final cell and paste the following code for the merchant spending analysis:

# Cell 4: Gold Table 3 - Merchant Spending Analysis
gold_merchant = spark.sql("""
    SELECT 
        merchant,
        category,
        COUNT(*) AS transaction_count,
        SUM(amount) AS total_spend,
        ROUND(AVG(amount), 2) AS avg_transaction
    FROM silver_transactions
    WHERE status = 'completed'
    GROUP BY merchant, category
    ORDER BY total_spend DESC
""")

gold_merchant.write.mode("overwrite").format("delta").saveAsTable("gold_merchant_spending")
print("Gold merchant spending written!")
display(gold_merchant)

What does this query do?

Ranks merchants by total spend (ORDER BY total_spend DESC) so you can immediately see where the most money is going.
Groups by both merchant and category so the same merchant appearing in multiple categories shows as separate rows.
This is the kind of table a finance team uses to negotiate vendor contracts or identify spending outliers.

Run Cell 4.

You should see merchants listed from highest to lowest total spend.

Seeing an AnalysisException about the table name?

Make sure your Gold Lakehouse is pinned as the default. If a different lakehouse is pinned, saveAsTable writes to the wrong metastore.
If you changed the default lakehouse after starting the session, you need to restart the Spark session for it to take effect.

Why is my Gold table not writing to the correct lakehouse?

✔️ Awesome, I've got everything!

Great. Double check you have all four cells saved in your notebook before moving on.

ⓧ I'd like to double check the full code

Here is the complete Silver_to_Gold_Aggregations notebook with all four cells:

# Cell 1: Read Silver data from Silver Lakehouse
# Replace with your actual ABFS path (right-click table in Silver Lakehouse > Copy ABFS path)
df_silver = spark.read.format("delta").load("abfss://<YourWorkspace>@onelake.dfs.fabric.microsoft.com/<SilverLakehouse>.Lakehouse/Tables/silver_transactions")

df_silver.createOrReplaceTempView("silver_transactions")
print(f"Silver transactions loaded: {df_silver.count()} rows")

# Cell 2: Gold Table 1 - Daily Transaction Summary by Category
gold_daily_summary = spark.sql("""
    SELECT 
        transaction_date,
        category,
        COUNT(*) AS transaction_count,
        SUM(amount) AS total_amount,
        ROUND(AVG(amount), 2) AS avg_amount
    FROM silver_transactions
    WHERE status = 'completed'
    GROUP BY transaction_date, category
    ORDER BY transaction_date, category
""")

gold_daily_summary.write.mode("overwrite").format("delta").saveAsTable("gold_daily_transaction_summary")
print("Gold daily summary written!")
display(gold_daily_summary)

# Cell 3: Gold Table 2 - Account Balance Snapshot
gold_account_balance = spark.sql("""
    SELECT 
        account_id,
        COUNT(*) AS total_transactions,
        SUM(CASE WHEN amount > 0 THEN amount ELSE 0 END) AS total_debits,
        SUM(amount) AS net_total,
        MIN(transaction_date) AS first_transaction,
        MAX(transaction_date) AS last_transaction
    FROM silver_transactions
    WHERE status = 'completed'
    GROUP BY account_id
    ORDER BY account_id
""")

gold_account_balance.write.mode("overwrite").format("delta").saveAsTable("gold_account_balance_snapshot")
print("Gold account balance snapshot written!")
display(gold_account_balance)

# Cell 4: Gold Table 3 - Merchant Spending Analysis
gold_merchant = spark.sql("""
    SELECT 
        merchant,
        category,
        COUNT(*) AS transaction_count,
        SUM(amount) AS total_spend,
        ROUND(AVG(amount), 2) AS avg_transaction
    FROM silver_transactions
    WHERE status = 'completed'
    GROUP BY merchant, category
    ORDER BY total_spend DESC
""")

gold_merchant.write.mode("overwrite").format("delta").saveAsTable("gold_merchant_spending")
print("Gold merchant spending written!")
display(gold_merchant)

Validate the Gold layer via SQL analytics endpoint

The SQL analytics endpoint gives you a read-only T-SQL query surface over your Delta Lake tables. Every Lakehouse automatically provisions one. You will use it to confirm your Gold tables are ready for BI consumption.

Navigate to your Gold Lakehouse in the Dev workspace.
In the top-right area of the ribbon, use the dropdown to switch to the SQL analytics endpoint view.
In the Explorer pane, expand Tables. You should see gold_daily_transaction_summary, gold_account_balance_snapshot, and gold_merchant_spending listed.

Tables may take up to a minute to appear

The SQL analytics endpoint syncs metadata automatically. Under normal conditions the lag is less than one minute. If you do not see your tables yet, wait a moment and refresh the Explorer pane.

Open a New SQL query window.
Run the following query to confirm data in your daily summary table:

SELECT * FROM gold_daily_transaction_summary ORDER BY transaction_date;

Verify that results appear showing daily totals grouped by category.
Run a similar query against the other two tables to confirm they contain data:

SELECT * FROM gold_account_balance_snapshot;
SELECT * FROM gold_merchant_spending;

You should see account summaries and merchant rankings returned. Your Gold layer is now queryable by anyone with access to this SQL analytics endpoint.

Why does this matter for BI?

Your analyst colleagues can now connect Power BI to these Gold tables directly using Direct Lake mode. They get live query performance against Delta tables without data imports or scheduled refreshes. This is the production pattern for serving BI from a Lakehouse.

Tables not showing in the SQL analytics endpoint?

Make sure you wrote to the Gold Lakehouse (not Silver). Check that the Gold Lakehouse was pinned as default before you ran the cells.
Try clicking the refresh icon in the Explorer toolbar to force a metadata sync.
Only Delta tables stored under the /Tables folder appear in the endpoint. Using saveAsTable writes to this location automatically.

My Gold tables are not appearing in the SQL analytics endpoint. What should I check?

Your Gold layer is complete. Next up, you will orchestrate the entire flow with a Data Pipeline so it runs end-to-end with a single click.

Orchestrate with a Data Pipeline

You have all three layers of the Medallion Architecture built and working. Bronze ingests raw data, Silver cleanses it, and Gold aggregates it for business consumption.

Right now, each piece runs independently. In production, a data engineer doesn't manually open each notebook and click Run every morning. A Data Pipeline orchestrates the full flow automatically: ingest, then transform, then aggregate, in sequence, with built-in dependency handling.

In this step, get ready to:

Create a Data Pipeline to orchestrate the full Medallion flow.
Add Dataflow and Notebook activities connected with success dependencies.
Run the pipeline end-to-end and verify all activities complete successfully.

Create the Data Pipeline

A pipeline is a workflow that chains activities together with dependencies. Each activity only runs after the previous one succeeds, so if your ingestion fails, the transformation notebooks never fire on stale data.

Navigate to your Dev workspace.
Select + New item at the top of the page.
Search for and select Data pipeline.
Enter Financial_Transactions_Pipeline as the name.
Select Create.

The pipeline editor opens with an empty canvas. You will see an Activities pane where you can browse and add activities to your pipeline.

What is a Data Pipeline?

A Data Pipeline is a logical grouping of activities that together perform a data workflow. Activities are the building blocks that move or transform data.

You connect activities with dependency paths so they execute in the correct order. If an upstream activity fails, downstream activities are skipped automatically.

Add activities to the pipeline canvas

Your pipeline needs three activities in sequence: a Dataflow activity for ingestion, then two Notebook activities for the Silver and Gold transformations.

In the pipeline Activities pane, search for Dataflow and select it to add it to the pipeline canvas.
Select the new Dataflow activity on the canvas.
In the properties pane below the canvas, select the Settings tab.
From the Dataflow dropdown, select Ingest Financial Transactions (the Dataflow Gen2 you created earlier).

Now add the first Notebook activity for the Bronze-to-Silver transformation.

In the Activities pane, search for Notebook and select it to add a Notebook activity to the canvas.
Select the Notebook activity on the canvas.
In the Settings tab of the properties pane, select Bronze_to_Silver_Transactions from the Notebook dropdown.
Now connect the Dataflow activity to this Notebook activity. Hover over the Dataflow activity until you see a green checkbox icon on the right side of it.
Click and drag from the green checkbox (the On success path) to the Notebook activity. A green arrow connects the two activities.

Add the second Notebook activity for Silver-to-Gold aggregations.

In the Activities pane, search for Notebook again and select it to add a second Notebook activity.
Select the new Notebook activity on the canvas.
In the Settings tab, select Silver_to_Gold_Aggregations from the Notebook dropdown.
Hover over the first Notebook activity (Bronze_to_Silver) and drag from its green On success checkbox to this second Notebook activity.

Your canvas now shows three activities connected in sequence: Dataflow -> Notebook (Bronze to Silver) -> Notebook (Silver to Gold), each linked by green success arrows.

What do the green arrows mean?

The green arrows are success dependency paths. They mean the downstream activity only starts after the upstream activity completes successfully. If the Dataflow ingestion fails, neither notebook runs.

This prevents your Silver transformation from running on stale Bronze data, and prevents Gold aggregations from running on an incomplete Silver table.

Don't see your notebooks in the dropdown?

Make sure you are selecting the correct workspace in the Settings tab. The notebook must exist in the same workspace as the pipeline, or you must select the correct workspace first.

If the list appears empty, try clicking the refresh icon next to the dropdown to reload available items.

Help me troubleshoot why my notebooks aren't appearing in the pipeline Notebook dropdown.

Run the pipeline end-to-end

With all three activities connected, you can now execute the entire Medallion flow with a single click. The pipeline runs each activity in sequence and reports success or failure for each.

Select the Home tab at the top of the pipeline editor.
Select Run.
A confirmation dialog appears. Select Save and run.
Monitor the execution in the Output tab below the canvas. You will see each activity's status update as it progresses.
Wait until all three activities show Succeeded status. Note the total run duration.

What should I expect during the run?

The Dataflow activity runs first and re-ingests your CSV into the Bronze table. Then the first Notebook activity starts a Spark session and runs your Bronze-to-Silver cleansing code. Finally, the second Notebook runs the Gold aggregations.

The Spark session initialization for the first notebook may take 5 to 10 seconds. The full pipeline should complete within a few minutes for your small dataset.

Seeing a failed activity?

Select the failed activity name in the Output tab to view error details. Common causes include an ABFS path that changed, or a Lakehouse that wasn't attached to the notebook correctly.

If the Notebook activity fails, open the notebook directly and run it manually to see which cell produced the error. Fix the issue in the notebook, then re-run the pipeline.

Help me debug why my pipeline activity is failing.

You now have a complete, orchestrated data engineering pipeline. This is the exact pattern production Fabric teams use daily. With a single click (or a schedule), your entire Medallion flow runs end-to-end: ingest, cleanse, aggregate. Next up, try the Secret Mission to make this pipeline production-grade with incremental loading.

Secret mission

Implement Incremental Load with Delta MERGE

A production pipeline does not reload all data every day. It processes only new and changed records. In this secret mission, you will modify your Bronze-to-Silver notebook to use the Delta Lake MERGE pattern. This is the technique that separates junior data engineering work from production-grade pipelines.

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 your organization's existing Fabric capacity in the Dev workspace, so there are no ongoing costs from the items you created.

Resources you used:

Data Pipeline: Financial_Transactions_Pipeline
Dataflow Gen2: Ingest Financial Transactions
Notebook: Bronze_to_Silver_Transactions
Notebook: Silver_to_Gold_Aggregations
Delta table in Bronze Lakehouse: raw_transactions
Delta table in Silver Lakehouse: silver_transactions
Delta tables in Gold Lakehouse: gold_daily_transaction_summary, gold_account_balance_snapshot, gold_merchant_spending
Uploaded file: Files/raw_transactions/financial_transactions.csv in Bronze Lakehouse.

Keep everything running

No action needed. Choose this if you plan to continue building on this pipeline, show it to your team lead, or use it as a reference for future projects.

All items remain in your Dev workspace and consume no additional cost beyond your existing Fabric capacity.
The pipeline is not scheduled, so it will not run automatically.
You may notice DataflowsStagingLakehouse and DataflowsStagingWarehouse items in your workspace. These were auto-created when you made your first Dataflow Gen2. Do not delete them.

Pause - I'll come back to this later

There are no running processes to stop. The pipeline only executes when you manually trigger it, so nothing is actively consuming compute right now.

Your notebooks, pipeline, dataflow, and Delta tables will remain in the Dev workspace until you return.
No action is required to pause.

Delete - I don't want to use this again

Remove all project items from your Dev workspace. The Lakehouses themselves were provisioned before this project, so you will only delete the items and tables you created inside them.

Delete workspace items (pipeline, dataflow, notebooks):

Return to your Dev workspace in the Fabric portal.
Select the vertical ellipsis next to Financial_Transactions_Pipeline and select Delete. Confirm by selecting Delete.
Repeat for Ingest Financial Transactions (Dataflow Gen2).
Repeat for Bronze_to_Silver_Transactions (Notebook).
Repeat for Silver_to_Gold_Aggregations (Notebook).

Drop Delta tables from your Lakehouses:

Open your Bronze Lakehouse and select the raw_transactions table in the Tables section.
Select the vertical ellipsis next to the table name and select Delete.
Open your Silver Lakehouse and delete the silver_transactions table using the same method.
Open your Gold Lakehouse and delete gold_daily_transaction_summary, gold_account_balance_snapshot, and gold_merchant_spending using the same method.

Delete the uploaded CSV file:

In the Bronze Lakehouse, expand the Files section.
Select the vertical ellipsis next to the raw_transactions folder and select Delete.

Do not delete DataflowsStaging items

Your workspace may contain items named DataflowsStagingLakehouse and DataflowsStagingWarehouse. These are shared infrastructure items created automatically by Fabric when the first Dataflow Gen2 runs in a workspace. They are required for all dataflows to operate. Do not delete them.

Do not delete the Lakehouses themselves

Your Bronze, Silver, and Gold Lakehouses were provisioned before this project and may be shared with other team members. Only delete the tables and files you created inside them.

Nice Work!

You just built a production-style data engineering pipeline using the Medallion Architecture pattern in Microsoft Fabric. This is the exact pattern your DE team uses daily, and you now have a working end-to-end solution to prove you can do the work.

Here's what you accomplished:

Built a Dataflow Gen2 that ingests raw CSV data into a Bronze Lakehouse, leveraging your existing Power Query skills in a data engineering context.
Wrote PySpark transformations in a Fabric Notebook to cleanse and standardize data into Silver Delta tables with deduplication, null handling, type casting, and date standardization.
Created business-ready Gold aggregation tables with Spark SQL and orchestrated the full pipeline with a Data Pipeline that chains ingestion and transformation activities end-to-end.
Implemented incremental loading with Delta MERGE, the production-grade pattern that processes only new and changed records without reprocessing the entire dataset.

Ready to quiz yourself?