Wawancara Rekayasa Data: Mengapa PostgreSQL 18 MERGE Dapat Gagal pada Baris Sumber Duplikat?

Pertanyaan dan skenario

Tabel snapshot pelanggan harian, staging_customer, digabungkan ke dalam customer_dim. Dalam satu batch, customer_id yang sama muncul dua kali: satu kali dari CRM dan satu kali dari koreksi manual. Target tidak memiliki kunci duplikat, namun pernyataan tersebut memunculkan pelanggaran kardinalitas (cardinality violation). Jelaskan bagaimana PostgreSQL 18 membentuk baris kandidat perubahan, mengapa satu baris target tidak dapat dimodifikasi lagi oleh baris sumber lain, dan bagaimana RETURNING merge_action() dapat menghasilkan hasil yang dapat diaudit.

Apa yang sedang diuji oleh pewawancara

• Membedakan baris sumber duplikat, kunci target duplikat, dan kondisi ON yang terlalu luas.
• Menjelaskan bahwa MERGE hanya mengeksekusi cabang WHEN pertama yang cocok untuk setiap baris kandidat perubahan.
• Mengambil keputusan yang dapat dipertanggungjawabkan antara deduplikasi, menolak batch, dan mempertahankan koreksi terbaru.
• Menggunakan RETURNING untuk bukti audit tingkat baris tanpa memperlakukannya sebagai log transaksi.
• Menangani konkurensi, eksekusi ulang, hak akses, dan peringatan kualitas data.

Pertanyaan klarifikasi sebelum menjawab

• Apakah customer_id merupakan business key, atau apakah tenant dan tanggal efektif harus membentuk composite key?
• Apakah kedua rekaman sumber memiliki versi yang tepercaya, waktu kejadian (event time), atau prioritas koreksi? Jawaban ini menentukan deduplikasi.
• Apakah batch yang tidak valid harus ditolak secara atomik, atau dapatkah baris yang sudah valid ditulis terlebih dahulu? Ini mengubah desain transaksi dan replay.
• Apakah audit memerlukan nilai lama, nilai baru, kolom sumber, dan aksi, atau hanya jumlah baris?
• Bisakah rekaman sumber tiba terlambat antar batch, dan apakah target mengizinkan penghapusan lunak (soft deletion)?

Kerangka jawaban 30 detik

"Pertama-tama saya membuktikan bahwa kondisi ON memetakan setiap kunci target ke paling banyak satu baris sumber. PostgreSQL MERGE membangun baris kandidat perubahan lalu mengeksekusi satu aksi per baris dalam urutan WHEN; beberapa baris sumber yang mengarah ke satu baris target akan menyebabkan kesalahan kardinalitas dan transaksi gagal. Saya akan melakukan deduplikasi secara deterministik berdasarkan versi atau waktu kejadian, dan menolak batch ketika konflik tidak dapat diselesaikan. RETURNING merge_action() mencatat operasi insert, update, dan delete, sementara tabel kontrol batch, unique constraint, dan kunci idempotensi membuat eksekusi ulang aman."

Jawaban mendalam langkah demi langkah

Buktikan kardinalitas pencocokan terlebih dahulu

Jalankan kueri kualitas data dengan kunci yang persis sama digunakan oleh ON dan temukan kunci target yang memiliki beberapa baris sumber. Jangan hanya menerapkan DISTINCT pada sumber: dua baris dengan kunci yang sama masih dapat mendeskripsikan fakta yang bertentangan. Jika business key adalah (tenant_id, customer_id), gunakan kedua kolom tersebut dalam MERGE dan dalam kueri kualitas.

Buat deduplikasi menjadi deterministik

Prioritaskan nomor versi. Jika tidak ada, gunakan waktu kejadian, prioritas sumber yang tepercaya, dan tie-breaker yang stabil. Fungsi window dapat memilih satu pemenang:

WITH ranked AS (
  SELECT s.*, row_number() OVER (
    PARTITION BY tenant_id, customer_id
    ORDER BY version DESC, event_at DESC, source_priority DESC, ingest_id DESC
  ) AS rn
  FROM staging_customer AS s
)
SELECT * FROM ranked WHERE rn = 1;

Jika tidak ada sumber yang terbukti lebih baru, tulis konflik tersebut ke tabel karantina dan tolak batch. Jangan pernah mengandalkan database untuk memilih baris secara arbitrer.

Rancang cabang WHEN dan output audit

Kondisi WHEN dievaluasi sesuai urutan penulisan, dan cabang pertama yang bernilai true akan dieksekusi. Tempatkan kondisi proteksi terlebih dahulu, seperti hanya memperbarui saat versi yang masuk lebih tinggi, lalu tangani insert NOT MATCHED dan pembersihan NOT MATCHED BY SOURCE yang disengaja. PostgreSQL 18 RETURNING dapat menampilkan kolom sumber, nilai target lama dan baru, serta merge_action(), tetapi fitur ini melaporkan baris yang diubah oleh pernyataan ini; ini tidak menggantikan tabel kontrol batch.

