Challenge: Upgrade the Data Warehouse
In early 2020, the Client's IT leadership team 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 the EDW need to be converted to Snowflake, but the accessory reporting tools and processes which relied on EDW data required conversion to fully benefit from the power, performance, and scalability of the new Snowflake warehouse.
Solution: Analysis and Execution
During the initial Analysis phase, Onebridge 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 phase 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, and then 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 & 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 & 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 & 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 the Client resolve technical challenges as they navigated the change management journey to Snowflake, gaining from our pool of prior experience in EDW conversions.
- The 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 by early 2021.
- Adopting the same agile processes, tools, and practices used by the Client, Onebridge provided effective coaching and leadership for the scrum team, sharing frequent progress updates to the IT leadership team.