Recovering what a decade of undocumented Kettle transformations actually does
PostgreSQLSQL ServerdbtThe team gained a readable map of an estate that had been effectively opaque, retired a set of jobs confirmed to feed nothing, and had the artifacts needed to scope a replacement honestly rather than …
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
- 1Parsed KTR and KJB files to extract table-level and column-level lineage without needing the design tool
- 2Reconciled static lineage against runtime logs to distinguish live jobs from dormant ones
- 3Documented embedded business rules found in JavaScript steps, calculators, and hand-written SQL
- 4Identified orphaned target tables and transformations with no downstream consumer
- 5Produced lineage diagrams and a per-transformation rule catalog in a format the team can maintain
Outcome
The team gained a readable map of an estate that had been effectively opaque, retired a set of jobs confirmed to feed nothing, and had the artifacts needed to scope a replacement honestly rather than guess at it.
Technologies
PostgreSQLSQL ServerdbtRelated Use Cases
Pentaho to Modern ELT Stack Migration
Planning and executing a staged move off Pentaho Data Integration onto a modern ELT stack, without a big-bang cutover of hundreds of transformations.
Pentaho Job Failure and Data Integrity Support
Ongoing support for a Pentaho Data Integration estate that must keep running reliably while a longer-term replacement is planned.
Ready for a Pentaho Health Check?
AceMQ's senior Pentaho engineers have handled this exact type of engagement before. Whether you need architectural guidance, hands-on remediation, or an ongoing managed partnership, we're ready to help.