Belajar Pentaho - Advanced ETL Patterns

#Pendahuluan

Di episode 4-8 kalian membangun ETL yang bekerja. Episode 15 membahas pola yang membuat ETL bekerja dalam jangka panjang — saat data terus bertambah, tabel membesar, dan jendela waktu eksekusi semakin sempit. Kalian akan belajar incremental load, change data capture, slowly changing dimensions, orkestrasi paralel, dan optimisasi volume besar.

Inilah episode yang mengubah kalian dari orang yang "bisa membuat transformasi" menjadi orang yang "merancang pipeline data warehouse" yang benar.

#Full Load vs Incremental Load

Full load — menghapus dan mengisi ulang seluruh tabel — sederhana tapi tidak bertahan: semakin besar data, semakin mahal, dan jendela malam semakin sempit. Incremental load hanya memproses data yang baru atau berubah sejak eksekusi terakhir.

Dua pendekatan untuk mengetahui apa yang berubah:

Berbasis watermark: simpan nilai terakhir dari kolom yang meningkat (misalnya tanggal atau id), lalu ambil data dengan nilai lebih besar dari watermark itu pada run berikutnya.
Change data capture (CDC): deteksi perubahan di level sumber — bisa lewat timestamp, log database, atau perbandingan snapshot.

Contoh sederhana incremental load berbasis watermark di SQL:

Ambil data baru setelah watermark

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

Nilai max_tanggal diperbarui setiap kali job selesai. Pola ini murah dan efektif selama sumber data punya kolom timestamp yang dapat diandalkan.

Tabel watermark itu sendiri sederhana — satu baris berisi nilai terakhir yang sudah diproses:

Struktur tabel watermark

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

Di job PDI, tabel ini di-update setelah transformasi berhasil — idealnya lewat job entry Transformation khusus yang hanya berisi Execute SQL script untuk menulis max_tanggal terbaru. Dengan begitu, urutan "proses data baru, lalu geser watermark" selalu berjalan atomik dalam satu job. Untuk memeriksa nilai watermark saat ini, cukup jalankan psql -c "SELECT * FROM watermark_tabel".

Danger

Watermark hanya valid jika kolom sumber dijamin monotonik dan konsisten. Jika data lama di-update setelah di-load, watermark tidak akan menangkapnya. Untuk kasus itu, butuh CDC berbasis update tracking atau reload berkala pada jendela waktu tertentu.

#Slowly Changing Dimensions (SCD)

Data dimensi seperti pelanggan berubah seiring waktu — alamat pindah, status berubah. SCD adalah cara standar menangani perubahan itu di data warehouse. Tiga tipe paling umum:

SCD Type 1: overwrite nilai lama — tidak menyimpan riwayat. Cocok untuk atribut yang tidak perlu ditelusuri.
SCD Type 2: tambahkan baris baru dengan valid_from, valid_to, dan flag aktif — menyimpan riwayat lengkap.
SCD Type 3: simpan nilai saat ini dan nilai sebelumnya di kolom terpisah — kompromi untuk riwayat terbatas.

Di PDI, pola SCD diimplementasikan dengan step Dimension lookup/update, yang otomatis mencari baris dimensi yang cocok dan memutuskan apakah memperbarui atau menambah baris baru.

#Surrogate Keys

Selain kolom alami seperti id_pelanggan, tabel dimensi biasanya memakai surrogate key — kunci buatan tanpa makna bisnis, sering berupa angka berurutan. Gunanya:

Melindungi integritas saat kunci alami berubah.
Mengurangi ukuran tabel fakta (integer 8 byte vs string panjang).
Memungkinkan SCD Type 2 yang butuh satu baris per versi.

Di PDI, surrogate key sering dihasilkan dengan kombinasi Add sequence atau query max+1, atau lewat mekanisme Dimension lookup/update yang mengelola kuncinya sendiri.

#Memilih Kolom untuk SCD Type 2

Saat menerapkan SCD Type 2, pilih atribut mana yang benar-benar perlu riwayat. Tidak semua kolom dimensi harus menyimpan versi — alamat pelanggan mungkin perlu riwayat, tapi kolom seperti flag teknis biasanya cukup overwrite (Type 1). Menyimpan riwayat untuk semua kolom hanya menggandakan ukuran tabel tanpa manfaat analisis. Tuliskan keputusan ini di dokumentasi dimensi (episode 22) agar mudah diingat tim.

Info

Prinsip desain: tabel fakta memakai surrogate key dari dimensi, bukan kunci alami sumber. Ini menjaga fakta tetap stabil meskipun sumber data mengubah kunci atau nilai atribut di masa depan.

#Orkestrasi Job Paralel dan Multi-Step

Saat banyak transformasi harus jalan dalam satu malam, jangan selalu jalankan serial. Beberapa transformasi independen bisa berjalan paralel — memangkas total durasi secara drastis.

Pola di PDI:

Paralel di dalam job: dari satu START, buat beberapa hop ke entry yang berbeda — semuanya jalan bersamaan. Hubungkan hasilnya dengan Simple evaluation atau Wait for.
Pemisahan dependensi: hanya step yang benar-benar bergantung yang dirantai serial; yang independen diparalelkan.
Kontrol resource: jangan paralelkan semua sekaligus — batasi jumlah eksekusi simultan agar tidak saling berebut memori.

Contoh: transformasi load_dim_customer dan load_dim_product berjalan paralel, dan hanya setelah keduanya selesai, load_fact_sales dijalankan. Desain ini bisa memangkas durasi hingga setengahnya.

#Optimisasi untuk Volume Data Besar

Beberapa teknik optimasi yang sudah disebut di episode 8-14, dirangkum untuk volume besar:

Minimalkan row stream: filter sedini mungkin, hapus kolom yang tidak perlu dengan Select values.
Ganti lookup per baris dengan join SQL atau lookup cache: aturan dari episode 8.
Bulk load: gunakan Bulk load step atau SQL massal alih-alih insert satu per satu.
Batch dan commit: atur ukuran batch pada step output agar tidak overload database.
Partisi data: bagi beban per tanggal atau per kunci untuk paralelisme.
Hindari sort tidak perlu: Sort rows mahal; gunakan Merge join hanya saat dibutuhkan.

Salah satu pengamatan yang paling sering menyelamatkan performa: step yang lambat biasanya bukan step pemrosesan, melainkan step yang menunggu — menunggu query database, menunggu disk, atau menunggu stream upstream. Cari yang menunggu, bukan yang sibuk.

Success

Aturan praktis optimasi: ukur dulu dengan Step Metrics, temukan step yang menunggu, perbaiki satu per satu, lalu ukur lagi. Optimasi tanpa pengukuran hanyalah dugaan yang sering salah sasaran.

#Penutup

Di episode 15 ini kalian menguasai pola ETL kelas produksi: incremental load berbasis watermark dan CDC, slowly changing dimensions, surrogate keys, orkestrasi job paralel, serta optimisasi untuk volume besar.

Inti yang harus dibawa pulang:

Incremental load menghemat waktu dan resource dibanding full load yang naif.
SCD Type 1/2/3 adalah bahasa standar untuk menangani perubahan dimensi.
Surrogate keys menjaga stabilitas tabel fakta di tengah perubahan sumber data.
Paralelisme yang tepat dan optimasi berbasis pengukuran menjaga durasi pipeline tetap terkendali.

Di episode 16, kita melebihi batas bawaan produk: custom plugins & extensibility — memakai JavaScript dan Java untuk logika khusus, membuat custom step dan job entry, memanfaatkan plugin komunitas dan marketplace, serta mengemas transformasi yang bisa dipakai ulang.