HEALTHCARE ANALYTICS ENGINEERING CASE STUDY
CareFlow Health Analytics
A healthcare operations analytics model that separates scheduling, care delivery and inpatient activity so each business event can be measured at the correct grain.
Tools used
- SQL
- dbt
- BigQuery
- Python
- GitHub Actions
01 / PROBLEM
Related healthcare events still represent different business processes.
The project models patient scheduling, completed visits, inpatient admissions, facility capacity and patient return behaviour. The main modelling challenge is keeping events separate when their grains and meanings differ.
An appointment is not the same as an encounter, and an encounter is not the same as an admission. Treating them as one event would distort no-show, waiting-time, visit-volume and inpatient metrics.
02 / REPORTING OUTPUT
Healthcare operations snapshot
Metrics calculated from the deterministic synthetic dataset.
03 / ARCHITECTURE
Source events flow through distinct transformation layers before reporting.
Raw operations
Patients, facilities, clinicians, appointments, encounters and admissions.
Standardise
Identifiers, timestamps, categories and explicit source fields.
Facts and dimensions
Separate appointment, encounter and admission facts plus reusable dimensions.
Reporting
Care KPIs, patient engagement, department performance, readmissions and occupancy.
04 / KEY DECISIONS
The most important choices are about event meaning, time and denominator quality.
Appointments and encounters stay separate
An appointment represents a scheduled booking. An encounter represents completed care activity. A cancelled or missed appointment should still exist for scheduling analysis without becoming an encounter.
Centralise appointment status logic
The intermediate appointment model creates Boolean fields for completed, no-show and cancelled outcomes so downstream marts do not repeatedly rewrite status logic.
appointment_status = 'COMPLETED' as is_completed
appointment_status = 'NO_SHOW' as is_no_show
appointment_status = 'CANCELLED' as is_cancelledWait time starts at arrival
Waiting time measures the period from patient arrival to consultation start. Consultation duration is handled separately so operational delay and care duration are not mixed together.
Use the patient's next admission
Admissions are ordered by patient and time. A window function identifies the next admission, allowing the current admission to be evaluated for a possible 30-day readmission.
Only mature discharges enter the readmission denominator
Discharges near the end of the dataset do not have a full 30-day period in which a later admission can be observed. The reporting mart therefore excludes those incomplete follow-up records from the denominator.
Bed occupancy is calculated at facility-day grain
Admissions are expanded across occupied calendar dates, then occupied beds are compared with licensed facility capacity for each day.
05 / METRIC DEFINITIONS
Operational metrics are defined upstream in dbt rather than recreated in dashboards.
Scheduling and patient flow
- Daily Active Patients
- distinct encounter patients per day
- Monthly Active Patients
- distinct encounter patients per month
- No-show rate
- no-shows / booked appointments
- Wait time
- arrival to consultation start
- Consultation time
- consultation start to end
Inpatient and engagement
- Length of stay
- admission to discharge
- 30-day readmission
- eligible discharge with next admission within 30 days
- Bed occupancy
- occupied beds / licensed beds
- Monthly patient return
- returning / prior-month active patients
06 / DATA QUALITY
Tests catch impossible event sequences and broken reporting assumptions.
Structural checks
- Business keys are unique and non-null.
- Appointments reference valid patients, clinicians and facilities.
- Appointment status values are controlled.
- Declared composite grains remain unique.
- Percentage metrics remain between 0 and 1.
Business-rule checks
- Consultation cannot start before arrival.
- Discharge cannot occur before admission.
- Readmission gaps must be valid.
- Returning patients cannot exceed the comparison population.
- Occupied beds cannot exceed licensed capacity in the synthetic model.
The readmission metric is an operational modelling example, not a clinical quality standard.
A production clinical measure could require planned-readmission exclusions, diagnosis logic, transfer rules, risk adjustment and other clinical definitions. The project intentionally keeps that boundary explicit rather than presenting the simplified metric as a medical-quality measure.
07 / DELIVERY
Reproducible synthetic data and automated checks keep the project reviewable.
Reproducible inputs
A deterministic Python generator creates the synthetic source files. dbt seed loads those files into the raw schema before the transformation models are built.
GitHub Actions
Repository checks enforce SQL conventions and generate the synthetic data. BigQuery-backed dbt steps can run when the required repository secrets are configured.