Star Schema & Dimensional Modeling

Star schema adalah teknik data modeling yang paling umum di data warehouse. Dinamakan "star" karena bentuknya: satu fact table di tengah, dikelilingi oleh dimension tables.

Fact Tables

Menyimpan event atau transaksi yang terjadi. Berisi foreign key ke dimensions dan metrik numerik.

-- Fact table: setiap baris = satu event/transaksi
CREATE TABLE fact_page_views (
    view_id BIGINT,
    user_key INT REFERENCES dim_user(user_key),
    page_key INT REFERENCES dim_page(page_key),
    date_key INT REFERENCES dim_date(date_key),
    device_key INT REFERENCES dim_device(device_key),

    -- Measures (metrik yang bisa di-aggregate)
    session_duration_sec INT,
    scroll_depth_pct DECIMAL(5,2),
    is_bounce BOOLEAN
);

-- Tipe fact tables:
-- 1. Transaction fact: setiap event (page view, order)
-- 2. Snapshot fact: state pada waktu tertentu (daily balance)
-- 3. Accumulating fact: lifecycle tracking (order → ship → deliver)

Dimension Tables

Menyimpan konteks deskriptif. Banyak kolom, sedikit baris (relatif).

CREATE TABLE dim_user (
    user_key INT PRIMARY KEY,    -- Surrogate key
    user_id INT,                 -- Natural key dari source
    name VARCHAR(255),
    email VARCHAR(255),
    city VARCHAR(100),
    country VARCHAR(100),
    plan VARCHAR(50),
    signup_date DATE,
    -- SCD Type 2 fields
    valid_from TIMESTAMP,
    valid_to TIMESTAMP,
    is_current BOOLEAN
);

CREATE TABLE dim_date (
    date_key INT PRIMARY KEY,    -- Format: YYYYMMDD
    full_date DATE,
    day_name VARCHAR(10),        -- Monday, Tuesday...
    month_name VARCHAR(10),
    quarter INT,
    year INT,
    is_weekend BOOLEAN,
    is_holiday BOOLEAN,
    fiscal_quarter INT
);

SCD (Slowly Changing Dimensions)

• Type 1 — Overwrite: update langsung. Kehilangan history.
• Type 2 — Add new row: simpan versi lama dan baru. Paling umum.
• Type 3 — Add column: simpan current dan previous value saja.

-- SCD Type 2: user upgrade plan
-- Baris lama ditutup, baris baru ditambahkan
-- BEFORE:
-- user_key=100, name="Budi", plan="free", valid_from="2024-01", valid_to=NULL, is_current=TRUE

-- AFTER upgrade:
-- user_key=100, name="Budi", plan="free", valid_from="2024-01", valid_to="2024-06", is_current=FALSE
-- user_key=101, name="Budi", plan="pro",  valid_from="2024-06", valid_to=NULL, is_current=TRUE

Query Star Schema

-- Revenue per month per city
SELECT
    d.month_name,
    d.year,
    u.city,
    SUM(f.amount) AS total_revenue,
    COUNT(*) AS order_count
FROM fact_orders f
JOIN dim_date d ON f.date_key = d.date_key
JOIN dim_user u ON f.user_key = u.user_key
WHERE d.year = 2024
GROUP BY d.month_name, d.year, u.city
ORDER BY total_revenue DESC;