Kontrol transaksi dan konkurensi

Pertahankan MERGE batch, insert audit, dan pembaruan status batch dalam satu transaksi. Berikan kunci idempotensi pada setiap batch input dan deteksi batch yang berhasil sebelum replay. Ikuti aturan isolasi database untuk eksekusi konkuren, dan pastikan paling banyak satu baris sumber kandidat per baris target sebelum eksekusi daripada menemukan duplikat saat runtime.

Contoh jawaban berkualitas tinggi

"Kesalahan ini tidak hanya dijelaskan oleh kunci target duplikat; fakta pentingnya adalah bahwa kondisi ON menghubungkan satu baris target ke beberapa baris sumber. Saya akan memeriksa kardinalitas sumber menggunakan kunci tenant dan pelanggan yang sama, lalu memberi peringkat berdasarkan versi, waktu kejadian, prioritas sumber, dan ID ingest. Konflik yang tidak dapat diselesaikan akan masuk ke karantina dan menggagalkan batch. Karena MERGE mengeksekusi cabang WHEN pertama yang cocok, saya menempatkan perlindungan versi yang lebih tinggi di awal. PostgreSQL 18 RETURNING merge_action() mencatat insert, update, atau delete ditambah nilai lama dan baru; baris audit dan kontrol batch tetap berada dalam transaksi yang sama sehingga eksekusi ulang, peringatan, dan replay memiliki bukti."

Kesalahan umum

• Kesalahan: Menerapkan DISTINCT pada sumber → Mengapa gagal: Fakta yang berbeda dapat digabungkan secara salah → Koreksi: Tentukan pemenang dengan versi dan prioritas bisnis, serta karantina konflik.
• Kesalahan: Mengasumsikan MERGE memilih baris sumber secara acak → Mengapa gagal: Beberapa modifikasi pada satu baris target memicu kesalahan kardinalitas → Koreksi: Buktikan pencocokan satu-ke-satu sebelum eksekusi.
• Kesalahan: Menganggap hitungan RETURNING sebagai keberhasilan batch → Mengapa gagal: Status zero-change, kegagalan, dan replay terlewatkan → Koreksi: Gunakan tabel kontrol batch terpisah dan status transaksi.
• Kesalahan: Hanya menguji satu thread → Mengapa gagal: Isolasi, data terlambat, dan replay tidak terverifikasi → Koreksi: Uji konkurensi, eksekusi ulang, dan batch yang terlambat.

Pertanyaan lanjutan dan tanggapan

Bagaimana jika salah satu baris sumber duplikat adalah penanda hapus (delete marker)?

Tempatkan penghapusan dan pembaruan ke dalam urutan versi yang sama dan biarkan hanya versi terbaru yang menang. Jika penghapusan tidak memiliki versi yang dapat dibandingkan, karantina konflik tersebut daripada membiarkan satu batch secara diam-diam memutuskan status pelanggan.

Bisakah RETURNING mencatat baris sumber yang tidak cocok dengan apa pun?

Fitur ini mengembalikan baris yang terpengaruh oleh INSERT, UPDATE, atau DELETE; ini bukan laporan kualitas data yang tidak cocok dari sisi sumber. Jalankan statistik anti-join secara terpisah, atau simpan kandidat dan aksi yang dimaksudkan dalam tabel pra-audit sebelum mengeksekusi MERGE.

Mengapa tidak menggunakan INSERT ... ON CONFLICT secara langsung?

Untuk insert-or-update berbasis unique-key sederhana, ON CONFLICT mungkin lebih sederhana. Pilih MERGE ketika pencocokan sumber, pembersihan data yang hilang dari sumber, atau beberapa cabang kondisional diperlukan. Jangan berasumsi semantik konkurensi dan hak aksesnya dapat dipertukarkan.

Apa yang berubah ketika target memiliki trigger?

Verifikasi bahwa trigger tidak mengubah match key atau membuat baris target yang sama menjadi kandidat baru di dalam pernyataan tersebut. Bedakan aksi MERGE dari efek samping trigger dalam data audit, dan uji rollback di lingkungan integrasi.

Referensi

• PostgreSQL Documentation 18: MERGE
• PostgreSQL Documentation 18: Merge Support Functions
• Greg Low: SQL Interview: 35 T-SQL Merge Statement Clauses
• Simplyblock: PostgreSQL MERGE tutorial

Tips menjawab

Buktikan kardinalitas satu-ke-satu pada ON terlebih dahulu, jelaskan urutan WHEN dan pelanggaran kardinalitas, lalu berikan deduplikasi deterministik, kontrol transaksi, audit RETURNING merge_action(), dan penanganan replay.

Kesimpulan satu kalimat

Jawaban MERGE yang kuat menghubungkan kardinalitas sumber, urutan cabang, dan output audit ke dalam satu kontrak data yang dapat dieksekusi ulang.