case Study

Making the Journey from On-Premises SQL Server to Snowflake

Client:

Clinical Asset Management Company

Company industry:

Employees:

3000

Industry:

Service Offering:
No items found.
Industries:
Healthcare and Life Sciences
Partners:
Services:
Platform and Tool Selection
Platform and Tool Selection
Data Warehouse Modernization
Data Warehouse Modernization
Data Warehouse Acceleration
Data Warehouse Acceleration
Data Warehouse Automation
Data Warehouse Automation
Technologies:
Data Cloud Migration
Case Study PDF Download:
Download PDF Copy

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.

Stay in Touch with Onebridge

* Indicates required field
Thank you for subscribing! Check your email for a confirmation and link to your profile.
Oops! Something went wrong while submitting the form.
Hey there! We hope you've noticed that none of our content is "gated," meaning we don't force you to provide your information in order to read our content. We work hard to provide valuable information to serve our audience and our clients, and we're proud of it.

If you'd like to be notified of new content, events, and resources from Onebridge, sign up for our newsletter here. After signing up, you'll get a profile link where you can tell us what topics you want to hear about. With Onebridge, you control your data.

You can also follow us on social media to see upcoming events and other resources, like blogs, eBooks, and more!