Pentaho to dbt Converter
An open-source converter that translates Pentaho Data Integration (Kettle) .ktr and .kjb packages, JavaScript and Java steps included, into dbt models, and labels every step with how faithfully it was carried over.
Teams leaving Pentaho Data Integration (Kettle) usually face a hand rewrite: hundreds of .ktr transformations and .kjb jobs, each a graph of steps with Kettle's own rules for nulls, empty strings and grouping. EKOS Lakehouse reads those files and translates them into dbt models: silver models for the cleansing part of each transformation, gold models for what it writes, dbt snapshots for slowly changing dimensions and dbt tests for Abort steps.
It does not hide what it could not carry over. Every step is labelled, a step it cannot translate disables the models that depend on it, and the result is proposed on a Git branch for a person to review. The converter is open source (MIT), part of EKOS Lakehouse, and targets dbt-trino over Apache Iceberg.
A worked example
This transformation comes from the converter's own test suite. fact_orders.ktr reads orders, computes an amount, drops void orders, looks up the customer's surrogate key in a type 2 dimension, and writes a fact table. A second branch aborts the run if any amount is negative.
| Kettle step | Type | What it does |
|---|---|---|
orders |
Table input | SELECT id, customer_id, ordered_at, qty, price, status FROM public.orders |
amount |
Calculator | amount = qty × price |
not void |
Filter rows | status <> 'void' |
customer key |
Dimension lookup | customer_tk from dim_customer, valid at ordered_at |
out |
Table output | truncate and load fact_orders |
negative → stop |
Filter rows → Abort | abort with "negative amount" if amount < 0 |
The input and its row-local steps become a silver model. Kettle treats an empty string as null, so the converter writes nullif. That is a rewritten step, which a reviewer should read:
-- dbt/models/silver/pentaho/shop/pentaho__shop__fact_orders__orders.sql
{{ config(materialized='table', schema='silver_pentaho', alias='fact_orders__orders') }}
WITH
n0_orders AS (
SELECT "id", "customer_id", "ordered_at", "qty", "price", nullif("status", '') AS "status"
FROM (SELECT id, customer_id, ordered_at, qty, price, status FROM {{ ref('public__orders') }} AS orders) AS s
),
n1_amount AS (
SELECT "id", "customer_id", "ordered_at", "qty", "price", "status", ("qty" * "price") AS "amount" FROM n0_orders
)
SELECT "id", "customer_id", "ordered_at", "qty", "price", "status", "amount" FROM n1_amount
The Table input reads the lakehouse's own silver model (ref('public__orders')), not the operational database. The filter and the dimension lookup become the gold model. Kettle compares nulls as values, so <> becomes IS DISTINCT FROM, and a missing dimension row gets Kettle's unknown key '0':
-- dbt/models/gold/pentaho/shop/gold__fact_orders.sql
{{ config(materialized='table', schema='gold', alias='fact_orders') }}
WITH
n2_not_void AS (
SELECT * FROM {{ ref('pentaho__shop__fact_orders__orders') }} WHERE ("status" IS DISTINCT FROM 'void')
),
n4_customer_key AS (
SELECT m.*, CASE WHEN lk.ekos_match THEN lk."customer_tk" ELSE '0' END AS "customer_tk"
FROM n2_not_void AS m
LEFT JOIN (SELECT "customer_id", "customer_tk", "date_from", "date_to", TRUE AS ekos_match
FROM {{ ref('gold__dim_customer') }}) AS lk
ON m."customer_id" = lk."customer_id" AND m."ordered_at" >= lk."date_from" AND m."ordered_at" < lk."date_to"
)
SELECT "id", "customer_id", "ordered_at", "qty", "price", "status", "amount", "customer_tk" FROM n4_customer_key
The Abort branch becomes a dbt test that fails when any row would have reached it. The dimension that dim_customer.ktr maintains becomes a dbt snapshot plus a gold dimension. A third transformation in the same package reads from a SQL Server connection that is not the lakehouse source. The converter does not guess at it. It writes the model disabled, with the reason:
-- dbt/models/gold/pentaho/shop/gold__leads.sql
-- DISABLED: not fully translated.
-- leads: `leads` is read through connection `crm` (MSSQLNATIVE crm), which is not the lakehouse source
{{ config(enabled=false) }}
Every step is labelled
| Label | Meaning |
|---|---|
| exact | the same semantics in SQL |
| rewritten | a deterministic rule a reviewer should read: empty string as null, nulls compared as values, Sunday numbered 1, grouping only consecutive rows |
| llm-drafted | a model drafted it and every check passed: it parses as Trino SQL and uses only the step's own input fields, listed functions and literals |
| gap | not translated: the original text is kept in the proposal and every model that depends on it is disabled |
Every enabled model is planned by Trino (EXPLAIN) before it is proposed, and a model Trino refuses is disabled. The translation is deterministic: the same package gives the same files. A language model is used only for steps the rules leave as gaps, and only when you configure one. A local Ollama model runs without a flag; a paid provider needs --allow-metered.
What it translates
| Pentaho | dbt |
|---|---|
| Table input on the lakehouse source | reads the silver models; its PostgreSQL is rewritten for Trino by a whitelist |
| A table one transformation writes and another reads | a ref() to the writer's model |
| Filters, calculations, casts, string operations, value mapping, dedupe | a silver model in silver_pentaho |
| Joins, lookups, group by, sort, unique rows, analytic queries, Switch/Case | lowered through a typed relational IR that keeps Kettle's semantics |
| Table output | a gold table (truncate) or an append model |
| Insert / Update | an incremental merge on the lookup keys |
| Dimension lookup/update | a dbt snapshot plus a gold dimension |
| Abort | a dbt test |
| Modified JavaScript Value, User Defined Java Class / Expression | executed symbolically into SQL: variables become expressions, if/else becomes CASE, dropping a row becomes a filter |
Not translated, each listed in the proposal with its reason: file and FTP inputs and outputs, web services, per-row executors, metadata injection, Normaliser/Denormaliser, Data validator, Delete, Combination lookup, and job entries other than running transformations. Compiled plugin steps (plugins/*.jar) are gaps in the public build.
Connection passwords never leave the files: Kettle's encrypted passwords and every password element are removed when a file is read.
Try it
curl -fsSL https://raw.githubusercontent.com/alexeyban/ekos-lakehouse/main/install.sh | sh
ekos-lakehouse lakehouse pentaho scan # transformations, jobs, unknown steps, broken links
ekos-lakehouse lakehouse pentaho translate etl --out preview/ # preview the dbt files without a branch
ekos-lakehouse lakehouse pentaho report --package etl # labels, gaps and open questions
Configuration, the console's Pentaho → dbt tab and model-drafted SQL are covered in the guide: Translating Pentaho to dbt.
Status, and a request
The checks prove that each model is well-formed, planned by Trino and invents nothing. They cannot prove that it returns the same rows as your transformation. Only running both on the same data does that, and it has not yet been done on a real Pentaho estate. If you have one, the verification guide runs the original with pan.sh and the translation with dbt on the same data and compares them. Send the counts and fidelity labels, never row values. A mismatch is the most useful result you can send.
Frequently asked questions
Can Pentaho jobs be converted to dbt automatically?
Mostly. EKOS Lakehouse translates the steps it has rules for (inputs, filters, calculations, lookups, joins, group by, dimension lookups, outputs, JavaScript and Java steps) into dbt models. It labels every step as exact, rewritten, llm-drafted or gap. Steps it cannot translate disable the models that depend on them instead of being dropped, so a person reviews what is left.
Does the converter handle JavaScript steps?
Yes. Modified JavaScript Value steps, and User Defined Java Class and Expression steps, are executed symbolically into SQL, with Kettle's own null semantics. Loops, state across rows, try blocks and calls into the Kettle API are reported as gaps that name the construct.
Do I need Pentaho installed to translate?
No. The converter reads the .ktr and .kjb XML files directly. You need Pentaho only to verify a translation by running the original transformation on the same data.
Which warehouse does the generated dbt target?
dbt-trino over Apache Iceberg tables, inside an EKOS Lakehouse pipeline repository. Pentaho's source tables resolve to the silver models the lakehouse already builds from PostgreSQL.
Is it free?
Yes. EKOS Lakehouse is open source under the MIT license. Translation is deterministic and runs without a language model. A model is used only for leftover steps, and only when you configure one.