ETL Migration - from legacy ETL to SQL
Legacy ETL platforms mainly implement filters, joins, aggregations, mappings, and transformations that can be expressed in SQL. {openAudit} analyzes ETL jobs, reconstructs the flows, and generates a documented, version-controllable SQL equivalent, independent of the source technology.
Process analysis
ETL repositories are analyzed to reconstruct how the flows actually work: sources, targets, transformations, parameters, and dependencies between processes.
Flow reconstruction
Processing chains are reconstructed from the ETL jobs, with intermediate flows, variables, and parameters. This mapping is the entry point for the conversion.
Rationalization
Cross-referencing data lineage (down to field level) with observed usage separates the data that is genuinely consumed from what isn’t. Each job also receives a complexity score (number of steps, depth of sub-processes, types of transformations, external dependencies), used to prioritize and plan the migration.
Result: a scope reduced to the processes that are actually used, prioritized according to their real complexity.
SQL generation
Filters, joins, aggregations, lookups, mappings, and calculations are translated into successive, flat SQL statements that preserve the logical structure of the original processes: each step remains identifiable.
Example: IBM DataStage jobs
Each stage type is translated according to its own logic:
- Runtime Column Propagation (RCP): the column list isn’t fixed in the job; it’s generated dynamically in the SQL, driven by the variables passed to the job.
- Oracle connectors (read / write): SELECT, UPDATE, MERGE, or INSERT generated from the stage’s options.
- Change Capture (CDC): Before/After comparison on primary keys, detecting new, modified, and deleted rows.
- CDC Transformer: conditional routing to a dedicated flow based on the detected change code.
- Shared Container: a factored logical block, rendered as a standardized SQL block reused across several jobs.
- Column Generator: calculated columns translated into typed SQL expressions.
- Modify: type conversions translated into SQL CAST (e.g. string to timestamp).
- Funnel: merging several flows via SQL UNION, with output schema harmonization.
Observed result: across an estate of several hundred DataStage jobs, more than 95% of the SQL translations succeed on the very first run.
Adapting to target platforms
Generation takes into account the dialects, functions, data types, and specific mechanisms of the target: Snowflake, BigQuery, Databricks SQL, Redshift, PostgreSQL, SQL Server, or other SQL-compatible engines.
Orchestration
Dependencies and scheduling are preserved. Airflow replaces the scheduling built into market ETL tools (DataStage, Talend, Informatica, BODS, ODI, Stambia…): sequences and schedules are automatically carried over as Airflow DAGs. When an external scheduler already drives the processes on the source side (Control-M, for example), it can be kept.
SQL as the pivot format
Transformations no longer depend on the proprietary components of the original ETL platform: they can be versioned, reviewed, tested, and deployed independently, and their logic stays under the company’s control.
{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, serverless SQL ETL with an embedded DuckDB engine. Its core is free; advanced features are licensed. The jobs automatically transcribed into SQL by {openAudit} run there as pipelines orchestrated by Airflow, with no dedicated infrastructure.
The generated SQL finds in {oa-tbx} a ready-to-use execution and orchestration environment.
- 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, open SQL; the core is free, only advanced features are licensed.
Key point: with {oa-tbx}, migrated flows become open SQL, readable and auditable line by line, and executable on any environment.
Discover {oa-tbx} → · ETL migration to {oa-tbx} in detail →
