Wawancara Data Engineering: Bagaimana Cara Memilih Tipe SCD dan Menangani Perubahan yang Terlambat?

Pertanyaan dan kapan ini berlaku

Ini adalah pertanyaan mengenai pemodelan dimensional dan semantik historis. Alamat pelanggan tidak dapat diperlakukan hanya sebagai nilai terbaru ketika laporan keuangan harus menjawab di wilayah mana seorang pelanggan berada pada tanggal di masa lalu. Asumsikan snapshot penuh harian, tanpa change log, dan target yang mendukung status saat ini, pencarian historis, dan replay. Dataquest mencantumkan SCD Tipe 1, 2, dan 3 di antara pertanyaan wawancara rekayasa data; AWS mendemonstrasikan Tipe 2 dari file penuh tanpa CDC.

Hal yang diuji oleh pewawancara

Menentukan apakah laporan meminta data "sekarang" atau "pada saat itu" sebelum memilih suatu tipe.
Membedakan semantik penimpaan (overwrite), penambahan versi (append-a-version), dan satu nilai sebelumnya.
Menjaga keunikan dengan natural key, surrogate key, waktu efektif, dan current flag.
Menangani snapshot yang terlambat, file duplikat, penghapusan, replay, dan penulisan bersamaan (concurrent writes) alih-alih hanya menulis satu UPDATE.

Klarifikasi yang perlu ditanyakan terlebih dahulu

Apakah laporan harus merekonstruksi status pada saat kejadian (event time), atau apakah profil saat ini sudah cukup?
Apakah snapshot menyertakan versi, waktu ekstraksi, atau watermark, dan mungkinkah data tiba tidak berurutan (out of order)?
Apakah ketiadaan data berarti penghapusan bisnis, kelalaian sementara, atau kerusakan sistem sumber?
Bisakah riwayat yang telah dipublikasikan dan fact join direvisi, serta apakah audit, replay, dan eksekusi ulang yang idempoten diperlukan?

Jawaban 30 detik

"Saya memperjelas semantik waktunya: Tipe 1 hanya menyimpan nilai saat ini, Tipe 2 menambahkan versi baru untuk setiap perubahan bisnis dengan valid_from dan valid_to, dan Tipe 3 menyimpan satu atau sejumlah kecil nilai sebelumnya. Untuk analisis point-in-time, saya memilih Tipe 2, mencari baris saat ini berdasarkan natural key, menggabungkan fakta melalui surrogate key, dan menggunakan watermark snapshot untuk mengidentifikasi data yang terlambat serta penghapusan. Saya menampung (land) dan melakukan deduplikasi pada setiap batch, kemudian menutup versi lama dan menyisipkan yang baru secara atomik; menjalankan ulang batch yang sama harus menghasilkan hasil yang persis sama."

Solusi langkah demi langkah

Langkah 1: Tentukan pertanyaan historis

Tipe 1 cocok untuk koreksi atau profil tanpa riwayat dan melakukan penimpaan langsung di tempat. Tipe 2 cocok untuk audit, atribusi pendapatan, dan kueri point-in-time dengan menyimpan setiap versi. Tipe 3 cocok untuk jendela perbandingan tetap 'saat ini versus sebelumnya'. Jika persyaratannya menanyakan wilayah pelanggan pada kuartal lalu, Tipe 1 dan 3 kehilangan informasi tersebut, jadi pilihlah Tipe 2.

Langkah 2: Tetapkan invarian Tipe 2

Simpan surrogate key, natural key, atribut, valid_from, valid_to, is_current, dan watermark batch sumber. Untuk setiap natural key, paling banyak satu baris memiliki is_current = true; interval efektif tidak boleh tumpang tindih. AWS menggunakan tanggal mulai/selesai, current flag, dan logical delete untuk riwayat penuh, sedangkan konfigurasi Tipe 2 Microsoft memerlukan natural key, surrogate key, dua tanggal, dan active flag.

Langkah 3: Simpulkan perubahan dari snapshot penuh

Tulis file ke landing table yang tidak dapat diubah (immutable) bersama dengan hash file, waktu ekstraksi, dan urutan batch. Bandingkan hash atribut untuk setiap natural key terhadap dimensi saat ini: sisipkan key baru, tutup dan tambahkan key yang berubah, serta lewati hash yang sama. Key yang hilang hanya dapat diartikan sebagai penghapusan jika kontrak snapshot lengkap terpenuhi; file inkremental tidak boleh menyimpulkan penghapusan dari ketiadaan data.

Langkah 4: Tangani data yang terlambat dan tidak berurutan

Pertahankan watermark monotonik untuk setiap sumber. Tolak atau karantina file yang berada di bawah watermark yang telah diterapkan. Jika koreksi historis diizinkan, bagi interval efektif yang terpengaruh dan hitung ulang fact join yang terdampak di dalam sebuah transaksi; jika laporan yang dipublikasikan bersifat immutable, masukkan batch ke dalam antrean koreksi dan publikasikan dampaknya. Menggunakan waktu kedatangan sebagai waktu validitas akan mengubah snapshot lama menjadi status baru.

Langkah 5: Penghapusan, replay, dan konkurensi

