From SQL Server to Snowflake: How We Helped an Award-Winning Healthcare System Unlock Pharmaceutical Intelligence

TL;DR

• A healthcare brokerage firm relied on fragmented SQL Server pipelines and manual workflows for pharmaceutical, claims, and benefits reporting, creating reporting delays as the business grew.
• BlueCloud modernized the environment using Snowflake, Power BI, dbt, and Azure, consolidating ETL processes, rebuilding data models, and automating executive reporting.
• Predictive analytics and GenAI capabilities were introduced across pharmaceutical data, enabling the organization to move beyond historical reporting to forward-looking insights.
• The result was faster reporting, automated dashboards that reduced manual effort, and a cloud-native data foundation built for AI-driven innovation.

‍

Overview

‍

A leading global insurance brokerage, risk management, and consulting firm partnered with BlueCloud to modernize its business intelligence ecosystem and transform how pharmaceutical, claims, and benefits data were consolidated, analyzed, and visualized — both internally and on behalf of their healthcare clients.  

One of the firm's key clients is a large, award-winning not-for-profit healthcare system, whose pharmaceutical and benefits data sat at the center of this transformation.

Operating within the Healthcare and Life Sciences industry, the firm needed a scalable, cloud-ready analytics environment capable of replacing legacy SQL Server-based reporting systems while improving the quality, speed, and depth of insights delivered to their clients.

BlueCloud delivered a multi-phase modernization initiative centered around Snowflake, Power BI, and advanced data engineering practices. The engagement consolidated fragmented ETL processes, modernized legacy BI systems, enhanced dashboard functionality, and established a scalable analytics framework capable of supporting future AI-driven innovation — for the firm and the healthcare clients they serve. By combining cloud-native technologies, modern data architecture, scalable reporting frameworks, and advanced analytics, BlueCloud helped unlock faster insights, improved reporting performance, and a future-ready analytics foundation across the entire client ecosystem.

‍

The health system's legacy analytics environment had become a significant operational constraint — limiting the speed, accuracy, and depth of pharmaceutical and benefits reporting across the organization. With a mission to serve diverse communities, the demand for timely, reliable pharmaceutical insights had outgrown the infrastructure built to deliver them.

Specific pain points included:

• Fragmented ETL pipelines and outdated SQL Server infrastructure with limited reporting flexibility

• Existing Power BI dashboards that lacked scalability, optimization, and KPI coverage

• Manual workflows that slowed insight delivery across growing pharmaceutical and claims datasets

• No cloud-ready architecture to support future growth or enterprise-wide scalability across a complex, multi-entity health system

‍

BlueCloud designed and implemented a comprehensive modernization strategy spanning data engineering, dashboard development, cloud readiness, reporting optimization, and advanced analytics enablement.  

The engagement was delivered through four connected use cases that together created a scalable analytics ecosystem powered by Snowflake, Power BI, and modern cloud-native technologies.

‍

Use Case 1: Transforming Pharmaceutical and Benefits Reporting with a Pharmacy Benefits Manager Dashboard

‍

The most immediate priority was consolidating the health system's complex pharmaceutical and benefits claims data into a unified, reliable reporting experience.  

The organization needed a centralized framework capable of managing large volumes of drug utilization, claims, member cost, and plan cost data, while improving reporting speed, consistency, and usability for both operational teams and leadership.

BlueCloud developed a Pharmacy Benefits Manager (PBM) Dashboard to meet this need.

Using Snowflake, Snowpark, dbt, Fivetran, Power BI, DAX, SQL Server, and Microsoft Azure, BlueCloud built a scalable analytics environment that automated reporting workflows and delivered improved visibility into drug usage patterns, claims details, member costs, and plan costs. Power BI outputs were embedded directly into PowerPoint presentations, significantly reducing manual effort in executive reporting.

‍

Impact

• Automated dashboards reduced manual reporting work and accelerated business decision-making

• Improved reporting consistency and streamlined executive communications through PowerPoint integration

• Scalable framework built to extend across additional reporting environments within the health system

‍

