The Transition of Care process was a labor-intensive monthly workflow involving both the clinical and analytics teams. Initially, there was no automation: the source file from the state was downloaded and validated manually, member demographic, claims, and prior authorization data were collected, cleaned, and formatted, and the results were manually loaded into four separate Excel files for other health plans in the state. This end-to-end process, including initial and final validation, had to be completed within seven business days, often running up against or missing the deadline.

I designed and implemented an SSIS-based ETL pipeline that automatically ingests the source file, integrates it with existing SQL Server data, validates it against predefined rules, and generates health plan–specific Excel files. The solution sends automated email notifications when the source file is ingested, including a breakdown of members gained or lost, their associated health plans, and any discrepancies requiring adjudication with the state. A second automated notification is sent when the Excel files are generated.

This solution was tested over three months with support from analytics and clinical teams, during which issues were addressed, and the workflow was streamlined. The automation reduced manual effort by over 30 hours per week, cut the turnaround time from seven days to three, and improved reporting accuracy by at least 15%.