Automation in a Successful Migration to Snowflake

As 2021 marches on, Snowflake marches on. As people who live and breathe migrations, we're always looking at how the evolution of a platform impacts getting to (or migrating to) the platform. In other words, what’s the need-to-know on migrating code and logic from a legacy source successfully to Snowflake? If you find yourself considering Snowflake as a destination or if you’ve still got data silos that you want to empty into Snowflake, what’s the best way to make the move smoothly and successfully? Well, we’ve been performing migrations for more than double the period of time that Snowflake has existed (not just to Snowflake, obviously). So we’ve seen a thing or two about what makes a migration successful and what makes one... not successful.

But if we had to boil down our expertise into a few simple words of support as you're wandering the virtual halls of your next meeting considering that migration to Snowflake, they would probably focus on planning and how automation fits into that plan.

You’ve got to have a plan. This blog post isn’t about building a plan, but you can learn more about building a successful migration strategy by reviewing our webinar on getting started in a migration to Snowflake. And you can always connect with us at any point, even if it’s just to chat about building a successful strategy. That’s something we like to talk about.

This blog post is about how automation fits into that plan. Yes… yes… we do make software to accelerate any code migration, so we’re going to be a bit biased. But consider why we make that software. Which came first, the software or the practice of performing a migration? We make automation software because we needed the right tooling to support our migration services. We built SnowConvert to use it ourselves when we perform a migration to Snowflake. We built the VBUC because to use it ourselves when we perform a migration from VB6 to C# (and other targets). Building the right kind of automation into a pipeline to successfully convert and deploy functioning code is one of the most critical pieces in a successful migration.

How Automation Fits Into the Plan

There are a lot of opinions out there on getting the right strategy in place, but the combination of planning and automation has been what has routinely worked for us. It’s worked so well, that we literally sell the software separate from performing a migration to support others doing their own migration. We wouldn’t sell it if it hasn’t been so successful for us in performing the migration work that we do every day. But we're not the only ones that benefit. Take a moment to review our webinar with Snowflake Professional Services that supports this idea of getting the right kind of automation involved in a migration.

You may be thinking, “All of this is great, but what kind of reduction in time and effort can I expect if I use an automation tool? What's the ROI?” Of course, those are the right questions. Luckily, as the heaviest user of our own software, we can provide an answer. Let’s use SnowConvert for Teradata as an example. As we have listed on our website, we’ve converted over 300 million lines of code, and well over 7 million objects (tables, views, procedures, etc.). That includes both discrete projects that we’ve done with partners and customers as well as numerous smaller runs of the tool. We’ve seen about a 96% average conversion rate across all of those lines of code. We’ve also seen an average reduction in time to complete a project of between 60 – 80%.

But those are our numbers. How can we generalize that for the next project? Let’s consider some estimates that Gartner released in a report they published in 2020, How to Succeed at Database and DBMS Migration. In this report, “the complexity and amount of custom procedural code is a big factor” in database migrations. It was estimated that the average migration’s time and effort has about 70% of it’s time spent in the procedural code migration (procedures, scripts, etc.) with about 15% in data object migration (tables and views) and another 15% in general overhead (setup, parallel running, decommission of the old system, etc.). Since Mobilize.Net SnowConvert automates about 96% of the lines of code on average, that’s a substantial reduction in effort.

Let’s zoom in on that a bit though. We’ve seen about 90% automated migration of procedures and scripts and 99+% automation in tables and even views. If we go with Gartner and assume that approximately 15% of the effort is in converting tables and views and 70% in procedures and scripts (we’ll just keep the 15% effort in the overhead), that’s a reduction of approximately 78% of the effort you needed to complete the migration.

But what about testing? Hard to trust a tool’s output the first time you use it, regardless of how legendary we know the tooling to be. Let's assume that in Gartner’s estimates that testing is actually as high as 50% of the effort in a regular migration. Let's also say that the same testing strategy will be used and no automation of that strategy will be done (which would be pretty wasteful of resources when using an automated approach… Mobilize.Net has internal tools that we help to automate the testing process and you can reach out to us if you would like more information). And let’s even say that the testing is not included the 15% overhead estimate they give (so… essentially saying that 50+15 = 65% of a migration is not automatable, which is a claim we would disagree with). There’s still a reduction in cost of 92% of that remaining 35% of work remaining to perform a migration (about 32%).

Regardless, when you are considering a migration of millions of lines of code and tens of thousands of objects, cutting the effort required to complete your migration by 1/3 is substantial. Cutting it by 78% is game changing. That's why we bring products that you can actually use into a market that only has services offerings. When you effectively use automation, your migration will not only take less time, but it will be more likely to be successful from our experience. 

Clear as mud? Fear not. We’ll be releasing a ROI calculator, and even more information on planning a successful migration to Snowflake in the coming weeks. In the meantime, let us know if you want to chat about everything that's new and shiny at Snowflake. We're always up for that conversation.