Build a Healthcare Analytics Lakehouse

Introduction

30 Second Summary

Healthcare clinics generate thousands of patient records every day. Turning that raw data into insights that improve patient care requires a well-structured analytics pipeline.

In this project, you will build a complete healthcare analytics lakehouse in Microsoft Fabric. You will ingest raw patient visit data, transform it into a clean star schema using PySpark, and create a Power BI report that surfaces trends across multiple clinics.

What You'll Build

A full data engineering pipeline that takes raw CSV patient data through ingestion, transformation, and reporting inside a Microsoft Fabric Lakehouse.

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

• A Microsoft Fabric workspace with a Lakehouse that stores raw healthcare data ingested via a Fabric pipeline.
• A PySpark notebook that transforms raw data into a star schema with 1 fact table and 3 dimension tables.
• A Power BI report connected via a semantic model showing patient visit trends across clinics.
• Secret Mission: Add pipeline orchestration with daily scheduling and monitoring.

Are there any prerequisites?

None. This guide walks you through setting up everything from scratch, including your Azure account and Fabric trial.

The project is completely free. You will need a credit or debit card for Azure identity verification, but you will not be charged.

Set Up Your Azure Account and Fabric Trial

Before you can build anything in Microsoft Fabric, you need access to the platform. Fabric is a cloud service hosted on Azure, so your first task is creating the accounts that unlock it.

In this step, you will create a free Azure account, set up a dedicated user in Microsoft Entra ID, and activate your 60-day Fabric trial. Everything is free and your card will not be charged.

In this step, get ready to:

• Create a free Azure account with your personal email.
• Create a user in Microsoft Entra ID for signing into Fabric.
• Activate the Microsoft Fabric 60-day free trial.

Create a free Azure account

Azure is Microsoft's cloud platform. A free account gives you access to the Azure portal, where you will create the identity that Fabric needs.

• Open this link in your browser: https://azure.microsoft.com/en-us/pricing/purchase-options/azure-account.
• Select Try Azure for free.
• Sign up using your personal email address (Gmail, Outlook, or Yahoo all work).
• Complete identity verification with your phone number when prompted.
• Provide a credit or debit card for verification.

Will I be charged?

No. Azure requires a card for identity verification only. You will not be charged unless you manually upgrade to a paid plan later. You also receive a $200 credit valid for 30 days.

• Once signup is complete, navigate to https://portal.azure.com and confirm you can see the Azure portal dashboard.

Can't complete signup?

Make sure your card is not a prepaid gift card. Azure requires a standard credit or debit card. If your phone number is not being accepted for verification, try using a different number that has not been linked to another Azure account.

Help me troubleshoot my Azure account creation

Create a user in Microsoft Entra ID

Microsoft Fabric requires an organizational identity to sign in. Your personal email alone is not enough. You need to create a dedicated user inside your Azure tenant's directory, called Microsoft Entra ID (formerly Azure Active Directory).

• In the Azure portal, click the search bar at the top of the page.
• Type Microsoft Entra ID and select it from the results.
• In the left menu, expand Manage and select Users.
• Select New user at the top of the Users page.
• Select Create new user.
• Enter Fabric Admin as the Display name.
• Enter fabricadmin as the User principal name.
• Set a password you will remember. Write it down somewhere safe.
• Select Create.

What is a User Principal Name?

The User Principal Name (UPN) is the login identity for your new user. It looks like an email address: fabricadmin@yourdomain.onmicrosoft.com. The domain portion is automatically assigned by Azure based on your tenant name.

• After the user is created, go back to the Users list.
• Find your new user and copy the full User principal name value (the full address ending in @yourdomain.onmicrosoft.com).
• Save this UPN somewhere you can access easily. You will use it in the next substep to sign into Fabric.

Don't see the new user in the list?

Click Refresh at the top of the Users list. If you still do not see the user, make sure you selected Create new user (not Invite external user) in the earlier step.

Help me find my Entra ID user

Sign in to Microsoft Fabric and activate the trial

Now that you have an organizational identity, you can sign into Fabric and activate the free trial. The trial gives you 64 capacity units for 60 days, which is more than enough compute power to complete this project.

• Open a new browser tab and navigate to https://app.fabric.microsoft.com.
• Sign in using the full UPN you copied (for example fabricadmin@yourdomain.onmicrosoft.com) and the password you set.

Why not use my personal email?

Fabric requires an organizational (work or school) account. Personal emails like Gmail or Outlook.com cannot activate a Fabric trial directly. The Entra ID user you just created acts as your organizational identity.

• Complete any MFA (multi-factor authentication) prompts if they appear. You may need the Microsoft Authenticator app on your phone.
• Once signed in, click your profile icon in the upper-right corner of the page.
• Select Free trial.
• Choose a capacity region close to your physical location.
• Select Activate.
• Wait for the confirmation message. You now have 60 days of Fabric access with 64 capacity units.

Trial activation not appearing?

If you do not see the Free trial option, try waiting 24 to 48 hours after creating your Azure account and Entra ID user. Microsoft may temporarily restrict trials for very new tenants. If the issue persists after waiting, contact Microsoft support through the Azure portal.

Help me activate my Fabric trial

What do I get with the trial?