Key yang hilang dalam snapshot lengkap akan membuat versi logical-delete atau menutup baris saat ini, sambil tetap mempertahankan bukti audit. Jadikan hash file atau batch sumber sebagai kunci idempotensi yang unik; menjalankan ulang suatu batch tidak boleh menambahkan versi lain. Gunakan transaksi atau merge lock untuk partisi natural-key sehingga penutupan dan penyisipan bersifat atomik. Terapkan batch berdasarkan urutan watermark; batch yang lebih lama tidak boleh membuka kembali interval yang sudah ditutup.

Kerangka SQL yang dapat diverifikasi

sql

-- current_dim: one current row per customer_id
BEGIN;

UPDATE dim_customer AS old
SET valid_to = :as_of,
    is_current = FALSE,
    source_batch = :batch_id
FROM stage_customer AS incoming
WHERE old.customer_id = incoming.customer_id
  AND old.is_current = TRUE
  AND old.row_hash <> incoming.row_hash;

INSERT INTO dim_customer (
    customer_key, customer_id, city, valid_from, valid_to,
    is_current, source_batch, row_hash
)
SELECT nextval('dim_customer_key_seq'), incoming.customer_id,
       incoming.city, :as_of, NULL, TRUE, :batch_id, incoming.row_hash
FROM stage_customer AS incoming
LEFT JOIN dim_customer AS old
  ON old.customer_id = incoming.customer_id
 AND old.is_current = TRUE
WHERE old.customer_id IS NULL
   OR old.row_hash <> incoming.row_hash;

COMMIT;

Di lingkungan produksi, constraint unik pada batch_id masih diperlukan serta pemeriksaan pra-commit untuk memastikan bahwa setiap natural key memiliki satu baris saat ini, interval tidak tumpang tindih, dan watermark input valid. Kerangka ini mengasumsikan stage_customer telah di-deduplikasi per batch; ini bukan penangan data out-of-order yang lengkap.

Contoh jawaban berkualitas tinggi

"Pertama, saya menanyakan riwayat apa yang harus dipertahankan oleh laporan. Gunakan Tipe 1 untuk nilai saat ini saja, Tipe 2 untuk status pada tanggal masa lalu mana pun, dan Tipe 3 untuk tepat satu nilai sebelumnya. Untuk snapshot penuh harian, tampung file mentah dan lakukan deduplikasi berdasarkan watermark batch dan hash file. Bandingkan natural key dan hash atribut untuk mengklasifikasikan insert, perubahan, dan no-op. Pembaruan Tipe 2 harus menutup baris lama dan menyisipkan versi baru secara atomik, dengan satu baris aktif dan interval yang tidak tumpang tindih. Key yang hilang hanya berarti penghapusan di bawah kontrak snapshot lengkap; batch yang terlambat diurutkan atau dikarantina berdasarkan watermark, dan kontrak laporan menentukan apakah riwayat perlu dikoreksi."

Kesalahan umum

Menggunakan Tipe 2 secara default untuk setiap dimensi → biaya penyimpanan dan kueri lebih tinggi → tanyakan kontrak riwayat terlebih dahulu.
Menggunakan waktu kedatangan sebagai waktu efektif → snapshot yang tidak berurutan merusak riwayat → gunakan waktu efektif bisnis atau antrean koreksi.
Menganggap key yang hilang dalam file inkremental sebagai penghapusan → data inkremental yang valid terhapus → simpulkan penghapusan hanya dari snapshot lengkap.
Memperbarui is_current tanpa versi surrogate-key → fakta tidak dapat digabungkan dengan status point-in-time → tutup baris lama dan tambahkan versi baru.
Menghilangkan kunci idempotensi batch → proses replay menambahkan riwayat duplikat → terapkan batasan keunikan pada hash file atau ID batch.

Pertanyaan lanjutan dan respons yang kuat

Haruskah kejadian yang terlambat merevisi laporan yang telah dipublikasikan?

Konfirmasikan apakah laporan tersebut dapat direvisi. Jika ya, bagi interval efektif berdasarkan waktu bisnis dan hitung ulang fakta yang terpengaruh; jika tidak, pertahankan hasil yang telah dipublikasikan dan terbitkan batch koreksi beserta dampaknya. Kedua kontrak mempertahankan batch mentah agar angka tidak berubah secara diam-diam.

Bagaimana jika satu pelanggan muncul dua kali dalam sebuah snapshot?

Perlakukan ini sebagai kesalahan kualitas data input. Pilih satu baris hanya jika versi bisnis atau urutan sumber menentukan pemenangnya; jika tidak, lakukan karantina dan buat peringatan. Menggunakan LIMIT 1 secara arbitrer membuat riwayat tidak dapat direproduksi.

Kapan Tipe 3 lebih baik daripada Tipe 2?

Gunakan Tipe 3 jika satu-satunya perbandingan yang diperlukan adalah saat ini versus sebelumnya dan versi yang lebih lama tidak akan pernah dikueri. Tipe ini menggunakan lebih sedikit ruang penyimpanan dan kueri yang lebih sederhana. Jika riwayat untuk tanggal arbitrer diperlukan di kemudian hari, migrasikan ke Tipe 2 dan sampaikan estimasi biaya backfill-nya.

Bagaimana cara memverifikasi bahwa interval tidak tumpang tindih?

Urutkan berdasarkan natural key dan periksa bahwa setiap interval berakhir tidak lebih lambat dari waktu mulai interval berikutnya, kemudian pastikan paling banyak ada satu baris saat ini per key. Jadikan pemeriksaan ini sebagai gerbang rilis batch (release gate) daripada sekadar pengambilan sampel retrospektif.