Wawancara Rekayasa Data: Merancang Dimensi SCD Type 2

Masalah dan Cakupan

Sebuah sumber pelanggan mengirimkan snapshot harian lengkap berisi 50 juta baris. Sekitar 1% dari atribut yang dipilih untuk pelacakan riwayat berubah setiap hari. Pesanan (orders) masuk secara terus-menerus, sementara koreksi sumber dapat tiba terlambat hingga tiga hari. Rancang dimensi pelanggan Slowly Changing Dimension Type 2 yang mempertahankan nilai yang berlaku saat setiap pesanan terjadi, menangani penghapusan, mendukung rerun yang aman, dan tetap dapat diaudit setelah backfill.

Tugas intinya adalah pemodelan dimensi dan kebenaran temporal. Change Data Capture dapat menyediakan event masukan, tetapi tidak mendefinisikan riwayat target. Jawabannya harus menentukan atribut mana yang layak mendapat perlakuan Type 2, membedakan business key yang tahan lama dari surrogate key suatu versi, mendefinisikan interval validitas yang tidak tumpang tindih, dan menjelaskan bagaimana sebuah fakta menentukan versi yang benar.

Skala data membuat pilihan yang ceroboh terlihat jelas. Tingkat perubahan harian 1% berarti sekitar 500.000 versi baru per hari dan 182,5 juta per tahun. Jika versi yang ditambahkan rata-rata berukuran 300 byte, itu berarti sekitar 54,75 GB data baris mentah per tahun sebelum indeks, replika, metadata, dan kompresi. Angka-angka ini merupakan asumsi wawancara dan dasar penentuan ukuran (sizing baseline), bukan jaminan penyimpanan.

Apa yang Dievaluasi Pewawancara

Jawaban dasar biasanya mengatakan, “kedaluwarsakan baris lama dan sisipkan yang baru.” Jawaban yang kuat pertama-tama mendefinisikan semantik riwayat. Type 1 menimpa sebuah nilai dan menghilangkan status sebelumnya. Type 2 membuat versi baru dengan surrogate key baru dan mempertahankan versi lama. Tidak setiap kolom sumber harus memicu versi baru: mengoreksi kapitalisasi mungkin cukup Type 1, sedangkan wilayah penjualan atau kelompok harga yang digunakan dalam pelaporan historis haruslah Type 2.

Sinyal berikutnya adalah kedisiplinan temporal. Kandidat harus menyatakan konvensi interval seperti [valid_from, valid_to), memastikan versi untuk satu business key tidak pernah tumpang tindih, dan hanya mengizinkan satu versi saat ini (current). Sebuah fakta pada waktu t cocok dengan versi yang awalnya berada pada atau sebelum t dan akhirnya setelah t. Menggunakan waktu muat (load time) ketika persyaratannya adalah waktu efektif bisnis (business effective time) secara diam-diam akan salah mengatribusikan data yang datang terlambat.

Pewawancara juga mencari perilaku tingkat produksi. Batch yang dijalankan berulang tidak boleh membuat versi lain. Snapshot yang tidak lengkap tidak boleh menghapus jutaan pelanggan. Menutup baris saat ini dan menyisipkan penggantinya harus bersifat atomik. Koreksi yang terlambat mungkin memerlukan pemisahan interval historis daripada hanya mengubah baris saat ini. Jika bisnis membutuhkan keduanya, yaitu “kapan nilai tersebut efektif” dan “kapan data warehouse mengetahuinya,” SCD Type 2 biasa tidaklah cukup; itu adalah persyaratan bitemporal.

Pertanyaan yang Perlu Diklarifikasi Sebelum Menjawab

