Migrate ODI to Any Technology

You decided to finally move your on-prem Data Warehouse to the Cloud Data Provider of your choice. How should you best proceed?

What's actually the hard part?

Contrary to the first intuition, migrating data and structures (such as tables) is easier than you might think. For example, Google's Database Migration Service can help you a great deal if GCP is your data platform of choice. In any case, you do it once.

The hard part is migrating the regular load of your DWH data, day after day, month after month. For that, you will need to convert your existing ETL to the ELT architecture of your choice.

If you have hundreds or thousands of ETL jobs, programming them manually in the new technology is an absolutely colossal task. It might take years and actually kill your project — since your new technology stack might get outdated even before you are finished. I've seen it happen.

Can you automate the conversion?

So, can you automate it? Somehow convert old-technology integration logic to the same logic in the new technology?

Perhaps your first thought nowadays is using AI. Think again. Do you really want to debug thousands of bugs AI hallucinates, while not really understanding the code it generated? You will need a rule-based, deterministic algorithm.

“Do you really want to debug thousands of bugs AI hallucinates, while not understanding the code it generated? You will need a rule-based, deterministic algorithm.

But some jobs need refactoring or optimization — what then?

Even so, we would still recommend migrating the jobs as they are now as the first step. Why? The reason is simple — testing. If the jobs are supposed to work in the same way, you can start them on identical environments with identical parameters, and see if the results are identical. Every row, every column. You can even automate such a test (contact me to find out how).

Only when the new technology performs identically to the old one would we suggest optimization — that is, if it is even needed in the cloud. As the last step, or perhaps a separate project, we would recommend functional / business refactoring. Sure, some of the old code does the old logic — but if you try to do refactoring and migration at once, how will you know what is the cause of a data error? Is it wrongly implemented new business logic, or migration bugs? Doing it sequentially simplifies things a great deal.

How should the migration algorithm be built?

Let us get back to our deterministic migration algorithm. You can implement it two ways. You can parse the integration job in the old technology and immediately generate the equivalent in the new technology stack. But we would recommend using a middle layer — one that simply contains the specs of the source job. That way, we can theoretically use the specs to generate target jobs in multiple technologies, if such a need ever arises.

A word about the middle layer: it would be great if it were human-readable, or if such a format could be automatically generated. In that way, the middle layer is more than just a specification of the integration job — it can serve as documentation as well.

How do we implement it?

Now that we have the architecture of the migration logic, how do we implement it? Usually, we will need an SQL parser flavored for the source technology. On top of that, we will need to parse the non-SQL specifics of the old ETL technology.

If you are an engineer, it is clear to you that developing a formal parser of a programming language is damn hard. But luckily, there are packaged solutions already doing that. Our very own EPIC Ingenioso supports SQL parsing in a number of flavors. It also supports Oracle Data Integrator on the source side, IBM DataStage, as well as stored procedures. On the target side we can do almost everything — dbt, Spark, Matillion, and the Cloud Data Platform's stored procedures. Don’t miss what’s next — subscribe to our newsletter.