Transformasi dengan SQL

Semua contoh sejauh ini menarik data keluar dari database, mengolahnya di Python, lalu memuatnya kembali. Perpindahan bolak-balik tersebut membayar biaya: serialisasi data, dua kali jaringan, dua pustaka yang harus sinkron. Ketika data memang sudah berada di dalam warehouse, SQL bisa menjalankan transformasi tanpa memindahkan data ke mana-mana — inilah semangat ELT pada chapter pertama. Pola kerjanya khas: data mentah mendarat di tabel staging, lalu query SQL membentuknya menjadi tabel siap pakai. Chapter ini membahas perangkat SQL untuk transformasi, mengujinya nyata pada alur staging ke dimensi, lalu membandingkannya dengan Python.

CREATE TABLE AS SELECT #

CREATE TABLE ... AS SELECT (CTAS) membangun tabel baru langsung dari hasil query — schema dan isi terbentuk sekali jalan. Karena seluruh ekspresi kolom SQL tersedia, CTAS menjadi wadah transformasi: fungsi string seperti TRIM dan LOWER membersihkan nilai, substr memotong bagian string, dan ekspresi CASE menurunkan kolom kategori. Berikut bentuk umumnya.

CREATE TABLE nama_tabel_baru AS
SELECT
    TRIM(kolom_kotor)        AS kolom_bersih,
    CASE kolom_status
        WHEN 'aktif'  THEN 'Y'
        ELSE 'N'
    END                       AS flag_aktif
FROM tabel_staging;

Query semacam ini membaca tabel staging, mengubah nilai per baris, dan menuliskan hasilnya ke tabel baru — tanpa satu baris keluar dari database. CTAS juga idempoten menurut pola truncate-and-load: jalankan ulang setelah mengosongkan tabel tujuan, hasilnya tetap sama.

Contoh: dari Staging ke Dimensi #

Alur lengkapnya berikut. Data pelanggan mentah masuk ke staging_pelanggan; CTAS membentuk dimensi bersih; lalu INSERT ... ON CONFLICT DO UPDATE (upsert versi SQLite) memperbarui tabel target ketika batch baru datang.

import sqlite3

koneksi = sqlite3.connect(":memory:")
koneksi.execute(
    "CREATE TABLE staging_pelanggan ("
    "pelanggan_id INTEGER, nama TEXT, kota TEXT, status TEXT, terdaftar TEXT)"
)
koneksi.executemany("INSERT INTO staging_pelanggan VALUES (?, ?, ?, ?, ?)", [
    (1, " budi ", "Jakarta", "aktif", "2026-01-15"),
    (2, "siti", "Bandung", " aktif ", "2026-02-03"),
    (3, "DEWI", "Jakarta", "nonaktif", "2026-03-20"),
])

# Transformasi di dalam database: CTAS + CASE + fungsi string
koneksi.execute("""
    CREATE TABLE dim_pelanggan AS
    SELECT
        pelanggan_id,
        LOWER(TRIM(nama)) AS nama,
        LOWER(TRIM(kota)) AS kota,
        CASE LOWER(TRIM(status))
            WHEN 'aktif' THEN 'Y'
            ELSE 'N'
        END AS pelanggan_aktif,
        substr(terdaftar, 1, 7) AS bulan_daftar
    FROM staging_pelanggan
""")
for baris in koneksi.execute("SELECT * FROM dim_pelanggan ORDER BY pelanggan_id"):
    print(baris)

# Upsert: batch baru masuk, konflik kunci diperbarui
koneksi.execute(
    "CREATE TABLE target_pelanggan (pelanggan_id INTEGER PRIMARY KEY, nama TEXT, kota TEXT)"
)
koneksi.execute("INSERT INTO target_pelanggan VALUES (1, 'Budi', 'Jakarta')")
koneksi.executemany("""
    INSERT INTO target_pelanggan (pelanggan_id, nama, kota)
    VALUES (?, ?, ?)
    ON CONFLICT(pelanggan_id) DO UPDATE SET
        nama = excluded.nama, kota = excluded.kota
""", [
    (1, "Budi", "Depok"),     # sudah ada -> di-update
    (2, "Siti", "Bandung"),   # baru -> di-insert
])
print(list(koneksi.execute("SELECT * FROM target_pelanggan ORDER BY pelanggan_id")))
koneksi.close()

Output yang Dihasilkan:

(1, 'budi', 'jakarta', 'Y', '2026-01')
(2, 'siti', 'bandung', 'Y', '2026-02')
(3, 'dewi', 'jakarta', 'N', '2026-03')
[(1, 'Budi', 'Depok'), (2, 'Siti', 'Bandung')]

