Hospital year-end capstone — board quality dashboard

The board meets Wednesday

A hospital operations star schema with 5 tables, 5,300+ rows, and 12 planted data quality issues. Duplicate patients that inflate readmission rates, ED department name variants that split your metrics, mixed date formats, and satisfaction scores that include impossible values.

Query a hospital operations database with planted EHR data quality issues, build CMS-ready reports, and learn to think like a provider-side data analyst who never ships a wrong metric.

Answer questions from the CMO, VP of Operations, and CEO about readmissions, wait times, and patient outcomes.

Real Hospital Scenarios

Duplicate patients, inconsistent department names, mixed date formats, and bad values that silently break your analysis.

EHR Data Quality Issues

Readmission rates, length of stay, ED throughput, utilization, CMS methodology, and age bucketing from DOB.

Provider-Specific Skills

Every mission maps to a real provider-side analyst task — quality scorecards, CMS dashboards, and provider performance reports you’ll see on the job.

Portfolio-Ready Skills

The hospital just failed its quality review.

Meet the patients

How many departments do we have?

Total encounter volume

Encounters by department

Insurance mix

Who's currently admitted?

Top 10 diagnoses

ED wait time overview

Departments with no encounters

Provider roster with red flags

Average length of stay by department

ED throughput — the real numbers

Readmission rate by insurance type

Duplicate patient audit

Patient age at admission

Rank specialties by encounter volume

ED wait times by day of week

Month-over-month encounter trend

Department utilization rates

Provider complication rates

Readmission cohort deep dive

Patient satisfaction trend with moving average

Top diagnoses year-over-year

Department scorecard

The CMS Quality Dashboard

Inpatient cohort returns matrix

Length-of-stay quartiles by department

HRRP-style readmission penalty exposure

Patient satisfaction × readmission gap by department

patient_id

first_name

last_name

date_of_birth

gender

insurance_type

zip_code

primary_language

is_deceased

dim_patients

department_id

department_name

department_type

floor

bed_count

manager_name

dim_departments

provider_id

specialty

hire_date

is_active

dim_providers

metric_id

encounter_id

metric_type

metric_value

measurement_date

reported_by

fact_quality_metrics

admit_date

discharge_date

diagnosis_code

diagnosis_description

encounter_type

discharge_disposition

fact_encounters

icd_code

icd_description

ccsr_category

ccsr_description

body_system

is_chronic

hcc_flag

dim_icd

diagnosis_id

dx_type

poa_indicator

dx_rank

fact_diagnoses

Hospital year-end capstone — board quality dashboard

The Brief

You'll practice