ETL Migration - from legacy ETL to an open SQL ETL - {oa.tbx}
Once ETL processes have been converted to SQL by {openAudit}, they can be run inside the {oa.tbx} container.
{oa.tbx} provides an execution environment for SQL, Python, or Java processes, driven by configuration and orchestration repositories.
Key point: the migrated processes become SQL instructions that can be executed independently of the original ETL platform.
Principles
SQL is encapsulated in open containers, in ETL mode.
- Containers: transformations run inside an external container, {oa.tbx}, detached from the target database. Temporary containers act as intermediate storage areas where data is loaded before transformation. Jobs are tested to verify correct behavior, and possibly the data itself, to guarantee its integrity.
- Orchestration with Airflow: Airflow drives the execution of the containers, step by step. Each transformation, within a container, is defined as a job.
The scheduling of an ETL migrated to Airflow automatically picks up all of the original instructions and scheduling logic.
Execution chain
ETL jobs are converted to SQL by {openAudit}.
The generated processes are integrated into a configuration repository and a control repository used by {oa.tbx}.
The workflows are then orchestrated by tools such as Airflow or Dagster.
Execution container
{oa.tbx} runs the processes described in the workflows.
Dataflows can combine:
- SQL;
- Python;
- Java;
- others.
The engine can also rely on embedded databases such as DuckDB for certain intermediate processing steps.
Sources and targets
Input data can come from any technology accessible via SQL or JDBC.
Results can be loaded into any target system compatible with standard write mechanisms.
The architecture remains independent of source and target technologies.
Orchestration
Orchestration remains separate from the processes themselves.
Dependencies, execution parameters, schedules, and triggers are handled by the workflow layer.
Business logic stays contained within the SQL scripts and dataflows executed by {oa.tbx}.
Benefits
{oa.tbx} runs open SQL pipelines, orchestrated by Airflow and independent of proprietary Cloud platform runtimes.
Across all migrated jobs — with volumes ranging from a few thousand to more than 50 million rows — execution times are often just a few seconds, with an almost systematic OK status.
Governance and data lineage
Once ETL processes have been converted to SQL, {oa.tbx} keeps the complete trace between the original logic and the executed SQL. This data lineage is exposed at two levels of detail, suited to different needs.
Key point: a synthetic view for governance, an exhaustive detail view for maintenance.
High-level pipeline view: only source and target tables are visible; intermediate ETL stages are grouped into a single SQL block. A readable view, even for complex pipelines.
Detail on click for a transformation: clicking a stage (a Transformer, for example) displays its generated SQL equivalent, along with its inputs/outputs and its original metadata (type, stage ID). The mapping between the original ETL logic and the executed SQL is immediate.
Result: complete governance of the migrated pipeline, from the overview down to the executed SQL, regardless of the original ETL platform.
SQL as the pivot format
The SQL produced by {openAudit} becomes the primary vehicle for the transformations.
Business rules, joins, aggregations, and calculations are expressed in an open, version-controllable format, independent of legacy ETL tools.
Result: a SQL-based ETL/ELT chain, orchestrated by workflows and run in an open environment.
