The Customer
A Fortune 500 national healthcare services company.
The Project
Data Warehouse Upgrade to Enable Scalable Analytics
Overview
This Fortune 500 Healthcare Services Company engaged Improving to upgrade their underperforming data warehouse into a modern data platform enabling scalable analytics. Their new, more valuable data architecture provides a higher-quality data environment, resulting in faster queries and data updates.
Challenge
Our client relied on a SQL Server instance to support their business operations, essentially using custom data analyses through a web portal. With 10s of millions of rows, thousands of lines of ETL code, and dozens of different search and aggregation patterns, the SQL Server was overloaded, which resulted in latency in live queries and delays in data updates. This complexity was also apparent in the data and code management for all the web and reporting systems. Additionally, the passing of new healthcare regulations to provide better patient/provider financial protections accelerated the demand for our client’s services, and their team needed additional support.
Solution
Our client needed to scale their reporting solution to accommodate more business cases, more data sources, and use more real-time sources. Improving's data engineering team worked to accelerate the business and technical scale, improved the reporting structure, and designed a modern data warehouse solution in Snowflake. Improving provided multiple resources to modernize its data platform on two fronts:
Migrate to a scalable modern data warehouse (Snowflake) to reduce the analytical load on the operational SQL Server
Accelerate SQL Server ELT development and warehouse performance to handle more use cases with higher complexity
Solution Part 1: Snowflake Migration
Improving recognized the scalability issues and complexities of using a transactional RDBMS for such complex reporting, especially since much of the reporting needed to be optimized using denormalization and pre-aggregations, which reduced the fidelity of the data used in reporting. Improving recommended Snowflake and brokered a conversation between our client, Snowflake, and internal architects. Improving is rebuilding the data warehouse in Snowflake using modern data engineering tools and practices, with the following highlights:
Ingested data from multiple sources (Salesforce, CDC, Kafka, BDPaaS, Hive, SQL Server)
Integrated multiple technologies as part of the stack (Snowflake, Kafka Connect, Talend, Airflow, Kubernetes)
Extended API connectivity to use Snowflake, in addition to SQL Server
Implemented data patterns storage (raw → consumption layers), historical reporting (time travel)
Solution Part 2: SQL Server ETL/Warehouse
Improving onboarded quickly to own and develop SQL Server ETL improvements, expand the warehouse, and organize the development process. We identified that with the speed of development, a lot of technical debt was left behind. While adding business features, Improving implemented a modern development process including:
Helped improve the agile processes (story writing, acceptance testing, and story organization) to fit data analytics use cases better
Implemented source control and change management for the database, procedure logic, and ETL in Github
Migrated deployments to use automated CI/CD process in Azure DevOps instead of cumbersome manual deployments
Developed Python-based automated testing framework used for test-driven development of new database features
Refactored ETL and slow-running processes using clean coding principles
Implemented data cataloging microsite using markdown and Github pages for users to discover, search and understand database technical design
Trained team on data engineering best practices like test automation, CI/CD, and documentation