This Fortune 500 Healthcare Services Company engaged Object Partners 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.
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.
Our client needed to scale their reporting solution to accommodate more business cases, more data sources, and use more real-time sources. OPI’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.
OPI provided multiple resources to modernize their data platform on two fronts:
Solution Part 1: Snowflake Migration
OPI 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. OPI recommended Snowflake and brokered a conversation between our client, Snowflake, and internal architects.
OPI is rebuilding the data warehouse in Snowflake using modern data engineering tools and practices, with the following highlights:
Solution Part 2: SQL Server ETL/Warehouse
OPI 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, OPI implemented a modern development process including:
OPI suggested various solutions, helped analyze the best options, recommended the best solutions for this specific situation, and then implemented those solutions. The result for our client is a more valuable data architecture and higher quality data environment.
The benefits of bringing skilled data engineers with experience in modern distributed data processing meant that our client could transition from maintaining existing SQL data to scaling human and system resources more effectively. The development improvements made it possible to scale from 1-2 data engineers to enable multiple resources to contribute in a scalable, quality-focused manner. Specifically:
Most data engineering teams treat testing, documentation, and the agile process as secondary concerns. OPI treats them as primary concerns because they are the foundation of having a trustworthy data system. Clean coding and proper engineering standards increase the velocity of the team in the long term while also improving the reliability and predictability of data. Our consultants build production-ready systems and not just code that gets put in production.
OPI understands that we shouldn’t build complex systems that the client is unable to understand or own. We work very hard to prove they meet the acceptance criteria, i.e., that we built the right system. We’ve seen too many technologists at other clients build novel but very complex solutions, and the client couldn’t effectively evaluate if they got what they needed. We really care about delivering what the client wanted and needed because consultants should do more than “implement the requirements.” When OPI builds out a data system, we solve problems with a holistic product view rather than focusing on one specific engineering problem.