Learn Pentaho - Error Handling & Debugging

#Introduction

Every pipeline will fail — it's not a question of "if", but "when". Database connections drop, the file being read changes format, strange data triggers errors. What sets great engineers apart isn't being free of errors, but the speed at which they find and fix problems.

This episode covers error handling and debugging in PDI: how to make sure failures are caught properly, how to find root causes with preview and breakpoints, and how to read logs and metrics to speed up diagnosis.

#Understanding the Types of Errors in PDI

Errors in PDI can be grouped into three types, each with a different handling strategy:

• Errors while reading data: file not found, connection failure, date format mismatch. This happens most often when a data contract changes.
• Errors during transformation: failed type conversion, division by zero, or data that doesn't match the step's rules. These usually only appear when strange data comes in.
• Errors while writing data: columns don't match the target table, unique constraints are violated, or the disk is full.

Most errors occur at a specific step with a message that's actually fairly explanatory — as long as you know where to look. The key: never ignore the red log.

#Handling Errors in Transformations

Within a transformation, there are several error handling mechanisms:

• Per-step error handling: many steps have an Error handling tab option to route failed rows to a special output, similar to the valid vs invalid row pattern in episode 6.
• Detection steps: Filter rows and Validator catch problems before a fatal error occurs.
• Tiered logging: writing the details of problem rows to a file for further investigation.

The best pattern is to prevent errors from appearing in the first place: validate early, and route suspicious rows to a clear path instead of letting them explode mid-pipeline. But for unavoidable errors, make sure there's a trail: write the key fields and error message to a log file.

When running a transformation from the command line, controlling the log level is very useful. Use the Debug level to see stream details when hunting a problem:

pan.sh -file=mytrans.ktr -level=Debug -logfile=/tmp/mytrans_debug.log

The available log levels: Error, Minimal, Basic, Detailed, Debug, and Rowlevel. Start with Basic, go up to Debug only while investigating, and don't forget to return to a normal level for production — -level=Basic is almost always enough for routine runs.

When a transformation fails, the error message usually points to the step and the offending row — for example ... at step 'Select values' .... These two pieces of information are your starting point for investigation: open that step, check the suspected fields with Preview, then trace their values back to the source. The habit of recording the full error message — not just its last sentence — will help a lot when you ask questions in the community.

#Handling Errors in Jobs

Jobs have a different error handling mechanism because of their orchestration nature. The main strategies:

• Failure hop: route failures to a special handling path — send a warning email, call a recovery job, or log it.
• Retry: some job entries support automatic retries with delays, for example retrying a connection that briefly dropped.
• Simple evaluation job entry: check conditions before continuing, so a small failure doesn't cascade into more expensive next steps.
• Abort: stop execution with a controlled failure status, complete with an explanatory message.

The industry standard you should emulate: every production job has a failure path that sends a notification and records complete context — which job, which step, what time, and the error message. Episode 13 will cover logging and observability comprehensively.

#Preview Rows and Breakpoints

Spoon's interactive debugging mechanisms are your main weapons:

• Preview: right-click a step and choose Preview to run the transformation up to that step and show a sample of the flowing rows. This is the fastest way to validate each stage.
• Breakpoint: right-click a hop and choose Set breakpoint. When the transformation runs, execution stops at that point and Spoon shows the held rows. You can continue row by row or let them all pass.
• Inspect: while a breakpoint is active, right-click the hop and choose Inspect to see the rows at that point.

These techniques let you "walk with the data" and find exactly at which step a value starts to go wrong. Combining previews at several points is often more effective than reading a long log line by line.

#Execution History as a Comparison Tool

Spoon stores the Execution history of every transformation run. After several runs, compare the Step Metrics numbers across runs: if a step starts processing slower or its error count rises, you can detect degradation before a full failure. This is like a mini dashboard that needs no external tools — just click the Execution History tab in the bottom panel.

A practical exercise: run the same transformation with different data volumes, then observe how time and row counts change. This ability to read trends between runs is your preparation for episode 13, when we discuss monitoring and observability comprehensively.

#Analyzing Step Logs and Performance Metrics

When a transformation runs, the log panel and Step Metrics tab give a health picture of each step. Things you should watch:

• Row count per step: lines input vs lines output — if the number drops drastically at a step, there's a filter or error cutting the flow.
• Execution speed: rows per second — a sudden drop indicates a bottleneck like an uncached lookup or slow disk.
• Error count per step: the real failure location, far more useful than a generic error message.
• Total execution time: the comparison between runs to detect gradual performance degradation.

To find a bottleneck, look at steps with a high active count or slowing lines read. Waiting steps — usually those doing database queries or disk writes — are the prime suspects.

#Recovery Best Practices

When a transformation truly fails in production, the goal is to get healthy again as fast as possible without corrupting data. The best practices:

• Idempotency: design transformations so they're safe to rerun. Use truncate at the start for full replaces, or a deduplication key for incremental loads, so the second run doesn't duplicate data.
• Adequate logging: save logs to a file at Basic or Detailed level; make sure they include the time, transformation name, and error message.
• Rollback documentation: write the recovery steps — for example "restore from the staging table backup" — in a runbook (covered fully in episode 19).
• Don't retry blindly: before rerunning, understand whether the failure is temporary (network) or permanent (file format changed). A wrong retry only repeats the failure.
• Gradual recovery: for large volumes, consider processing per batch or per date, so a failure only repeats a small portion of the data.

#Conclusion

In episode 7 you equipped yourself for facing failures: understanding the types of errors in PDI, handling errors in transformations and jobs, using preview and breakpoints for debugging, analyzing step logs and performance metrics, and applying safe recovery best practices.

The key takeaways:

• Errors in PDI can be prevented early with validation; unavoidable ones must leave a log trail.
• Preview and breakpoints let you "walk with the data" and find where it goes wrong.
• Step Metrics reveal bottlenecks: watch the row counts and speed of each step.
• Idempotency is the key to recovery — transformations must be safe to rerun.

In episode 8, we widen our reach: advanced sources & targets — connecting databases, CSV, Excel, XML, JSON, and REST APIs, using Hadoop and cloud storage connectors, writing to data warehouses, and leveraging lookup and caching for performance.