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 SQL equivalent independent of the source technology.

Key point: the processes are rebuilt as documented, version-controllable SQL.

Process analysis

ETL repositories are analyzed to reconstruct sources, targets, transformations, parameters, and dependencies between processes.

The collected metadata makes it possible to reconstruct how the flows actually work.

Unused or unconsumed components can be identified before migration.

Result: a migration scope reduced to the processes that are actually used.

Flow reconstruction

Processing chains are reconstructed from the ETL jobs.

Dependencies between processes, intermediate flows, variables, and parameters are integrated into the target model.

This mapping is the entry point for the conversion.

Rationalization

Cross-referencing data lineage (down to field level) with actually observed usage makes it possible to identify the data that is genuinely consumed and set aside what isn’t.

Each job is also analyzed to assess its complexity: number of steps, depth of sub-processes, types of transformations, and external dependencies. These metrics are aggregated into a complexity score, used to prioritize and plan the migration.

Result: a migration scope reduced to the processes that are actually used, prioritized according to their real complexity.

SQL generation

The detected transformations are converted into SQL.

Filters, joins, aggregations, lookups, mappings, and calculations are translated into successive, flat SQL statements.

The resulting SQL preserves the logical structure of the original processes.

Key point: each processing step remains identifiable in the generated SQL.

Example: IBM DataStage jobs

For a DataStage estate, 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

The generated SQL can be adapted to the dialects of the target platforms.

Functions, data types, and specific mechanisms are taken into account during generation.

The same processes can be produced for Snowflake, BigQuery, Databricks SQL, Redshift, PostgreSQL, SQL Server, or other SQL-compatible engines.

Orchestration

Dependencies and process scheduling are preserved.

The generated processes can be integrated into the orchestration tools already present in the Information System.

Typical use case: integration into Airflow, Control-M, or any other existing scheduler.

SQL as the pivot format

SQL becomes the common representation of the processes.

Transformations no longer depend on the proprietary components of the original ETL platform.

Processes can be versioned, reviewed, tested, and deployed independently of the source technology.

Key point: the transformation logic stays under the company’s control and no longer depends on a specific ETL engine.