The Fabric trial includes 64 capacity units (F64), up to 1 TB of OneLake storage, and access to all Fabric workloads including Data Engineering, Data Factory, and Power BI. A few features like Copilot and Private Link are not available during the trial, but you will not need them for this project.

Your Azure account, Entra ID user, and Fabric trial are all set. Next up, you will create your workspace and lakehouse where your healthcare data will live.

Create Your Workspace and Lakehouse

Your Azure account and Fabric trial are live. Now it is time to build the foundation for your analytics project.

Every data engineering project needs a home for its data. In Microsoft Fabric, that home is a Lakehouse. A lakehouse combines the flexibility of a data lake (store any file type) with the structure of a data warehouse (query with SQL). In this step, you will create a workspace, spin up a lakehouse, and load in the raw healthcare data you will transform later.

In this step, get ready to:

• Create a Fabric workspace to organize all your project items.
• Create a lakehouse to store and query your healthcare data.
• Upload raw CSV files into the lakehouse.

Create a Fabric workspace

A workspace is like a project folder in Fabric. It groups together all related items: lakehouses, notebooks, pipelines, and reports.

• Open https://app.fabric.microsoft.com and confirm you are signed in with your Entra ID user.
• In the left navigation bar, select Workspaces.
• Select + New workspace.
• Enter HealthcareAnalytics as the workspace name.
• Expand the Advanced section.
• Under License mode, select Trial to use your free trial capacity.
• Select Apply.

Your new workspace opens. You should see an empty workspace page with the name HealthcareAnalytics at the top.

Why a dedicated workspace?

Workspaces keep related items together and let you control who has access. In a real organization, you would have separate workspaces for different teams or environments (dev, test, production).

Don't see the Trial license option?

Make sure you activated the Fabric trial in Step 1. If you skipped that step, go to your profile icon in the upper-right corner and select Free trial to activate it.

If you still do not see it, confirm you are signed in with the Entra ID user (the @yourdomain.onmicrosoft.com account), not your personal email.

Help me troubleshoot why I can't see the Trial license mode in Microsoft Fabric.

Create a lakehouse

The lakehouse is where your raw files and structured tables will live. Fabric automatically gives each lakehouse a SQL analytics endpoint and a semantic model for reporting.

• Inside your HealthcareAnalytics workspace, select + New item.
• Search for and select Lakehouse.
• Enter clinic_lakehouse as the name.
• Keep Lakehouse schemas checked. This allows organizing tables into logical groups.
• Select Create.

The lakehouse opens in the Lakehouse Explorer pane. You should see two top-level sections: Tables and Files.

What is the Lakehouse Explorer?

The Lakehouse Explorer is the left-side panel that shows all the files and tables in your lakehouse. Tables holds structured Delta Lake tables you can query with SQL or PySpark. Files holds raw files in any format (CSVs, images, JSON).

Everything is stored in OneLake, Fabric's unified storage layer. Think of OneLake as a single storage pool that all Fabric items share.

Seeing a naming error?

Lakehouse names can only contain alphanumeric characters and underscores. Make sure you typed clinic_lakehouse with an underscore, not a hyphen or space.

Help me fix this lakehouse naming error in Microsoft Fabric.

Upload raw data files

Your lakehouse needs data to work with. You will create three CSV files on your local machine that represent patient visits across a multi-clinic healthcare network, then upload them into the lakehouse.

• On your Windows machine, open File Explorer and create a new folder on your Desktop called clinic_data.

You will create three CSV files inside this folder. Open Notepad (press the Windows key, type Notepad, and press Enter) for each file below.

• Create the first file by pasting the following content into Notepad and saving it as patient_visits.csv inside your clinic_data folder:

visit_id,visit_date,patient_id,clinic_id,visit_type,duration_minutes,charge_amount
V001,2024-01-15,P001,C001,Dialysis,180,1200.00
V002,2024-01-15,P002,C001,Dialysis,200,1350.00
V003,2024-01-16,P003,C002,Consultation,45,250.00
V004,2024-01-17,P001,C001,Dialysis,190,1280.00
V005,2024-01-18,P004,C003,Dialysis,175,1150.00
V006,2024-01-20,P005,C002,Lab Work,30,180.00
V007,2024-01-22,P002,C001,Dialysis,195,1300.00
V008,2024-01-25,P006,C003,Consultation,50,275.00
V009,2024-02-01,P003,C002,Dialysis,185,1220.00
V010,2024-02-03,P007,C001,Dialysis,200,1350.00
V011,2024-02-05,P004,C003,Dialysis,180,1200.00
V012,2024-02-08,P008,C002,Lab Work,25,160.00
V013,2024-02-10,P001,C001,Dialysis,190,1280.00
V014,2024-02-14,P005,C002,Dialysis,185,1220.00
V015,2024-02-18,P009,C003,Consultation,55,300.00
V016,2024-02-20,P002,C001,Dialysis,195,1300.00
V017,2024-03-01,P010,C001,Dialysis,180,1200.00
V018,2024-03-05,P003,C002,Dialysis,190,1280.00
V019,2024-03-08,P006,C003,Lab Work,35,200.00
V020,2024-03-10,P004,C003,Dialysis,175,1150.00
V021,2024-03-15,P007,C001,Dialysis,200,1350.00
V022,2024-03-18,P008,C002,Consultation,40,225.00
V023,2024-03-22,P001,C001,Dialysis,185,1220.00
V024,2024-03-25,P005,C002,Dialysis,190,1280.00
V025,2024-04-01,P009,C003,Dialysis,180,1200.00
V026,2024-04-05,P010,C001,Dialysis,195,1300.00
V027,2024-04-08,P002,C001,Consultation,45,250.00
V028,2024-04-12,P003,C002,Dialysis,185,1220.00
V029,2024-04-15,P006,C003,Dialysis,175,1150.00
V030,2024-04-20,P004,C003,Lab Work,30,180.00

