Scaling Predictive Census with Azure Data Engineering

Business Challenge

Situation

A healthcare provider faced significant challenges in migrating its Predictive Census application from AWS to Azure. The key issues included:

Managing large-scale HL7 message ingestion and census calculation

Ensuring high accuracy while handling vast volumes of HL7 messages.

Processing inefficiencies

Difficulties in efficiently storing, processing, and forecasting patient census data.

Integration complexities

Seamless data sharing with other systems for actionable insights.

Manual onboarding

A labor-intensive process for onboarding new clients, slowing down adoption.

Goals

The primary objectives were:

Enhancing data accuracy and reliability by optimizing HL7 message processing.
Automating census forecasting to improve efficiency and reduce manual effort.
Building a scalable architecture to support future growth and seamless client onboarding.
Ensuring smooth integration with other enterprise systems for real-time insights.

A robust Azure-based data engineering solution was implemented to address these challenges effectively.

Solution

A robust Azure-based data engineering solution was implemented, leveraging the following components:

Data Integration Framework

Azure Event Hubs to source HL7 messages.

Azure Data Factory (ADF) pipelines nad function apps for ETL processes, including parsing, validating, and storing HL7 messages in Postgres database.

Blob storage for intermediate and archival data storage.

Census Calculation Logic

Designed procedures to aggregate HL7 messages into encounters and calculate census history using Postgres SQL functions.

Implemented percentile-based length-of-stay filters to handle incomplete data effectively.

Scalable Architecture

Replaced AWS services (S3, Lambda, MSK, etc.), Kafka and MongoDB with Azure counterparts to improve efficiency and scalability.

Integrated ML forecasting models using Azure Machine Learning for short term – 7 days and long term - 120-day census predictions.

Client Integration

Configured other system procedures to seamlessly publish census history and forecasts into their reporting tables.

Technology & Tools

Azure Components

Event Hubs, Data Factory, Function Apps, ADLS, Postgres database.

Programming

Python (for HL7 parsing), SQL for database procedures.

Machine Learning

Azure ML for training and deploying census prediction models.

Business Outcomes

Efficiency Gains

Reduced manual effort in processing HL7 messages by automating end-to-end data pipelines.

Improved Accuracy

Enhanced census calculations with data validation and robust encounter aggregation logic.

Scalability

Future proofed architecture, capable of handling growing data volumes and new clients.

Customer Satisfaction

Delivered accurate and timely census forecasts, enabling other systems to optimize staffing decisions.