Use Case 2: Modernizing BI with Power BI and a Cloud-Ready Data Model

‍

The health system's legacy SQL Server-based business intelligence environment was no longer capable of meeting evolving reporting requirements, growing pharmaceutical and claims data volumes, or new KPI demands across an expanding care network.

BlueCloud built a brand-new Power BI reporting environment from the ground up, including a modern star-schema data model with a central fact table and related dimensions fed by Claims and Medispan data.  

The solution improved KPI visibility, enabled significantly faster dashboard performance, and supported more efficient analytics workflows across the organization. The architecture was also designed with a clear path toward full Snowflake Cloud migration from on-premises SQL Server infrastructure, laying the foundation for long-term scalability.

Impact

• Significantly faster reporting performance compared to the legacy environment

• New KPI coverage aligned to evolving operational reporting requirements

• Cloud-ready architecture establishing a clear path to full Snowflake migration

‍

Use Case 3: Optimizing Pharmaceutical Data Insights with Predictive and GenAI Capabilities

‍

With foundational reporting in place, BlueCloud elevated the depth and forward-looking capability of the client’s pharmaceutical analytics. Existing data pipelines required significant optimization to support faster analytics delivery, and visualization capabilities were not keeping pace with the needs of an innovative, data-driven healthcare organization.

BlueCloud rebuilt data ingestion and processing workflows for speed and reliability, implemented advanced Power BI dashboards tailored for pharmaceutical analytics, and developed improved visualization experiences to help clinical and operational users consume and interpret data more efficiently.  

Predictive analytics and GenAI models were then applied across pharma data, moving the organization from historical reporting into forward-looking, decision-grade intelligence for the first time.

Impact

• Faster and more reliable access to pharmaceutical and claims data across the organization

• Improved visualization experiences that reduced time-to-value for operational and clinical teams

• Predictive and GenAI-powered analytics introduced for the first time, enabling proactive pharmaceutical decision-making

‍

Use Case 4: Phase 2 Enhancement of Power BI Dashboards

‍

Following the success of the initial modernization, BlueCloud launched a second phase focused on enhancing Power BI dashboards, optimizing data structures, and improving scalability across the reporting ecosystem.

BlueCloud introduced new dashboard capabilities, restructured existing data tables, and embedded stronger data engineering practices designed to improve long-term reliability and usability across the health system's growing reporting environment.

Impact

• Richer analytics experiences with improved reporting efficiency and stronger system scalability

• Optimized table structures that improved dashboard performance and usability

• Enhanced dashboards delivering more actionable pharmaceutical and benefits insights across the organization

‍

Tech Stack

The modernization initiative leveraged a modern cloud-native analytics stack, including:

Power BI + DAX

Through its partnership with BlueCloud, this award-winning, innovative not-for-profit healthcare system successfully transformed its legacy BI environment into a scalable, cloud-ready analytics ecosystem — improving the quality, speed, and depth of pharmaceutical and benefits intelligence:

• Faster and more reliable reporting across pharmaceutical, claims, and benefits data

• Automated dashboards that significantly reduced manual reporting efforts organization-wide

• Improved KPI visibility and reporting consistency across operational and executive teams

• The scalable framework also created opportunities to extend the solution across additional client environments, supporting broader insight generation and operational cost savings.

• A modernized BI infrastructure providing a strong foundation for predictive analytics and AI-driven innovation

• Modernized BI from legacy SQL Server to cloud-native

‍

Why It Matters

‍

BlueCloud helped an award-winning, innovative not-for-profit healthcare system move from a fragmented, legacy-bound analytics environment to a modern, Snowflake-powered intelligence platform.  

By consolidating ETL processes, modernizing Power BI infrastructure, introducing cloud-native data architecture, and applying predictive and GenAI capabilities across pharmaceutical data, BlueCloud enabled the organization to make faster, smarter, and more confident decisions, establishing a future-ready foundation for continued innovation in healthcare analytics and better outcomes for the communities it serves.

See how BlueCloud helps Healthcare and Life Sciences organizations unlock the full potential of their data with AI-ready analytics on Snowflake.

‍