Not too long ago I wrote a blog post about key strategies for a successful data warehouse migration. In this follow-up, I'll share our experience using SnowConvert and Cursor to accelerate the migration process at no additional cost. During our planning phase, the Snowflake team recommended SnowConvert to help convert Redshift SQL code into Snowflake-compatible SQL. Since Redshift integration was new to SnowConvert and the tool was recently made available for free, our migration presented a perfect opportunity to test it.
Given the novelty of SnowConvert's recent Redshift integration, our team also chose to use Cursor as a backup solution. This dual approach saved us countless hours by eliminating the need for manual code reviews and reducing costly repeated dbt model runs.
SnowConvert:
Snowflake’s SnowConvert is a high-fidelity SQL conversion solution designed to help automate the migration of code from legacy platforms such as Teradata, Oracle, SQL Server, and Amazon Redshift into Snowflake compatible SQL. SnowConvert builds an abstract syntax tree as well as a symbol table to create a semantic model of your source code. It processes SQL files from a specified directory and outputs both the converted SQL files and detailed logs to a desired destination. These logs include error messages and functional difference messages that highlight areas where full automation wasn’t possible (source 1, 2, 3).
Cursor:
Cursor serves as a code assistant, functioning much like a co-pilot. In our project, we used Cursor to select entire SQL files and ask targeted questions against the file or code base to help make code created in Redshift compatible with Snowflake. Beyond simple SQL conversions, Cursor also provided optimization recommendations for performance improvements in Snowflake. This tool was particularly useful whenever we noticed conversion errors from the SnowConvert platform, when trying to clean up sql files, or converting dbt files back to jinja syntax.
Leveraging SnowConvert is a pretty straight forward process. First you can select the folder of files you want to be converted (this can also include folders within a folder). Then, you can choose the output destination you want your file to go to. In this case, we had our files output directly to our dbt file folder structure.
With a hit of a button and just seconds later, SnowConvert is able to run the files and make them Snowflake SQL compatible. There is a quick summary tab that helps to highlight the overall conversion. Additionally, in your detailed output folder there are logs and reporting files to help drill into the different files that were converted and any errors that may have come up.
Files with incompatibilities were cleaned up using Cursor as a way to still be able to quickly convert SQL to be compatible with Snowflake. Once conversion was complete, we ran our dbt models to ensure that it was working as expected.
For models with SQL changes, we carefully QA’d these tables to ensure that less than 3% variance existed between the warehouses.
While SnowConvert is an awesome tool, there were some limitations that are worth touching on. Note that these limitations were relevant at the time of our migration and SnowConvert is continuing to update different features.
dist and sort key configs, we had to know and manually remove these from our models to get true performance gains of leveraging Snowflake.Integrating SnowConvert and Cursor into our migration strategy saved significant time and cost. The conversion of dbt model files, which could have taken over a month, was reduced to less than half that time using these tools. This combination allowed us to efficiently convert Redshift-specific SQL with SnowConvert, while addressing any shortcomings through Cursor.
Given how common dbt and Looker are in the Snowflake tech stack, I anticipate that Snowflake will eventually want to add support for jinja and LookML syntax in SnowConvert. It will also be cool to see how Snowflake considers other core components of migration as part of SnowConvert’s offering (such as expanded data QA and, of course, how Snowflake will integrate it with AI).
For anyone who finds themselves needing to migrate to Snowflake from Redshift, I would highly recommend leveraging SnowConvert as a means of accelerating the process!
