Agent-assisted migration of legacy ETL jobs to PySpark

1,000+legacy jobs inventoried

3 checkscolumns, row count, whole rows

[ Architecture ]

How it works

Run a scenario to follow it through the system, step by step. Select a component to read its role and see what it connects to.

A legacy job rebuilt one for one in PySpark: plain code writes the skeleton, an agent fills only the marked gaps, and the job is accepted when its outputs match today's. Fictitious data.

file

the legacy ETL tool

Daily load job

Reads a source table, looks up a reference table and writes a reporting table.

ConsoleSimulated run · illustrative data

Press Migrate a legacy job to watch each step run through the system.

01Legacy ETL & MDMJob exported with its settings and run order
02Job readerRead into a job description · no model call
03LineageSource, lookup and target tables linked
04PySpark skeletonSkeleton written in the same order · 2 gaps marked
05Coding agentGaps filled from the job's mapping · behaviour kept
06Baseline checkColumns, row count, then whole rows match
✓The job is accepted. On the same inputs, its outputs are the same as today's.

Problem

A legacy ETL tool and the flows of a master-data hub (MDM) have to move to PySpark on the cloud. Nobody had a full map of which jobs feed which tables, and the reports downstream must not change.

Data

Exported job definitions, database views and procedures, the table catalogue, and frozen copies of today’s inputs and outputs. The agent works on these copies only, never on the live systems.

Approach

Jobs and flows are parsed into a lineage between jobs and tables, which gives the migration plan and the run order.
Plain code reads every component of a job and writes a PySpark skeleton in the same order, with the same names. Every part that needs a decision is marked as a gap.
An agent fills only those gaps. It never invents a column or a business rule, keeps today’s behaviour even when it looks wrong and flags it, and stops to ask a person when a rule is ambiguous. Each decision is logged with the job.
The first phase is a one-for-one rebuild, so every job can be compared with today’s. Simplifying the jobs comes after.

Evaluation

Each migrated job runs on the same inputs as today, and its outputs are compared in three passes: columns, row count, then whole rows, with no key needed. A procedure that rewrites its own table is checked from a snapshot taken before it runs.

Result

More than a thousand legacy jobs were inventoried and their lineage rebuilt, giving the client a map of its jobs and tables that did not exist before. The method carries over to another source tool: only the job reader and the agent’s skills change.

My role

Tech lead with two developers: roadmap, lineage and acceptance methods, and the rules the agent works under.