Surrogate Key

Chapter Fact Table dan Dimension Table menutup dengan fact table yang bergabung ke dimension table melalui sebuah key. Pertanyaan desain yang langsung muncul: key apa yang sebaiknya dipakai — kode produk dari sistem operasional, atau angka teknis yang dibuat warehouse sendiri? Pilihan tersebut terlihat sepele saat proyek dimulai, tetapi menentukan apakah warehouse tetap sehat ketika bisnis berganti sistem, sumber data bertambah, dan tabel membesar. Surrogate key adalah jawaban baku dimensional modeling untuk ketiga masalah tersebut.

Natural Key vs Surrogate Key #

Natural key adalah identifier yang sudah tersedia di dunia operasional: nomor KTP pelanggan, kode produk, atau alamat email. Surrogate key adalah nilai teknis tanpa makna bisnis — biasanya integer berurutan — yang dibuat khusus oleh warehouse dan tidak pernah dikenal oleh sistem sumber. Keduanya bukan pesaing, melainkan berpasangan: surrogate key menjadi primary key dimensi, sedangkan natural key disimpan sebagai kolom atribut biasa.

Tiga Alasan Warehouse Membutuhkan Surrogate Key #

Alasan pertama adalah bisnis berubah. Perusahaan berganti sistem ERP dan menerbitkan kode produk baru, pelanggan mengganti alamat email, atau format nomor identitas diperbarui. Bila natural key dipakai sebagai primary key, perubahan tersebut memaksa pembaruan jutaan baris fact yang mereferensikan key lama. Dengan surrogate key, perubahan natural key cukup ditangani sebagai pembaruan atribut pada satu baris dimensi, dan seluruh riwayat fact tetap menunjuk ke key integer yang sama.

Alasan kedua adalah integrasi multi sumber. Toko online kita menjual lewat website sendiri dan lewat marketplace, dan kedua sistem sama-sama memakai kode P-100 untuk produk yang berbeda. Natural key dari satu sistem tidak cukup untuk menjadi identitas global; pasangan kolom (sumber, kode_sumber) plus satu surrogate key tunggal menjadi jembatan yang aman.

Alasan ketiga adalah performa join. Fact table bertambah jutaan baris per bulan, dan setiap query analitik melakukan join ke beberapa dimensi sekaligus. Membandingkan integer 8 byte jauh lebih murah daripada membandingkan string panjang, dan index di atas kolom integer juga lebih kecil sehingga lebih banyak muat di memori.

Menghasilkan Surrogate Key #

Surrogate key umumnya dihasilkan otomatis oleh database melalui mekanisme autoincrement. PostgreSQL menyediakan klausa GENERATED ALWAYS AS IDENTITY (standar SQL) serta tipe SERIAL yang lebih lama, keduanya ditopang objek sequence yang mengeluarkan angka monoton. SQLite memakai aturan yang lebih ringkas: kolom bertipe INTEGER PRIMARY KEY otomatis menjadi alias rowid, sehingga baris baru tanpa nilai akan menerima angka berturut dari mesin penyimpanan.

-- Skema padanan di PostgreSQL (berjalan di PostgreSQL)
CREATE TABLE dim_pelanggan (
    sk_pelanggan BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    email        TEXT NOT NULL
);

Klausa GENERATED ALWAYS mencegah aplikasi menyisipkan nilai manual ke kolom tersebut, sehingga satu-satunya sumber surrogate key adalah mekanisme database. Penjelasan lengkap klausa identity ada di dokumentasi PostgreSQL , sementara aturan INTEGER PRIMARY KEY di SQLite dirinci di dokumentasi SQLite .

Lookup saat Load #

Fact table dari sistem sumber hanya memuat natural key, jadi proses load wajib menerjemahkan natural key menjadi surrogate key melalui lookup: cari baris dimensi yang cocok; bila tidak ada, sisipkan baris baru dan ambil SK hasil autoincrement. Skrip berikut memuat dimensi produk dari dua sistem berbeda, lalu menjalankan ulang satu load untuk membuktikan lookup bersifat idempotent.

import sqlite3

koneksi = sqlite3.connect(":memory:")
cur = koneksi.cursor()

# dimensi produk: surrogate key + natural key gabungan (sumber, kode_sumber)
cur.execute("""
CREATE TABLE dim_produk (
    sk_produk    INTEGER PRIMARY KEY,  -- surrogate key
    sumber       TEXT NOT NULL,        -- sistem asal data
    kode_sumber  TEXT NOT NULL,        -- natural key di sistem asal
    nama_produk  TEXT NOT NULL,
    kategori     TEXT NOT NULL,
    UNIQUE (sumber, kode_sumber)
)
""")


