Bagaimana kunci NOT ENFORCED pada BigQuery memengaruhi optimasi join?

Konteks dan cakupan

Anda mengelola skema bintang di BigQuery: store_sales adalah tabel fakta dan customer adalah tabel dimensi. Tim menginginkan deklarasi primary key dan foreign key agar pengoptimal kueri dapat menggunakan metadata keunikan dan relasi untuk mengurangi operasi join. Pewawancara menanyakan tiga hal: apa jaminan dari constraint yang tidak diberlakukan (unenforced constraint), penulisan ulang ekuivalen mana yang aman, dan bagaimana mencegah kesalahan tak terdeteksi ketika terjadi pergeseran data (data drift).

Asumsikan kueri hanya memproyeksikan kolom tabel fakta, foreign key tabel fakta dapat bernilai null (nullable), dan primary key dimensi bersifat unik serta non-null. Dokumentasi Google Cloud menyatakan bahwa BigQuery tidak memberlakukan constraint ini; pemilik data wajib menjaga keabsahan data, dan kueri yang berjalan di atas constraint yang terlanggar dapat mengembalikan hasil yang salah.

Apa yang dinilai oleh pewawancara

Apakah Anda dapat membedakan antara metadata pengoptimal dan pemeriksaan integritas pada saat penulisan (write-time).
Apakah Anda memahami eliminasi join dari prinsip keunikan dan pencocokan opsional, alih-alih sekadar menganggap kunci sebagai indeks.
Apakah Anda mengetahui bahwa NOT ENFORCED tidak menolak kunci duplikat atau foreign key yatim (orphan).
Apakah Anda menerapkan kontrak tersebut ke dalam gerbang pemuatan (load gate), pemantauan, dan rollback daripada hanya berhenti pada DDL.

Jawaban yang lemah menyatakan "primary key itu unik dan foreign key mereferensikannya." Jawaban yang kuat menyatakan bahwa pengoptimal dapat menulis ulang kueri berdasarkan deklarasi yang salah, sehingga kegagalannya berupa data yang salah dan bukan sekadar data yang lebih lambat; setiap deklarasi membutuhkan bukti yang dapat diuji ulang.

Klarifikasi sebelum menjawab

Apakah ini tabel native BigQuery atau tabel eksternal? Dukungan constraint dan aturan penulisan ulang berbeda, jadi tentukan cakupannya terlebih dahulu.
Apakah kueri hanya memproyeksikan kolom tabel sebelah kiri? Memilih kolom tabel sebelah kanan biasanya mencegah eliminasi join.
Apakah foreign key bersifat nullable? NULL berarti tidak ada kecocokan yang diperlukan dan mengubah filter dalam penulisan ulang yang ekuivalen.
Apakah constraint dikelola oleh satu pipeline atau replikasi lintas sistem? Pemuatan lintas sistem memerlukan pemeriksaan sebelum dan sesudah tabel ditulis.

Jawaban-jawaban ini mengubah hasil: memilih kolom sebelah kanan, primary key duplikat, atau orphan key yang non-null membuat eliminasi join tidak aman. Jika yang tersedia hanya konsistensi akhir (eventual consistency), hasil pemeriksaan harus dijadikan gerbang rilis (release gate).

Kerangka jawaban 30 detik

"Kunci pada BigQuery adalah metadata deklaratif dan berstatus NOT ENFORCED secara default. Kunci tersebut tidak menolak primary key duplikat atau foreign key yatim saat penulisan. Nilai utamanya adalah memberi pengoptimal fakta keunikan dan relasi; misalnya, join yang hanya mengembalikan kolom tabel fakta terkadang dapat direduksi menjadi filter non-null. Data harus memenuhi deklarasi tersebut, jika tidak, penulisan ulang kueri dapat menghasilkan data yang salah. Pertama, saya memastikan proyeksi kolom dan semantik NULL, lalu menjalankan pemeriksaan kunci duplikat, orphan key, dan rekonsiliasi jumlah baris setelah setiap pemuatan. Pemeriksaan yang gagal akan memblokir rilis constraint atau membatalkan (rollback) batch tersebut."

Solusi langkah demi langkah

1. Pisahkan dua peran dari sebuah kunci

