MIMIC Clinical Analytics — Project Report

Overview

This project ingests the MIMIC-IV Clinical Database Demo v2.2 — an openly available subset of 100 deidentified patients from Beth Israel Deaconess Medical Center — and transforms it through a Bronze → Silver → Gold medallion architecture on Databricks. The Gold layer feeds SQL reporting views consumed by a published Power BI dashboard.

Data is sourced from the hosp/ folder of the PhysioNet download (hospital-level tables). ICU tables are available in the download but are out of scope for this pipeline.

Architecture

Data flows through three medallion layers, orchestrated by a Databricks Job on Serverless compute. All managed tables live under workspace.mimic.

[ Raw CSV ] Unity Catalog Volume (raw_data)
                ↓
[ Bronze ] bronze_patients · bronze_admissions · bronze_transfers · bronze_diagnoses · bronze_procedures · bronze_prescriptions · bronze_diagnosis_lookup
                ↓
[ Silver ] silver_patients · silver_admissions · silver_transfers · silver_diagnoses · silver_procedures · silver_prescriptions · silver_diagnosis_lookup
                ↓
[ Gold ] dim_patient · dim_diagnosis · fact_admission · fact_diagnosis · fact_procedure · fact_prescription · fact_transfer
                ↓
[ Views ] vw_admission_overview · vw_diagnosis_analysis · vw_prescription_analysis · vw_transfer_analysis · vw_procedure_analysis
                ↓
[ Power BI ] MIMIC Analytics Dashboard (DAX metrics, interactive visuals)

ETL Pipeline

The MIMIC Daily ETL Pipeline Databricks Job runs six sequential tasks. Each task depends on the previous completing successfully. All runs have succeeded with a typical duration of 1–3 minutes.

Bronze → Silver → Dimensions → Facts → Quality → Views

Task	Notebook	Layer	Description
Bronze	`01_bronze_ingestion`	Bronze	Create schema; load raw CSV from Unity Catalog Volume into Delta tables
silver	`02_silver_transformations`	Silver	Clean, type-cast, deduplicate, enforce referential integrity
dimensions	`03_gold_dimensions`	Gold	Build `dim_patient` and `dim_diagnosis`
facts	`04_gold_facts`	Gold	Build fact tables including 30-day readmission logic on admissions
quality	`05_data_quality`	QA	Validate dates, orphan records; persist to `data_quality_results`
views	`06_reporting_views`	Gold	Create five SQL views for Power BI and ad-hoc reporting

Data Source

Source: MIMIC-IV Clinical Database Demo v2.2 · DOI: 10.13026/dp1f-ex47 · License: ODbL v1.0

Table	Rows	Description	Primary Key	Foreign Keys
`patients`	100	Patient demographics	`subject_id`	—
`admissions`	275	Hospital admissions	`hadm_id`	`subject_id`
`transfers`	1,190	Ward / ICU transfers	`transfer_id`	`subject_id`, `hadm_id`
`diagnoses_icd`	4,506	ICD diagnoses	—	`subject_id`, `hadm_id`, `icd_code`
`procedures_icd`	722	ICD procedures	—	`subject_id`, `hadm_id`, `icd_code`
`prescriptions`	18,087	Medications	—	`subject_id`, `hadm_id`
`d_icd_diagnoses`	109,775	ICD diagnosis lookup (reference)	`icd_code`	—

Gold Layer & Reporting

Dimension Tables

Table	Contents
`dim_patient`	Patient demographics — gender, anchor age, anchor year group, date of death
`dim_diagnosis`	ICD code lookup enriched with long titles from `d_icd_diagnoses`

Fact Tables

Table	Contents
`fact_admission`	Admission events with 30-day readmission flag (window function logic)
`fact_diagnosis`	Diagnosis events per admission
`fact_procedure`	Procedure events per admission
`fact_prescription`	Medication orders per admission
`fact_transfer`	Ward / ICU transfer events

Reporting Views (Power BI source)

View	Purpose
`vw_admission_overview`	Admission volumes, types, lengths of stay, mortality
`vw_diagnosis_analysis`	Top diagnoses, primary vs secondary breakdown
`vw_prescription_analysis`	Medication volumes by drug and route
`vw_transfer_analysis`	Care unit movement patterns
`vw_procedure_analysis`	Procedure frequency and trends

Technologies & Concepts

Databricks

Lakehouse development environment

Unity Catalog

Data organisation and governance

Unity Catalog Volumes

Raw file storage

Apache Spark

Distributed data processing

PySpark

ETL transformations

Spark SQL

Analytical querying

Delta Lake

Managed analytical tables

Medallion Architecture

Bronze, Silver and Gold processing

Window Functions

30-day readmission logic

Data Quality Testing

Validation and integrity checks

Dimensional Modelling

Facts and dimensions

Databricks Jobs

Pipeline / DAG orchestration

SQL Warehouse

BI query endpoint

Power BI

Interactive analytics and dashboards

DAX

Business metrics

DAX Measures

The Power BI dashboard defines 18 DAX measures across four report pages, querying the Gold-layer SQL views. Canonical definitions are in powerbi/mimic_powerbi_dax_measures.md.

1. Hospital Overview

Total Patients =
DISTINCTCOUNT(vw_admission_overview[patient_id])

Total Admissions =
DISTINCTCOUNT(vw_admission_overview[admission_id])

Average LOS =
AVERAGE(vw_admission_overview[length_of_stay_days])

In-Hospital Deaths =
CALCULATE(
    DISTINCTCOUNT(vw_admission_overview[admission_id]),
    vw_admission_overview[died_in_hospital] = TRUE()
)

30-Day Readmissions =
CALCULATE(
    DISTINCTCOUNT(vw_admission_overview[admission_id]),
    vw_admission_overview[is_30_day_readmission] = TRUE()
)

Readmission Rate =
DIVIDE(
    [30-Day Readmissions],
    [Total Admissions],
    0
)

Format Readmission Rate as a percentage.

2. Diagnosis Analysis

Diagnosis Records =
COUNTROWS(vw_diagnosis_analysis)

Diagnosed Patients =
DISTINCTCOUNT(vw_diagnosis_analysis[patient_id])

Primary Diagnoses =
CALCULATE(
    COUNTROWS(vw_diagnosis_analysis),
    vw_diagnosis_analysis[is_primary_diagnosis] = TRUE()
)

3. Medication Analysis

Total Prescriptions =
COUNTROWS(vw_prescription_analysis)

Patients Receiving Medication =
DISTINCTCOUNT(vw_prescription_analysis[patient_id])

Unique Drugs =
DISTINCTCOUNT(vw_prescription_analysis[drug_name])

Avg Prescription Duration =
AVERAGE(
    vw_prescription_analysis[prescription_duration_days]
)

4. Hospital Operations

Total Transfers =
COUNTROWS(vw_transfer_analysis)

Transferred Patients =
DISTINCTCOUNT(vw_transfer_analysis[patient_id])

Avg Transfer Duration =
AVERAGE(vw_transfer_analysis[transfer_duration_hours])

Total Procedures =
COUNTROWS(vw_procedure_analysis)

Procedure Patients =
DISTINCTCOUNT(vw_procedure_analysis[patient_id])

MIMIC Clinical Analytics
Project Report