Challenge: Upgrade the Data Warehouse
The IT leadership team at a clinical asset management company decided to transfer their existing on-premises enterprise data warehouse (SQL Server) to Snowflake, the cloud data warehouse.
Not only did the enterprise data stored in their EDW need to be converted to Snowflake, but so did the accessory reporting tools and processes that relied on EDW data. This would ensure that our client would fully benefit from the power, performance, and scalability of the new Snowflake warehouse.
The company chose Onebridge to help with this huge undertaking and guide the initiative to success. And that's just what we did.
Solution: Analysis and Execution
During the initial analysis phase, our team helped the client create an inventory of stored procedures, Power BI dashboards, cubes (DAX measures), and sandboxes, which offer a non-production environment in which to explore EDW data.
We assigned “effort estimates” to each item to inform the project’s overall staffing strategy, all based on experience gained from previous EDW conversion projects.
The execution and enablement phases consisted of:
- Data validation: Onebridge completed a detailed analysis of the data converted to the tables in Snowflake against the data in the legacy EDW. The team collectively identified over 103 data discrepancies and resolved those differences to provide the business with confidence in the data within the new EDW. Additionally, there were a total of 74 dimensional model tables reviewed and validated.
- Stored-procedure conversion: The development team methodically analyzed approximately 30 stored legacy procedures from the SQL Server. Then we converted the code to the query syntax required in Snowflake.
- Power BI dashboard conversion: After reviewing the usage and criticality of existing Power BI dashboards, we converted and rebuilt 15 dashboards to extract data from Snowflake instead of the legacy server.
- Change management: The client's various functional teams (like Finance, Product Development, and Financial Planning and Analysis) used sandboxes regularly. We helped them with the change management element of converting those users to the new Snowflake environment. This process included reviewing and prioritizing key sandbox items to convert, training those internal users on the new tools in place, and conducting weekly stakeholder meetings to answer questions regarding the overall journey on a regular basis.
- Agile planning and delivery methods: Throughout the conversion process, the Snowflake Scrum team used Agile delivery processes to continually refine the backlog to deliver value to business users early. This methodology extended to the review of in-scope reports, stored procedures, and database views which required conversion.
Results: Boosted Momentum Toward Ambitious Goals
Our work transitioning the client to the new Snowflake environment yielded many positive results:
- Our flexible, responsive Onebridge team assisted with several conversion-related activities, including SQL work and Power BI report development, providing reliable support for urgent needs
- Our technical expertise and leadership helped our client resolve technical challenges as they navigated the change management journey to Snowflake, gaining from our pool of prior experience in EDW conversions
- Our client set aggressive goals for the entire enterprise to transition from the on-premises SQL server to Snowflake. Our focused and strategic delivery provided the momentum needed to complete the transition within a year
- Adopting the same Agile processes, tools, and practices used by the client, we provided effective coaching and leadership for the Scrum team, sharing frequent progress updates to the IT leadership team
.jpg)
