Snowflake Schema

Star schema pada chapter sebelumnya menyimpan atribut kategori langsung di dalam dim_produk. Dengan hanya tiga produk, pengulangan teks kategori belum terasa; namun bayangkan dimensi dengan 50.000 produk — nama kategori, nama divisi, dan deskripsi vendor akan tertulis berulang pada ribuan baris. Snowflake schema menyikapi masalah tersebut dengan menormalisasi dimensi: atribut yang berulang dipisah ke tabel sendiri sehingga strukturnya menyerupai kepingan salju bila digambar. Chapter ini membangun versi snowflake dari dimensi produk kita, menguji query yang setara, lalu membandingkan kedua skema secara menyeluruh.

Menormalisasi dim_produk #

Perbandingan paling jelas muncul bila kedua varian berdiri berdampingan. Skrip berikut membuat dim_produk_star (bentuk star dari chapter sebelumnya), dim_kategori, dan dim_produk_snow yang merujuk ke tabel kategori — akhiran _star dan _snow sengaja dipakai agar kedua varian bisa dibandingkan dalam satu basis data (dijalankan nyata dengan SQLite melalui modul sqlite3 Python):

CREATE TABLE dim_produk_star (
    produk_id INTEGER PRIMARY KEY,
    nama      TEXT NOT NULL,
    kategori  TEXT NOT NULL,
    harga     INTEGER NOT NULL
);
CREATE TABLE dim_kategori (
    kategori_id    INTEGER PRIMARY KEY,
    nama_kategori  TEXT NOT NULL
);
CREATE TABLE dim_produk_snow (
    produk_id   INTEGER PRIMARY KEY,
    nama        TEXT NOT NULL,
    kategori_id INTEGER NOT NULL REFERENCES dim_kategori(kategori_id),
    harga       INTEGER NOT NULL
);
CREATE TABLE fact_pesanan (
    produk_id INTEGER NOT NULL,
    qty       INTEGER NOT NULL,
    jumlah    INTEGER NOT NULL
);

INSERT INTO dim_kategori VALUES
    (10, 'Aksesoris'),
    (20, 'Monitor');
INSERT INTO dim_produk_star VALUES
    (1, 'Mouse Wireless M10', 'Aksesoris', 150000),
    (2, 'Keyboard Mekanik K75', 'Aksesoris', 450000),
    (3, 'Monitor 24 Inci IPS', 'Monitor', 1200000);
INSERT INTO dim_produk_snow VALUES
    (1, 'Mouse Wireless M10', 10, 150000),
    (2, 'Keyboard Mekanik K75', 10, 450000),
    (3, 'Monitor 24 Inci IPS', 20, 1200000);
INSERT INTO fact_pesanan VALUES
    (1, 2, 300000),
    (3, 1, 1200000),
    (2, 1, 450000),
    (2, 1, 450000),
    (1, 2, 300000),
    (3, 1, 1200000),
    (1, 2, 300000);

Pada varian star, teks 'Aksesoris' tertulis pada dua baris produk; pada varian snowflake, teks tersebut tersimpan sekali di dim_kategori dan produk hanya menyimpan kunci 10.

Query: Hasil Sama, Join Bertambah #

Pertanyaan bisnisnya sama: berapa pendapatan per kategori? Versi star menjawab dengan satu join, persis seperti chapter sebelumnya:

SELECT ps.kategori, SUM(f.jumlah) AS pendapatan
FROM fact_pesanan f
JOIN dim_produk_star ps ON ps.produk_id = f.produk_id
GROUP BY ps.kategori
ORDER BY ps.kategori;

Output yang Dihasilkan:

Aksesoris | 1800000
Monitor | 2400000

Versi snowflake harus menembus satu tabel lagi karena kategori tidak lagi tinggal di tabel produk:

SELECT k.nama_kategori, SUM(f.jumlah) AS pendapatan
FROM fact_pesanan f
JOIN dim_produk_snow ps ON ps.produk_id = f.produk_id
JOIN dim_kategori k ON k.kategori_id = ps.kategori_id
GROUP BY k.nama_kategori
ORDER BY k.nama_kategori;

Output yang Dihasilkan:

Aksesoris | 1800000
Monitor | 2400000

Hasil kedua query identik — 1800000 untuk Aksesoris dan 2400000 untuk Monitor — dan pada data sekecil ini durasinya pun nyaris sama. Bedanya baru terasa pada skala nyata: setiap level normalisasi menambah satu join pada hampir semua query, dan optimizer engine seperti DuckDB atau PostgreSQL harus bekerja lebih keras memilih urutan join yang efisien.

Pengulangan Data dan Biaya Perawatan #

Snowflake menghemat penyimpanan dengan menghapus pengulangan. Pada varian star, pengulangan tersebut dapat diukur langsung:

SELECT kategori, COUNT(*) AS jumlah_baris
FROM dim_produk_star
GROUP BY kategori
ORDER BY kategori;

Output yang Dihasilkan:

Aksesoris | 2
Monitor | 1

Teks 'Aksesoris' menempati dua baris. Konsekuensi praktisnya muncul saat perawatan: mengganti nama kategori menjadi “Aksesori” pada star schema mengharuskan UPDATE pada setiap baris produk yang bersangkutan, sedangkan pada snowflake cukup satu UPDATE di dim_kategori dan seluruh produk ikut berubah. Begitu pula kesalahan ketik hanya bisa muncul sekali pada snowflake, tetapi bisa berbeda-beda antar baris pada star.

Snowflake mulai masuk akal pada dimensi yang sangat besar. Dimensi pelanggan dengan 10 juta baris akan menyimpan nama kota dan provinsi berulang jutaan kali pada bentuk star; memisahkan hierarki kota ke tabel sendiri memangkas ukuran dimensi secara nyata. Sebaliknya, dimensi kecil seperti dim_toko dua baris tidak menuai penghematan apa pun dari normalisasi — biaya join tambahan tetap dibayar setiap query tanpa tabungan penyimpanan yang berarti.

Star vs Snowflake dalam Satu Tabel #

Kedua skema tersebut sama-sama berpusat pada pasangan fact table dan dimension table. Pasangan ini punya variasi penting yang harus dikuasai — jenis fact table, degenerate dimension, hingga dimensi yang memerankan banyak peran — dan semuanya akan kita bedah pada chapter Fact Table dan Dimension Table.