Using GenAI to support migrations from Redshift to Databricks

Problem statement

With Databricks' rapid growth, we are seeing an uptick in migration projects from other platforms, including Netezza and Redshift. A platform such as Databricks offers enormous benefits for companies looking for a holistic approach to data engineering, data science, and data warehousing. 

However, migrations can be an expensive, exhaustive, and intensive process due to years of technical development on the source platform that needs to be ported over. Differences in design patterns, feature/functionality, and overall compatibility can cause serious headaches for those involved. Fortunately for us today, we now have generative AI to help us through these challenges.

In this blog, we will walk through a pattern you can adopt with Matillion if you want to migrate from another platform to Databricks. While this example showcases Redshift to Databricks, you will see how this approach can apply across other platforms with some slight changes.

In short, we are looking to capture all source DDLs from tables in a Redshift schema(s), load those scripts into a delta table in our Databricks environment, and then run them through an LLM to help us convert the original DDL syntax to Databricks sparkSQL compliant code. 

Once the code is converted, we will systematically run the DDLs via Matillion and capture their deployment status (pass/fail) so we can identify which DDLs need some human interaction.

In our example, we saw a conversion rate north of 75%, which is significant if you consider this on a larger scale. By using this GenAI approach, you can potentially reduce your migration workloads to 25% of the originally anticipated effort. If you translate that to work hours and employee costs, that could mean weeks of work and thousands of dollars saved while expediting the return of value of the investment into Databricks and Matillion.

Capturing the source DDL

Pulling a list of tables from Redshift

For this demonstration, we are looking to pull the list of table names from a schema called ‘public’ in our Redshift environment. This is done using a Database Query component in Matillion and executing the following query:

Please note that to pull from multiple schemas, alter the table_schema filter from ‘=’ to an ‘IN’ statement. The component will output this list of tables into a new delta table called redshift_stg_info_schema_tables.

Pulling DDLs from Redshift

Some platforms like Oracle and Snowflake have easy-to-use functions to get DDL scripts for a database object, but unfortunately, this is not true for all platforms. This is the case with Redshift, where you have to stitch together the DDLs instead of having a convenient function to call and get the response in a single response.

Fortunately, AWS has an aws-redshift-utils public repo that provides some handy scripts for Redshift users. For this scenario, we used the v_generate_tbl_ddl.sql to query the DDLs for our list of tables. A table iterator will read the list of table names from our newly loaded redshift_stg_info_schema_tables table and dynamically pass the values to the DDL query via a loop.    

We now have the list of DDL scripts for each table in our delta table in Databricks, albeit broken up across multiple rows. This is a simple problem to solve using native Databricks functions like ARRAY_JOIN() and COLLECT_LIST().

Collapsing multi-line DDL

With our DDLs being delivered from Redshift across multiple lines, we need to run a quick transformation to group these records into a single row per table. 

Here is the SparkSQL script that achieves that in Matillion:

Using GenAI to convert DDL 

Now that we have imported the DDLs and grouped them into a single row, the last transformation step is to run them through an LLM using the native ai_query() function from Databricks. 

Matillion provides a native component in our transformation pipelines that allows users to integrate these functions easily. By choosing a model (in this case, DBRX), you can provide it with a prompt and pass it any relevant columns from our source table (full_ddl is the column into which we collapsed the DDLs in our previous steps). 

Here is the sample prompt provided in our User Prompt field:

And this is the comparison of the Redshift DDL and the output from the GenAI prompt:

Here is some sample output:

Executing DDL in Databricks

Now that we have the converted DDLs for Databricks stored in our delta table, the final step is to run them through and capture if the DDL executed successfully or failed. 

Just like we looped over the table names from our Redshift schema to get the individual DDLs over to Databricks, we will loop over all the converted DDLs from our delta table and pass those into a sub job that executes the DDL and updates the execution status.

In our loop, we store the DDL into a variable called ${plv_dbx_ddl} and execute it via a SQL script component in our sub job. 

Using our flow control options (success = green line, fail = red line), we will call the update on the script table and pass in a success of fail value to another variable called plv_ddl_status to our transformation job.

This transformation job will ultimately be the process that changes our dbx_ddl_execution_status field to Success or Fail so we can see what the results of our DDL execution was.

Summary

After running this process through our list of tables, we were able to convert 16 out of 20 tables—an 80% success rate. As with any pipeline, especially a GenAI integrated one, there is always room for improvement. But for a job that we were able to build in a few hours, it certainly provides a great step forward to the art of the possible when it comes to migrations and the use of GenAI in real-world applications.

If you’re a visual learner or prefer to watch a video walkthrough of this blog, please watch the video below!