def lookup_atau_buat(sumber, kode, nama, kategori):
    """Cari SK dari natural key; buat SK baru bila belum terdaftar."""
    baris = cur.execute(
        "SELECT sk_produk FROM dim_produk WHERE sumber = ? AND kode_sumber = ?",
        (sumber, kode),
    ).fetchone()
    if baris is not None:
        return baris[0]                 # sudah ada: pakai ulang SK lama
    cur.execute(
        """INSERT INTO dim_produk (sumber, kode_sumber, nama_produk, kategori)
           VALUES (?, ?, ?, ?)""",
        (sumber, kode, nama, kategori),
    )
    return cur.lastrowid                # SK baru dari autoincrement


print("Load 1 - dari ERP:")
for kode, nama, kat in [
    ("P-100", "Kemeja Flanel", "Pakaian"),
    ("P-200", "Sepatu Lari", "Olahraga"),
]:
    sk = lookup_atau_buat("erp", kode, nama, kat)
    print(f"  {kode} -> sk_produk {sk}")

print("Load 2 - dari Marketplace (kode P-100 dipakai produk berbeda):")
for kode, nama, kat in [
    ("P-100", "Kipas Angin Mini", "Elektronik"),
    ("M-777", "Topi Baseball", "Aksesori"),
]:
    sk = lookup_atau_buat("marketplace", kode, nama, kat)
    print(f"  {kode} -> sk_produk {sk}")

print("Load 3 - ERP dijalankan ulang:")
sk = lookup_atau_buat("erp", "P-100", "Kemeja Flanel", "Pakaian")
print(f"  P-100 -> sk_produk {sk}")

print()
print("Isi akhir dim_produk:")
for baris in cur.execute(
    """SELECT sk_produk, sumber, kode_sumber, nama_produk, kategori
       FROM dim_produk ORDER BY sk_produk"""
):
    print(" ", baris)

Output yang Dihasilkan:

Load 1 - dari ERP:
  P-100 -> sk_produk 1
  P-200 -> sk_produk 2
Load 2 - dari Marketplace (kode P-100 dipakai produk berbeda):
  P-100 -> sk_produk 3
  M-777 -> sk_produk 4
Load 3 - ERP dijalankan ulang:
  P-100 -> sk_produk 1

Isi akhir dim_produk:
  (1, 'erp', 'P-100', 'Kemeja Flanel', 'Pakaian')
  (2, 'erp', 'P-200', 'Sepatu Lari', 'Olahraga')
  (3, 'marketplace', 'P-100', 'Kipas Angin Mini', 'Elektronik')
  (4, 'marketplace', 'M-777', 'Topi Baseball', 'Aksesori')

Mari kita telaah baris-baris kode di atas:

1. Baris 8-16 (CREATE TABLE dim_produk): kolom sk_produk bertipe INTEGER PRIMARY KEY sehingga otomatis menjadi alias rowid SQLite; constraint UNIQUE (sumber, kode_sumber) menjamin satu produk dari satu sistem hanya terdaftar sekali.
2. Baris 21-26 (cabang lookup): query SELECT mencari SK berdasarkan pasangan natural key; bila baris ditemukan, fungsi langsung mengembalikan SK lama tanpa menulis apa pun — inilah yang membuat Load 3 menerima sk_produk 1, bukan angka baru.
3. Baris 27-32 (cabang insert): bila natural key belum terdaftar, fungsi menyisipkan baris baru tanpa mengisi sk_produk, lalu membaca cur.lastrowid untuk mengambil angka autoincrement yang baru saja diberikan SQLite.
4. Baris 43-49 (Load 2): kode P-100 dari marketplace tidak bertabrakan dengan P-100 milik ERP karena kolom sumber membedakan keduanya — kedua produk justru mendapat SK berbeda, 1 dan 3.

Surrogate key memberi dimensi identitas yang stabil, tetapi identitas stabil saja belum cukup: nilai atribut seperti kota pelanggan dan kategori produk juga berubah seiring waktu, dan warehouse harus memutuskan apakah perubahan tersebut menimpa nilai lama atau disimpan sebagai riwayat. Keputusan tersebut dibahas sistematis pada Chapter Slowly Changing Dimensions.