An end-to-end healthcare informatics and data engineering pipeline demonstrating how to architect, seed, and query an enterprise-level clinical reporting warehouse. This project replicates an Epic Clarity relational database architecture, mapping complex patient, provider, scheduling, and diagnostic transactional workflows. Utilizing a dual-language pipeline (SQL + R), the data engine moves from initial database normalization to high-ROI operational reporting, culminating in a publication-grade data visualization that tracks patient care-access latency.

Overview: Healthcare delivery networks frequently suffer from severe resource imbalances—certain clinics experience scheduling bottlenecks and clinician burnout, while others operate significantly under capacity. This project demonstrates a complete data engineering and clinical operations workflow designed to automate utilization auditing. Key Methodologies & Technologies Demonstrated: Relational Database Simulation: Designed and built an in-memory SQLite database utilizing DBI and RSQLite to establish a relational schema of transactional EHR encounters, scheduling logs, and provider registries. SQL Query Optimization: Engineered complex multi-table joins and Common Table Expressions (CTEs) to aggregate raw patient-facing minutes into clinical hours and map them against scheduled shift durations. High-Fidelity Reporting: Created a highly polished, executive-ready horizontal bar chart in ggplot2 featuring explicit target thresholds, optimized color scales, and precise label alignment to instantly highlight capacity gaps. Strategic Action Plan: Delivered actionable recommendations to shift capacity from under-utilized regions (North Mankato) and safeguard high-volume clinics (St. Cloud Urgent Care) against provider attrition.