Deklarasi primary key berarti setiap baris bernilai unik dan non-null. Deklarasi foreign key berarti setiap nilai non-null harus ada pada primary key yang direferensikan. BigQuery dapat membaca deklarasi tersebut untuk optimasi, tetapi tidak melakukan validasi penulisan. NOT ENFORCED adalah kontrak eksplisit; cacat yang timbul adalah data tidak valid, bukan sintaks yang tidak valid.

2. Memahami eliminasi inner-join

Perhatikan kueri yang hanya memilih kolom tabel fakta:

sql

SELECT ss.*
FROM store_sales AS ss
JOIN customer AS c
  ON ss.sales_customer = c.customer_name;

Jika customer.customer_name adalah primary key unik non-null dan setiap ss.sales_customer non-null cocok dengan satu pelanggan, join tersebut tidak akan menduplikasi baris tabel fakta. Pengoptimal dapat menulis ulangnya menjadi:

sql

SELECT *
FROM store_sales
WHERE sales_customer IS NOT NULL;

Penulisan ulang ini bergantung pada dua fakta: foreign key non-null memiliki kecocokan, dan kecocokan tersebut paling banyak satu baris. Ini tidak ekuivalen jika kueri membutuhkan kolom tabel sebelah kanan, perlu membedakan baris yang tidak cocok, atau primary key terduplikasi.

3. Batasan untuk outer join dan urutan join

Left outer join juga dapat dihilangkan jika right join key unik dan hanya kolom tabel sebelah kiri yang diproyeksikan. Dalam kueri multi-join, metadata kunci dapat menyediakan informasi kardinalitas untuk penyusunan ulang urutan join (join reordering). Ini adalah deduksi berbasis metadata; BigQuery tidak memindai tabel sebelah kanan pada saat runtime untuk membuktikan keunikan.

4. Terapkan validasi pada gerbang pemuatan (load gate)

Jalankan setidaknya tiga pemeriksaan untuk setiap batch:

sql

-- Duplicate or null primary keys
SELECT customer_name, COUNT(*) AS n
FROM customer
GROUP BY customer_name
HAVING customer_name IS NULL OR n > 1;

-- Orphan foreign keys
SELECT COUNT(*) AS orphan_count
FROM store_sales AS ss
LEFT JOIN customer AS c
  ON ss.sales_customer = c.customer_name
WHERE ss.sales_customer IS NOT NULL
  AND c.customer_name IS NULL;

Rekonsiliasi jumlah baris batch, jumlah foreign key non-null, dan jumlah kunci yang cocok. Simpan hasilnya di tabel kualitas data. Hanya versi yang lolos dari gerbang ini yang boleh memublikasikan deklarasi constraint baru atau mengizinkan kueri hilir mengandalkan optimasi tersebut.

5. Pilih antara deklarasi, validasi, atau penulisan ulang kueri

Deklarasi cocok untuk kunci dimensi yang stabil dan dapat diuji, serta memungkinkan pengoptimal memanfaatkan relasi secara otomatis. Validasi aplikasi cocok untuk jalur penyerapan (ingress) yang harus menolak data buruk sejak awal. Penulisan ulang kueri secara eksplisit cocok untuk masa migrasi saat kontrak belum tepercaya, meski menimbulkan konsekuensi logika duplikat. Ketiganya dapat berdampingan: validasi di pipeline, deklarasikan relasi yang tepercaya, dan rekonsiliasikan laporan kritis.

6. Rancang alur penanganan kegagalan (failure path)

Jika pemeriksaan gagal, pertahankan tabel atau view tepercaya terakhir, tandai batch saat ini sebagai tidak dapat dipublikasikan, dan kirimkan peringatan ke pemilik data. Jangan menghapus constraint sebagai satu-satunya "perbaikan"; hal itu menyembunyikan akar masalah. Lacak sumber kunci duplikat, keterlambatan replikasi, atau urutan penghapusan, lalu pilih pemutaran ulang (replay), deduplikasi, atau perbaikan dimensi.

Contoh jawaban berkualitas tinggi

