CASE STUDY Medical

Disrupting the Pharmacy Benefit Management Market

INTRODUCTION

Customer
Overview

The company partners with its clients to provide the highest quality prescription healthcare services at the lowest price.

As a PBM responsible for developing and maintaining formularies, contracting with pharmacies, negotiating discounts and rebates with drug manufacturers, as well as processing and paying prescription drug claims, the Health Tech company’s ability to access and manage large amounts of data is critical.

  • Founded in 1998

    Founded in 1998

  • $3B+ annual managed pharmacy program drug spend

    $3B+ annual managed pharmacy program drug spend

  • 20 years as a forward- thinking industry leader

    20 years as a forward- thinking industry leader

Business Goals

CLIENT OBJECTIVES

Higher Service Levels
Higher Service Levels

address new and increasing customer requirements

Increased operating efficiencies
Streamline Operations

make the company more agile and efficient

Digital Transformation
Digital Transformation

upgrade IT infrastructure to support new market demands

streamlining software testing
the challenge

TO MODERNIZE THE business model

With more than 20 years as a forward-thinking health and technology industry leader, the company had experienced first-hand, the pain of being tied to antiquated, traditional PBM legacy systems – long implementation timelines, plan design changes requiring IT and development teams, black-box pricing and more.

They wanted to fundamentally change its business model to solve these challenges and more with a new cloud-based enterprise platform solution that would simplify pharmacy benefit design, management, and execution in an infinitely scalable environment, including the underlying databases and analytic tools.

A need to purchase more databases TO KEEP UP

  • In traditional systems, the Health Tech company would have needed to purchase more and more databases to keep up with expanding datasets and users. In addition, the limitations of traditional database architectures made each successive expansion progressively less effective.
  • To design a platform and analytic environment that delivers on the promise of expanded data access and insight, the company believed that it had to completely discard traditional data architectures like its existing Microsoft SQL solution and look at more flexible, affordable and scalable solutions based in the cloud.

The Solution

Working
AS A TEAM

The company’s IT team engaged with Infostretch’s digital engineering team to help diagnose and solve for the best path forward. Infostretch’s extensive digital skillset, and specific experience in healthcare was an important factor in the decision.

After working with the Health Tech company’s team, and taking into account all the factors involved, the Infostretch team recommended a cloud-based data warehouse and data analytics solution built on AWS and Snowflake.

Not only would this approach be able to handle and scale easily with the Health Tech company’s growing data management needs, it would also enable the company to automate many of the manual business functions which were core to the problem.

  • The scope of Infostretch’s engagement included:

  • Ideation and blueprinting
    Ideation and blueprinting of their revised workflows with a complete Proof of Concept of the future state user experience and underlying architecture to support the scale the Health Tech company required
  • Assistance employing DevSecOPs
    Assistance employing DevSecOPs and Cloud Native process to bring efficiency and scale to its PBM operations
  • Providing data to the Health Tech
    Providing the Health Tech company’s clients with access to real-time performance data to better understand and manage their business
  • Development of an implementation wizard
    Development of an implementation wizard with a new, flexible, affordable and scalable architecture and analytic environment that delivered on the promise of expanded data access and insight
  • Seamless migration to AWS
    Seamless migration from Microsoft SQL to AWS
  • Automation of key business functions
    Automation of key business functions such as invoicing and margin calculation, eliminating expensive manual efforts and errors

After completing the initial project discovery phase and a proof of concept, Infostretch laid out a five step execution plan for The Health Tech company
migration-of-legacy
Migration of Legacy ETL to Matillion Jobs

migration-of-legacy
Migration of Data Analytics Reports pointing to Snowflake

migration-of-legacy
Automate Invoice Margin Calculation

migration-of-legacy
Real-Time Data Ingestion into Data Warehouse

migration-of-legacy
Providing Technical Support

STEP 1

MIGRATION OF LEGACY ETL TO MATILLION JOBS

Existing ETL jobs were being implemented using Microsoft SSIS and Informatica ETL.

With the new solution, all jobs had to be migrated to Matillion Jobs to extract, transform and load data into Snowflake. This included around 60+ jobs to be migrated to support different business functions.

Key steps included:
  • Identifying Data Sources

    Identifying Data Sources

  • Implementing Matillion Jobs

    Implementing Matillion Jobs

  • Testing Jobs

    Testing Jobs

  • Understanding existing implementation

    Understanding existing implementation

STEP 2

CREATION OF DATA ANALYTICS REPORTS POINTING TO SNOWFLAKE

Approximately 25+ reports were developed to point to Snowflake Data Warehouse.

Core activities included:
  • Understanding existing queries used for reporting

    Understanding existing queries used for reporting

  • Migrating queries to support Snowflake syntax

    Migrating queries to support Snowflake syntax

  • Validating reports

    Validating reports

STEP 3

AUTOMATION OF INVOICE MARGIN CALCULATION

Price management was a key service provided by the Health Tech company including the calculation of invoice margins.

To date this had been a manual process – with contract rules maintained in Excel spreadsheets and invoice margins calculated by finance personnel for each contractor every 15 days. Infostretch developed a custom module using Matillion Jobs – automating the entire process from calculating the Pharmacy invoicing margin to sending invoices to contractors.

Key steps included:
  • Understanding and incorporating invoice calculation rules

    Understanding and incorporating invoice calculation rules

  • Prepare Snowflake queries to calculate  invoice pharmacy rules

    Prepare Snowflake queries to calculate invoice pharmacy rules

  • Design and implement the UI Portal to manage the rules

    Design and implement the UI Portal to manage the rules

  • Implementing Matillion Jobs

    Implementing Matillion Jobs

STEP 4

REAL-TIME DATA INGESTION INTO DATA WAREHOUSE

Existing jobs were transferring data on a daily basis. As a result, there were no real-time updates being made to the data warehouse or in the reports.

This phase involved:
  • Implementing CDC mechanism

    Implementing CDC mechanism

  • Configuring Source Kafka Connector

    Configuring Source Kafka Connector

  • Implementing transformation jobs

    Implementing transformation jobs

  • Configuring Sink Kafka Connector - Snowflake

    Configuring Sink Kafka Connector – Snowflake

STEP 5

PROVIDING
TECHNICAL SUPPORT

  • Technical support

    In parallel to the four phases above, Infostretch provided (and continues to provide) technical support for each implemented component

Results

Key Outcomes
& Achievements

Increased Revenues
Increased
Revenues

50% revenue growth
within first 12 months

Higher Service Levels
Higher
Service Levels

Reduced customer on-boarding time – from 90 days to a few hours

Operating Efficiency
Faster Cycle
Time, Reduced Costs

Increased automation, agility and scale; easy access to performance data