• Atribut mana yang memengaruhi analisis historis? Lacak hanya bidang Type 2 yang telah disepakati. Kolom audit dan timestamp penyerapan (ingestion) tidak boleh membuat versi bisnis baru.
• Apakah customer_id stabil dan tidak pernah digunakan kembali? Ini adalah business key. Setiap versi historis menerima surrogate key customer_sk yang terpisah.
• Apakah sumber menyediakan waktu efektif atau urutan yang tepercaya? Snapshot harian membuktikan kapan suatu nilai diamati, belum tentu kapan nilai tersebut mulai berlaku. Tanpa waktu sumber yang andal, warehouse tidak dapat mengarang batas tanggal mundur (backdated boundary) yang benar.
• Apakah setiap snapshot secara eksplisit ditandai lengkap? Perlakukan ketiadaan data sebagai penghapusan hanya setelah pemeriksaan kelengkapan, jumlah baris, dan total kontrol lolos. Ekstrak parsial bukanlah feed penghapusan.
• Apa arti dari sebuah penghapusan? Desain ini menutup versi aktif dan menyisipkan versi tombstone saat ini dengan is_deleted = true, mempertahankan batas penghapusan yang eksplisit.
• Apakah fakta menyimpan customer_sk saat penyerapan atau melakukan join berdasarkan waktu saat kueri? Menyelesaikan surrogate key selama pemuatan fakta membuat kueri berikutnya lebih sederhana. Join temporal tetap berguna untuk backfill dan validasi.
• Apakah koreksi diizinkan untuk menulis ulang riwayat bisnis sebelumnya? Jika ya, simpan versi sumber mentah dan jejak audit karena backfill secara sah dapat mengubah hasil analitis sebelumnya.
• Haruskah sistem mempertahankan waktu pengetahuan (knowledge time) serta waktu bisnis (business time)? Jika auditor membutuhkan keduanya, modelkan waktu valid dan waktu sistem secara terpisah alih-alih memaksakan keduanya ke dalam satu interval.

Jawaban 30 Detik

I would first define the business key, Type 2 attributes, trusted effective time, and delete policy.
Each version gets a surrogate key and a half-open [valid_from, valid_to) interval; one version
per customer is current. I stage and deduplicate a complete snapshot, hash only tracked attributes,
then classify rows as unchanged, new, changed, or deleted. A changed key closes the old version and
inserts the new one atomically. The batch ID and source version make reruns idempotent. Facts resolve
the version effective at order time. Late corrections split the historical interval they affect, and
validation rejects duplicate current rows, overlapping intervals, broken fact references, or an
implausible delete surge.

Pembahasan Mendalam Langkah demi Langkah

Langkah 1: Tentukan grain dan kolom sebelum pemuatan

Grain-nya adalah satu versi dari satu pelanggan selama satu interval validitas. Tabel praktis berisi:

dim_customer {
  customer_sk       // surrogate primary key for this version
  customer_id       // durable business key from the source
  segment
  sales_region
  valid_from
  valid_to           // null means open-ended
  is_current
  is_deleted
  change_hash        // canonical hash of Type 2 attributes only
  source_version
  load_batch_id
}

Gunakan [valid_from, valid_to) secara konsisten. Ketika perubahan efektif pada 2026-07-10T09:00:00Z, baris lama berakhir pada saat itu dan baris baru dimulai pada saat yang sama. Konvensi half-open membuat batas tersebut tepat dimiliki oleh satu versi. Simpan timestamp dalam zona waktu dan presisi yang ditentukan; mencampur tanggal, waktu lokal, dan UTC akan menciptakan celah buatan atau pencocokan ganda.

Business key mengelompokkan versi-versi yang ada. Surrogate key mengidentifikasi satu status historis yang tidak dapat diubah (immutable) dan merupakan foreign key yang disimpan oleh fakta. is_current adalah sebuah kemudahan, bukan kebenaran independen: nilai ini harus selaras dengan valid_to IS NULL. Hash hanyalah optimasi perbandingan. Kanonisasikan nilai null, tipe data, Unicode, dan urutan kolom, serta tetap pertahankan kolom aktual yang dilacak untuk keperluan penjelasan dan audit.

Langkah 2: Tentukan ukuran jalur penulisan dan pemindaian

Snapshot harian memindai 50 juta baris sumber. Pada tingkat perubahan 1%:

50,000,000 × 1% = 500,000 new versions/day
500,000 × 365 = 182,500,000 new versions/year
182,500,000 × 300 bytes ≈ 54.75 GB/year of raw row data

