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.
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.
Data: all patient, clinician, facility and hospital records are fictional and generated for this project. No real healthcare or employer data is used.
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.
What is the appointment no-show rate?
How long do patients wait before consultation?
Which departments have the highest encounter volume?
What is the average inpatient length of stay?
What percentage of eligible discharges are followed by another admission within 30 days?
How heavily are licensed beds being used?
02 / REPORTING OUTPUT
Healthcare operations snapshot
Metrics calculated from the deterministic synthetic dataset.
Appointments2,500booked events
Patient encounters1,818completed visits
No-show rate13.16%appointment KPI
Average wait44.15 minarrival to consultation
Admissions309inpatient events
Average LOS4.83 dayslength of stay
30-day readmission22.18%complete follow-up cohort
Facilities5licensed capacity modelled
03 / ARCHITECTURE
Source events flow through distinct transformation layers before reporting.
SOURCES
Raw operations
Patients, facilities, clinicians, appointments, encounters and admissions.
→
STAGING
Standardise
Identifiers, timestamps, categories and explicit source fields.
→
CORE
Facts and dimensions
Separate appointment, encounter and admission facts plus reusable dimensions.
→
MARTS
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.
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_cancelled
Wait 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 Patientsdistinct encounter patients per day
Monthly Active Patientsdistinct encounter patients per month
No-show rateno-shows / booked appointments
Wait timearrival to consultation start
Consultation timeconsultation start to end
Inpatient and engagement
Length of stayadmission to discharge
30-day readmissioneligible discharge with next admission within 30 days
Bed occupancyoccupied beds / licensed beds
Monthly patient returnreturning / 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.
PRODUCTION CONSIDERATION
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.