What is this data?

This file contains 30 patient visit records across three clinics from January to April 2024. Each row represents one visit with the visit type (Dialysis, Consultation, or Lab Work), duration, and charge amount.

This simulates the kind of multi-tenant healthcare data you would see in a real dialysis analytics platform.

• Create the second file by pasting the following content into a new Notepad window and saving it as clinics.csv in the same clinic_data folder:

clinic_id,clinic_name,city,state,tenant_id
C001,Sunrise Dialysis Center,Houston,TX,T001
C002,Lakewood Renal Care,Chicago,IL,T002
C003,Pacific Kidney Institute,San Diego,CA,T003

What is the tenant_id column?

The tenant_id column represents the multi-tenant pattern. Each clinic belongs to a separate tenant (organization). In a production system, this column controls which users can see which data.

• Create the third file by pasting the following content into a new Notepad window and saving it as patients.csv in the same clinic_data folder:

patient_id,patient_name,age_group,insurance_type,primary_clinic_id
P001,John Smith,55-64,Medicare,C001
P002,Maria Garcia,45-54,Private,C001
P003,Robert Johnson,65+,Medicare,C002
P004,Linda Williams,55-64,Medicaid,C003
P005,David Brown,45-54,Medicare,C002
P006,Sarah Davis,35-44,Private,C003
P007,Michael Wilson,65+,Medicare,C001
P008,Jennifer Taylor,55-64,Private,C002
P009,James Anderson,45-54,Medicaid,C003
P010,Patricia Thomas,65+,Medicare,C001

Save as CSV, not TXT

When saving in Notepad, change the Save as type dropdown from Text Documents (*.txt) to All Files (*.*). Then type the full filename including the .csv extension. This prevents Notepad from adding a hidden .txt extension.

• Verify your clinic_data folder contains all three files: patient_visits.csv, clinics.csv, and patients.csv.

Now upload these files into your lakehouse.

• Go back to your browser where clinic_lakehouse is open in the Lakehouse Explorer.
• In the Lakehouse Explorer, right-click on Files and select New subfolder.
• Name the subfolder raw and press Enter.
• Click the raw folder to open it.
• Select Upload in the top toolbar, then select Upload files.
• In the file picker, navigate to your Desktop clinic_data folder and select all three CSV files.
• Select Upload to confirm.

After the upload completes, you should see all three files listed under Files > raw in the Lakehouse Explorer.

Files not appearing after upload?

Click the three-dot menu (ellipsis) next to Files in the Lakehouse Explorer and select Refresh. The Explorer does not always auto-refresh after uploads.

If you see files ending in .csv.txt, Notepad added a hidden extension. Delete them, re-save in Notepad with All Files (*.*) selected, and upload again.

Help me troubleshoot why my CSV files are not appearing in the Fabric Lakehouse Explorer.

Your workspace and lakehouse are set up with raw data ready to go. Next, you will build a Fabric pipeline to ingest this CSV data into a proper Delta table.

Ingest Data with a Fabric Pipeline

Your lakehouse is ready with raw CSV files sitting in the Files/raw folder. Right now those files are just flat text. To make them queryable and usable for analytics, you need to load them into structured Delta tables.

A Fabric pipeline automates this ingestion process. Instead of writing code to move data manually, you configure a visual pipeline with a Copy data activity that reads from a source and writes to a destination. This is the exact pattern used in production lakehouse architectures.

In this step, get ready to:

• Create a Fabric pipeline in your workspace.
• Configure a Copy data activity to load CSV data into a Delta table.
• Verify the data landed in your lakehouse Tables section.

Create a new pipeline

Pipelines live at the workspace level, not inside a lakehouse. You need to navigate back to your workspace to create one.

• Select HealthcareAnalytics in the left navigation breadcrumb to return to your workspace.
• Select + New item in the workspace toolbar.
• Search for Pipeline in the search box and select it.
• Name the pipeline ingest_clinic_data.
• Select Create.

The pipeline editor opens with an empty canvas. This is where you will add activities that define your data movement logic.

What is a Fabric pipeline?

A pipeline is a visual workflow that orchestrates data movement and transformation. Each pipeline contains one or more activities (Copy data, Notebook, Stored procedure, etc.) that execute in sequence or in parallel.

The Copy data activity is the most common starting point. It reads from a source, applies optional transformations, and writes to a destination. Think of it as a configurable data pump.

Add a Copy data activity to load CSV into a Delta table

The Copy data assistant walks you through configuring a source and destination step by step. You will point it at your patient_visits.csv file and tell it to write a new Delta Lake table.

• On the pipeline canvas, select Copy data assistant from the center of the canvas or the toolbar.
• For the data source, select the OneLake tab.
• Select your clinic_lakehouse from the list.
• Navigate to Files > raw and select patient_visits.csv.
• Select Next.

