The Plan Staff Services process handled encounter data from the clinical team for submission to the state to secure health plan reimbursement. The original process was only partially automated by a prior team that had since been let go, leaving behind a system prone to significant errors. This cumbersome workflow required extracting data from the Snowflake data warehouse, compiling it into a flat file, submitting it to the health plan for review, re-uploading it to the warehouse, and then producing a differently formatted flat file for submission to the state’s analytics team — a biweekly process riddled with inefficiencies and errors that reduced the reimbursement received for services provided.

I developed a completely new SSIS-based ETL process that streamlined the workflow to pull data from Snowflake only once, on a weekly schedule, and ingest it directly into SQL Server. Using stored procedures and SSIS script tasks, the solution automatically generates the flat file for the state and sends targeted email notifications to the clinical team regarding any service record errors. The process leverages ECG services to securely transmit the flat file to the state, eliminating the final manual step.

This fully automated, scheduled process was validated in phases during development to ensure a seamless transition from the old system. By reducing run frequency intervals and removing manual handling, the solution cut processing time to about one hour per week and improved accuracy by over 60%, ensuring faster, more reliable reimbursement for services rendered.