Learn Pentaho - Advanced ETL Patterns

#Introduction

In episodes 4-8 you built ETL that works. Episode 15 covers the patterns that make ETL work in the long run — when data keeps growing, tables get bigger, and the execution window gets narrower. You'll learn incremental load, change data capture, slowly changing dimensions, parallel orchestration, and large-volume optimization.

This is the episode that turns you from someone who "can build transformations" into someone who "designs data warehouse pipelines" properly.

#Full Load vs Incremental Load

Full load — truncating and refilling the entire table — is simple but doesn't scale: the bigger the data, the more expensive it gets, and the night window gets narrower. Incremental load only processes data that is new or changed since the last execution.

Two approaches to know what changed:

• Watermark-based: store the last value of a monotonically increasing column (for example tanggal or id), then fetch data with values greater than that watermark on the next run.
• Change data capture (CDC): detect changes at the source level — via timestamps, database logs, or snapshot comparison.

A simple example of watermark-based incremental load in SQL:

SELECT id, nama, jumlah, tanggal
FROM orders
WHERE tanggal > (SELECT max_tanggal FROM watermark_tabel)

The max_tanggal value is updated each time the job finishes. This pattern is cheap and effective as long as the source data has a reliable timestamp column.

The watermark table itself is simple — one row containing the last processed value:

CREATE TABLE watermark_tabel (
  tabel_sumber VARCHAR(100),
  max_tanggal  DATE,
  updated_at   TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In a PDI job, this table is updated after the transformation succeeds — ideally via a dedicated Transformation job entry containing only an Execute SQL script that writes the newest max_tanggal. That way, the "process new data, then advance the watermark" sequence always runs atomically within one job. To check the current watermark value, simply run psql -c "SELECT * FROM watermark_tabel".

#Slowly Changing Dimensions (SCD)

Dimension data like customers changes over time — addresses move, statuses change. SCD is the standard way to handle those changes in a data warehouse. The three most common types:

• SCD Type 1: overwrite the old value — no history kept. Suitable for attributes that don't need tracing.
• SCD Type 2: add a new row with valid_from, valid_to, and an active flag — keeps complete history.
• SCD Type 3: store the current value and the previous value in separate columns — a compromise for limited history.

In PDI, the SCD pattern is implemented with the Dimension lookup/update step, which automatically looks up the matching dimension row and decides whether to update it or add a new row.

#Surrogate Keys

Besides natural columns like id_pelanggan, dimension tables usually use a surrogate key — a synthetic key with no business meaning, often a sequential number. Its uses:

• Protects integrity when natural keys change.
• Reduces the fact table size (8-byte integer vs long string).
• Enables SCD Type 2, which needs one row per version.

In PDI, surrogate keys are often generated with a combination of Add sequence or a max+1 query, or through the Dimension lookup/update mechanism, which manages its own keys.

#Choosing Columns for SCD Type 2

When implementing SCD Type 2, choose which attributes truly need history. Not every dimension column must keep versions — a customer's address may need history, but columns like technical flags are usually fine with overwriting (Type 1). Keeping history for every column just doubles the table size without analytical benefit. Write these decisions down in the dimension documentation (episode 22) so the team can remember them easily.

#Parallel and Multi-Step Job Orchestration

When many transformations must run in one night, don't always run serially. Several independent transformations can run in parallel — drastically cutting total duration.

The patterns in PDI:

• Parallel within a job: from a single START, create several hops to different entries — all run simultaneously. Join the results with Simple evaluation or Wait for.
• Dependency separation: only steps that truly depend on each other are chained serially; independent ones are parallelized.
• Resource control: don't parallelize everything at once — limit the number of simultaneous executions so they don't fight over memory.

Example: the load_dim_customer and load_dim_product transformations run in parallel, and only after both finish does load_fact_sales run. This design can cut duration by up to half.

#Optimization for Large Data Volumes

Several optimization techniques already mentioned in episodes 8-14, summarized for large volumes:

• Minimize the row stream: filter as early as possible, drop unneeded columns with Select values.
• Replace per-row lookup with SQL joins or lookup cache: the rule from episode 8.
• Bulk load: use a Bulk load step or bulk SQL instead of one-by-one inserts.
• Batch and commit: set the batch size on output steps so the database isn't overloaded.
• Data partitioning: split the load per date or per key for parallelism.
• Avoid unnecessary sorts: Sort rows is expensive; use Merge join only when needed.

One observation that most often saves performance: the slow step is usually not a processing step, but a step that's waiting — waiting on a database query, waiting on disk, or waiting on an upstream stream. Look for the waiter, not the busy one.

#Conclusion

In episode 15 you mastered production-grade ETL patterns: watermark-based incremental load and CDC, slowly changing dimensions, surrogate keys, parallel job orchestration, and optimization for large volumes.

The key takeaways:

• Incremental load saves time and resources compared to naive full load.
• SCD Types 1/2/3 are the standard language for handling dimension changes.
• Surrogate keys keep fact tables stable amid source data changes.
• Proper parallelism and measurement-based optimization keep pipeline durations under control.

In episode 16, we go beyond the product's built-in limits: custom plugins & extensibility — using JavaScript and Java for custom logic, creating custom steps and job entries, leveraging community plugins and the marketplace, and packaging reusable transformations.