Now configure the destination where the data will be written.

• For the destination, select Lakehouse and connect to clinic_lakehouse.
• Select Tables for the Root folder.
• Select Load to new table and enter raw_patient_visits as the table name.
• Select Next.
• Select Save + Run to create and immediately execute the pipeline.

The pipeline starts executing. You can monitor progress in the Output tab at the bottom of the pipeline editor. Wait until the status shows Succeeded.

Why load to Tables instead of Files?

The Files section stores raw, unmanaged data (CSVs, Parquet, JSON). The Tables section stores managed Delta Lake tables with schema enforcement, ACID transactions, and time travel. Loading into Tables converts your CSV into a Delta table that you can query with SQL and connect to Power BI.

Pipeline stuck or showing failed?

If the pipeline takes more than 5 minutes or shows a failure status, check the Output tab and select the run to see error details. The most common issue is selecting the wrong file path in the source configuration.

If you see a permissions error, confirm you are signed in with your Entra ID user (the @yourdomain.onmicrosoft.com account), not your personal email.

Need help? Help me troubleshoot my Fabric pipeline failure

Verify data landed in the lakehouse

A successful pipeline run does not always mean the data looks right. You should always verify that rows actually loaded into the destination table.

• Navigate back to clinic_lakehouse by selecting it from your HealthcareAnalytics workspace.
• In the Lakehouse Explorer pane on the left, expand Tables.
• You should see raw_patient_visits listed as a table.
• Select raw_patient_visits to open the data preview.
• Confirm you see 30 rows of patient visit data with columns like visit_id, visit_date, clinic_id, and charge_amount.

Don't see the table listed?

If raw_patient_visits does not appear under Tables, click the ellipsis (...) next to Tables and select Refresh. Fabric sometimes takes a moment to register newly created tables in the Explorer.

If it still does not appear, go back to your pipeline and confirm the Output tab shows a Succeeded status. A failed pipeline will not produce a table.

Still stuck? Help me find my raw_patient_visits table in the lakehouse

Your raw patient visit data is now stored as a structured Delta table. Next up, you will write PySpark code to transform this raw data into a clean star schema with dimension tables and a fact table.

Transform Data with PySpark

Your pipeline successfully moved raw CSV data into a Delta Lake table. Now you have raw data sitting in the lakehouse, but it is not structured for analytics.

In this step, you will write PySpark code in a Fabric notebook to transform your raw CSVs into a star schema. A star schema organizes data into a central fact table surrounded by dimension tables, making analytics queries fast and intuitive.

In this step, get ready to:

• Create a PySpark notebook attached to your lakehouse.
• Build three dimension tables (clinic, patient, date) from your raw CSV files.
• Create a fact table with foreign keys and verify the complete star schema.

Create a new notebook and attach the lakehouse

Fabric notebooks let you write and run PySpark code directly against your lakehouse data. The notebook you create here will hold all your transformation logic.

• Navigate to your clinic_lakehouse in the HealthcareAnalytics workspace.
• Select Open notebook from the top menu.
• Select New notebook.
• Click the notebook name at the top of the page and rename it to transform_star_schema.
• Verify that clinic_lakehouse appears in the Lakehouse Explorer pane on the left. It should be attached automatically since you opened the notebook from inside the lakehouse.

Don't see the lakehouse in the Explorer pane?

If the Lakehouse Explorer is empty, select Add in the Explorer pane, choose Existing lakehouse, and select clinic_lakehouse from your workspace.

help me attach my lakehouse to this notebook

Load raw data and create dimension tables

Dimension tables hold the descriptive attributes your analysts will filter and group by. You will create three: one for clinics, one for patients, and one for dates.

What is a star schema?

A star schema places measurable events (visits, transactions, orders) in a central fact table. Descriptive context (who, where, when) lives in surrounding dimension tables.

When drawn on a diagram, the fact table in the center with dimensions radiating outward looks like a star. This layout lets Power BI slice facts by any dimension without complex joins.

• In the first code cell of your notebook, paste the following configuration code:

# Configuration - Enable VORDER compression and optimize writes
spark.conf.set("spark.sql.parquet.vorder.enabled", "true")
spark.conf.set("spark.sql.adaptive.enabled", "true")

print("Spark configuration set: VORDER enabled, adaptive execution enabled")

What does this code do?

VORDER is a compression algorithm built into Microsoft Fabric. It organizes data in a way that Power BI can load into memory faster.

Adaptive execution lets Spark automatically tune query plans at runtime. Together, these settings ensure every Delta table you write is optimized for reporting.

• Run the cell by pressing Shift+Enter or clicking the play button to the left of the cell.
• You should see the output: Spark configuration set: VORDER enabled, adaptive execution enabled.

• Click + Code below the first cell to add a new code cell. Paste the following code to create the clinic dimension table:

# Create dim_clinic from clinics.csv
df_clinics = spark.read.format("csv").option("header", "true").load("Files/raw/clinics.csv")

# Select only dimensional attributes (drop tenant_id for the dimension table)
dim_clinic = df_clinics.select("clinic_id", "clinic_name", "city", "state")

dim_clinic.write.mode("overwrite").format("delta").saveAsTable("dim_clinic")

print("dim_clinic created successfully")
dim_clinic.show()

What does this code do?