Hal ini memisahkan biaya pemindaian dari biaya penulisan perubahan. Lakukan staging pada snapshot sekali, proyeksikan hanya kolom yang diperlukan, dan bandingkan dengan baris dimensi saat ini berdasarkan business key. Partisi atau pengelompokan harus mengikuti pola kueri dan pemeliharaan aktual—sering kali business key untuk pencarian dan waktu validitas untuk pemangkasan (pruning)—daripada membuat ribuan partisi harian kecil. Ukur indeks, kompresi kolumnar, retensi, dan amplifikasi backfill dengan data berbentuk produksi.

Langkah 3: Buat batch biasa menjadi deterministik dan atomik

Tempatkan ekstrak di bawah snapshot_id yang tidak dapat diubah. Sebelum menyentuh target, verifikasi penanda kelengkapan, skema, keunikan key yang diharapkan, jumlah baris, dan total kontrol. Lakukan deduplikasi berdasarkan customer_id menggunakan versi sumber yang tepercaya; dua baris dengan peringkat sama tetapi berbeda adalah kesalahan input, bukan alasan untuk memilih salah satunya secara sembarangan.

Kanonisasikan atribut yang dilacak dan bandingkan setiap baris yang di-stage dengan versi target saat ini:

1. Tidak ada business key saat ini: sisipkan versi pertamanya.
2. Nilai yang dilacak sama: tidak melakukan apa pun; menyegarkan loaded_at tidak boleh membuat riwayat baru.
3. Nilai yang dilacak berbeda: tutup baris saat ini pada waktu efektif tepercaya dan sisipkan versi baru saat ini.
4. Key target saat ini tidak ada dalam snapshot lengkap yang terverifikasi: tutup baris tersebut dan sisipkan versi tombstone saat ini.

Untuk setiap key yang berubah, proses penutupan dan penyisipan terjadi dalam satu transaksi target atau satu operasi tabel atomik. Aturan keunikan pada versi saat ini melindungi dari dua pemuat yang berjalan bersamaan. Catat snapshot_id, load_batch_id, dan source_version, serta tolak versi sumber yang telah diterapkan sebelumnya. Percobaan ulang setelah pengakuan (acknowledgement) yang hilang kemudian akan konvergen tanpa menduplikasi riwayat.

Langkah 4: Selesaikan fakta pada waktu event bisnis

Saat memuat pesanan, selesaikan versi pelanggan menggunakan timestamp bisnis pesanan, lalu simpan customer_sk dalam fakta. Pencarian point-in-time yang netral terhadap vendor memiliki bentuk seperti ini:

SELECT d.customer_sk
FROM dim_customer AS d
WHERE d.customer_id = :customer_id
  AND d.is_deleted = false
  AND d.valid_from <= :order_time
  AND (d.valid_to > :order_time OR d.valid_to IS NULL);

Ketidaksamaan tersebut mengkodekan interval half-open. Pencarian harus mengembalikan tepat satu baris. Nol kecocokan memerlukan kebijakan anggota tidak dikenal (unknown-member) atau karantina yang eksplisit; kecocokan ganda membuktikan adanya riwayat yang rusak. Untuk fakta yang datang terlambat, gunakan waktu peristiwanya, bukan waktu penyerapannya. Pola Type 2 dari Kimball menggunakan surrogate key versi dalam fakta justru agar fakta yang dimuat sebelumnya mempertahankan profil historis kontemporer.

Langkah 5: Perlakukan koreksi terlambat sebagai operasi bedah interval

Misalkan warehouse saat ini memiliki Seattle dari 1 Juli dan Denver dari 12 Juli. Pada 14 Juli, warehouse menerima koreksi tepercaya yang menyatakan bahwa Denver mulai efektif pada 10 Juli. Hanya memperbarui baris saat ini akan membuat data 10 Juli dan 11 Juli salah. Temukan versi yang memuat tanggal 10 Juli, tutup Seattle pada 10 Juli, dan ubah awal Denver menjadi 10 Juli. Jika koreksi memasukkan nilai ketiga di dalam interval yang ada, pisahkan interval tersebut dan pertahankan batas berikutnya yang diketahui.

