--- title: "Pharmacy Data Integration and Automated ETL Testing | Smart Data" url: https://www.smartdata.net/case-studies/pharmacy-data-integration-retail-health description: "Smart Data moved pharmacy data from disparate retail systems into a patient case-management platform in near real time, with far less ETL testing effort." source: "Smart Data (smartdata.net). Markdown copy of the page; the HTML page is canonical." --- # Pharmacy data moved into a shared care platform, tested by 5 people instead of 80 Smart Data built the near real-time data integration that let pharmacists share care plans with the whole patient care team, and an automated test process that cut the testing effort from 80 people to 5. Built onSQL Server 80 to 5 people needed for the same testing, after automation Industry Healthcare Engagement Integration A national retailer with in-store pharmacies and a patient case-management company partnered to improve pharmacy patient care. Together they put in place a Pharmacist Care Plan: one record of the patient's, pharmacist's and care team's concerns and goals for getting the most out of medications. Making that work meant moving large volumes of pharmacy data into the case-management software quickly and accurately. SDS, a Smart Data company, was asked to design and test the integration. ## The challenge - Pharmacists needed to exchange information in real time with the entire patient care team - Data had to come from several disparate retail pharmacy systems - Records needed multiple transformations from the source formats before loading - High volumes created complexity and performance pressure - Patient data had to stay protected under HIPAA throughout development and testing ## The solution A team of seven developers built and hardened the pipeline: - Automated ETL in SQL Server Integration Services (SSIS) pulls data from pharmacy system APIs, transforms it and loads it into the case-management software - Data from pharmacies and medical clinics is collected in near real time, so patients and prescribers can view their records - New processes for managing and testing SSIS packages - Test and development environments brought to parity with production, using sanitized data that protects HIPAA information while still reflecting real production scenarios - An automated testing process for the SSIS packages ## The results Near real-time pharmacy and clinic data available to the full patient care team Testing accuracy and scale increased by an order of magnitude **5**people able to do the testing that previously took 80 The test architecture was adopted by other organizations, raising their productivity too ## Where this is going We build data pipelines that move sensitive data between systems quickly and prove they work before production. If that is your situation, talk to us about scope. Client HealthcareRetail and distribution Platform and technology - ETL & Data Pipelines - SSIS - SQL Server - Azure DevOps The result ## The same testing, done by 5 people instead of 80 805 people needed for the same testing, once the SSIS packages were tested automatically - Pharmacy systems and clinics - Automated ETL in SSIS - Case-management platform - One Pharmacist Care Plan ## Working on something similar? Tell us what you are building and what it has to connect to. Here is what happens after you reach out. ### A short call about the work What runs today, what needs to change and the date you are working toward. ### An engineer looks at what’s running Scope comes from your systems as they are, before anything is priced. ### You get a written proposal Covering scope, team, approach and price. [info@smartdata.net](mailto:info@smartdata.net)[(937) 886-9166](tel:+19378869166)