• spark.read.format("csv") reads the raw CSV file from the Files/raw folder in your lakehouse.
• .select() picks only the columns relevant to this dimension. The tenant_id column is dropped because it is an operational field, not a reporting attribute.
• .write.mode("overwrite").format("delta").saveAsTable() writes the data as a Delta table in the lakehouse Tables section.

• Run the cell with Shift+Enter.
• You should see dim_clinic created successfully followed by a table showing 3 rows (Sunrise Dialysis Center, Lakewood Renal Care, Pacific Kidney Institute).

Seeing a FileNotFoundException?

Make sure the path Files/raw/clinics.csv matches exactly. The path is case-sensitive. Open the Lakehouse Explorer on the left and expand Files > raw to confirm your CSV file names.

help me fix this file not found error in my Fabric notebook

• Add another new code cell and paste the following code to create the patient dimension table:

# Create dim_patient from patients.csv
df_patients = spark.read.format("csv").option("header", "true").load("Files/raw/patients.csv")

# Select dimensional attributes
dim_patient = df_patients.select("patient_id", "patient_name", "age_group", "insurance_type")

dim_patient.write.mode("overwrite").format("delta").saveAsTable("dim_patient")

print("dim_patient created successfully")
dim_patient.show()

What does this code do?

This follows the same pattern as dim_clinic. It reads patients.csv, selects the reporting-relevant columns (dropping primary_clinic_id since that relationship lives in the fact table), and saves it as a Delta table.

• Run the cell with Shift+Enter.
• You should see dim_patient created successfully followed by a table showing 10 patient rows.

• Add a new code cell and paste the following code to create the date dimension table:

# Create dim_date from the date range in visits
from pyspark.sql.functions import col, to_date, year, quarter, month, dayofweek, date_format, explode, sequence, lit

# Read visits to get the date range
df_visits = spark.read.format("csv").option("header", "true").load("Files/raw/patient_visits.csv")
df_visits = df_visits.withColumn("visit_date", to_date(col("visit_date"), "yyyy-MM-dd"))

# Get min and max dates
min_date = df_visits.agg({"visit_date": "min"}).collect()[0][0]
max_date = df_visits.agg({"visit_date": "max"}).collect()[0][0]

# Generate a date range
date_range = spark.sql(f"SELECT explode(sequence(to_date('{min_date}'), to_date('{max_date}'), interval 1 day)) as full_date")

# Build the dimension
dim_date = date_range.select(
    date_format(col("full_date"), "yyyyMMdd").cast("int").alias("date_key"),
    col("full_date"),
    year(col("full_date")).alias("year"),
    quarter(col("full_date")).alias("quarter"),
    month(col("full_date")).alias("month"),
    date_format(col("full_date"), "MMMM").alias("month_name"),
    dayofweek(col("full_date")).alias("day_of_week")
)

dim_date.write.mode("overwrite").format("delta").saveAsTable("dim_date")

print("dim_date created successfully")
dim_date.show(5)

What does this code do?

• It reads the visit dates from patient_visits.csv to find the earliest and latest dates in your data.
• It generates one row for every calendar day between those dates using Spark SQL's sequence() function.
• Each row gets a date_key (integer in yyyyMMdd format) plus extracted year, quarter, month, month name, and day of week. This lets Power BI slice by any time grain without recalculating.

• Run the cell with Shift+Enter.
• You should see dim_date created successfully followed by a preview of 5 rows showing date_key, full_date, year, quarter, month, month_name, and day_of_week columns.

Seeing an AnalysisException on the sequence function?

Make sure you included the import line at the top of this cell: from pyspark.sql.functions import col, to_date, year, quarter, month, dayofweek, date_format, explode, sequence, lit. Without the import, Spark does not recognize these functions.

help me debug this AnalysisException in my dim_date cell

Create the fact table with foreign keys

The fact table holds the measurable events: each patient visit with its duration, charge amount, and foreign keys linking back to the dimension tables you just created.

• Add a new code cell and paste the following code to create the fact table:

# Create fact_visits with foreign keys
from pyspark.sql.functions import col, to_date, date_format

# Re-read visits data with proper types
df_visits = spark.read.format("csv").option("header", "true").load("Files/raw/patient_visits.csv")

# Transform: add date_key as integer (yyyyMMdd format) and cast numeric columns
fact_visits = df_visits.select(
    col("visit_id"),
    date_format(to_date(col("visit_date"), "yyyy-MM-dd"), "yyyyMMdd").cast("int").alias("date_key"),
    col("patient_id"),
    col("clinic_id"),
    col("visit_type"),
    col("duration_minutes").cast("int"),
    col("charge_amount").cast("double"),
    year(to_date(col("visit_date"), "yyyy-MM-dd")).alias("year")
)

# Write as Delta table partitioned by year
fact_visits.write.mode("overwrite").format("delta").partitionBy("year").saveAsTable("fact_visits")

print("fact_visits created successfully")
print(f"Total rows: {fact_visits.count()}")
fact_visits.show(5)

What does this code do?

• date_format(...).cast("int").alias("date_key") converts each visit date into an integer key (like 20240115) that matches the date_key in your dim_date table. This is the foreign key relationship.
• .cast("int") and .cast("double") convert string values from the CSV into proper numeric types for aggregation.
• .partitionBy("year") splits the Delta table into folders by year. When a query filters by year, Spark only reads the relevant partition instead of scanning all data.

