How to convert Pentaho (PDI) jobs to Databricks: a step-by-step migration guide

Pentaho Data Integration (PDI, also known as Kettle) still runs a lot of nightly ETL: transformations built in Spoon years ago, chained into jobs, launched by Kitchen from cron. It works, but it runs on servers someone has to patch, it scales by adding bigger machines, and fewer engineers know it every year.

Moving that logic to Databricks is mostly mechanical, if you do it in the right order. Here’s the approach we use.

1. Inventory the estate from the files themselves

Every transformation (.ktr) and job (.kjb) is an XML file, which makes the inventory unusually easy. If your work lives in a database repository or on Pentaho Server, export it to files first.

Then count what you actually have. Each step in a transformation is recorded with a <type> element, so a one-liner shows the shape of the estate:

grep -rhoE '<type>[A-Za-z]+</type>' --include=*.ktr . | sort | uniq -c | sort -rn

(Database connection types such as ORACLE appear in the same list; ignore them.) The result tells you how much is standard steps that convert cleanly and how much is custom code. Look in particular for:

• ScriptValueMod (Modified JavaScript) and UserDefinedJavaClass: logic that has to be read and rewritten by hand.
• DimensionLookup and CombinationLookup: slowly changing dimensions and surrogate keys, which need a deliberate design on Delta.
• Variables and parameters in kettle.properties and job parameters: environment settings, paths and dates you must carry over.
• Connections in shared.xml and inside each file: your list of source systems.

Also record, for each job, its schedule, its runtime and who uses the output. As with any legacy estate, part of it will feed nothing anyone still reads. Retire that before you migrate it.

2. Map each PDI step to its Databricks equivalent

PDI streams rows between steps; Databricks works on whole datasets with Spark SQL. That changes some habits: you no longer sort before a join or a group by, and lookups become joins. The mapping for the most common steps:

PDI step	On Databricks
Table input	Ingest into a bronze Delta table (Lakeflow Connect or a JDBC read); for large or frequently changing sources, use change data capture instead of full reloads
Text file input, CSV file input	Auto Loader into bronze, which tracks which files it has already processed
Excel input	A Python read (for example with pandas) written to Delta
Select values, Filter rows, Calculator, String operations, Value mapper	Expressions in a SQL SELECT, in the silver layer
Stream lookup, Database lookup	A join
Merge join, Sort rows	A join; no sort needed
Group by, Memory group by	GROUP BY; no pre-sorting
Unique rows	DISTINCT or a window function
Insert/Update, Update, Delete	MERGE INTO on a Delta table
Dimension lookup/update (SCD type 1 or 2)	AUTO CDC (previously APPLY CHANGES) with SCD type 2 in Lakeflow Declarative Pipelines, or a MERGE
Combination lookup/update	MERGE with an identity column as the surrogate key
Modified JavaScript, User Defined Java Class	Rewrite in SQL where possible, otherwise a Python function; test each one
Table output, bulk loaders	Write to a Delta table

Write the converted transformations as Lakeflow Declarative Pipelines (formerly Delta Live Tables) or plain SQL and Python notebooks, organized in bronze, silver and gold layers. Declarative pipelines also give you the data quality checks PDI never had: expectations on each table that fail or quarantine bad rows instead of loading them silently.

3. Rebuild the jobs as Lakeflow Jobs

PDI jobs are orchestration, and they map closely to Lakeflow Jobs (formerly Databricks Workflows):

• Transformation and Job entries become tasks.
• Success and failure hops become task dependencies, using run-if conditions for the failure paths.
• Start entry schedules, cron and the Pentaho Server scheduler become the job schedule, or a file-arrival trigger where a job used to poll for files.
• Mail entries become job notifications.
• SQL entries become SQL tasks.
• File management entries (copy, move, SFTP) usually disappear: files land in cloud storage and Auto Loader picks them up.

Keep the job boundaries the business recognizes, such as “nightly finance load”, so the people who watch these jobs today can still find them.

4. Run both side by side and compare every output

Conversion is the easy part. The part that keeps the business’s trust is proving the new pipelines produce the same numbers:

1. Point the converted pipelines at the same sources and run them alongside PDI for an agreed period.
2. Compare each target table: row counts, sums of key measures, and a full row-level comparison for the tables that feed finance or regulatory reports.
3. Explain every difference. Most come from a few repeatable causes: rounding and data types, how nulls and empty strings are treated, string trimming, and time zones.
4. Cut over one domain at a time, once its comparisons have been clean for an agreed number of runs and its owner has signed off.

5. Switch off the old servers

A migration only pays back when the Pentaho servers, Carte nodes and cron jobs are actually turned off. Put a decommission date on each wave from the start, and remove the old schedules the day a wave cuts over, so nothing keeps running “just in case”.

The short version

Export everything to files, count the step types, map standard steps to SQL and Delta, rewrite the scripted steps by hand, rebuild jobs as Lakeflow Jobs, and prove every table in a parallel run before you switch.

We use the same approach for Teradata and Informatica PowerCenter migrations. If you want an estimate for your own Pentaho estate, the Data Platform Assessment includes the inventory, the effort by step type and a fixed-price plan for each wave. See also our cloud data migration service.