ETL Migration - from legacy ETL to dbt
Migrating to dbt means transforming proprietary ETL processes into SQL models run directly in the target data engine.
Before any conversion, {openAudit} analyzes flows, dependencies, and usage to identify the scope that is actually worth migrating.
Key point: the migration starts with a rationalization phase of the existing estate.
Flow analysis
ETL processes are analyzed to reconstruct dependencies, transformations, and usage.
Data lineage is used to identify active flows, dead branches, and unconsumed components.
This phase reduces the volume of processes to be migrated.
Result: a migration scope refocused on the processes that are actually used.
Conversion to SQL
ETL jobs are broken down into elementary operations: filters, joins, aggregations, lookups, business calculations, and transformations.
These components are converted into documented SQL, independent of the source technology.
SQL becomes the pivot representation of the processes.
Key point: business logic is rebuilt in SQL before the dbt project is generated.
Generating dbt models
The SQL processes are organized into dbt models.
Dependencies between models are automatically generated from the flows identified during the analysis.
The project structure, references between models, and documentation can be produced automatically.
Progressive validation
Migration is carried out in batches.
The generated models are validated and then progressively integrated into the target environment.
Discrepancies can be checked process by process during the transition phase.
Typical use case: progressive migration of several hundred ETL jobs to dbt.
Target architecture
The processes become version-controllable, maintainable SQL models.
Dependencies are explicit, transformations are visible, and processes can be run directly by Snowflake, BigQuery, Databricks SQL, Redshift, PostgreSQL, or any other compatible engine.
Key point: the processes no longer depend on the legacy ETL platform but on a dbt project built on open SQL.
{oa-tbx}: the recommended target
Rewriting DataStage, PowerCenter, BODS, Talend, ODI, or SSIS jobs in another proprietary ETL means renewing the licenses, the dedicated infrastructure, and the vendor lock-in.
{oa-tbx} is an open-source, serverless SQL ETL with an embedded DuckDB engine. The jobs automatically transcribed into SQL by {openAudit} run there as pipelines orchestrated by Airflow, with no dedicated infrastructure.
The SQL generated for dbt can just as well be executed in {oa-tbx}: the pivot format stays the same, only the execution environment changes.
- Automatic transcription: {openAudit} converts legacy jobs into flat SQL, without months of reverse engineering.
- Open orchestration: the job sequence of the source ETL is reproduced as-is as an Airflow DAG.
- Embedded DuckDB: an open-source analytical SQL engine with no server to administer; jobs ranging from a few thousand to over 50 million rows often run in a few seconds.
- Pipeline as Code: SQL jobs and Airflow DAGs in Python, versioned in Git and deployed with Terraform.
- Governance through data lineage: each original transformation stays linked to the SQL actually executed.
- Zero lock-in: containerized, Java / Python, no proprietary license or roadmap.
Key point: with {oa-tbx}, migrated flows become open SQL, readable and auditable line by line, and executable on any environment.
