Valley Regional Health: The Readmission Dashboard That Dropped 12% of Patients
Valley Regional Health operates three acute-care hospitals. The quality team built a monthly 30-day readmission dashboard by joining an admissions fact table to a patient dimension table on medical_record_number. The join is an inner join. Data profiling was skipped because the project was behind schedule. When the dashboard went live, it reported a 4.1% readmission rate, while the finance department's quarterly report, built from a different extract, reported 7.6%. The quality lead insists the dashboard is correct because it refreshes nightly without errors. An analyst notices that 12% of admission rows failed to match a patient record. She also finds that in March the source system changed medical record numbers from 8 digits to 10 digits with two leading zeros, and that the dimension table contains both formats for some patients. The executive sponsor wants the dashboard published for the board meeting in five days.
1. Diagnose the most likely cause of the discrepancy and identify the stage of the analysis process where it should have been caught.
Read the worked answer
The most likely cause is the inner join combined with the medical record number format change. If 12% of admission rows fail to match, an inner join silently drops them. Those dropped rows are not a random sample: they are likely concentrated among patients whose IDs are in the old 8-digit format or who have duplicate dimension records. If readmitted patients are over-represented among unmatched rows, the dashboard undercounts readmissions, which is consistent with 4.1% versus finance's 7.6%. The nightly refresh proves only that the job ran, not that the logic is correct.
This should have been caught during data understanding and data preparation, before modelling or reporting. Data profiling would have shown the ID length distribution, match rate, duplicates, and null patterns. A post-join reconciliation - total admissions before and after the join, and matched versus unmatched counts - is a basic control. The fact that two reports differ by nearly a factor of two is itself a signal that should trigger investigation, not a defence of the dashboard.
2. Specify the validation tests and acceptance criteria you would run before publishing, including how to handle the 12% unmatched rows.
Read the worked answer
Run these tests before publishing:
- Row-count reconciliation: admissions in source = matched + unmatched + rejected. Report every category.
- Key quality profile: ID length, leading zeros, duplicates in the dimension, and patients with multiple records. Quantify how many admissions are affected.
- Format normalisation test: standardise to 10 digits and re-run the match rate; compare results.
- Fuzzy or deterministic fallback: match on additional fields (date of birth, sex, admission date, facility) only with a documented rule and manual review for ambiguous cases.
- Recompute the measure both ways: inner join versus validated match, and compare against finance's extract at patient and month level.
- Reconcile definitions: confirm whether both reports use the same 30-day window, same index admission exclusion, and same planned/unplanned readmission rule.
Acceptance criteria: unmatched rate below an agreed threshold (for example, under 1%), documented reason for every remaining unmatched category, and a variance explanation against finance within a stated tolerance. Do not fill the 12% with guesswork. Publish a corrected figure with a data-quality note.
3. Draft the message to the executive sponsor: what to say about the five-day deadline, and what governance controls prevent recurrence.
Read the worked answer
Tell the sponsor: the dashboard is not ready for the board because it currently drops 12% of admissions and reports roughly half the readmission rate in finance's extract. Publishing it would create a false assurance about patient safety and could misdirect quality investment. Offer a five-day path: normalise the ID format, re-run the join, reconcile to finance, and publish with a clear 'under validation' label if the match rate cannot be fully resolved. If the match rate remains poor, present the validated finance figure and the known limitation rather than the dashboard.
Prevent recurrence with controls: a data-quality dashboard that tracks match rate and ID format drift, a schema-change notification from the source-system owner, a peer review of join logic, and a reconciliation step in the monthly close. Assign an owner for the patient dimension and require sign-off before metrics go to the board.