How to migrate Pentaho to Databricks

A Guide to Migrating from Pentaho to Databricks

I've spent more nights than I care to admit staring at a stalled Pentaho Data Integration (PDI) job, watching the log files tick by, and praying the JVM doesn't run out of memory before the month-end reports are done. If you're reading this, you probably have too.

Pentaho, or Kettle as many of us old-timers still call it, has been a workhorse. Its visual, drag-and-drop interface made ETL accessible. For a long time, it was a perfectly reasonable choice for structured data warehousing. But the world changed. Data volumes exploded. Unstructured data became critical. And the demand for near-real-time analytics and machine learning moved from a "nice-to-have" to a board-level imperative.

Suddenly, our reliable PDI server started to feel less like a workhorse and more like a relic. The very things that made it great—its node-based, record-by-record processing model and its monolithic server architecture—became its biggest limitations. Scaling is a nightmare, maintenance is a chore, and integrating with the modern data stack feels like fitting a square peg in a round hole.

This brings us to Databricks. And let me be clear from the outset: this is not a sales pitch for a silver bullet. A Pentaho to Databricks migration is not a simple "lift and shift." It's a fundamental paradigm shift. It's about moving from a visual, imperative, and often rigid ETL process to a code-first, declarative, and infinitely scalable data lakehouse architecture.

I've led these migrations. I've seen them succeed spectacularly and I've seen them get bogged down in technical debt and misunderstandings. This is the guide I wish I had when I started—a no-fluff, first-person account of what it really takes to move from the world of Kettle transformations to the world of Apache Spark on Databricks. We'll cover everything from initial analysis and code conversion to orchestration and troubleshooting, all based on in-the-trenches experience.

Part 1: Before You Write a Single Line of Code: The Audit and Strategy Phase

This is where most projects go wrong. The temptation is to jump right in, pick a simple job, and start converting it. Resist this urge. A successful ETL migration is 90% planning and 10% execution.

Your first step isn't coding; it's archaeology. You need to understand what your Pentaho environment is actually doing.

The Challenge: The Black Box of PDI

Pentaho jobs are stored as XML files (.ktr for transformations, .kjb for jobs). While human-readable, they are incredibly verbose and interconnected. A single business process might span dozens of jobs and transformations, calling each other, setting variables, and passing rowsets in memory. It's a tangled web, and documentation is often outdated or non-existent.

Your goal is to perform a thorough metadata extraction and analysis. You need to answer:

• Inventory: How many jobs and transformations do we have?
• Dependencies: Which jobs call which transformations? What are the execution dependencies?
• Complexity: Which transformations are simple (e.g., table copy) versus complex (e.g., heavy JavaScript, looping, web service lookups)?
• Redundancy: How many transformations are doing the exact same thing? (I once found 17 different transformations that all did a minor variation of looking up a customer dimension).
• Business Logic: Where is the critical business logic hidden? Is it in a "Filter Rows" step, a "Database Join," or buried in a "Modified Java Script" step?

Your Approach: "Refactor and Re-architect," Not "Lift and Shift"

You cannot—and should not—aim for a 1:1 migration. Trying to replicate Pentaho’s step-by-step, procedural logic in Spark is a recipe for disaster. You'll end up with horrendously inefficient and unmaintainable code. For example, forcing Spark to process data row-by-row with a UDF (User Defined Function) to mimic a JavaScript step negates almost all of Spark's performance benefits.

Instead, the goal is to extract the business intent of a PDI job and re-implement it using the declarative, set-based patterns of the data lakehouse. This means consolidating dozens of Pentaho steps into a single, elegant Spark SQL query.

This is the most critical phase and a message for the Project Managers and CXOs reading this: Rushing the analysis to "show progress" will dramatically increase project cost and risk. Investing in a deep, automated audit upfront will pay for itself tenfold by preventing rework and ensuring the new system is actually an improvement, not just a more expensive version of the old one.

---

Part 2: The Rosetta Stone: Mapping Pentaho Concepts to Databricks

Once you have your inventory, you need a mental map to translate Pentaho's components to the Databricks world. This is your core translation guide. The philosophy is to move from granular steps to broader, set-based operations.

Here’s a detailed mapping table based on common Kettle transformations:

