Snowflake - Pharmacy Client

Posted by Encora on May 24, 2023 8:57:41 AM
Encora’s approach drove significant improvements across the client’s eCommerce KPI dashboard.

Topics: HealthTech

Industry Context

Our client provides a range of products and services to both individual and chain pharmacies across the United States, enabling them to handle prescription processing, make strategic operational and financial decisions, focus on patient-centered clinical care, and implement wellness programs.

This client was one of the early adopters of Snowflake after they came out of stealth mode in 2014 which allow the team to explore and leverage the emerging features, and in some cases request for new elements.

PH Industry

Client Challenge

  • Our client aimed to enhance its business intelligence by collecting information on all their pharmacies and presenting the analysis to them via a SaaS platform 
  • The existing data warehouse that was hosted onpremises was unable to analyze data from multiple pharmacies or an entire chain of pharmacies
  • Lack to ability to perform ad hoc queries
  • Processing larger volumes of data was becoming difficult and expensive, as it would require the acquisition of additional hardware
PH Challenge

Approach

  • Envision: Product Management, and implementation of JIRA for maintaining epics and user stories.
  • Enable: Cloud Services, data visualization, bulk data ingestion, and continuous data ingestion (using snow pipes). Platform Modernization, provided data solutions using a modern data stack that includes SnapLogic for data ingestion, Snowflake as the data warehouse, and Looker as the reporting layer.
  • Engineer:  Product Engineering & Development, ETL processes design, development, testing, QA Automation. DevOps, the DevOps team has achieved single-click deployment which includes provisioning the server from VMware, deploying the configuration servers, fetching, and deploying the application artifacts. Data Science & Engineering, leveraged SnapLogic to help clients ingest data from various sources into the data warehouse. Quality Assurance, design, and implementation of Quality Engineering infrastructure. Use of SnapLogic and Looker API for automated testing and creation to ensure consistent results.
  • Engage:  Digital Experience intuitive design, agile processing power, enhanced safety features, ease of use, and flexible architecture.
PH Approach

Results

  • Pharmacies can now obtain in-depth understanding of their operations, finances, and clinical decisions that prioritize patients
  • Due to Snowflake's seamless integration with Looker, optimized usage of SQL, and minimized joins, Looker was able to attain the targeted query performance using a warehouse that was half the size of its competitors
  • Users can utilize a unified interface to perform various tasks such as logging into accounts, creating new databases and warehouses, and managing permissions
  • Our client is now able to identify the top-performing technicians in a pharmacy with the lowest rates of errors, and subsequently improve their utilization rates
  • Initial data migration decreased from 1800 minutes to 12 minutes after Snowflake
  • EDW processing decreased from 780 minutes to 7 minutes after Snowflake
PH  Results
Pharmacy metric -99.4%

Initial data migration

Pharmacy metric -99.1%

EDW processing