• Run the cell with Shift+Enter.
• You should see fact_visits created successfully and Total rows: 30 followed by a preview of 5 rows.

Seeing Total rows: 0?

If the row count is 0, the CSV read likely failed silently. Check that the path Files/raw/patient_visits.csv is correct and that the file contains data. Expand Files > raw in the Lakehouse Explorer to confirm the file is there.

help me figure out why my fact_visits table has zero rows

• Add a final code cell and paste the following verification code:

# Verify star schema - show all tables and row counts
tables = spark.sql("SHOW TABLES")
tables.show()

print("\nRow counts:")
print(f"  fact_visits: {spark.table('fact_visits').count()}")
print(f"  dim_clinic: {spark.table('dim_clinic').count()}")
print(f"  dim_patient: {spark.table('dim_patient').count()}")
print(f"  dim_date: {spark.table('dim_date').count()}")

# Quick validation query: visits per clinic
print("\nVisits per clinic:")
spark.sql("""
    SELECT c.clinic_name, COUNT(*) as visit_count, SUM(f.charge_amount) as total_revenue
    FROM fact_visits f
    JOIN dim_clinic c ON f.clinic_id = c.clinic_id
    GROUP BY c.clinic_name
    ORDER BY visit_count DESC
""").show()

What does this verification do?

This cell confirms your star schema is complete. It lists all tables in the lakehouse, prints row counts for each, and runs a sample analytics query joining fact_visits to dim_clinic.

If the join returns results, your foreign keys are working correctly.

• Run the cell with Shift+Enter.
• You should see a tables list, row counts (fact_visits: 30, dim_clinic: 3, dim_patient: 10, dim_date: 97), and a visits-per-clinic breakdown showing Sunrise Dialysis Center with the most visits.

• In the Lakehouse Explorer on the left, click the ellipsis (...) next to Tables and select Refresh.
• You should now see all four new tables listed: dim_clinic, dim_date, dim_patient, and fact_visits.

Tables not appearing in the Explorer?

If you do not see the tables after refreshing, close the notebook tab and navigate back to clinic_lakehouse from the workspace. The Lakehouse Explorer should show all tables once you reopen it.

help me see my tables in the Lakehouse Explorer

✔️ Awesome, I've got everything!

Great. Double check you see all four tables in the Lakehouse Explorer and that your verification cell shows 30 fact rows joining correctly to the clinic dimension.

ⓧ I'd like to double check the full code

Here is the complete notebook code across all six cells. Each separator comment marks where a new cell begins.

# Cell 1: Configuration - Enable VORDER compression and optimize writes
spark.conf.set("spark.sql.parquet.vorder.enabled", "true")
spark.conf.set("spark.sql.adaptive.enabled", "true")

print("Spark configuration set: VORDER enabled, adaptive execution enabled")

# Cell 2: Create dim_clinic from clinics.csv
df_clinics = spark.read.format("csv").option("header", "true").load("Files/raw/clinics.csv")

# Select only dimensional attributes (drop tenant_id for the dimension table)
dim_clinic = df_clinics.select("clinic_id", "clinic_name", "city", "state")

dim_clinic.write.mode("overwrite").format("delta").saveAsTable("dim_clinic")

print("dim_clinic created successfully")
dim_clinic.show()

# Cell 3: Create dim_patient from patients.csv
df_patients = spark.read.format("csv").option("header", "true").load("Files/raw/patients.csv")

# Select dimensional attributes
dim_patient = df_patients.select("patient_id", "patient_name", "age_group", "insurance_type")

dim_patient.write.mode("overwrite").format("delta").saveAsTable("dim_patient")

print("dim_patient created successfully")
dim_patient.show()

# Cell 4: Create dim_date from the date range in visits
from pyspark.sql.functions import col, to_date, year, quarter, month, dayofweek, date_format, explode, sequence, lit

# Read visits to get the date range
df_visits = spark.read.format("csv").option("header", "true").load("Files/raw/patient_visits.csv")
df_visits = df_visits.withColumn("visit_date", to_date(col("visit_date"), "yyyy-MM-dd"))

# Get min and max dates
min_date = df_visits.agg({"visit_date": "min"}).collect()[0][0]
max_date = df_visits.agg({"visit_date": "max"}).collect()[0][0]

# Generate a date range
date_range = spark.sql(f"SELECT explode(sequence(to_date('{min_date}'), to_date('{max_date}'), interval 1 day)) as full_date")

# Build the dimension
dim_date = date_range.select(
    date_format(col("full_date"), "yyyyMMdd").cast("int").alias("date_key"),
    col("full_date"),
    year(col("full_date")).alias("year"),
    quarter(col("full_date")).alias("quarter"),
    month(col("full_date")).alias("month"),
    date_format(col("full_date"), "MMMM").alias("month_name"),
    dayofweek(col("full_date")).alias("day_of_week")
)

dim_date.write.mode("overwrite").format("delta").saveAsTable("dim_date")

print("dim_date created successfully")
dim_date.show(5)

# Cell 5: Create fact_visits with foreign keys
from pyspark.sql.functions import col, to_date, date_format

# Re-read visits data with proper types
df_visits = spark.read.format("csv").option("header", "true").load("Files/raw/patient_visits.csv")

