Recovering what a decade of undocumented Kettle transformations actually does

Overview

You cannot safely migrate or modify what you cannot read. Long-lived Pentaho estates typically have no lineage documentation, and the transformation XML is the only surviving specification of the business rules.

Challenge

The company had inherited a Pentaho estate through two acquisitions. There was no diagram of what fed what, several transformations wrote to tables nobody could account for, and at least one nightly job had been failing partially for months without anyone being able to determine whether the output still mattered.

Environment

Pentaho Data Integration on-premises reading from SQL Server and flat-file drops, writing to an on-premises reporting warehouse and several downstream extracts.

Approach

AceMQ parsed the transformation and job XML directly to extract source tables, target tables, step-level logic, and job orchestration, then reconciled that static picture against runtime logs and actual database write activity to separate what runs from what merely exists.

Solution

• Parsed KTR and KJB files to extract table-level and column-level lineage without needing the design tool
• Reconciled static lineage against runtime logs to distinguish live jobs from dormant ones
• Documented embedded business rules found in JavaScript steps, calculators, and hand-written SQL
• Identified orphaned target tables and transformations with no downstream consumer
• Produced lineage diagrams and a per-transformation rule catalog in a format the team can maintain

Outcome

Technologies