Migrate ODI / DataStage to dbt

In the World of Cloud Data Warehouses (Cloud DWH), dbt often comes hand in hand with client sites on Databricks or Snowflake.

Data Build Tool, or dbt for short, handles the T(transform) part of ELT process. Its syntax is based on SQL so it should be easy to adopt for a seasoned data engineer. Besides doing transformation of data from source format to format needed for reporting and analytics, it also handles a number of other things. Orchestration, data lineage or data quality just to name some.

So, migrating your DWH to Databricks / Snowflake often means the biggest part of the work will be the migration of your ETL to dbt.

What's the best way to do it?

If you have just a few integration routines, you can reprogram them manually and keep the new installation tightly under your control. But let's face it, it never is just a few integration routines. There are hundreds at best, thousands if you are not that lucky.

So, is there way to automate it?

Of course, it heavily depends upon old ETL's technology, framework, development standards, and consistency. But the answer is generally yes. Here we will analyze 2 traditional ETL tools, ODI and IBM DataStage.

When it comes to Oracle Data Integrator, the situation is somewhat simpler, until you start digging under the hood. It is essentially SQL generator, so generated SQL could be used as a base for analog dbt jobs. Well, to some extent. You will still need to map source and target schemas, load parameters and a number of other things. In other words, copy pasting SQL won't suffice, you will need an algorithm to do it. Or a packaged solution which already supports such migration path.

When it comes to DataStage, the situation is even worse. It upgrades SQL (sure, you can use SQL in DataStage as well) with its own transformation syntax, joins and files with temporary data. So you will need to parse that as well and “translate” it into dbt friendly SQL.

Luckily, our EPIC Ingenioso can do it. It can parse xml export of DataStage jobs and turn it into SQL of any flavor. All that while self-documenting the process as you go. The same goes for ODI. The best part is, it is NOT AI based solution. It is 100% deterministic – no halucinations, just predictable output.