Learn Pentaho - Building a Basic Transformation

#Introduction

This is the episode where you actually build something. You'll assemble your first transformation: reading data from databases and files, reshaping it, then writing the results. More importantly, you'll understand the concepts that drive it all — row stream, field metadata, and the data flow between steps.

Think of a transformation as a cooking recipe: ingredients go in one side, pass through several processing stages, and come out as a finished dish. In Spoon, each stage is a step.

#Anatomy of a Transformation

A PDI transformation is built from three kinds of elements:

• Input step: where data comes in. Examples are Table input (reading SQL results from a database) and Text file input (reading flat files like CSV).
• Transform step: data processing. Examples are Filter rows, Select values, Sort rows, Calculator, and String operations.
• Output step: where data goes. Examples are Table output (writing to a database) and Text file output (writing to a file).

You connect these steps with hops — arrow lines that show the direction of data flow. The flowing data is called the row stream: each row passes through steps one by one, gets processed, then continues to the next step.

To start, create a new transformation and add a Table input from the palette. Double-click the step and fill in its SQL:

SELECT id, nama, tanggal, jumlah
FROM orders
WHERE status = 'PENDING'

Don't forget to select the LAB_PG database connection you created in episode 3. After running the transformation (the play button), you can inspect the flowing data by right-clicking the step and choosing Preview.

#Understanding the Row Stream and Field Metadata

As data flows from one step to another, every row carries field metadata: field names, data types (String, Integer, Number, Date, Boolean), length, and precision. This metadata is determined by the input step and modified by transformation steps.

To see a step's metadata, open the step dialog and check the Fields tab on steps like Table input — or right-click a hop and choose Show input/output fields. For example, the Table input step above will produce fields id as Integer, nama as String, tanggal as Date, and jumlah as Number.

Understanding metadata is critical because many PDI errors are rooted in type mismatches: joining two streams whose fields share a name but have different types, or writing String values into Number columns in the database. The habit of checking metadata before assembling the next step will save you a lot of debugging time.

#Understanding Hops and Multiple Flow

One hop detail that often confuses beginners: a step can send rows to several next steps at once. When you connect one output to two steps, PDI asks whether you want a copy hop — an identical copy of each row sent to both branches — or a distribution hop that splits rows round-robin. For flows whose content must be identical in both branches, choose copy; for splitting the load across processing branches, choose distribution.

This pattern is useful, for example, when one stream must go to a normalization process and an audit process at the same time — without accidentally streaming rows twice.

#Exploring Field Types in Preview

When using Preview, pay attention to two things on the Fields tab: the displayed data type and null values. A lot of data that looks like text is actually stored as Date or Number by the input step — and misreading these types is the most common source of errors from episode 6 onward. Get used to adjusting the Type and Format columns in the step dialog so the metadata matches reality.

A quick exercise: add Select values after Text file input, change the harga type to Number, and watch how Preview displays the numbers correctly. This is the metadata-check cycle — change, preview, validate — that you'll repeat hundreds of times.

#Reading a CSV File with Text File Input

Now let's add a second source: Text file input to read a CSV file. Set the file name, then click Get Fields to let Spoon guess the structure and field types from the first row. Review the guesses on the Fields tab — fix any wrong types, for example a harga column read as String when it should be Number.

A common pattern you should recognize early: field mapping. When two streams are joined or written to a target, Spoon matches field names. If field names differ between source and target, you need to normalize them with the Select values step (to select, rename, and change field types) or Rename fields.

#Filtering, Joining, and Splitting Data

The following three transformation steps you'll use almost every day. Practice all three in this transformation:

#Filter Rows

Filter rows splits a stream in two: rows matching the condition flow to the "true" hop, the rest to the "false" hop. Example: split orders with jumlah above 100,000 into one branch and the rest into another. This is the basis of conditional routing in data flow.

#Join Rows / Merge Join

To combine two streams by a key, use Merge Join (which requires both streams to already be sorted on the key field) or Database lookup (which fetches additional values from the database per row). Example: join order data with customer data on id_pelanggan so the report includes customer names.

#Split Rows

Split Rows splits one field with a delimiter into multiple rows. Example: a kategori field containing A,B,C can be split into three separate rows. The reverse is done with Join rows or Group by for aggregation.

#Writing Results to Output

Every practical transformation ends with an output. The two most common outputs:

• Table output: writes the row stream to a database table. Open this step, choose the connection and target table, then the Database Fields tab to map stream fields to table columns.
• Text file output: writes to a flat file, for example CSV for reports or cross-system exchange. Set the delimiter, date format, and encoding on the available tabs.

Before writing to a table, consider clearing the old table contents first with the Execute SQL script step or setting the Truncate table option on Table output — depending on whether data is loaded incrementally or as a full replace.

#Running and Checking Results

When all steps are connected, run the transformation with the Run button. Watch the log panel and Step Metrics: you'll see the number of rows each step read, the speed, and execution time.

To inspect data mid-flow without running everything, right-click on a hop or step and select Preview. This runs the transformation up to that step and shows a sample of rows — the fastest way to validate that intermediate results are correct.

An example of running a transformation from the command line:

pan.sh -file=/home/kalian/lab/transformations/first_steps.ktr

You'll see the same execution log as in Spoon. Add -level=Basic or -level=Detailed to control how much log detail is printed.

#Conclusion

In episode 4 you assembled your first transformation: adding input, transformation, and output steps; understanding the row stream and field metadata; using Table input, Text file input, Filter rows, Merge Join, Split Rows, and Table output; then running the transformation and checking the preview.

The key takeaways:

• A transformation is a chain of steps connected by hops; data flows as a row stream.
• Every row carries field metadata — always check field types before joining streams.
• Filter rows splits a flow, Merge Join and Database lookup combine data, Split rows splits values.
• Preview and logs are your main validation tools before running to production.

In episode 5, we step up a level: jobs & orchestration — building a job that manages the execution order of transformations, using control steps like Start, Transformation, Shell, FTP, and Email, managing success/failure flows, and scheduling execution with Kitchen and cron.