Healthcare Claims Medallion Lakehouse
Build a Bronze/Silver/Gold Delta lakehouse from CMS Medicare claims data.
Introduction
30 Second Summary
Healthcare claims data is messy. Millions of records across multiple file types, inconsistent formats, and complex relationships between patients and their medical events make it one of the hardest datasets to work with.
In this project, you will build a three-layer medallion architecture lakehouse in Microsoft Fabric that transforms raw CMS Medicare synthetic claims data into clean, aggregated analytics tables.
What You'll Build
You will process real-world CMS beneficiary and claims data through Bronze, Silver, and Gold layers using Spark SQL in Fabric notebooks.
By the end of this project, you'll have:
- A Fabric Lakehouse with Bronze Delta Lake tables preserving raw CMS Medicare data exactly as received.
- Silver Delta tables with enforced schemas, null handling, type casting, and deduplication applied to beneficiary and claims data.
- Gold analytical tables with patient-level cost and utilization metrics produced from multi-table joins, queryable through the SQL analytics endpoint.
- Secret Mission: Implement SCD Type 2 logic to track how chronic conditions evolve across a 3-year window.
Are there any prerequisites?
You should have SQL experience (SELECT, JOIN, GROUP BY, CASE statements) and an active Microsoft Fabric trial account. The Fabric trial is free with no credit card required.
Download CMS Data and Create Your Fabric Lakehouse
Before you can build any transformations, you need raw data and a place to store it. The CMS DE-SynPUF dataset is a synthetic version of real Medicare claims data, containing beneficiary demographics, inpatient stays, and outpatient visits.
In this step, you will download the CMS data files, set up a Microsoft Fabric workspace, create a Lakehouse, and upload the raw CSVs into an organized folder structure that mirrors the Bronze layer of a medallion architecture.
In this step, get ready to:
- Download three CMS DE-SynPUF Sample 1 data files.
- Create a Fabric workspace and lakehouse.
- Upload the CSV files into an organized Bronze folder structure.
Download the CMS DE-SynPUF data files
The CMS DE-SynPUF is a free, publicly available synthetic Medicare dataset that mirrors real CMS claims data structures. You only need three of the eight files from Sample 1.
- Open the CMS DE-SynPUF Sample 1 download page in your browser:
- https://www.cms.gov/data-research/statistics-trends-and-reports/medicare-claims-synthetic-public-use-files/cms-2008-2010-data-entrepreneurs-synthetic-public-use-file-de-synpuf/de10-sample-1
- Click DE1.0 Sample 1 2008 Beneficiary Summary File (ZIP) to download it.
- Click DE1.0 Sample 1 2008-2010 Inpatient Claims (ZIP) to download it.
- Click DE1.0 Sample 1 2008-2010 Outpatient Claims (ZIP) to download it.
- Extract all three ZIP files to your local machine.
What are these files?
Each file contains a different type of Medicare claims data for the same set of synthetic beneficiaries. The beneficiary file has demographics and chronic condition flags. The inpatient file has hospital admission claims. The outpatient file has non-admission visit claims.
All three share a linking key called DESYNPUF_ID that connects each patient across file types.
After extracting, you should see these CSV files in your downloads folder:
- DE1_0_2008_Beneficiary_Summary_File_Sample_1.csv
- DE1_0_2008_to_2010_Inpatient_Claims_Sample_1.csv
- DE1_0_2008_to_2010_Outpatient_Claims_Sample_1.csv
Don't see CSV files after extracting?
Some ZIP utilities create a subfolder with the same name as the ZIP file. Look inside any subfolders for the .csv file. On macOS, double-clicking a ZIP automatically extracts it into the same folder. On Windows, right-click the ZIP and select Extract All.
help me find my extracted CMS CSV files
Create your Fabric workspace and lakehouse
A workspace in Fabric is a container that holds all your project items. Inside it, a lakehouse stores both raw files and structured Delta tables.
- Sign in to the Microsoft Fabric portal at app.fabric.microsoft.com.
- In the left navigation pane, select Workspaces.
- At the bottom of the Workspaces pane, select + New workspace.
- In the Name field, enter MedicareLakehouse.
- Expand Advanced and confirm the Workspace type is set to Fabric Trial capacity.
- Select Apply to create and open the workspace.
Now create the lakehouse inside your new workspace.
- Select + New item.
- Search for or select Lakehouse.
- Enter claims_lakehouse as the name.
- Select Create.
The lakehouse opens in the Lakehouse explorer pane. You should see two top-level sections: Tables and Files.
What is a lakehouse?
A Fabric lakehouse combines the flexibility of a data lake (store any file format) with the structure of a data warehouse (query with SQL). It stores raw data under Files and managed Delta Lake tables under Tables.
Fabric automatically provisions a SQL analytics endpoint alongside each lakehouse so you can query your Delta tables with T-SQL.
Don't see the Lakehouse option?
Make sure your workspace is backed by a Fabric Trial capacity. If the Lakehouse item does not appear, ask your Fabric admin to assign capacity to the workspace.
help me troubleshoot why I cannot create a lakehouse in Microsoft Fabric
Create the folder structure and upload CSV files
The Bronze layer of a medallion architecture stores raw data exactly as received, organized by source type. You will create subfolders under Files to separate each data source.
- In the Lakehouse explorer, hover over Files and select the triple ellipsis (...) that appears.
- Select New subfolder.
- Enter bronze as the folder name and confirm.
- Hover over the new bronze folder, select the (...) menu, and select New subfolder.
- Enter beneficiary and confirm.
- Repeat to create a second subfolder inside bronze named inpatient.
- Repeat once more to create a third subfolder inside bronze named outpatient.
Your folder structure should now look like this:
- Files/bronze/beneficiary
- Files/bronze/inpatient
- Files/bronze/outpatient
Now upload each CSV into its corresponding folder.
- Click on the beneficiary folder to open it in the main view.
- Hover over Files/bronze/beneficiary in the explorer, select the (...) menu, then select Upload and Upload files.
- Browse to your extracted files and select DE1_0_2008_Beneficiary_Summary_File_Sample_1.csv.
- Click Upload.
- Navigate to the inpatient folder and repeat the upload process for DE1_0_2008_to_2010_Inpatient_Claims_Sample_1.csv.
- Navigate to the outpatient folder and repeat the upload process for DE1_0_2008_to_2010_Outpatient_Claims_Sample_1.csv.
Why upload directly through the Lakehouse explorer?
For small files from a local machine, direct upload through the Lakehouse explorer is the simplest path. It avoids the complexity of setting up Data Factory pipelines while keeping your focus on transformation logic.
After uploading, click on each folder in the explorer to confirm the CSV file appears inside it. You should see one file per folder.
Files not showing up after upload?
Try refreshing the Lakehouse explorer by clicking the refresh icon in the explorer pane toolbar. If the upload failed, check that you are uploading individual .csv files and not the original .zip archives.
help me troubleshoot files not appearing in my Fabric lakehouse after upload
Your data is staged and ready to go. Next up, you will create a Fabric notebook and use Spark SQL to ingest these raw CSVs into Bronze Delta tables.
Ingest Raw Data into Bronze Delta Tables
Your lakehouse has raw CSV files sitting in organized folders. Now you need to convert them into a format the analytics engine can actually query efficiently.
In this step, you will create a Fabric notebook and write Spark SQL to ingest all three CSVs into Delta Lake tables. This is the Bronze layer of the medallion architecture. Bronze tables preserve the raw data exactly as received with no transformations applied.
In this step, get ready to:
- Create a Fabric notebook attached to your lakehouse.
- Write Spark SQL to ingest raw CSVs into Bronze Delta tables.
- Validate that all records loaded correctly with expected row counts.
Create a notebook from the lakehouse
Fabric notebooks let you run code against your lakehouse data. Creating the notebook from within the lakehouse automatically attaches it as the default data source.
- In your claims_lakehouse view, find the Open notebook dropdown on the Home ribbon.
- Select New notebook.
- Wait for the notebook to load. This can take up to two minutes as it starts a Spark session.
- Confirm that claims_lakehouse appears in the left-hand Lakehouse explorer pane with a pin icon next to it. The pin icon indicates it is the default lakehouse.
Why does the default lakehouse matter?
The default lakehouse determines which Hive metastore your Spark SQL uses at runtime. When you use a relative path like Files/bronze/... in your SQL, Spark resolves it against the default lakehouse.
Don't see claims_lakehouse in the explorer?
If the Lakehouse explorer is empty, select Add lakehouse in the left pane. Choose Existing lakehouse, select claims_lakehouse, then click Add.
help me attach my lakehouse to a Fabric notebook
Write Spark SQL to create the Bronze Delta tables
Each Bronze table reads its corresponding CSV and stores it as a Delta table with no transformations. The %%sql magic command tells the notebook to run the cell as Spark SQL.
- Click into the first code cell of your notebook. Paste the following Spark SQL code:
%%sql
-- Create Bronze Delta tables from raw CSV files
-- Beneficiary Summary 2008
CREATE OR REPLACE TABLE bronze_beneficiary
USING DELTA
AS SELECT * FROM csv.`Files/bronze/beneficiary/DE1_0_2008_Beneficiary_Summary_File_Sample_1.csv`
OPTIONS (header = "true", inferSchema = "true");
-- Inpatient Claims 2008-2010
CREATE OR REPLACE TABLE bronze_inpatient
USING DELTA
AS SELECT * FROM csv.`Files/bronze/inpatient/DE1_0_2008_to_2010_Inpatient_Claims_Sample_1.csv`
OPTIONS (header = "true", inferSchema = "true");
-- Outpatient Claims 2008-2010
CREATE OR REPLACE TABLE bronze_outpatient
USING DELTA
AS SELECT * FROM csv.`Files/bronze/outpatient/DE1_0_2008_to_2010_Outpatient_Claims_Sample_1.csv`
OPTIONS (header = "true", inferSchema = "true");What does this code do?
CREATE OR REPLACE TABLE ... USING DELTA creates a managed Delta table in the lakehouse's Tables section. If the table already exists, it gets replaced cleanly.
The csv.`Files/...` syntax reads a CSV file directly from the lakehouse's Files area. The OPTIONS clause tells Spark to treat the first row as column headers and automatically infer data types from the values.
SELECT * preserves every column exactly as-is. This is the Bronze layer principle: store the raw data with zero modification so you always have an untouched copy.
- Run the cell by pressing Shift+Enter or clicking the play button to the left of the cell.
- Wait for execution to complete. The cell status indicator shows a green checkmark when finished. This may take 1-2 minutes on the first run as Spark starts up.
Seeing a path not found error?
Double check that your folder names match exactly: Files/bronze/beneficiary/, Files/bronze/inpatient/, and Files/bronze/outpatient/. Folder and file names are case-sensitive.
Also confirm the CSV file names match what you uploaded. Expand the Files section in the Lakehouse explorer to verify the paths.
help me fix a path not found error in my Fabric Spark SQL notebook
Validate the Bronze layer row counts
Before moving on, you need to confirm that all records from each CSV made it into the Delta tables. The CMS DE-SynPUF Sample 1 has known approximate record counts you can compare against.
- Add a new cell below the first one by hovering between cells and selecting Code.
- Paste the following validation query into the new cell:
%%sql
-- Validate row counts for Bronze tables
SELECT 'bronze_beneficiary' AS table_name, COUNT(*) AS row_count FROM bronze_beneficiary
UNION ALL
SELECT 'bronze_inpatient', COUNT(*) FROM bronze_inpatient
UNION ALL
SELECT 'bronze_outpatient', COUNT(*) FROM bronze_outpatient;What should I see?
The query returns a table with three rows showing the record count for each Bronze table. Expected approximate values for Sample 1:
- bronze_beneficiary: approximately 116,000 rows.
- bronze_inpatient: approximately 27,000 rows.
- bronze_outpatient: approximately 283,000 rows.
- Run the cell by pressing Shift+Enter.
- Confirm the row counts are in the expected ranges. Your exact numbers may vary slightly but should be close to the values above.
- Expand the Tables node in the Lakehouse explorer on the left. You should see bronze_beneficiary, bronze_inpatient, and bronze_outpatient listed. If they do not appear, select the ellipsis (...) next to Tables and choose Refresh.
Row counts showing 0 or tables missing?
If counts are zero, your CSV files may not have been uploaded into the correct subfolders. Go back to the Lakehouse explorer, expand Files > bronze, and confirm each subfolder contains its CSV.
If tables are not appearing under the Tables section, select the ellipsis (...) next to Tables and click Refresh. Delta tables must live directly under the Tables section to be recognized by the lakehouse.
help me troubleshoot missing Delta tables in my Fabric lakehouse
✔️ Awesome, I've got everything!
Great. Double check you have two cells in your notebook and all three tables visible under Tables in the Lakehouse explorer.
ⓧ I'd like to double check the full code
Here is the complete code for both notebook cells:
Cell 1:
%%sql
-- Create Bronze Delta tables from raw CSV files
-- Beneficiary Summary 2008
CREATE OR REPLACE TABLE bronze_beneficiary
USING DELTA
AS SELECT * FROM csv.`Files/bronze/beneficiary/DE1_0_2008_Beneficiary_Summary_File_Sample_1.csv`
OPTIONS (header = "true", inferSchema = "true");
-- Inpatient Claims 2008-2010
CREATE OR REPLACE TABLE bronze_inpatient
USING DELTA
AS SELECT * FROM csv.`Files/bronze/inpatient/DE1_0_2008_to_2010_Inpatient_Claims_Sample_1.csv`
OPTIONS (header = "true", inferSchema = "true");
-- Outpatient Claims 2008-2010
CREATE OR REPLACE TABLE bronze_outpatient
USING DELTA
AS SELECT * FROM csv.`Files/bronze/outpatient/DE1_0_2008_to_2010_Outpatient_Claims_Sample_1.csv`
OPTIONS (header = "true", inferSchema = "true");Cell 2:
%%sql
-- Validate row counts for Bronze tables
SELECT 'bronze_beneficiary' AS table_name, COUNT(*) AS row_count FROM bronze_beneficiary
UNION ALL
SELECT 'bronze_inpatient', COUNT(*) FROM bronze_inpatient
UNION ALL
SELECT 'bronze_outpatient', COUNT(*) FROM bronze_outpatient;Your Bronze layer is locked in. You now have a permanent, untouched copy of the raw CMS data stored as efficient Delta tables. Next up, you will apply data quality rules to create a clean Silver layer.
Build the Silver Layer with Data Quality Rules
Your Bronze layer is loaded with raw CMS data exactly as it arrived from the source files. The problem is that this raw data stores dates as numeric strings, uses cryptic codes (1 and 2) for chronic conditions, and leaves payment columns as loosely inferred types.
The Silver layer fixes these issues by applying data quality rules: casting columns to proper types, replacing coded values with meaningful booleans, filtering out unusable records, and deriving new calculated columns. This is where raw data becomes trustworthy, queryable data.
In this step, get ready to:
- Clean and standardize the beneficiary table with proper date types, boolean condition flags, and a derived age column.
- Cast inpatient claims columns to correct types and derive length of stay.
- Deduplicate and clean outpatient claims data.
Create the silver beneficiary table
The beneficiary file contains date columns stored as numeric strings in yyyyMMdd format (like 19230101). Chronic condition indicators are coded as 1 (Yes) and 2 (No), and multiple columns need explicit type enforcement.
- Add a new cell to your notebook by clicking + Code below your existing cells.
- Create the silver_beneficiary table by pasting the following Spark SQL into the new cell:
%%sql
CREATE OR REPLACE TABLE silver_beneficiary
USING DELTA
AS
SELECT
DESYNPUF_ID,
TO_DATE(CAST(BENE_BIRTH_DT AS STRING), 'yyyyMMdd') AS birth_date,
CASE WHEN BENE_DEATH_DT IS NOT NULL AND BENE_DEATH_DT != ''
THEN TO_DATE(CAST(BENE_DEATH_DT AS STRING), 'yyyyMMdd')
ELSE NULL
END AS death_date,
CAST(BENE_SEX_IDENT_CD AS INT) AS sex_code,
CAST(BENE_RACE_CD AS INT) AS race_code,
CAST(SP_STATE_CODE AS INT) AS state_code,
CAST(BENE_COUNTY_CD AS INT) AS county_code,
-- Chronic condition flags: 1=Yes, 2=No -> boolean
CASE WHEN SP_ALZHDMTA = 1 THEN TRUE ELSE FALSE END AS has_alzheimers,
CASE WHEN SP_CHF = 1 THEN TRUE ELSE FALSE END AS has_heart_failure,
CASE WHEN SP_CHRNKIDN = 1 THEN TRUE ELSE FALSE END AS has_chronic_kidney,
CASE WHEN SP_CNCR = 1 THEN TRUE ELSE FALSE END AS has_cancer,
CASE WHEN SP_COPD = 1 THEN TRUE ELSE FALSE END AS has_copd,
CASE WHEN SP_DEPRESSN = 1 THEN TRUE ELSE FALSE END AS has_depression,
CASE WHEN SP_DIABETES = 1 THEN TRUE ELSE FALSE END AS has_diabetes,
CASE WHEN SP_ISCHMCHT = 1 THEN TRUE ELSE FALSE END AS has_ischemic_heart,
CASE WHEN SP_OSTEOPRS = 1 THEN TRUE ELSE FALSE END AS has_osteoporosis,
CASE WHEN SP_RA_OA = 1 THEN TRUE ELSE FALSE END AS has_arthritis,
CASE WHEN SP_STRKETIA = 1 THEN TRUE ELSE FALSE END AS has_stroke,
-- Derived: age at start of 2008
FLOOR(DATEDIFF(TO_DATE('2008-01-01'), TO_DATE(CAST(BENE_BIRTH_DT AS STRING), 'yyyyMMdd')) / 365.25) AS age_at_start,
-- Reimbursement amounts
CAST(MEDREIMB_IP AS DOUBLE) AS ip_annual_reimbursement,
CAST(MEDREIMB_OP AS DOUBLE) AS op_annual_reimbursement,
CAST(MEDREIMB_CAR AS DOUBLE) AS carrier_annual_reimbursement
FROM bronze_beneficiary
WHERE DESYNPUF_ID IS NOT NULL;What does this query do?
This single SELECT applies four categories of data quality rules to the raw beneficiary data:
- Date casting: TO_DATE(CAST(... AS STRING), 'yyyyMMdd') converts numeric date strings into proper DATE types. The death date gets special handling because alive beneficiaries have NULL or empty values.
- Boolean flags: Each chronic condition column uses CASE to convert the CMS coding (1=Yes, 2=No) into TRUE/FALSE values that are far easier to query and aggregate.
- Derived column: age_at_start calculates each beneficiary's age at the beginning of 2008 using date arithmetic.
- NULL filter: WHERE DESYNPUF_ID IS NOT NULL removes any records missing the linking key, since these cannot be joined to claims later.
- Run the cell by clicking the play button or pressing Shift+Enter.
- Wait for the cell to finish executing. In the Lakehouse explorer on the left, select the ellipsis (...) next to Tables and select Refresh. You should see silver_beneficiary appear in the Tables list.
Cell failing with a parse error?
- Make sure the first line of the cell is exactly %%sql with no spaces before it. This magic command tells the notebook to interpret the cell as Spark SQL.
- Check that claims_lakehouse is still attached as the default lakehouse. Look for the pin icon in the Lakehouse explorer. If it is missing, re-add it.
- help me debug my silver_beneficiary query
Create the silver inpatient table
The inpatient claims file has the same date format issues plus payment columns that need numeric enforcement. You will also derive a length_of_stay column that calculates how many days each hospital admission lasted.
- Add another new code cell below your previous one by clicking + Code.
- Create the silver_inpatient table by pasting this query:
%%sql
CREATE OR REPLACE TABLE silver_inpatient
USING DELTA
AS
SELECT
DESYNPUF_ID,
CLM_ID,
TO_DATE(CAST(CLM_FROM_DT AS STRING), 'yyyyMMdd') AS claim_start_date,
TO_DATE(CAST(CLM_THRU_DT AS STRING), 'yyyyMMdd') AS claim_end_date,
CAST(CLM_PMT_AMT AS DOUBLE) AS claim_payment_amount,
CAST(NCH_BENE_IP_DDCTBL_AMT AS DOUBLE) AS inpatient_deductible_amount,
CAST(NCH_PRMRY_PYR_CLM_PD_AMT AS DOUBLE) AS primary_payer_paid_amount,
CAST(CLM_PASS_THRU_PER_DIEM_AMT AS DOUBLE) AS per_diem_amount,
CAST(NCH_BENE_BLOOD_DDCTBL_LGCY_AMT AS DOUBLE) AS blood_deductible_amount,
CAST(CLM_UTLZTN_DAY_CNT AS INT) AS utilization_day_count,
ADMTNG_ICD9_DGNS_CD AS admitting_diagnosis_code,
AT_PHYSN_NPI AS attending_physician_npi,
-- Derived: length of stay in days
DATEDIFF(
TO_DATE(CAST(CLM_THRU_DT AS STRING), 'yyyyMMdd'),
TO_DATE(CAST(CLM_FROM_DT AS STRING), 'yyyyMMdd')
) AS length_of_stay
FROM bronze_inpatient
WHERE DESYNPUF_ID IS NOT NULL;What does this query do?
This transforms the raw inpatient claims into a clean, strongly-typed table:
- CLM_FROM_DT and CLM_THRU_DT get the same date parsing treatment as the beneficiary dates.
- All payment columns (CLM_PMT_AMT, deductibles, per diem) are cast to DOUBLE so you can safely aggregate them in the Gold layer.
- DATEDIFF calculates the number of days between claim start and end, giving you length_of_stay without manual calculation.
- Records with NULL DESYNPUF_ID are filtered out since they cannot be linked to a beneficiary.
- Run the cell and wait for it to finish.
- Refresh the Tables section in the Lakehouse explorer. You should now see silver_inpatient listed alongside silver_beneficiary.
Not seeing silver_inpatient in the Tables list?
- Confirm the cell finished without errors. Look for a green checkmark or success status below the cell.
- Click the ellipsis (...) next to Tables and select Refresh again. Fabric may take a moment to discover new tables.
- help me troubleshoot my silver_inpatient table creation
Create the silver outpatient table
The outpatient claims file needs the same date and payment casting as inpatient. It also benefits from SELECT DISTINCT to remove any exact duplicate claim rows that may exist in the raw data.
- Add one more code cell below by clicking + Code.
- Create the silver_outpatient table by pasting this query:
%%sql
CREATE OR REPLACE TABLE silver_outpatient
USING DELTA
AS
SELECT DISTINCT
DESYNPUF_ID,
CLM_ID,
TO_DATE(CAST(CLM_FROM_DT AS STRING), 'yyyyMMdd') AS claim_start_date,
TO_DATE(CAST(CLM_THRU_DT AS STRING), 'yyyyMMdd') AS claim_end_date,
CAST(CLM_PMT_AMT AS DOUBLE) AS claim_payment_amount,
CAST(NCH_BENE_BLOOD_DDCTBL_LGCY_AMT AS DOUBLE) AS blood_deductible_amount,
CAST(NCH_PRMRY_PYR_CLM_PD_AMT AS DOUBLE) AS primary_payer_paid_amount,
ADMTNG_ICD9_DGNS_CD AS admitting_diagnosis_code,
AT_PHYSN_NPI AS attending_physician_npi
FROM bronze_outpatient
WHERE DESYNPUF_ID IS NOT NULL;What does this query do?
This follows the same pattern as the inpatient transformation with two key differences:
- SELECT DISTINCT deduplicates any exact duplicate rows. Outpatient claims data can contain duplicates from processing retries at the source.
- The column set is smaller because outpatient claims have fewer payment breakdowns than inpatient. The same date casting and NULL filtering logic applies.
- Run the cell and wait for it to complete.
- Refresh the Tables section one more time. You should now see all three Silver tables: silver_beneficiary, silver_inpatient, and silver_outpatient.
Seeing a column resolution error?
- Column names like NCH_BENE_BLOOD_DDCTBL_LGCY_AMT must match the Bronze table exactly. If you see an unresolved column error, click on bronze_outpatient in the Lakehouse explorer to preview its columns and compare the spelling.
- Make sure %%sql is the very first line in the cell with nothing above it.
- help me fix my silver_outpatient column error
✔️ Awesome, I've got everything!
Great. Double check you have three Silver tables visible in the Lakehouse explorer's Tables section, then continue to the next step.
ⓧ I'd like to double check the full code
Here are all three Silver layer cells together for comparison:
%%sql
CREATE OR REPLACE TABLE silver_beneficiary
USING DELTA
AS
SELECT
DESYNPUF_ID,
TO_DATE(CAST(BENE_BIRTH_DT AS STRING), 'yyyyMMdd') AS birth_date,
CASE WHEN BENE_DEATH_DT IS NOT NULL AND BENE_DEATH_DT != ''
THEN TO_DATE(CAST(BENE_DEATH_DT AS STRING), 'yyyyMMdd')
ELSE NULL
END AS death_date,
CAST(BENE_SEX_IDENT_CD AS INT) AS sex_code,
CAST(BENE_RACE_CD AS INT) AS race_code,
CAST(SP_STATE_CODE AS INT) AS state_code,
CAST(BENE_COUNTY_CD AS INT) AS county_code,
-- Chronic condition flags: 1=Yes, 2=No -> boolean
CASE WHEN SP_ALZHDMTA = 1 THEN TRUE ELSE FALSE END AS has_alzheimers,
CASE WHEN SP_CHF = 1 THEN TRUE ELSE FALSE END AS has_heart_failure,
CASE WHEN SP_CHRNKIDN = 1 THEN TRUE ELSE FALSE END AS has_chronic_kidney,
CASE WHEN SP_CNCR = 1 THEN TRUE ELSE FALSE END AS has_cancer,
CASE WHEN SP_COPD = 1 THEN TRUE ELSE FALSE END AS has_copd,
CASE WHEN SP_DEPRESSN = 1 THEN TRUE ELSE FALSE END AS has_depression,
CASE WHEN SP_DIABETES = 1 THEN TRUE ELSE FALSE END AS has_diabetes,
CASE WHEN SP_ISCHMCHT = 1 THEN TRUE ELSE FALSE END AS has_ischemic_heart,
CASE WHEN SP_OSTEOPRS = 1 THEN TRUE ELSE FALSE END AS has_osteoporosis,
CASE WHEN SP_RA_OA = 1 THEN TRUE ELSE FALSE END AS has_arthritis,
CASE WHEN SP_STRKETIA = 1 THEN TRUE ELSE FALSE END AS has_stroke,
-- Derived: age at start of 2008
FLOOR(DATEDIFF(TO_DATE('2008-01-01'), TO_DATE(CAST(BENE_BIRTH_DT AS STRING), 'yyyyMMdd')) / 365.25) AS age_at_start,
-- Reimbursement amounts
CAST(MEDREIMB_IP AS DOUBLE) AS ip_annual_reimbursement,
CAST(MEDREIMB_OP AS DOUBLE) AS op_annual_reimbursement,
CAST(MEDREIMB_CAR AS DOUBLE) AS carrier_annual_reimbursement
FROM bronze_beneficiary
WHERE DESYNPUF_ID IS NOT NULL;%%sql
CREATE OR REPLACE TABLE silver_inpatient
USING DELTA
AS
SELECT
DESYNPUF_ID,
CLM_ID,
TO_DATE(CAST(CLM_FROM_DT AS STRING), 'yyyyMMdd') AS claim_start_date,
TO_DATE(CAST(CLM_THRU_DT AS STRING), 'yyyyMMdd') AS claim_end_date,
CAST(CLM_PMT_AMT AS DOUBLE) AS claim_payment_amount,
CAST(NCH_BENE_IP_DDCTBL_AMT AS DOUBLE) AS inpatient_deductible_amount,
CAST(NCH_PRMRY_PYR_CLM_PD_AMT AS DOUBLE) AS primary_payer_paid_amount,
CAST(CLM_PASS_THRU_PER_DIEM_AMT AS DOUBLE) AS per_diem_amount,
CAST(NCH_BENE_BLOOD_DDCTBL_LGCY_AMT AS DOUBLE) AS blood_deductible_amount,
CAST(CLM_UTLZTN_DAY_CNT AS INT) AS utilization_day_count,
ADMTNG_ICD9_DGNS_CD AS admitting_diagnosis_code,
AT_PHYSN_NPI AS attending_physician_npi,
-- Derived: length of stay in days
DATEDIFF(
TO_DATE(CAST(CLM_THRU_DT AS STRING), 'yyyyMMdd'),
TO_DATE(CAST(CLM_FROM_DT AS STRING), 'yyyyMMdd')
) AS length_of_stay
FROM bronze_inpatient
WHERE DESYNPUF_ID IS NOT NULL;%%sql
CREATE OR REPLACE TABLE silver_outpatient
USING DELTA
AS
SELECT DISTINCT
DESYNPUF_ID,
CLM_ID,
TO_DATE(CAST(CLM_FROM_DT AS STRING), 'yyyyMMdd') AS claim_start_date,
TO_DATE(CAST(CLM_THRU_DT AS STRING), 'yyyyMMdd') AS claim_end_date,
CAST(CLM_PMT_AMT AS DOUBLE) AS claim_payment_amount,
CAST(NCH_BENE_BLOOD_DDCTBL_LGCY_AMT AS DOUBLE) AS blood_deductible_amount,
CAST(NCH_PRMRY_PYR_CLM_PD_AMT AS DOUBLE) AS primary_payer_paid_amount,
ADMTNG_ICD9_DGNS_CD AS admitting_diagnosis_code,
AT_PHYSN_NPI AS attending_physician_npi
FROM bronze_outpatient
WHERE DESYNPUF_ID IS NOT NULL;Your Silver layer is complete. You now have three clean, strongly-typed Delta Lake tables with proper dates, meaningful boolean flags, and derived metrics. Next up, you will join these tables together in the Gold layer to produce patient-level analytics.
Build Gold Analytical Tables
Your Silver layer is clean, typed, and deduplicated. Now it is time to answer the business questions that raw data can never address on its own: which patients cost the most, and which chronic conditions drive those costs?
The Gold layer brings multiple Silver tables together through joins and aggregations to produce analytics-ready tables. In this step, you will join beneficiary demographics with their inpatient and outpatient claims to build patient-level summaries, then pivot chronic conditions into a cost-comparison table.
In this step, get ready to:
- Create a patient-level summary table joining beneficiaries with aggregated claims.
- Build a chronic condition cost analysis table.
- Validate the Gold layer with analytical queries.
Create the gold patient summary table
The gold_patient_summary table brings together each beneficiary's demographics with their total inpatient and outpatient activity. This requires LEFT JOINs to aggregated subqueries so that patients with zero claims still appear in the output.
- Click the + Code button below your last cell to add a new cell (Cell 6).
- Create the Gold patient summary by pasting this Spark SQL into the new cell:
%%sql
CREATE OR REPLACE TABLE gold_patient_summary
USING DELTA
AS
SELECT
b.DESYNPUF_ID,
b.birth_date,
b.age_at_start,
b.sex_code,
b.state_code,
b.has_alzheimers,
b.has_heart_failure,
b.has_chronic_kidney,
b.has_cancer,
b.has_copd,
b.has_depression,
b.has_diabetes,
b.has_ischemic_heart,
b.has_osteoporosis,
b.has_arthritis,
b.has_stroke,
-- Count of chronic conditions
(CAST(b.has_alzheimers AS INT) + CAST(b.has_heart_failure AS INT) +
CAST(b.has_chronic_kidney AS INT) + CAST(b.has_cancer AS INT) +
CAST(b.has_copd AS INT) + CAST(b.has_depression AS INT) +
CAST(b.has_diabetes AS INT) + CAST(b.has_ischemic_heart AS INT) +
CAST(b.has_osteoporosis AS INT) + CAST(b.has_arthritis AS INT) +
CAST(b.has_stroke AS INT)) AS chronic_condition_count,
-- Inpatient metrics
COALESCE(ip.total_ip_admissions, 0) AS total_ip_admissions,
COALESCE(ip.total_ip_cost, 0.0) AS total_ip_cost,
COALESCE(ip.avg_length_of_stay, 0.0) AS avg_length_of_stay,
-- Outpatient metrics
COALESCE(op.total_op_visits, 0) AS total_op_visits,
COALESCE(op.total_op_cost, 0.0) AS total_op_cost,
-- Combined
COALESCE(ip.total_ip_cost, 0.0) + COALESCE(op.total_op_cost, 0.0) AS total_combined_cost
FROM silver_beneficiary b
LEFT JOIN (
SELECT
DESYNPUF_ID,
COUNT(*) AS total_ip_admissions,
SUM(claim_payment_amount) AS total_ip_cost,
AVG(length_of_stay) AS avg_length_of_stay
FROM silver_inpatient
GROUP BY DESYNPUF_ID
) ip ON b.DESYNPUF_ID = ip.DESYNPUF_ID
LEFT JOIN (
SELECT
DESYNPUF_ID,
COUNT(*) AS total_op_visits,
SUM(claim_payment_amount) AS total_op_cost
FROM silver_outpatient
GROUP BY DESYNPUF_ID
) op ON b.DESYNPUF_ID = op.DESYNPUF_ID;What does this query do?
This query builds one row per patient by joining three data sources together.
- The base table is silver_beneficiary (aliased as b), which provides demographics and chronic condition flags.
- Two LEFT JOIN subqueries aggregate silver_inpatient and silver_outpatient down to one row per patient before joining.
- COALESCE fills in 0 for patients who have no claims, so they appear in the output with zero cost instead of NULL.
- The chronic_condition_count sums the boolean flags (cast to INT) to give a comorbidity score per patient.
- Run the cell by clicking the play button or pressing Shift+Enter.
- You should see a success message confirming the table was created. In the Lakehouse explorer on the left, expand Tables to see gold_patient_summary listed.
Table not appearing in the explorer?
- Click the refresh icon (circular arrow) next to Tables in the Lakehouse explorer. New tables sometimes take a moment to appear.
- If you see an error about a missing column, confirm that your Silver tables from the previous step ran successfully. The column names in the subqueries must match the Silver layer exactly.
- Make sure your notebook is still attached to claims_lakehouse (check the lakehouse selector at the top left of the notebook).
Why isn't my Gold table showing up in Fabric?
Create the chronic condition costs table
The second Gold table pivots the 11 chronic condition flags into rows, calculating cost and utilization metrics for each condition. This makes it easy to compare which conditions are the most expensive across the patient population.
- Add a new cell (Cell 7) below your last cell.
- Create the chronic condition costs table by pasting this query:
%%sql
CREATE OR REPLACE TABLE gold_chronic_condition_costs
USING DELTA
AS
SELECT
'Alzheimers' AS condition_name,
COUNT(*) AS patient_count,
AVG(total_combined_cost) AS avg_total_cost,
AVG(total_ip_admissions) AS avg_admissions,
SUM(total_combined_cost) AS total_cost_all_patients
FROM gold_patient_summary WHERE has_alzheimers = TRUE
UNION ALL
SELECT 'Heart Failure', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_heart_failure = TRUE
UNION ALL
SELECT 'Chronic Kidney', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_chronic_kidney = TRUE
UNION ALL
SELECT 'Cancer', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_cancer = TRUE
UNION ALL
SELECT 'COPD', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_copd = TRUE
UNION ALL
SELECT 'Depression', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_depression = TRUE
UNION ALL
SELECT 'Diabetes', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_diabetes = TRUE
UNION ALL
SELECT 'Ischemic Heart', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_ischemic_heart = TRUE
UNION ALL
SELECT 'Osteoporosis', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_osteoporosis = TRUE
UNION ALL
SELECT 'Arthritis', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_arthritis = TRUE
UNION ALL
SELECT 'Stroke', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_stroke = TRUE;What does this query do?
Each SELECT block filters gold_patient_summary to patients with a specific chronic condition, then calculates four metrics for that group: patient count, average total cost, average admissions, and total cost across all patients with that condition.
UNION ALL stacks the 11 condition results into a single table with one row per condition. This is the medallion architecture at work: raw data becomes clean data, which becomes business-facing analytics.
- Run the cell.
- Refresh the Lakehouse explorer. You should now see gold_chronic_condition_costs alongside gold_patient_summary under Tables.
Seeing an error about gold_patient_summary?
This table reads directly from gold_patient_summary. If Cell 6 failed or was not run, this cell will fail too. Go back and confirm Cell 6 completed successfully before re-running Cell 7.
Why is my gold_chronic_condition_costs query failing?
Validate your Gold tables with analytical queries
With both Gold tables created, you can now run analytical queries to confirm the data tells a coherent story. These queries surface cost patterns, comorbidity effects, and condition-level comparisons.
- Add a new cell (Cell 8). Find the top 10 highest-cost patients by pasting this query:
%%sql
-- Top 10 highest-cost patients
SELECT DESYNPUF_ID, age_at_start, chronic_condition_count, total_ip_admissions, total_op_visits, total_combined_cost
FROM gold_patient_summary
ORDER BY total_combined_cost DESC
LIMIT 10;What should I see?
You should see a table with 10 rows showing the patients with the highest combined inpatient and outpatient costs. Notice how high-cost patients tend to have higher chronic condition counts and more admissions.
- Run the cell to see the results.
- Add a new cell. Calculate average cost by number of chronic conditions by pasting this query:
%%sql
-- Average cost by number of chronic conditions
SELECT
chronic_condition_count,
COUNT(*) AS patient_count,
ROUND(AVG(total_combined_cost), 2) AS avg_cost,
ROUND(AVG(total_ip_admissions), 2) AS avg_admissions
FROM gold_patient_summary
GROUP BY chronic_condition_count
ORDER BY chronic_condition_count;What should I see?
You should see one row per chronic condition count (0 through 11). Average cost and admissions should increase as the condition count rises. This demonstrates the well-known pattern that comorbidities compound healthcare utilization.
- Run the cell.
- Add one more cell. Compare admission rates between diabetic and non-diabetic patients by pasting this query:
%%sql
-- Admission rate: diabetic vs non-diabetic
SELECT
has_diabetes,
COUNT(*) AS patient_count,
ROUND(AVG(total_ip_admissions), 3) AS avg_ip_admissions,
ROUND(AVG(total_combined_cost), 2) AS avg_total_cost
FROM gold_patient_summary
GROUP BY has_diabetes;What should I see?
You should see two rows: one for has_diabetes = TRUE and one for has_diabetes = FALSE. Diabetic patients should show higher average admissions and costs, which matches known clinical patterns.
- Run the cell to confirm the results appear.
Seeing empty or zero-value results?
- If all costs show as 0, your Silver inpatient or outpatient tables may not have loaded correctly. Go back to Cells 4 and 5 and re-run them, then re-run Cell 6 before running the validation queries again.
- If the query returns no rows at all, confirm that gold_patient_summary was created successfully by running SELECT COUNT(*) FROM gold_patient_summary in a new cell.
My Gold table validation queries are returning unexpected results. Help me debug.
✔️ Awesome, I've got everything!
Great. Double check you have both Gold tables showing in the Lakehouse explorer and all three validation queries returned results.
ⓧ I'd like to double check the full code
Here is the complete code for all three cells you added in this step.
%%sql
CREATE OR REPLACE TABLE gold_patient_summary
USING DELTA
AS
SELECT
b.DESYNPUF_ID,
b.birth_date,
b.age_at_start,
b.sex_code,
b.state_code,
b.has_alzheimers,
b.has_heart_failure,
b.has_chronic_kidney,
b.has_cancer,
b.has_copd,
b.has_depression,
b.has_diabetes,
b.has_ischemic_heart,
b.has_osteoporosis,
b.has_arthritis,
b.has_stroke,
-- Count of chronic conditions
(CAST(b.has_alzheimers AS INT) + CAST(b.has_heart_failure AS INT) +
CAST(b.has_chronic_kidney AS INT) + CAST(b.has_cancer AS INT) +
CAST(b.has_copd AS INT) + CAST(b.has_depression AS INT) +
CAST(b.has_diabetes AS INT) + CAST(b.has_ischemic_heart AS INT) +
CAST(b.has_osteoporosis AS INT) + CAST(b.has_arthritis AS INT) +
CAST(b.has_stroke AS INT)) AS chronic_condition_count,
-- Inpatient metrics
COALESCE(ip.total_ip_admissions, 0) AS total_ip_admissions,
COALESCE(ip.total_ip_cost, 0.0) AS total_ip_cost,
COALESCE(ip.avg_length_of_stay, 0.0) AS avg_length_of_stay,
-- Outpatient metrics
COALESCE(op.total_op_visits, 0) AS total_op_visits,
COALESCE(op.total_op_cost, 0.0) AS total_op_cost,
-- Combined
COALESCE(ip.total_ip_cost, 0.0) + COALESCE(op.total_op_cost, 0.0) AS total_combined_cost
FROM silver_beneficiary b
LEFT JOIN (
SELECT
DESYNPUF_ID,
COUNT(*) AS total_ip_admissions,
SUM(claim_payment_amount) AS total_ip_cost,
AVG(length_of_stay) AS avg_length_of_stay
FROM silver_inpatient
GROUP BY DESYNPUF_ID
) ip ON b.DESYNPUF_ID = ip.DESYNPUF_ID
LEFT JOIN (
SELECT
DESYNPUF_ID,
COUNT(*) AS total_op_visits,
SUM(claim_payment_amount) AS total_op_cost
FROM silver_outpatient
GROUP BY DESYNPUF_ID
) op ON b.DESYNPUF_ID = op.DESYNPUF_ID;%%sql
CREATE OR REPLACE TABLE gold_chronic_condition_costs
USING DELTA
AS
SELECT
'Alzheimers' AS condition_name,
COUNT(*) AS patient_count,
AVG(total_combined_cost) AS avg_total_cost,
AVG(total_ip_admissions) AS avg_admissions,
SUM(total_combined_cost) AS total_cost_all_patients
FROM gold_patient_summary WHERE has_alzheimers = TRUE
UNION ALL
SELECT 'Heart Failure', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_heart_failure = TRUE
UNION ALL
SELECT 'Chronic Kidney', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_chronic_kidney = TRUE
UNION ALL
SELECT 'Cancer', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_cancer = TRUE
UNION ALL
SELECT 'COPD', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_copd = TRUE
UNION ALL
SELECT 'Depression', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_depression = TRUE
UNION ALL
SELECT 'Diabetes', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_diabetes = TRUE
UNION ALL
SELECT 'Ischemic Heart', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_ischemic_heart = TRUE
UNION ALL
SELECT 'Osteoporosis', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_osteoporosis = TRUE
UNION ALL
SELECT 'Arthritis', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_arthritis = TRUE
UNION ALL
SELECT 'Stroke', COUNT(*), AVG(total_combined_cost), AVG(total_ip_admissions), SUM(total_combined_cost)
FROM gold_patient_summary WHERE has_stroke = TRUE;%%sql
-- Top 10 highest-cost patients
SELECT DESYNPUF_ID, age_at_start, chronic_condition_count, total_ip_admissions, total_op_visits, total_combined_cost
FROM gold_patient_summary
ORDER BY total_combined_cost DESC
LIMIT 10;%%sql
-- Average cost by number of chronic conditions
SELECT
chronic_condition_count,
COUNT(*) AS patient_count,
ROUND(AVG(total_combined_cost), 2) AS avg_cost,
ROUND(AVG(total_ip_admissions), 2) AS avg_admissions
FROM gold_patient_summary
GROUP BY chronic_condition_count
ORDER BY chronic_condition_count;%%sql
-- Admission rate: diabetic vs non-diabetic
SELECT
has_diabetes,
COUNT(*) AS patient_count,
ROUND(AVG(total_ip_admissions), 3) AS avg_ip_admissions,
ROUND(AVG(total_combined_cost), 2) AS avg_total_cost
FROM gold_patient_summary
GROUP BY has_diabetes;Your Gold layer is complete. You now have patient-level summaries and condition-level cost comparisons built from linked claims data. Next up, you will query these Gold tables through the SQL analytics endpoint to see how Delta Lake tables in Fabric become instantly accessible for T-SQL analytics without any extra configuration.
Query the Gold Layer via SQL Analytics Endpoint
Your Gold tables now contain patient-level cost metrics and chronic condition breakdowns built from hundreds of thousands of claims records. The final piece of a production medallion architecture is making those tables accessible to analysts and reporting tools without requiring Spark notebooks.
Microsoft Fabric automatically provisions a SQL analytics endpoint for every lakehouse. This endpoint exposes your Delta tables as queryable T-SQL tables, letting anyone with SQL skills run ad-hoc analytics directly in the browser.
In this step, get ready to:
- Switch to the SQL analytics endpoint view of your lakehouse.
- Run T-SQL analytical queries against your Gold tables.
- Confirm the full Bronze-Silver-Gold table lineage in the Lakehouse explorer.
Switch to the SQL analytics endpoint
The SQL analytics endpoint provides a read-only T-SQL surface over your Delta tables. Every lakehouse in Fabric automatically provisions one. No extra setup is needed.
- In the top-right area of the Lakehouse ribbon, locate the dropdown that shows the current view mode.
- Click the dropdown and select SQL analytics endpoint.
- Wait a few seconds for the query editor to load. You should see the Explorer pane on the left listing all your Delta tables as SQL tables.
What is the SQL analytics endpoint?
The SQL analytics endpoint runs on the same engine as the Fabric Data Warehouse. It exposes every Delta table in your lakehouse as a T-SQL table without copying data or provisioning infrastructure.
This means business analysts can query your Gold tables using familiar T-SQL syntax. They do not need access to Spark notebooks.
- In the Explorer pane, expand the tables list. You should see all your tables: bronze_beneficiary, bronze_inpatient, bronze_outpatient, silver_beneficiary, silver_inpatient, silver_outpatient, gold_patient_summary, and gold_chronic_condition_costs.
Tables not showing up?
The SQL analytics endpoint syncs with lakehouse changes via a background process. If your Gold tables are missing, click the Refresh button in the Explorer toolbar to force a metadata sync. It may take up to a minute for newly created tables to appear.
help me refresh my SQL analytics endpoint tables
Run T-SQL analytical queries against the Gold tables
With the SQL analytics endpoint active, you can write T-SQL queries directly in the browser query editor. You will run three queries that surface different analytical angles from your Gold tables.
- Click New Query in the toolbar to open a blank query tab.
- Paste the following query to see cost distribution by age band:
-- Cost distribution by age band
SELECT
CASE
WHEN age_at_start < 65 THEN 'Under 65'
WHEN age_at_start BETWEEN 65 AND 74 THEN '65-74'
WHEN age_at_start BETWEEN 75 AND 84 THEN '75-84'
ELSE '85+'
END AS age_band,
COUNT(*) AS patient_count,
ROUND(AVG(total_combined_cost), 2) AS avg_cost,
ROUND(SUM(total_combined_cost), 2) AS total_cost
FROM gold_patient_summary
GROUP BY
CASE
WHEN age_at_start < 65 THEN 'Under 65'
WHEN age_at_start BETWEEN 65 AND 74 THEN '65-74'
WHEN age_at_start BETWEEN 75 AND 84 THEN '75-84'
ELSE '85+'
END
ORDER BY avg_cost DESC;What does this query do?
- The CASE expression buckets patients into four age bands using the age_at_start column you derived in the Silver layer.
- It calculates patient count, average combined cost, and total cost per age band.
- Results are ordered by average cost descending, so you can immediately see which age group is most expensive.
- Click Run (or press F5) to execute the query.
- You should see a results grid with four rows, one per age band, showing patient counts and costs.
- Open a new query tab by clicking New Query again.
- Paste this query to rank chronic conditions by average total cost:
-- Chronic conditions ranked by average total cost
SELECT
condition_name,
patient_count,
ROUND(avg_total_cost, 2) AS avg_total_cost,
ROUND(avg_admissions, 2) AS avg_admissions,
ROUND(total_cost_all_patients, 2) AS total_cost_all_patients
FROM gold_chronic_condition_costs
ORDER BY avg_total_cost DESC;What does this query do?
This query reads directly from gold_chronic_condition_costs and ranks all 11 chronic conditions by their average total cost per patient. The Gold table already has the aggregation done, so this query is a simple read with ordering.
- Click Run to execute. You should see 11 rows, one per condition, sorted from highest to lowest average cost.
- Open one more query tab by clicking New Query.
- Paste this cross-tab query showing admission counts by state and condition:
-- Cross-tab: admission counts by state and condition
SELECT
state_code,
COUNT(*) AS total_patients,
SUM(CASE WHEN has_diabetes = 1 THEN 1 ELSE 0 END) AS diabetic_patients,
SUM(CASE WHEN has_heart_failure = 1 THEN 1 ELSE 0 END) AS heart_failure_patients,
SUM(CASE WHEN has_copd = 1 THEN 1 ELSE 0 END) AS copd_patients,
ROUND(AVG(CAST(total_ip_admissions AS FLOAT)), 2) AS avg_ip_admissions
FROM gold_patient_summary
GROUP BY state_code
ORDER BY total_patients DESC;What does this query do?
- The SUM(CASE WHEN ... THEN 1 ELSE 0 END) pattern pivots boolean condition flags into patient counts per state.
- This gives you a cross-tab view: for each state code, how many patients have diabetes, heart failure, or COPD, plus the average inpatient admissions.
- Results are ordered by total patients descending to surface the most populated states first.
- Click Run to execute. You should see a results grid with one row per state code, showing condition counts and average admissions.
Seeing an invalid column error?
If you see an error about an invalid column name, the SQL analytics endpoint may not have finished syncing your Gold tables. Click Refresh in the Explorer toolbar and wait 30 seconds before running the query again.
help me debug a column not found error in the SQL analytics endpoint
Confirm the full medallion architecture in the Lakehouse explorer
The final check is confirming that your entire medallion pipeline is visible and accessible from a single location. The Lakehouse explorer shows every table across all three layers.
- In the Explorer pane on the left, scroll through the full list of tables.
- Confirm you can see all three layers: the bronze_ tables (raw ingestion), the silver_ tables (cleaned and validated), and the gold_ tables (analytical aggregations).
- Click on gold_patient_summary in the Explorer to preview the first 1,000 rows. You should see patient-level records with all the joined metrics: chronic condition counts, inpatient costs, outpatient costs, and total combined cost.
Why does the naming convention matter?
The bronze_, silver_, gold_ prefix convention makes the medallion architecture self-documenting. Anyone browsing the Lakehouse explorer can immediately identify which layer a table belongs to and what level of refinement to expect from it.
Your medallion lakehouse is now complete. You have raw CMS data preserved in Bronze, cleaned and validated data in Silver, and analytical aggregations in Gold. All layers are queryable through both Spark notebooks and the SQL analytics endpoint.
Next up is the Secret Mission, where you will implement Slowly Changing Dimension Type 2 logic to track how beneficiary chronic conditions evolve across multiple years.
Secret mission
Implement SCD Type 2 for Beneficiary Chronic Conditions
The CMS data spans three years (2008-2010), with separate beneficiary files for each year. A patient diagnosed with diabetes in 2009 who did not have it in 2008 represents a real clinical change. Can you track these changes over time using the industry-standard Slowly Changing Dimension Type 2 pattern?
Clean Up Your Resources
Clean Up Your Resources
This project runs entirely on the Microsoft Fabric free trial, so there are no ongoing financial costs while your trial is active. The trial expires automatically after 60 days. Decide whether to keep your resources running, pause them to come back later, or delete them entirely.
Resources you used:
- Microsoft Fabric workspace ("MedicareLakehouse") containing your lakehouse, notebooks, and all Delta tables.
- The claims_lakehouse Lakehouse item (Bronze, Silver, and Gold tables plus uploaded CSV files).
- Local CMS DE-SynPUF CSV files downloaded to your computer.
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 when you started it. All workspace items persist until the trial expires.
- Your local CSV files stay on your machine wherever you extracted them.
Pause - I'll come back to this later
Free up local disk space while keeping your cloud resources intact for later.
- Delete the local CMS CSV files from your computer. You can re-download them from the CMS website anytime.
- Your Fabric workspace and all Delta tables remain available in the cloud until your trial expires.
Delete - I don't want to use this again
Remove all project resources and start fresh.
- Delete the Fabric workspace (this removes the lakehouse, notebooks, and all Delta tables at once).
- In Microsoft Fabric, select your workspace from the left navigation menu.
- Select Workspace settings.
- Select Other, then select Remove this workspace.
- When the warning appears, select Delete to confirm.
Delete the local CSV files from your computer by running the appropriate command for your operating system.
On macOS or Linux:
rm -rf ~/Downloads/DE1_0_2008*On Windows (PowerShell):
Remove-Item -Recurse -Force ~\Downloads\DE1_0_2008*Adjust the path if needed
If you extracted the CMS files to a folder other than Downloads, update the path in the command above to match your actual location.
Nice Work!
Nice Work!
You just built a staff-level medallion lakehouse in Microsoft Fabric. From raw CMS Medicare claims CSVs to queryable Gold analytics tables, you implemented the exact architecture pattern used by data engineers in healthcare and enterprise environments.
Here's what you accomplished:
- Ingested real-world CMS Medicare claims data (3 file types, 400K+ records) into a structured Bronze layer preserving raw data as Delta tables.
- Applied data quality transformations in a Silver layer including type casting, null handling, deduplication, and derived columns like length_of_stay and age_at_start.
- Built Gold analytical tables with multi-table joins producing patient-level cost and utilization metrics, queryable through both Spark SQL notebooks and the SQL analytics endpoint.
- Implemented SCD Type 2 logic to track how beneficiary chronic conditions evolved across a 3-year window.
Ready to quiz yourself?