Translating Pentaho to dbt
Translate Pentaho (Kettle) .ktr/.kjb packages, JavaScript and Java steps included, into silver and gold dbt-trino models on an Iceberg lakehouse, each step labelled with how faithfully it was carried over.
EKOS Lakehouse translates Pentaho Data Integration (Kettle) packages into dbt-trino models in your lakehouse pipeline repository: silver models for the cleansing part of each transformation, gold models for what it writes. Every step is labelled with how faithfully it was carried over, Trino plans every model before anything is proposed, and nothing is merged for you: translating proposes a branch, and merging it is the approval.
Design: EKOS RFC 0200 (umbrella), 0201 (the Kettle model), 0202 (expressions and the relational IR), 0203 (JavaScript and Java steps), 0204 (medallion lowering), 0205 (model-drafted SQL), 0206 (compiled plugins) and 0207 (the console tab). It needs a lakehouse project whose plan has been generated: Pentaho's tables resolve to the silver models it already builds.
Fidelity labels
| Label | Meaning |
|---|---|
| exact | the same semantics in SQL |
| rewritten | a deterministic rule a reviewer should read: Kettle compares nulls as values, treats an empty string as null, numbers Sunday as 1, groups only consecutive rows, … |
| llm-drafted | a model drafted it and every check passed (section 5) |
| gap | not translated; its original text is kept in the proposal, and every model that depends on it is disabled |
A gap never passes through silently: a disabled model cannot be built by accident, and the proposal lists each gap with its reason.
1. Point it at a package
A package is a directory of .ktr / .kjb files, optionally with repository exports, shared.xml, kettle.properties and plugins/. Any [[sources]] directory that holds Kettle files is a package named after the source, or you can name packages explicitly:
[extensions.lakehouse]
source-database = "shop" # Pentaho connections to this database read the lakehouse
[extensions.lakehouse.pentaho-packages]
etl = "pentaho/etl"
ekos-lakehouse lakehouse pentaho scan
scan lists each package's transformations, jobs, steps by category, step types EKOS does not know, and links between files that do not resolve (Mapping, Transformation/Job Executor and job entries, with ${Internal.Entry.Current.Directory} resolved). Variables are resolved from parameters, kettle.properties and Set Variables entries, each with where its value came from; an unresolved one is a gap.
Connection passwords never leave the files. Kettle's Encrypted … passwords and every password element are removed when a file is read, before anything reaches the artifact store or the ledger.
2. Translate
ekos-lakehouse lakehouse pentaho translate etl
| Pentaho | dbt |
|---|---|
| Table input on the lakehouse source | reads its silver models ({{ ref('public__orders') }}); its PostgreSQL is rewritten for Trino by a whitelist, and anything outside it stops that branch by name |
| A table one transformation writes and another reads | a ref() to the writer's model, not a physical table |
| Each input and its row-local steps (filters, calculations, casts, string operations, value mapping, dedupe) | a silver model in silver_pentaho |
| 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 test |
Joins, lookups, group by, sort, unique rows, analytic queries, Switch/Case and Filter rows all lower into one typed relational IR that keeps Kettle's semantics. Every model is then planned by Trino (EXPLAIN), and a model it refuses is disabled. The result goes on branch ekos/pentaho/<package>/<id> with proposals/pentaho-<package>.md. Use --out DIR to preview into a directory instead, or --no-explain to skip Trino.
3. Read the result
ekos-lakehouse lakehouse pentaho report --package etl
ekos-lakehouse lakehouse pentaho report --package etl --step "fact_orders.ktr:tax" # original next to SQL
The console's Pentaho → dbt tab shows the same thing. You get the packages, the transformation graph coloured by fidelity, the models and the steps; clicking a step puts its original code next to its SQL. The tab also lists open questions and shows the proposal. It can run a translation as a job, and a translation that would call a paid model is refused unless the tenant allows metered calls. AI agents read the translation with the MCP tool ekos_pentaho_translation.
4. JavaScript and Java steps
Modified JavaScript Value, User Defined Java Class and User Defined Java Expression steps are executed symbolically rather than pattern-matched. Variables become SQL expressions, if/else becomes CASE, and dropping a row becomes a filter. Kettle's JavaScript semantics are kept: null + x prints "null" in a string, and null is 0 in arithmetic. Loops, state across rows, try and calls into the Kettle API are gaps that name the construct that stopped them.
Compiled plugin steps (plugins/*.jar) need a Java decompiler. EKOS defines the seam (JavaDecompiler); the private EKOS binary build implements it, recovering JVM bytecode to statements (93.8% of the method bodies of pdfbox.jar), and the recovered processRow is then translated like a UDJC step. The public build reports compiled plugins as gaps.
5. Model-drafted SQL for the rest
A model can propose SQL for a code step the translator left as a gap, as long as that step has one input with known fields and declared outputs. A draft counts only if it passes three checks:
- Every expression parses as Trino SQL.
- Anti-invention: every identifier is an input field, every function is on the translator's list, and every literal appears in the step's own code.
- Every declared output has an expression, and nothing else is set.
A failed check goes back to the model with its findings, up to three attempts. A "cannot" answer leaves the gap and records the model's reason. What survives is labelled llm-drafted and is planned by Trino like every other model.
[llm.tasks.translate]
provider = "ollama" # local: runs without a flag
model = "llama3"
ekos-lakehouse lakehouse pentaho translate etl # Ollama route
ekos-lakehouse lakehouse pentaho translate etl --allow-metered # any paid route; budget from [llm] budget-usd
Without --allow-metered, a paid route drafts nothing; the command says how many steps a model could draft and through which model. On 15 steps the deterministic translator also handles, Claude Haiku 5.5 matched it on 11, row by row on Trino over rows with nulls and edge values; nearly every disagreement was on the null row. That is why a draft stays labelled for review.
6. Verify it against Pentaho, and tell us
The checks prove each model is well-formed, planned 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 we have not yet run that comparison on real Pentaho estates.
ekos-lakehouse lakehouse pentaho translate <package> --out /tmp/pentaho-preview- Run the original with
pan.shinto a scratch schema (never production). dbt build --select path:models/silver/pentaho path:models/gold/pentaho- Compare in Trino: counts, then
EXCEPT ALLboth ways over the shared columns.
The verification guide has the exact queries, and the repository has an issue template for the result. Send counts and fidelity labels, never row values. A mismatch is the most useful result you can send.
What is not translated
File and FTP inputs and outputs, web services, executors that run a transformation per row, metadata injection, Normaliser/Denormaliser, Data validator, Delete, Combination lookup, and job entries other than running transformations; a job's order is recorded for the orchestrator but not written as models. Each is listed in the proposal with its reason.