Pentaho Component	Databricks Equivalent	Key Considerations & Paradigm Shift
Input Steps
Table Input	spark.read.jdbc(...)	Straightforward. Use JDBC connections. Ensure you use dbtable or a query. For large tables, enable partitioning (partitionColumn, lowerBound, etc.) to parallelize the read.
CSV File Input	spark.read.csv(...) or COPY INTO	COPY INTO is often faster and more robust for loading files into Delta Lake tables. Define the schema explicitly (.schema()) to avoid type inference errors.
Microsoft Excel Input	spark.read.format("excel").load(...)	Requires an external library (e.g., com.crealytics:spark-excel). Often a sign of manual processes. A good opportunity to ask: "Can we get this data from a proper source system instead?"
Output Steps
Table Output / Update / Insert/Update	df.write.format("delta").mode("overwrite/append").saveAsTable(...) or MERGE INTO	This is a huge win. The MERGE INTO command on a Delta Lake table elegantly replaces the complex "Lookup -> Filter -> Update/Insert" pattern used for SCDs (Slowly Changing Dimensions). It's atomic and far more efficient.
Text File Output	df.write.csv(...) or ... .parquet(...)	Always prefer writing to Parquet or Delta format over CSV. It's columnar, compressed, and vastly more performant for downstream analytics.
Lookup Steps
Database Lookup / Stream Lookup	DataFrame.join(...)	Move away from row-by-row lookups. Pre-load your lookup/dimension tables into DataFrames and perform a broadcast join (for small tables) or a shuffle-sort-merge join (for large tables). This is a core concept of Apache Spark.
Join Steps
Merge Join / Sorted Merge	DataFrame.join(...)	Spark's join optimizer handles the strategy. You just declare the join (dfA.join(dfB, on="key", how="inner")). No need to pre-sort data like in Pentaho; Spark handles the data distribution and shuffling.
Transform Steps
Filter Rows	DataFrame.filter() or WHERE clause in Spark SQL	A direct and simple translation. This is one of the few 1:1 mappings.
Select values	DataFrame.select() or withColumnRenamed()	Used for selecting, renaming, and reordering columns. Very intuitive.
Formula / Calculator	DataFrame.withColumn() using Spark SQL functions	Instead of a separate step, this becomes a column transformation. Example: df.withColumn("total", col("price") * col("quantity")). Spark has a rich library of built-in functions.
Modified Java Script Value	Python/Scala UDF or (preferably) native Spark SQL functions	This is a major danger zone. Avoid UDFs unless absolutely necessary. A UDF is a black box to Spark's optimizer. First, try to rewrite the JavaScript logic using a chain of when/otherwise and built-in functions. Only use a UDF as a last resort.
Group by / Memory Group by	DataFrame.groupBy(...).agg(...)	Another core Spark pattern. Declarative and highly optimized. You specify the grouping keys and the aggregations (sum, avg, count, collect_list, etc.).
Sort rows	DataFrame.orderBy() or sort()	In Spark, ordering is expensive as it forces a global shuffle. Only use orderBy when you need a final, sorted output (e.g., for a report). For operations like joins, sorting is handled internally.
Flow Steps
Blocking step	N/A (Handled by Spark's DAG)	The concept of "blocking" to wait for all rows to arrive is implicit in Spark's execution model. Operations like groupBy or join are natural blocking points (shuffle boundaries). You don't manage this manually.
Copy rows to result / Get rows from result	Passing DataFrames between tasks in a Databricks Workflow	Instead of an in-memory rowset, you might write an intermediate DataFrame to a Delta table and have the next task read it. This provides resilience and debuggability.

---

Part 3: From Clicks to Code - Refactoring Complex Pentaho Logic

Simple mappings are one thing. The real challenge in a data pipeline modernization project is refactoring the tangled, procedural logic that defines your core business rules. Let's look at two classic examples.

Example 1: The Ubiquitous SCD Type 2 Transformation

In Pentaho, implementing a Slowly Changing Dimension (SCD) Type 2 load is a multi-step, visual ballet.

The Pentaho Way:

1. Table Input: Read the source data.
2. Database Lookup: Look up the dimension table on the natural key. Get the surrogate key and versioning columns.
3. Filter Rows:
   • If no match -> New Record.
   • If match, but key attributes have changed -> Expire Old Record & Insert New Record.
   • If match and no change -> Do nothing.
4. Add constants/sequences: Set is_current flags, valid_from, valid_to dates.
5. Insert/Update Steps: Route the filtered rows to different steps to perform the actual database operations.

This is brittle, hard to debug, and performs a row-by-row lookup against the database, which scales poorly.

The Databricks/Delta Lake Way:

With Databricks and Delta Lake, this entire mess collapses into a single, atomic, and highly readable MERGE INTO statement.

    -- Assumes you have a source view/table 'updates_view' and a target Delta table 'dim_customers'

    MERGE INTO dim_customers target
    USING updates_view source
    ON source.customer_id = target.customer_id

    -- Case 1: New customers. Insert a new record.
    WHEN NOT MATCHED THEN
      INSERT (customer_id, address, is_current, valid_from, valid_to)
      VALUES (source.customer_id, source.address, true, current_date(), '9999-12-31')

    -- Case 2: Existing customers with a changed address.
    -- This requires a multi-step merge or a more advanced pattern.
    -- The common approach is to first expire old records, then insert new ones.
    -- For simplicity, let's show the logic for a Type 1 update (overwrite).
    -- A full Type 2 requires more steps, often handled in a staging layer.
    WHEN MATCHED AND target.address <> source.address THEN
      UPDATE SET
        address = source.address,
        last_updated_ts = current_timestamp()

For a true SCD Type 2, you'd combine this with an INSERT for the new versioned rows and a separate UPDATE to expire the old ones, often orchestrated in a single transaction. Delta Live Tables (DLT) further simplifies this with its APPLY CHANGES INTO syntax, which is purpose-built for this kind of change data capture. This is a perfect example of replacing procedural complexity with a declarative, business-focused implementation.

Example 2: The Dreaded "Modified Java Script" Step

This is the ultimate "black box" in PDI. It's where developers who couldn't find a built-in step just wrote custom code. This code often contains critical business logic.

The Pentaho Way (a simplified example):

    // Assume input rows have 'first_name', 'last_name', 'state_code'
    var full_name = first_name + ' ' + last_name;
    var region;

    switch(state_code) {
        case 'CA':
        case 'OR':
        case 'WA':
            region = 'West';
            break;
        case 'NY':
        case 'MA':
        case 'NJ':
            region = 'East';
            break;
        default:
            region = 'Central';
    }

The (Bad) Databricks Way - Using a UDF:

You could wrap this logic in a Python UDF. It seems easy, but it kills performance. Spark's Catalyst Optimizer cannot "see" inside the UDF, so it can't perform predicate pushdown or other optimizations.

    # Don't do this unless you have to!
    from pyspark.sql.functions import udf
    from pyspark.sql.types import StringType

    def assign_region(state_code):
        if state_code in ('CA', 'OR', 'WA'):
            return 'West'
        elif state_code in ('NY', 'MA', 'NJ'):
            return 'East'
        else:
            return 'Central'

    assign_region_udf = udf(assign_region, StringType())

    df_with_region = df.withColumn("region", assign_region_udf(df["state_code"]))

The (Correct) Databricks Way - Using Native Functions:

The best practice is to express this logic using Spark's own functional constructs. This is fully optimizable and will execute orders of magnitude faster on a large dataset.

    from pyspark.sql.functions import col, concat_ws, when

    df_transformed = df.withColumn(
        "full_name",
        concat_ws(" ", col("first_name"), col("last_name"))
    ).withColumn(
        "region",
        when(col("state_code").isin('CA', 'OR', 'WA'), 'West')
        .when(col("state_code").isin('NY', 'MA', 'NJ'), 'East')
        .otherwise('Central')
    )

This code is not only more performant but also more readable and testable. It clearly states the business rule without hiding it in opaque code. The refactoring ETL process from Pentaho to Databricks is fundamentally about this kind of translation.

---

Part 4: Who's in Charge Now? Orchestration and CI/CD

In Pentaho, you orchestrate workflows using Jobs (.kjb files), which call transformations and other jobs. You trigger these using the built-in scheduler or an external one calling kitchen.sh or pan.sh. This works, but it's isolated. Monitoring, alerting, and dependency management are rudimentary.

In the Databricks ecosystem, your primary tool for job orchestration is Databricks Workflows.

Databricks Workflows vs. Pentaho Jobs:

• Multi-Task Jobs: A single Databricks Workflow can be a Directed Acyclic Graph (DAG) of multiple tasks. A task can be a notebook, a Python script, a dbt model, or a SQL query. This allows you to build complex dependencies (e.g., "run my data cleansing notebook, and if it succeeds, run the aggregation notebook and the data quality check in parallel").
• Parameterization: You can pass parameters between tasks, replacing Pentaho's "get variables" and "set variables" steps in a much more robust way.
• Repair and Rerun: If one task in a 10-task workflow fails, you can fix it and rerun from the point of failure. This is a massive time-saver compared to re-running an entire Pentaho job.
• Integrated Monitoring & Alerting: Get alerts on Slack, PagerDuty, etc., directly from the workflow UI.
• Git Integration (CI/CD): This is a game-changer. Your ETL logic (notebooks, Python scripts) lives in a Git repository. Databricks Workflows can pull directly from Git, allowing for true CI/CD for data pipelines. You can have development branches, pull requests for code reviews, and automated deployment pipelines—concepts that are very difficult to implement in Pentaho's XML-based world.

This move to a code-and-Git-based workflow is a significant cultural shift for a traditional ETL team, but it's one that brings immense benefits in terms of reliability, collaboration, and governance. The new Unity Catalog feature in Databricks further enhances this by providing fine-grained governance, data lineage, and discovery across your entire lakehouse.

---

Part 5: Choosing Your Weapons: The Migration Tooling Landscape

Okay, let's talk about the elephant in the room. How do you actually perform the code conversion from thousands of .ktr and .kjb files to PySpark or Spark SQL?

You have three main options:

1. The Full Manual Rewrite: Your team reads the Pentaho XML, understands the logic, and rewrites it from scratch in Databricks.

   • Pros: You get perfectly idiomatic, clean code. Forces a deep understanding of the business logic.
   • Cons: Incredibly slow, expensive, and high-risk. Prone to human error and interpretation mistakes. Your senior developers, the only ones who can do this, become a massive bottleneck. For any large-scale migration, this is unfeasible.

2. In-House Scripting: A smart developer on your team says, "Hey, it's just XML. I can write a Python script to parse it and generate some basic PySpark."

   • Pros: Seems cheap and gives you full control.
   • Cons: This is a classic trap. You are not just building a script; you are building a compiler. You will quickly discover the immense complexity of Pentaho's internal logic, variable propagation, context, etc. Your script will become a fragile, unmaintainable monster, and your smart developer will now be full-time support for their own tool instead of migrating pipelines.

3. Automated Conversion Tools: These tools are designed to parse the Pentaho XML and generate target Databricks code. However, their quality varies wildly.

   • Generic/Syntactic Converters: Many tools perform a simple, regex-like mapping. They'll turn a "Filter Rows" step into a .filter() clause. They fail miserably with complex logic, variable context, or job orchestration. They generate code that looks right but is often logically incorrect or horrendously inefficient. They create a false sense of progress.
   • Context-Aware Converters (Travinto): This is where I've had the most success, and I want to be specific about why, without it sounding like a sales pitch. When my team evaluated tools for a major Pentaho to Databricks project, we found that most solutions fell into the "syntactic converter" trap. They gave us code, but it was low-quality and required a near-total rewrite, defeating the purpose.

   Travinto's approach was different, and here’s what made it work for us in a real-world project:
   It didn't just translate; it analyzed. It built a complete dependency graph of our entire Pentaho environment—jobs, transformations, database connections, and all.

   • Deep Analysis: It didn't just map Step A to Function A. It understood the flow. It could trace a variable set in a parent job, through a transformation, into a JavaScript step, and correctly apply its value in the converted PySpark code. This context is everything.
   • Pattern Recognition: It identified common Pentaho patterns (like the SCD2 example) and replaced them with the correct, idiomatic Databricks equivalent (MERGE), rather than a clunky, literal translation of the steps.
   • Focus on Refactoring: The tool's output wasn't just code; it was a refactoring plan. It flagged redundant transformations, identified complex logic that needed senior developer review, and automatically converted the 70-80% of "standard" ETL work.

   Crucially, it wasn’t a magic button. It was an accelerator. It handled the tedious, error-prone bulk conversion, which freed up my senior architects and developers to focus on the highest-value tasks: optimizing the most complex 20% of the logic, designing the new data models in the cloud data platform, and ensuring the business outcomes were met. It turned a multi-year, high-risk rewrite into a manageable, phased project.

---

Part 6: Field Notes: Your Troubleshooting FAQ

You will run into problems. Here are some of the common "gotchas" I've seen and how to solve them.

• Q: My converted Spark job is syntactically correct but produces different results than the Pentaho job.

  • A: 99% of the time, this is due to Null Handling. Pentaho and Spark/SQL treat nulls differently in joins and comparisons. For example, in SQL, null = null evaluates to false or unknown, while in some Pentaho steps, you might have been implicitly treating them as equal. You need to explicitly handle nulls in your join keys: (a.key = b.key) OR (a.key IS NULL AND b.key IS NULL).

• Q: My Spark job is much slower than I expected. What's wrong?

  • A: Welcome to performance tuning in Spark! The most common culprits are:
    1. Data Skew: One partition has way more data than the others. Use the Spark UI to diagnose this. You can mitigate it by salting your join keys.
    2. Massive Shuffles: A groupBy or join on a high-cardinality key is causing a huge amount of data to be moved across the network. Look for opportunities to filter data before the shuffle.
    3. Inefficient UDFs: As discussed, a Python UDF can cripple your job. Rewrite it with native functions.
    4. Wrong File Formats: You're reading/writing millions of small CSV files. Consolidate them and convert to Delta/Parquet using OPTIMIZE and Z-ORDER.

• Q: How do I handle Pentaho's environment variables (${INTERNAL_JOB_VARIABLE})?

  • A: Use Databricks Widgets for notebooks that need to be run interactively with different parameters. For production jobs, define parameters in your Databricks Workflow and access them in your code using dbutils.widgets.get("my_parameter"). This is a much cleaner and more explicit way to manage configuration.

• Q: How do I validate that the migrated data is correct?

  • A: Data validation is non-negotiable. Don't just trust the job ran successfully.
    1. Reconciliation Queries: Write SQL queries that run against both the old Pentaho-loaded target and the new Databricks-loaded target. Compare row counts, SUMs of key metrics, MIN/MAX on dates, etc.
    2. Automate with Tools: For more robust validation, use a library like Great Expectations to define your data quality rules as code and run them as part of your Databricks workflow.

---

The View from the C-Suite: Translating Tech to Business Value

For the CXOs and Project Managers, let's distill this down. Why undertake this complex, costly project?

• From Capital Expense to Operational Expense: You move from large, upfront licensing and hardware costs for Pentaho servers to a pay-as-you-go cloud model with Databricks. This improves cash flow and TCO.
• Scalability on Demand: You are no longer constrained by the physical size of your PDI server. If you have a massive data load for month-end processing, Databricks can automatically spin up a 100-node cluster to handle it, and then spin it down when finished. This eliminates bottlenecks and ensures business deadlines are met.
• Speed to Insight: The data lakehouse architecture, combining the scale of a data lake with the reliability of a data warehouse, allows your analysts and data scientists to work on the same, fresh data. This drastically reduces the time from data ingestion to business insight.
• Unlocking AI/ML and Future Growth: Pentaho is an ETL tool. Databricks is a unified platform for data engineering, Databricks SQL analytics, and machine learning. By migrating, you are not just modernizing your ETL; you are positioning your company to leverage advanced analytics and AI on your core business data, creating a significant competitive advantage.

Final Thoughts: Your Path Forward

Migrating from Pentaho to Databricks is a data pipeline modernization project that is more about people and process than it is about technology. It's about shifting your team from a visual, procedural mindset to a code-first, declarative one.

My strongest advice is this:

1. Don't Boil the Ocean: Start with a single, high-value, but non-catastrophic data pipeline. Choose one that is causing pain (e.g., it's slow, it fails often) but won't bring down the entire company if the migration timeline slips.
2. Prove the Value: Use this first migration as a pilot project. Document the performance gains, the reduction in maintenance overhead, and the new capabilities. This success story will be your key to getting buy-in for the broader migration program.
3. Invest in the Right Tools and Training: This is a paradigm shift. Your team will need training on Spark, Python, and the Databricks platform. And critically, you need to leverage tools that accelerate, rather than hinder, your progress.

In my experience, a tool like Travinto proved to be that accelerator. It managed the heavy lifting of analysis and code conversion, de-risking the project and allowing my best people to focus on what they do best: designing robust, scalable data architecture. It allowed us to deliver a superior system faster and with more confidence than a manual rewrite ever could have. It’s not about replacing your team; it's about empowering them.

The journey is challenging, but the destination—a scalable, reliable, and future-proof cloud data platform—is well worth the effort. Good luck.