Innovative Regional Bank Translates Its Transformation Layer to Snowflake
Customer's Challenge
Amid modernizing its aging data stack, one of the leading regional banks in the Midwest set its sights on converting its transformation layer to Snowflake. With a redesigned information architecture and over 1,400 stored procedures (some dating back 20 years), they needed a partner with extensive Snowflake experience and the right tools and experience to help them achieve this.
phData's Solution
phData translated all 1,400 stored procedures from SQL Server to Snowflake, converting the code to use the new information architecture and accounting for nuances between the two systems. This included creating accelerators to automate the process of converting the code and validating the results.
Results
By the end of the 6-month engagement, the client had all their transformation procedures seamlessly running in Snowflake, empowering them to harness Snowflake’s capabilities and reconnect their Power BI reports to Snowflake tables. This preparation set the stage for them to triumphantly complete their extensive migration from SQL Server before the deadline.
The Full Story
A leading Midwest bank was facing an enormous problem: how to efficiently convert 20 years’ worth of transformational stored procedures from SQL Server to Snowflake. To exacerbate this challenge, they completely changed their information architecture between the systems, adding schema, consolidating databases, and even changing every table name and some column names, which would require manual rewrites of their procedures.
With many of the procedures being tens of thousands of lines long, the client realized this process would take years and would not meet their deadline to be entirely off SQL Server unless they enlisted the help of phData to speed and automate the process.
In the ensuing data engineering engagement, phData was able to automate much of the process, using some pre-existing tooling at their disposal and creating new tools in Python to speed up the process further. The process included:
- Using the SQL Translation tool from the phData Toolkit to change the dialect of the procedures from SQL Server to Snowflake.
- Creating a Python script to map and translate the database, schema, and table names from their previous SQL Server names to their Snowflake equivalents.
- Utilizing the Data Source tool from the phData Toolkit to validate that the output of the SQL Server procedure and its Snowflake counterparts were identical.
- Creating a Python script to automate finding new or changes to existing procedures in the Snowflake production environment, creating a new feature branch in their version control to be approved and merged into their main branch by their dev team.
Why phData?
The client selected phData over other vendors due to our team’s expertise in Snowflake and extensive tooling for automating and accelerating the process. By leveraging phData’s SQL Translator and Data Source tools from the Toolkit, we delivered the translated transformation layer in a fraction of the time it would have taken manually.
Take the next step
with phData.
Learn how phData can help solve your most challenging data analytics and machine learning problems.