"Saya memperlakukan primary key dan foreign key BigQuery sebagai kontrak optimasi, bukan constraint transaksi. Pertama, saya memastikan kueri hanya memproyeksikan kolom fakta, mendefinisikan arti dari foreign key bernilai null, dan memverifikasi keunikan kunci dimensi. Dalam kondisi tersebut, inner join dapat diubah menjadi filter fakta non-null, beberapa left join dapat dihilangkan, dan urutan join dapat memanfaatkan kardinalitas yang dideklarasikan. Risikonya adalah BigQuery tidak memberlakukan kontrak ini: primary key duplikat atau foreign key yatim membuat pengoptimal menulis ulang kueri menggunakan metadata palsu, yang dapat secara tak terdeteksi mengubah hasil.

"Sebelum rilis, saya memeriksa primary key yang null dan duplikat, foreign key yatim, serta rekonsiliasi jumlah baris untuk setiap batch. Batch yang gagal tidak boleh memublikasikan tabel atau constraint baru; versi tepercaya sebelumnya tetap aktif. Selama migrasi dengan metadata yang belum tepercaya, saya menonaktifkan penulisan ulang dependen atau menggunakan join eksplisit hingga beberapa batch berhasil lolos. Hal ini mempertahankan manfaat optimasi sekaligus menjaga kebenaran data tetap dapat diaudit."

Kesalahan umum

Kesalahan: Menyatakan bahwa PRIMARY KEY menolak duplikasi → Mengapa gagal: Dokumentasi BigQuery menyatakan constraint ini berstatus unenforced → Solusi: Terapkan deteksi duplikasi pada gerbang pemuatan (load gate).
Kesalahan: Menghapus setiap join yang memiliki foreign key → Mengapa gagal: Kunci tersebut mungkin yatim (orphan) dan kueri mungkin membutuhkan kolom dari tabel sebelah kanan → Solusi: Verifikasi data dan kondisi proyeksi kolom terlebih dahulu.
Kesalahan: Memeriksa data historis hanya satu kali → Mengapa gagal: Pemuatan inkremental, backfill, dan keterlambatan replikasi dapat memasukkan kembali kunci yang salah → Solusi: Jalankan pemeriksaan batch secara kontinu dan simpan metriknya.
Kesalahan: Menghapus constraint setelah terjadi kesalahan hasil kueri → Mengapa gagal: Metadata optimasi hilang sementara cacat data pada sumbernya tetap ada → Solusi: Bekukan rilis, cari penyebabnya, dan pulihkan versi yang tepercaya.

Pertanyaan lanjutan dan respons

Bagaimana jika tabel dimensi kemasukan kunci duplikat hari ini?

Jeda kueri yang mengandalkan deklarasi tersebut, beralihlah ke snapshot yang telah dideduplikasi secara eksplisit atau snapshot tepercaya, tandai batch yang terdampak, dan ulangi rekonsiliasi. Pulihkan deklarasi hanya setelah tabel dimensi diperbaiki dan lolos pengujian.

Mengapa foreign key yang nullable memerlukan filter non-null?

NULL berarti baris fakta tersebut tidak memiliki kecocokan pelanggan. Inner join membuang baris tersebut, sehingga penulisan ulang yang ekuivalen harus mempertahankan WHERE sales_customer IS NOT NULL; menghilangkannya akan mengubah hasil akhir.

Bagaimana cara membuktikan bahwa eliminasi join mempertahankan hasil yang sama?

Jalankan kueri asli dan kueri hasil penulisan ulang secara berdampingan pada partisi yang representatif. Bandingkan jumlah baris, himpunan primary key, dan hasil agregasi, lalu catat versi pemeriksaan constraint-nya. Terapkan penulisan ulang hanya setelah pemeriksaan kualitas data dan rekonsiliasi hasil kueri lolos.

Kapan sebaiknya menghindari optimasi berbasis constraint?

Hindari jika constraint berasal dari replikasi yang lambat atau tidak dapat diaudit, saat backfill sering terjadi, atau saat tidak ada gerbang batch. Satu pemindaian tambahan lebih baik daripada kueri yang lebih murah tetapi mempercayai metadata palsu.

Bisakah constraint BigQuery menggantikan transaksi lintas tabel?

Tidak. Constraint ini tidak memberlakukan konsistensi penulisan atau menyediakan atomic commit lintas tabel. Semantik transaksi merupakan ranah sistem hulu atau orkestrator pemuatan, dengan hasil validasi akhir dibawa ke dalam data warehouse.

Referensi

Dokumentasi Google Cloud: BigQuery primary and foreign keys.
Blog Google Cloud: Join Optimizations with BigQuery Primary and Foreign Keys.