Healthcare Data & Real-Time Systems
SQL performance work, automated archiving, real-time comms, and Azure data pipelines for a healthcare product.
Overview
Backend and data work for a healthcare platform: making reporting fast, keeping the production database lean, pushing real-time updates to clients, and moving large datasets reliably through Azure.
The Problem
Reporting queries were slow and getting slower as data grew, the production database carried years of cold data it didn't need, clients needed live updates, and large healthcare datasets had to be ingested and cleaned before they were trustworthy.
My Role
Software Engineer — UpfrontHealthCare
- Optimized high-latency SQL reporting queries with indexing, partitioning and execution-plan analysis, significantly improving retrieval performance.
- Built an automated data-archiving system using MySQL stored procedures and partitioning strategies to take cold data off the primary production database.
- Built low-latency real-time communication with WebSockets and Server-Sent Events, handling concurrent connections efficiently.
- Integrated and managed healthcare data pipelines on Azure Data Factory for processing and transforming large datasets.
- Implemented data cleansing and transformation workflows with Azure Dataflows to remove duplicates and improve data quality before storage.
Engineering Challenges
Queries that degrade with scale
Reporting slowed as tables grew. Execution-plan analysis drove targeted indexes and table partitioning so hot queries only touch the partitions they need.
A production DB carrying dead weight
Years of cold rows inflate every scan and backup. Scheduled stored procedures move aged data into partitioned archive tables automatically.
Real-time fan-out
Clients need live updates without polling. WebSockets for bidirectional flows and SSE for one-way streams, chosen per use case to keep connection handling cheap.
Trustworthy ingestion
Raw healthcare data has duplicates and inconsistencies. Azure Data Factory pipelines plus Dataflows cleansing steps normalize and de-duplicate before anything is stored.
Solution
Database layer: indexing + partitioning informed by execution plans, and stored-procedure-driven archiving on a schedule. Application layer: WebSocket and SSE channels for real-time updates. Data layer: Azure Data Factory orchestration with Dataflows for cleansing and transformation ahead of storage.
Key Features
- ›Optimized SQL reporting
- ›Automated, partitioned data archiving
- ›WebSocket + SSE real-time updates
- ›Azure Data Factory ingestion pipelines
- ›Dataflows-based cleansing and de-duplication
Architecture
- 01/Clients (real-time: WebSocket / SSE)
- 02/Application services
- 03/MySQL — indexed + partitioned; archive tables
- 04/Scheduled stored procedures — archiving
- 05/Azure Data Factory + Dataflows — ingest, clean, transform
Technology
Result
Significantly faster reporting, a lighter production database, and reliable ingestion of large healthcare datasets.