Terapkan koreksi sesuai urutan versi sumber dan kunci atau serialisasikan pembaruan per business key. Simpan input mentah dan audit koreksi yang berisi interval sebelumnya, interval baru, versi sumber, batch, dan alasan. Selesaikan ulang fakta yang terpengaruh ketika kontrak bisnis menyatakan bahwa foreign key mereka harus mencerminkan riwayat yang telah dikoreksi.

Terdapat batas informasi: snapshot yang pertama kali diamati pada 14 Juli tidak dapat membuktikan bahwa suatu nilai menjadi efektif pada 10 Juli kecuali sumber tersebut membawa waktu bisnis yang tepercaya atau catatan berurutan lainnya. Gunakan 14 Juli sebagai waktu pengamatan atau karantina koreksi tersebut; memundurkan tanggalnya secara diam-diam akan menciptakan presisi palsu. Jika warehouse harus mempertahankan waktu valid 10 Juli dan waktu pengetahuan 14 Juli, tambahkan interval waktu sistem kedua dan sebut model tersebut bitemporal.

Langkah 6: Validasi invarian dan batasan operasional

Uji target setelah setiap batch dan sebelum publikasi:

• setiap surrogate key unik;
• setiap business key memiliki tepat satu versi saat ini, termasuk tombstone untuk key yang dihapus;
• is_current selaras dengan valid_to yang terbuka;
• interval untuk satu business key tidak pernah tumpang tindih dan setiap baris memiliki valid_from sebelum valid_to jika batas akhir ada;
• nilai sumber yang tidak berubah tidak menambahkan versi baru;
• versi sumber atau pemutaran ulang batch yang sama tidak menghasilkan baris baru;
• setiap surrogate key fakta non-unknown merujuk pada baris dimensi yang efektif pada waktu event fakta;
• jumlah baris yang disisipkan, diubah, tidak diubah, dan dihapus cocok (rekonsiliasi) dengan snapshot yang di-stage.

Pantau kelengkapan snapshot, key duplikat, tingkat perubahan, tingkat penghapusan, pertumbuhan versi, koreksi terlambat yang ditolak, pencarian fakta nol dan multi-kecocokan, latensi pemuatan, dan delta rerun. Hilangnya 40 juta key secara tiba-tiba harus memicu kegagalan tertutup (fail closed) sebelum menjadi 40 juta tombstone. Uji key baru, snapshot identik berulang, perubahan normal, penghapusan dan pembuatan ulang, koreksi terlambat tiga hari, versi sumber yang tidak berurutan, snapshot parsial, dan crash antara penutupan dan penyisipan.

Contoh Jawaban yang Kuat

“Saya akan memodelkan satu baris per versi pelanggan, bukan satu baris per pelanggan. customer_id adalah business key yang tahan lama, sementara setiap versi mendapatkan customer_sk baru. Bidang Type 2 disepakati dengan analis—misalnya segmen dan wilayah penjualan—sehingga timestamp pemuatan atau koreksi pemformatan tidak menciptakan riwayat baru. Setiap baris menggunakan interval half-open [valid_from, valid_to), dan tepat satu baris per pelanggan berstatus saat ini (current).

Sumber memindai 50 juta baris setiap hari tetapi hanya mengubah sekitar 500.000 baris. Itu berarti sekitar 182,5 juta versi baru per tahun; dengan ilustrasi 300 byte per versi, pertumbuhan mentah sekitar 54,75 GB sebelum overhead fisik. Saya akan men-stage snapshot sekali dan membandingkannya hanya dengan baris dimensi saat ini daripada menggabungkan riwayat lengkap secara berulang.

Setiap snapshot memiliki ID yang stabil dan harus lolos pemeriksaan kelengkapan, keunikan, skema, jumlah baris, dan total kontrol. Saya mengkanonisasikan dan melakukan hashing hanya pada kolom Type 2 yang dilacak. Key baru disisipkan, hash yang sama tidak melakukan apa pun, dan key yang berubah akan menutup interval lama dan menyisipkan versi baru dalam satu operasi atomik. Key yang hilang dan terverifikasi akan menutup versi lama dan menyisipkan tombstone penghapusan. Versi sumber ditambah ID batch membuat pemuatan menjadi idempoten, dan aturan keunikan baris saat ini memblokir versi duplikat yang terjadi bersamaan.

