Learn Pentaho - Data Quality & Transformation Patterns

#Introduction

Real-world data is never clean. Columns are empty, date formats are mixed up, customer names are written inconsistently, and duplicates appear endlessly. This episode covers data quality — the skill that separates amateur pipelines from professional ones.

You'll learn transformation patterns to clean, validate, normalize, and deduplicate data. This isn't just a list of steps; it's a way of thinking about the truthfulness of data before it's trusted for decision-making.

#Data Validation: Stop Before It's Too Late

Validation is the first gate. Before data enters the warehouse, make sure it's fit for purpose. The two most used steps:

• Validator: defines per-field rules — type, length, not null, or matching a regex. Rows that fail validation are separated into a dedicated output.
• Filter rows: separates rows based on simple conditions, for example excluding rows with jumlah <= 0.

The classic pattern: valid rows continue the flow to the next process, failed rows go to a special log file or a Write to log step so they can be investigated. Never let bad data silently make its way into the warehouse.

#Cleansing and Normalization

Cleansing removes dirt from data; normalization makes its values consistent. Several techniques you must master:

• String operations: removing double spaces with trim, changing letter case, and cleaning unwanted characters.
• Data type conversion: ensuring fields have the correct types — dates as Date, numbers as Number. The Select values step can change types while also renaming fields.
• Regex evaluation / String replacement: using regular expressions to find and replace patterns. A real example: formatting phone numbers or cleaning inconsistent postal codes.

An example of using String operations to normalize names: apply trim then convert to title case. Or use the Replace in string step to replace all occurrences of certain characters:

pola:    [^a-zA-Z0-9 ]
tujuan:  remove all characters other than letters, numbers, and spaces

Regex is one of the most valuable skills in the ETL world. Take time to practice the basics — you'll use it almost every day.

Besides regex, String operations offers common transformations in one dialog: trim, lower, upper, initCap, pad, and substr. The most common combination is trim then initCap for person names, or upper for codes compared case-insensitively. The rule is simple: make values consistent before the data is used for joins or deduplication, not after.

A real normalization example: the no_hp column holds various formats like 0812-3456-7890, +62 812 3456 7890, and 62812.3456.7890. With chained Replace in string steps — remove spaces, strip punctuation, then normalize the prefix — you convert all variants into one standard format that can be matched and checked for duplicates. Before copying patterns into Spoon, get used to testing them first in the terminal with grep -E "pola" contoh.txt — instant feedback from the command line is far faster than trial-and-error in the step dialog.

#Handling Missing Values

Empty data doesn't mean lost. What matters is how you handle it deliberately. Several strategies:

• Remove rows: appropriate if rows with empty fields are useless for analysis.
• Fill with default values: use the If field value is null or Calculator step with a replacement function, for example replacing null with the string UNKNOWN or the number 0.
• Fill with derived values: for example a column average, the value from a previous row, or a lookup result — more sensible for certain analytical data.

The If field value is null step separates rows that have empty values so you can handle the two groups differently. Don't let nulls flow all the way to the database without a clear decision.

#Data Deduplication

Duplicates are the enemy of data quality. Two main approaches:

• Sort rows + Row comparator / Unique rows: after sorting by key, remove rows identical to the previous one.
• Group by + aggregation: if the data is already aggregated, this combines duplicates into one row with a summary.

For more complex data — for example determining the single best representation per customer from several sources — use Group by with aggregations like MIN, MAX, or picking the first valid value. This technique is called deduplication and is crucial when combining data from many systems.

#Lookup: Enriching Data from Other Sources

Lookup is the way to add information from reference tables. The three most useful steps:

• Database lookup: fetches additional values from the database per row based on a key. Simple, but every row does a query — be careful with large volumes.
• Stream lookup: looks up values in another (stream) flow already loaded into memory. Faster for small-to-medium data.
• Value mapper: maps simple values one-to-one without a database, for example PRIORITY=1 becomes URGENT.

Combining lookup with caching — enabling Enable caching on Database lookup or loading a small reference table into memory — is one of the easiest and most effective performance optimizations.

#Aggregation and Healthy Transformation Patterns

Finally, master aggregation. The Group by step groups data by one or more fields then computes aggregates like SUM, COUNT, AVG, MIN, and MAX. Example: calculate total sales per customer per month from a transaction table.

Here's the query equivalent to a Group by result in PDI — understanding both makes you freer in choosing where to do the aggregation:

SELECT id_pelanggan,
       DATE_TRUNC('month', tanggal) AS bulan,
       SUM(jumlah) AS total,
       COUNT(*)    AS jumlah_transaksi
FROM orders
GROUP BY id_pelanggan, DATE_TRUNC('month', tanggal)

Which one to choose — the Group by step or an SQL query — depends on whether the aggregation must be visibly readable in the pipeline or can be left to the database. Both are valid, as long as the results are measured and consistent.

Beyond techniques, there are healthy transformation patterns to make a habit:

• One transformation, one responsibility: don't build a giant transformation with 50 steps. Break it into several transformations with descriptive names.
• Separate cleansing from aggregation: cleaning transformations produce clean, reusable data, not something hidden inside a report transformation.
• Log at every important stage: add Write to log steps at key points to ease auditing.
• Document data decisions: annotate steps that perform business transformations whose purpose isn't obvious.

These patterns aren't just tidiness — they're what make a pipeline easy to debug and hand over to other teams, a topic that will be revisited in episode 22.

#Conclusion

In episode 6 you mastered data quality techniques in PDI: validation with Validator and Filter rows, cleansing and string normalization with regex and type conversion, handling missing values, deduplication, lookup for enrichment, and aggregation with Group by.

The key takeaways:

• Make explicit decisions for every problem row: reject, repair, or flag.
• Consistent normalization (trim, case, data types) prevents mysterious errors at later stages.
• Missing values are handled with a deliberate strategy, not left to flow through.
• Correct deduplication and lookup determine the quality of the analysis born from the data.

In episode 7, we discuss the tense moments: error handling & debugging — handling errors in transformations and jobs, using preview rows and breakpoints, reading step logs and performance metrics, and recovery strategies for failed transformations.