In the world of Cloud Data Warehouses (Cloud DWH), dbt often comes hand in hand with client sites on Databricks or Snowflake.
What does dbt actually do?
Data Build Tool, or dbt for short, handles the T (transform) part of the ELT process. Its syntax is based on SQL, so it should be easy to adopt for a seasoned data engineer. Besides transforming data from the source format to the format needed for reporting and analytics, it also handles a number of other things — orchestration, data lineage, and 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 migrate your ETL?
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 a way to automate it? Of course, it heavily depends on the old ETL's technology, framework, development standards, and consistency. But the answer is generally yes. Here we will analyze two traditional ETL tools: ODI and IBM DataStage.
Migrating from Oracle Data Integrator (ODI)
When it comes to Oracle Data Integrator, the situation is somewhat simpler — until you start digging under the hood. It is essentially an SQL generator, so the generated SQL could be used as a base for analogous 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 that already supports such a migration path.
Migrating from IBM DataStage
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.
How does EPIC Ingenioso handle it?
Luckily, our EPIC Ingenioso can do it. It can parse the XML export of DataStage jobs and turn it into SQL of any flavor — all while self-documenting the process as you go. The same goes for ODI. The best part is, it is NOT an AI-based solution. It is 100% deterministic — no hallucinations, just predictable output.
The best part is, it is NOT an AI-based solution. It is 100% deterministic — no hallucinations, just predictable output.
Subscribe to stay updated and get our latest articles straight to your inbox.
Frequently asked questions
Answers to the most frequently asked questions.
dbt (Data Build Tool) handles the transform (T) step of ELT. It is SQL-based and also covers orchestration, data lineage, and data quality.
Migrating data structures is a one-time job, but the recurring data load — your hundreds or thousands of ETL jobs — has to be rebuilt as dbt transformations, which is where most of the effort goes.
Generally yes, depending on the old ETL's technology and consistency. ODI generates SQL that can seed dbt jobs (with schema/parameter mapping), while DataStage needs its own transformation syntax parsed and translated to dbt-friendly SQL.
No. EPIC Ingenioso is 100% deterministic — it parses ODI and DataStage jobs and produces predictable SQL output with no hallucinations, self-documenting as it goes.
Tags