Pesanan menyelesaikan customer_sk dengan valid_from <= order_time dan valid_to > order_time, memperlakukan nilai null pada batas akhir sebagai terbuka. Pencarian harus mengembalikan satu versi yang tidak dihapus. Fakta yang datang terlambat menggunakan waktu pesanan, bukan waktu kedatangan. Koreksi dimensi yang terlambat diterapkan pada interval yang memuat waktu efektif tepercayanya: pisahkan atau sesuaikan interval tersebut dan pertahankan batas-batas berikutnya yang diketahui. Jika sumber hanya memberikan waktu kedatangan, saya tidak akan mengarang waktu efektif tiga hari lebih awal. Jika waktu bisnis dan waktu pengetahuan warehouse harus dapat dikueri, saya akan mengusulkan model bitemporal.

Sebelum publikasi, saya memeriksa satu versi saat ini per business key, tidak ada tumpang tindih interval, batas valid, integritas referensial fakta, rekonsiliasi jumlah sumber-ke-target, dan nol perubahan baris pada rerun yang identik. Saya memberikan peringatan pada tingkat penghapusan atau perubahan yang tidak normal dan menyimpan snapshot mentah serta audit koreksi sehingga backfill dapat dijelaskan dan dibatalkan (reversible).”

Kesalahan Umum

• Melakukan hashing pada setiap kolom sumber → Timestamp audit membuat versi yang tidak bermakna → Lakukan hash hanya pada atribut Type 2 yang dilacak secara kontraktual setelah dikanonisasi.
• Menggunakan natural key sebagai primary key dimensi → Beberapa versi historis akan bertabrakan → Kelompokkan berdasarkan business key dan identifikasi setiap versi dengan surrogate key.
• Mencampur batas akhir interval inklusif → Sebuah fakta pada batas perubahan cocok dengan dua baris → Terapkan [valid_from, valid_to) secara konsisten dari awal hingga akhir.
• Menggunakan waktu penyerapan sebagai waktu efektif → Pembaruan dan fakta yang terlambat akan terhubung ke status historis yang salah → Gunakan waktu bisnis yang tepercaya dan nyatakan tindakan fallback jika tidak tersedia.
• Memperlakukan setiap baris snapshot yang hilang sebagai dihapus → Ekstrak parsial dapat menghapus seluruh dimensi → Wajibkan penanda kelengkapan dan pemeriksaan anomali sebelum menerapkan semantik ketiadaan data.
• Menutup dan menyisipkan dalam commit terpisah → Kerusakan sistem (crash) menyisakan nol atau dua baris saat ini → Terapkan transisi versi secara atomik dan tegakkan keunikan baris saat ini.
• Membuat versi pada setiap rerun → Percobaan ulang menggembungkan riwayat dan mengubah jawaban masa lalu → Simpan versi sumber dan identitas batch, lalu buat input yang sama menjadi no-op.
• Hanya memperbaiki baris saat ini untuk koreksi yang terlambat → Fakta sebelumnya tetap terikat pada status yang salah → Pisahkan atau sesuaikan interval historis yang terpengaruh dan selesaikan ulang rentang fakta yang terdampak.
• Menyebut sembarang dua timestamp sebagai “bitemporal” → Maknanya tetap ambigu → Sebutkan waktu valid dan waktu sistem secara spesifik, serta tentukan interval dan aturan koreksinya.
• Hanya memeriksa jumlah baris → Tumpang tindih dan baris saat ini yang duplikat dapat lolos → Validasi invarian temporal, referensial, idempotensi, dan rekonsiliasi.

Pertanyaan Lanjutan dan Tanggapan

Pertanyaan Lanjutan 1: Mengapa tidak menggunakan Type 1 untuk setiap atribut?

