Appointments & patient flow
Daily Active Patientsdistinct daily patients
Monthly Active Patientsdistinct monthly patients
No-show rateno-shows ÷ bookings
Wait timearrival → consultation
HEALTHCARE ANALYTICS ENGINEERING
CareFlow Health Analytics
A synthetic healthcare operations project using dbt and BigQuery SQL to model appointments, patient encounters, inpatient admissions, facility capacity and patient return patterns.
Data note: all patient, clinician, facility and hospital records are fictional and generated for this project. No real healthcare or employer data is used.
SYNTHETIC DATASET
Healthcare operations snapshot
Appointments2,500Jan to Jun 2026
Patient encounters1,818completed visits
No-show rate13.16%appointment KPI
Average wait time44.15 minarrival to consultation
Admissions309inpatient events
Average length of stay4.83 dayssynthetic benchmark
30-day readmission22.18%complete 30-day follow-up
Facilities5licensed capacity modelled
MODEL STRUCTURE
Separate scheduling, care delivery and inpatient events.
01 / SOURCES
Raw operations
Patients, clinicians, facilities, appointments, encounters and admissions.
02 / STAGING
Standardise
Explicit fields, consistent identifiers, timestamps and categories.
03 / CORE
Facts & dimensions
Appointment, encounter and admission facts plus patient, clinician and facility dimensions.
04 / MARTS
Reporting
Daily care KPIs, patient engagement, department performance, readmissions and bed occupancy.
KEY METRICS
Operational measures defined in the modelling layer.
Inpatient operations
Length of stayadmission → discharge
30-day readmissionnext admission ≤ 30 days
Bed occupancyoccupied ÷ licensed beds
Return ratereturning ÷ prior-month patients
Data quality
Tests cover primary keys, source relationships, appointment statuses, timing order, readmission consistency, percentage ranges and facility capacity. Test queries return the exact records that violate a rule.
Incremental appointment processing
The appointment fact uses BigQuery MERGE logic and reprocesses a three-day window so recent delayed status updates can be captured without rebuilding the entire fact table.
Appointments vs encounters
Appointments and encounters are separate because a booking can be completed, cancelled or missed. Only completed care activity becomes an encounter. This keeps scheduling metrics separate from consultation metrics.
Readmission scope
The readmission mart only includes discharges with a complete 30-day follow-up window. It is an operational modelling example, not a clinical quality measure, and does not apply diagnosis-based exclusions or risk adjustment.