# Transform: add date_key as integer (yyyyMMdd format) and cast numeric columns
fact_visits = df_visits.select(
    col("visit_id"),
    date_format(to_date(col("visit_date"), "yyyy-MM-dd"), "yyyyMMdd").cast("int").alias("date_key"),
    col("patient_id"),
    col("clinic_id"),
    col("visit_type"),
    col("duration_minutes").cast("int"),
    col("charge_amount").cast("double"),
    year(to_date(col("visit_date"), "yyyy-MM-dd")).alias("year")
)

# Write as Delta table partitioned by year
fact_visits.write.mode("overwrite").format("delta").partitionBy("year").saveAsTable("fact_visits")

print("fact_visits created successfully")
print(f"Total rows: {fact_visits.count()}")
fact_visits.show(5)

# Cell 6: Verify star schema - show all tables and row counts
tables = spark.sql("SHOW TABLES")
tables.show()

print("\nRow counts:")
print(f"  fact_visits: {spark.table('fact_visits').count()}")
print(f"  dim_clinic: {spark.table('dim_clinic').count()}")
print(f"  dim_patient: {spark.table('dim_patient').count()}")
print(f"  dim_date: {spark.table('dim_date').count()}")

# Quick validation query: visits per clinic
print("\nVisits per clinic:")
spark.sql("""
    SELECT c.clinic_name, COUNT(*) as visit_count, SUM(f.charge_amount) as total_revenue
    FROM fact_visits f
    JOIN dim_clinic c ON f.clinic_id = c.clinic_id
    GROUP BY c.clinic_name
    ORDER BY visit_count DESC
""").show()

Your star schema is complete. Four Delta tables are now sitting in the lakehouse, optimized for reporting with VORDER compression and year-based partitioning. Next up, you will connect these tables to Power BI through a semantic model and build your first analytics report.

Build a Semantic Model and Power BI Report

Your star schema tables are ready in the lakehouse. However, raw tables alone don't produce business insights. You need a reporting layer that understands how the tables relate to each other and can aggregate data on the fly.

In this step, you will create a semantic model that maps the relationships between your fact and dimension tables. Then you will build a Power BI report with interactive visualizations that surface patient visit trends across clinics.

In this step, get ready to:

• Create a Direct Lake semantic model from your lakehouse tables.
• Define star schema relationships between fact and dimension tables.
• Build a Power BI report with a bar chart, line chart, and slicer.

Create a semantic model from lakehouse tables

A semantic model is the layer between your raw Delta tables and the Power BI reports that business users interact with. When you create one from a lakehouse, Fabric generates a Direct Lake model that reads Delta tables directly from OneLake without importing or duplicating data.

• Navigate to your clinic_lakehouse in the HealthcareAnalytics workspace.
• Select New semantic model from the top ribbon.
• In the dialog that appears, check all four tables: fact_visits, dim_clinic, dim_patient, and dim_date.
• Select Confirm to generate the semantic model.

The model opens automatically in the web modeling experience. You should see your four tables displayed as boxes on a canvas.

What is a Direct Lake semantic model?

Traditional Power BI models import data into memory, creating a separate copy. Direct Lake skips the import step entirely. It reads your Delta tables straight from OneLake, which means your reports always reflect the latest data without scheduled refreshes.

This is why you enabled VORDER compression in the previous step. VORDER uses Power BI's native compression algorithm, so Direct Lake can load data into memory faster.

Don't see New semantic model in the ribbon?

Make sure you are inside the lakehouse view (not the notebook or workspace view). You need to open clinic_lakehouse directly from the workspace item list.

If the button is grayed out, check that your Tables section shows all four tables. You may need to refresh by clicking the ellipsis (...) next to Tables and selecting Refresh.

help me find the New semantic model button in my lakehouse

Define relationships between tables

For Power BI to aggregate measures correctly (like total revenue per clinic), it needs to know which columns link your tables together. You will create three relationships that connect the fact table to each dimension.

• Select Open data model if you are not already in the model diagram view.
• Drag clinic_id from the fact_visits table onto clinic_id in the dim_clinic table.
• Drag patient_id from fact_visits onto patient_id in dim_patient.
• Drag date_key from fact_visits onto date_key in dim_date.

After each drag, Fabric creates a Many-to-one relationship automatically. You should see three lines connecting fact_visits to each dimension table on the diagram.

Why Many-to-one relationships?

Each visit row in the fact table points to exactly one clinic, one patient, and one date. But each clinic, patient, or date can appear in many visit rows. This Many-to-one pattern is the foundation of star schema design. It tells Power BI how to filter and aggregate correctly when you slice data by clinic, patient, or time period.

Relationship line not appearing?

Make sure you are dragging from the fact table column to the dimension table column (not the other way around). The column names must match exactly on both sides.

If the drag-and-drop does not register, try double-clicking the canvas background and using the Manage relationships dialog to create them manually.

help me create relationships in my Fabric semantic model

Create a Power BI report

With relationships in place, Power BI can now aggregate and slice your healthcare data. You will build three visuals that answer key business questions: which clinics are busiest, how revenue trends over time, and how to filter by geography.

• From the semantic model view, select New report in the top ribbon.
• The Power BI report editor opens with a blank canvas and your four tables listed in the Data pane on the right.

Now add your first visual: a clustered bar chart showing visits per clinic.