Mari kita telaah bagian-bagian kunci di atas:

1. Baris 4-12 (staging): tabel staging sengaja tanpa constraint — tugasnya hanya menampung data mentah apa adanya, termasuk spasi dan kapital acak dari tiga sumber aplikasi.
2. Baris 19-20 (cleaning): LOWER(TRIM(...)) merapikan dua lapis sekaligus: spasi dibuang, kapital diseragamkan ke huruf kecil — SQLite tidak menyediakan fungsi title case, jadi pilihan huruf kecil konsisten lebih aman daripada kapitalisasi campuran.
3. Baris 21-24 (CASE): status teks diturunkan menjadi flag Y/N per pelanggan — contoh derivasi kolom berbasis kategori.
4. Baris 25 (derivasi): substr(terdaftar, 1, 7) memotong tanggal "2026-01-15" menjadi bulan "2026-01", siap dipakai sebagai kunci agregasi bulanan.
5. Baris 36-44 (upsert): ON CONFLICT(pelanggan_id) DO UPDATE mencocokkan kunci; baris 1 sudah ada sehingga kota Jakarta ditimpa Depok, baris 2 baru sehingga di-insert — baris terakhir output membuktikan keduanya.

Window Function untuk Transformasi #

CTAS dan CASE bekerja per baris secara mandiri. Window function menambahkan kesadaran lintas baris: tiap baris bisa “melihat” baris lain dalam kelompoknya. Contoh berikut menurunkan kolom total_kota (jumlah penjualan seluruh bulan untuk kota tersebut) dan peringkat (urutan bulan berdasarkan total, terbesar duluan) pada tiap baris.

import sqlite3

koneksi = sqlite3.connect(":memory:")
koneksi.execute("CREATE TABLE penjualan (kota TEXT, bulan TEXT, total INTEGER)")
koneksi.executemany("INSERT INTO penjualan VALUES (?, ?, ?)", [
    ("Jakarta", "2026-09", 1250000),
    ("Bandung", "2026-09", 640000),
    ("Jakarta", "2026-10", 1420000),
    ("Bandung", "2026-10", 715000),
])
koneksi.execute("""
    CREATE TABLE penjualan_transform AS
    SELECT
        kota, bulan, total,
        SUM(total) OVER (PARTITION BY kota) AS total_kota,
        ROW_NUMBER() OVER (PARTITION BY kota ORDER BY total DESC) AS peringkat
    FROM penjualan
""")
for baris in koneksi.execute(
        "SELECT * FROM penjualan_transform ORDER BY kota, bulan"):
    print(baris)
koneksi.close()

Output yang Dihasilkan:

('Bandung', '2026-09', 640000, 1355000, 2)
('Bandung', '2026-10', 715000, 1355000, 1)
('Jakarta', '2026-09', 1250000, 2670000, 2)
('Jakarta', '2026-10', 1420000, 2670000, 1)

SUM(total) OVER (PARTITION BY kota) menjumlahkan tanpa menggabungkan baris — Bandung mendapat total_kota 1.355.000 pada kedua barisnya. ROW_NUMBER() OVER (PARTITION BY kota ORDER BY total DESC) memberi nomor urut dalam kelompok kota; Oktober yang lebih besar menjadi peringkat 1. Peringkat berbasis ROW_NUMBER inilah yang nanti dipakai untuk deduplikasi keep-latest pada chapter Deduplikasi Data. Pembahasan window function yang lebih dalam tersedia pada chapter Window Functions di section Dasar SQL.

MERGE, UPSERT, dan Pilihan Engine #

Standar SQL menyediakan statement MERGE untuk logika upsert; PostgreSQL menyebutnya MERGE ... WHEN MATCHED ... WHEN NOT MATCHED (berjalan di PostgreSQL, tidak didukung SQLite), sedangkan SQLite memakai INSERT ... ON CONFLICT DO UPDATE seperti contoh di atas, dan DuckDB memakai INSERT ... ON CONFLICT DO UPDATE juga. Perbedaan dialek ini wajar dijumpai — bentuk logikanya sama, hanya sintaksnya yang menyesuaikan engine. Daftar sintaks per statement tersedia di referensi bahasa SQLite dan dokumentasi PostgreSQL .

SQL atau Python untuk Transformasi #

Transformasi staging ke dimensi tadi masih bekerja pada satu tabel. Dua operasi berikutnya justru berdiri di atas lebih dari satu tabel: menggabungkan fakta dengan dimensi, lalu merangkumnya bertingkat dari harian ke bulanan. Chapter Join dan Agregasi Data melanjutkan ke sana.