Case Study: Top Speed Migration of SQL Server to Snowflake in 60 Days

About Client

The Client is one of the most well-known high-end performance sports car brands in the world. Their portfolio of cars span the supercar category but also high-end SUVs, sedans, and EVs.

Challenge

The organization had a significant footprint of logic for data sourced from SAP that had been built in SQL Server for consolidating and curating data. The cost of this footprint was a major expense and the performance of the analytics was slow to execute. 

Snowflake offered a compelling cloud destination as it provided the underpinnings of the future state landscape the client was looking for. However, they needed a way to deploy Snowflake with their current assets without committing the budgets all upfront for a major code conversion effort. Additionally, they were seeking a way to minimize the spend on external tooling for the first phase of their deployment.

Navigating Constraints

The US division of the organization was receiving regular data feeds out of SAP that had several years worth of transformation logic built up, which ultimately landed in SQL Server. The rewriting or conversion of this logic was not a project the organization wanted to undertake.

The organization was completely new to Snowflake so they needed assistance in learning security, error logging, access control, data loading, and managing the Snowflake deployment.

The organization wanted to minimize the cost of external tooling acquired to deliver their proof stage.

Win 1: 

Intricity assisted the client in creating a strategic roadmap that could satisfy the short- and long-term migration plans, which also abided by the limited budgets available for the proof stage and the future budgets of their full offload stage.

Win 2:

The client was set up with a foundational access control model in Snowflake which gave them a template for their users and their data access rights. This later was very useful in providing data access to their data science team which needed the ability to freely explore the data without exposing that exploration to any production processes. 

Win 3:

Intricity implemented its "Do No Harm" migration methodology which enabled the client to migrate all their SQL Server assets to Snowflake by replicating the tables over and setting up a regular sync. Once the data was replicated the reports and downstream query content could simply change their connection parameters to source from Snowflake rather than SQL Server.

Win 4:

Intricity's original plan was to leverage off-the-shelf data replication, however, they needed to minimize the cost of the proofing phase. Intricity leveraged FlowObjects Data Loader which provided the mechanics to load data efficiently into Snowflake. This effectively provided a very low-cost method of loading CSV files from SQL Server, by splitting and encrypting the files, to land into Snowflake.

Win 5:

The Data Loader did not have a mechanism for capturing changed data, so Intricity needed a method to identify when new data landed in SQL Server for the proof stage. Intricity identified components native to SQL Server which tracked changed data it could use to schedule the acquisition of CSV files from SQL Server.

Win 6:

The client was given an environment that mirrored the existing SQL Server instance but loaded to Snowflake. The cost of SQL Server went from many hundreds of thousands of dollars to a few hundred dollars and all the data consumers could use the scalability of Snowflake's compute, zero-copy cloning, and time travel. Analytics ran faster, data science teams could work independently without impacting production, proof costs were kept at a minimum. The full migration finished in a 60-day period without additional software costs.

The organization has later planned phases to execute a migration of the SAP data transformation logic to ultimately point directly to Snowflake. However, the organization is no longer "under the gun" as the business has access to all their existing functionality powered by Snowflake.

Phase 2:

As depicted below.