• Click an empty area of the canvas to ensure nothing is selected.
• In the Visualizations pane, select the Clustered bar chart icon.
• From the Data pane, expand dim_clinic and drag clinic_name to the Y-axis well.
• Expand fact_visits and drag visit_id to the X-axis well.
• In the X-axis well, click the dropdown on visit_id and select Count as the aggregation.

You should see a horizontal bar chart with three bars. Sunrise Dialysis Center has the most visits.

Next, add a line chart showing revenue trends by month.

• Click an empty area of the canvas (away from the bar chart).
• In the Visualizations pane, select the Line chart icon.
• From dim_date, drag month_name to the X-axis well.
• From fact_visits, drag charge_amount to the Y-axis well.
• Verify the Y-axis aggregation shows Sum of charge_amount (this is usually the default for numeric fields).

The line chart displays total revenue across January through April 2024.

Finally, add a slicer so users can filter the entire report by city.

• Click an empty area of the canvas.
• In the Visualizations pane, select the Slicer icon.
• From dim_clinic, drag city into the slicer's Field well.

You should see a slicer with three city options: Houston, Chicago, and San Diego. Clicking any city filters both charts to show only that city's data.

• Select File in the top-left menu, then select Save.
• Name the report Clinic Analytics Report and select Save.

What does the slicer do?

A slicer is an interactive filter control placed directly on the report canvas. When a user clicks a city in the slicer, Power BI uses the relationships you defined earlier to filter the fact table down to only visits at clinics in that city. Both the bar chart and line chart update instantly because they all share the same underlying semantic model.

Charts showing unexpected values?

If the bar chart shows very large numbers, check that the X-axis aggregation is set to Count (not Sum). Click the dropdown arrow next to visit_id in the X-axis well to change it.

If charts appear empty, make sure the slicer is not filtering out all data. Click Select all in the slicer or clear any active selections.

help me fix my Power BI visuals

Your healthcare analytics lakehouse is now complete from end to end. You have raw data flowing through a pipeline, a star schema built with PySpark, and a live Power BI report connected via Direct Lake. Next up, you can take on the Secret Mission to automate this entire workflow with pipeline orchestration.

Secret mission

Add Pipeline Orchestration with Monitoring

Your lakehouse is built. Your data transforms cleanly. But in a production environment, nobody runs notebooks by hand every morning. Can you automate the entire transformation workflow with a scheduled pipeline and monitoring?

Clean Up Your Resources

Clean Up Your Resources

Decide whether to keep your resources running, pause them to come back later, or delete them entirely. This project runs on a free Azure account and a 60-day Fabric trial, so there are no ongoing costs unless you explicitly upgrade your Azure subscription.

Cost warning

Your Fabric trial lasts 60 days. After that, non-Power BI items (pipelines, notebooks, lakehouses) become inactive. Your Azure free account includes $200 credit for 30 days, but your card will not be charged unless you manually upgrade to a paid plan.

Resources you used:

• Azure free account.
• Microsoft Entra ID user (fabricadmin@yourdomain.onmicrosoft.com).
• Microsoft Fabric trial (60-day, 64 capacity units).
• HealthcareAnalytics workspace.
• clinic_lakehouse (Lakehouse with Delta tables).
• ingest_clinic_data pipeline.
• transform_star_schema notebook.
• Semantic model and Clinic Analytics Report (Power BI).
• Local clinic_data folder with CSV files.

Keep everything running

No action needed. Choose this if you are still actively building or plan to extend the project with the Secret Mission.

• Your Fabric trial remains active for 60 days from activation.
• Your Azure free account stays active with $200 credit for 30 days.
• All workspace items (lakehouse, pipeline, notebook, report) stay as they are.

Pause - I'll come back to this later

Since this project runs on a free trial with no per-minute charges, there is nothing to pause in the traditional sense. Your resources remain idle until you use them again.

• Note the date your Fabric trial expires (60 days from activation). You can check this in your profile settings under the trial banner.
• Note the date your Azure free credit expires (30 days from account creation).
• Bookmark your workspace URL so you can find it quickly when you return.

Delete - I don't want to use this again

Remove all project resources and start fresh. Follow these steps to clean up everything.

• In Microsoft Fabric, open Workspaces from the left navigation.
• Hover over HealthcareAnalytics, select the three-dot menu, and choose Delete workspace. This removes the lakehouse, pipeline, notebook, semantic model, and report.
• To delete the Entra ID user, go to the Azure portal, open Microsoft Entra ID, select Users, find the user you created, and select Delete user.
• To cancel your Azure free account entirely, go to Subscriptions in the Azure portal, select your subscription, and choose Cancel subscription.
• Delete the local clinic_data folder from your Windows machine.

That's a Wrap!

That's a Wrap!

Nice work! You just built a complete healthcare analytics lakehouse from scratch using Microsoft Fabric.

You've learned how to:

• Built a Fabric Lakehouse and ingested raw healthcare data using a Fabric pipeline with the Copy data activity.
• Transformed raw CSVs into a star schema (1 fact table + 3 dimension tables) using PySpark with VORDER compression and Delta Lake partitioning.
• Created a semantic model with star schema relationships and built a Power BI report connected via Direct Lake.
• Secret Mission: Orchestrated the transformation notebook in a scheduled pipeline with monitoring.

Ready to quiz yourself?