Type 1 tepat untuk nilai yang bentuk sebelumnya tidak memiliki makna analitis, seperti perbaikan salah ketik. Ini menghancurkan nilai lama, sehingga tidak dapat menjawab “wilayah mana yang memiliki pesanan ini saat itu?” Klasifikasikan atribut berdasarkan semantik pelaporan. Dimensi tunggal dapat menggunakan Type 1 untuk beberapa kolom dan Type 2 untuk kolom lainnya, asalkan aturan pembaruannya eksplisit.

Pertanyaan Lanjutan 2: Haruskah baris saat ini menggunakan valid_to = NULL atau nilai sentinel masa depan yang jauh?

Keduanya bisa digunakan. NULL membuat status “terbuka” menjadi eksplisit tetapi memerlukan predikat yang menangani null. Nilai sentinel seperti timestamp maksimum yang didukung dapat menyederhanakan filter rentang tetapi dapat bocor ke dalam laporan atau melebihi rentang tanggal engine lain. Pilih satu representasi, buat is_current konsisten dengannya, dan uji setiap kueri serta konektor pada konvensi yang sama.

Pertanyaan Lanjutan 3: Bagaimana Anda menangani penghapusan yang diikuti oleh pembuatan ulang dari business key yang sama?

Tutup versi bisnis yang aktif pada saat penghapusan dan tambahkan tombstone penghapusan. Pada saat pembuatan ulang, tutup tombstone tersebut dan sisipkan versi aktif baru dengan surrogate key baru. Pertama, konfirmasikan apakah sumber benar-benar menggunakan kembali identitas entitas yang sama; jika pengenal tersebut didaur ulang untuk orang yang berbeda, perkenalkan key tahan lama yang membedakan entitas-entitas tersebut.

Pertanyaan Lanjutan 4: Bagaimana jika sumber tidak memiliki updated_at yang andal?

Bandingkan daftar eksplisit kolom kanonikal yang dilacak, seperti yang dilakukan oleh strategi pemeriksaan alat snapshot. Interval yang dihasilkan dimulai saat warehouse mengamati perubahan, belum tentu saat perubahan bisnis terjadi. Dokumentasikan batasan tersebut. Jika riwayat efektif bisnis yang tepat diperlukan, dapatkan urutan sumber, log audit, CDC feed, atau domain event daripada mengarang sebuah timestamp.

Pertanyaan Lanjutan 5: Apa bedanya ini dengan pipeline CDC?

CDC menjawab operasi insert, update, dan delete mana yang telah di-commit dan dalam urutan sumber apa. SCD Type 2 menjawab bagaimana atribut dimensi yang dipilih menjadi versi historis dan surrogate key mana yang harus dirujuk oleh sebuah fakta. CDC feed dapat menggerakkan pemuat SCD, dan sebuah snapshot juga dapat menggerakkannya. Penangkapan yang andal tidak dengan sendirinya mencegah interval validitas yang tumpang tindih atau join point-in-time yang salah.

Pertanyaan Lanjutan 6: Kapan Anda harus menghindari pertumbuhan baris Type 2?

Hindari untuk atribut yang berubah dengan sangat cepat dan tidak diperlukan untuk pengelompokan historis, payload bentuk bebas yang besar, dan status operasional yang lebih baik direpresentasikan sebagai event atau fakta. Gunakan Type 1, mini-dimension terpisah, tabel fakta periodik atau akumulasi, atau tabel riwayat khusus sesuai dengan kebutuhan kueri. Keputusan ini mengikuti semantik analitis dan biaya penulisan/kueri yang terukur.

Pertanyaan Lanjutan 7: Bagaimana Anda membuktikan bahwa backfill yang terlambat tidak merusak riwayat?

Jalankan backfill ke staging atau tabel bayangan (shadow table), bandingkan kumpulan interval berdasarkan business key, dan laporkan versi yang disisipkan, dipisahkan, diperpendek, diperpanjang, dan dihapus. Pastikan tidak ada tumpang tindih, tepat satu baris saat ini, surrogate key yang tidak terpengaruh tetap stabil, dan perubahan foreign key fakta yang diharapkan hanya terjadi di dalam jendela waktu yang dikoreksi. Simpan versi sumber mentah dan manifes batch untuk pemutaran ulang dan rollback.