Belajar Data Engineer - SQL & Database Fundamental

#Pendahuluan

Setelah di episode 2 kita merancang arsitektur data platform, kini saatnya turun ke level paling operasional: SQL. Di dunia data engineering, SQL bukan sekadar "bahasa query" — ia adalah bahasa transformasi, bahasa pengecekan kualitas, dan bahasa antar muka dengan warehouse. Sebagian besar tool yang akan kalian pelajari nanti (dbt, BigQuery, Snowflake, Spark SQL) berdiri di atas sintaks SQL yang sama.

Episode ini mengambil database PostgreSQL dari episode 0 (pg-lab) sebagai laboratorium. Tujuannya: membawa kalian dari query biasa ke query analitik yang efisien — memahami OLTP vs OLAP, menguasai CTE dan window functions, dan tahu kapan sebuah index benar-benar membantu.

#OLTP vs OLAP: Dua Dunia yang Berbeda

Database yang menyimpan data transaksional dan database yang melayani analitik punya perbedaan fundamental — dan data engineer harus paham keduanya:

Kesalahan klasik: menjalankan query analitik berat langsung di database produksi. Inilah salah satu alasan mengapa data engineer membangun warehouse terpisah — beban analitik tidak boleh mengganggu transaksi yang melayani pelanggan.

#Mempersiapkan Data Latihan

Buat data latihan sederhana untuk praktik di database de_lab:

CREATE TABLE IF NOT EXISTS orders (
    id SERIAL PRIMARY KEY,
    customer_id INT NOT NULL,
    amount NUMERIC(12,2) NOT NULL,
    status TEXT NOT NULL,
    created_at TIMESTAMP NOT NULL DEFAULT now()
);
 
INSERT INTO orders (customer_id, amount, status, created_at)
SELECT (random() * 100)::int + 1, (random() * 500)::numeric(12,2) + 10,
       (ARRAY['completed', 'pending', 'cancelled'])[floor(random() * 3) + 1],
       now() - (random() * INTERVAL '90 days')
FROM generate_series(1, 50000);

#CTE: Query yang Terbaca dan Terpakai Ulang

Common Table Expression (CTE) memecah query kompleks menjadi blok bernama yang mudah dibaca dan ditest per-bagian. Ini adalah senjata utama data engineer untuk menulis transformasi yang bisa dipelihara:

WITH customer_totals AS (
    SELECT
        customer_id,
        COUNT(*) AS total_orders,
        SUM(amount) AS total_spend
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id
),
ranked AS (
    SELECT
        customer_id,
        total_orders,
        total_spend,
        RANK() OVER (ORDER BY total_spend DESC) AS rank
    FROM customer_totals
)
SELECT *
FROM ranked
WHERE rank <= 10
ORDER BY rank;

Perhatikan alurnya: blok customer_totals menghitung agregasi, blok ranked memberi peringkat, dan query utama memfilter top-10. Setiap blok bisa diuji sendiri-sendiri — ini cara kerja transformasi dbt di episode 7.

#Window Functions: Analisis Tanpa Menghilangkan Detail

Aggregation biasa (GROUP BY) meratakan detail per baris. Window function menghitung agregasi tanpa menggabungkan baris — mempertahankan detail sambil menambahkan kolom hasil perhitungan:

SELECT
    DATE(created_at) AS order_date,
    SUM(amount) AS daily_revenue,
    SUM(SUM(amount)) OVER (ORDER BY DATE(created_at)) AS running_total,
    AVG(SUM(amount)) OVER (ORDER BY DATE(created_at)
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) AS revenue_7d_ma
FROM orders
WHERE status = 'completed'
GROUP BY DATE(created_at)
ORDER BY order_date;

Window functions yang paling sering dipakai di data engineering:

• ROW_NUMBER(), RANK(), DENSE_RANK() — memberi nomor urut per partisi, misalnya mengambil "3 order terbaru per pelanggan".
• LAG() / LEAD() untuk membandingkan nilai antar baris, dan SUM() OVER dengan ROWS BETWEEN untuk running total & moving average.

#Deduplikasi dengan Row_Number

Salah satu masalah paling umum di pipeline: data duplikat dari sumber. Window function adalah cara kanonik menghapus duplikat:

WITH deduped AS (
    SELECT *, ROW_NUMBER() OVER (
        PARTITION BY customer_id, amount, status, DATE(created_at)
        ORDER BY created_at DESC
    ) AS rn
    FROM orders
)
DELETE FROM orders USING deduped
WHERE orders.id = deduped.id AND deduped.rn > 1;

Partisi menentukan kriteria duplikat (misalnya pesanan yang sama), sedangkan ORDER BY menentukan baris mana yang dipertahankan (yang terbaru). Pola ini akan muncul berulang kali di pipeline silver.

#Index untuk Beban Analytics

Index mempercepat query dengan mengorbankan kecepatan write. Pada beban OLAP, index yang tepat membuat perbedaan menit vs milidetik. Prinsipnya:

• Partial index untuk subset data yang sering difilter — hemat ruang, cepat.
• Composite index untuk filter & grouping kombinasi kolom.
• Hati-hati: di warehouse kolumnar seperti BigQuery/Snowflake, konsep index berbeda (ada clustering/partitioning) — tapi prinsip memilih kolom yang sering difilter tetap sama.

CREATE INDEX idx_orders_status_date
ON orders (status, created_at DESC);
 
EXPLAIN ANALYZE
SELECT DATE(created_at), SUM(amount)
FROM orders
WHERE status = 'completed'
GROUP BY DATE(created_at);

EXPLAIN ANALYZE adalah jendela ke cara database mengeksekusi query: apakah memakai index, berapa baris yang di-scan, dan di mana bottleneck-nya. Ingat, ANALYZE benar-benar menjalankan query — di database produksi gunakan dengan bijak, atau baca rencana eksekusi dengan EXPLAIN saja. Latih kebiasaan ini sejak sekarang; ini menyelamatkan kalian di episode 22 (performance optimization).

#Praktik: Query Analytics dari Ujung ke Ujung

Gabungkan semua teknik dalam satu query yang menjawab pertanyaan bisnis realistis — "Bagaimana tren nilai pesanan per pelanggan 7 harinya?":

WITH orders_daily AS (
    SELECT
        customer_id,
        DATE(created_at) AS order_date,
        SUM(amount) AS amount
    FROM orders
    WHERE status = 'completed'
    GROUP BY customer_id, DATE(created_at)
)
SELECT
    customer_id,
    order_date,
    amount,
    AVG(amount) OVER (
        PARTITION BY customer_id
        ORDER BY order_date
        ROWS BETWEEN 6 PRECEDING AND CURRENT ROW
    ) AS spend_7d_ma
FROM orders_daily
ORDER BY customer_id, order_date;

Pola ini akan kalian tulis ulang di dbt, Spark, dan BigQuery dengan sintaks nyaris identik.

#Kesalahan Umum (Common Pitfalls)

1. Menulis query raksasa tanpa CTE. 200 baris subquery bersarang sulit dibaca, sulit di-debug. Pecah jadi blok bernama.

2. Memakai GROUP BY padahal butuh detail per baris. Jika kalian butuh kolom detail sekaligus agregasi, itu kerja window function, bukan group.

3. Mengabaikan EXPLAIN ANALYZE. Query yang "berfungsi" tapi lambat adalah bug tersembunyi. Biasakan memeriksa rencana eksekusi.

4. Membuat index berlebihan. Tiap index memperlambat write. Buat index hanya untuk pola filter yang benar-benar dipakai.

#Penutup

Di episode 3 ini kalian telah memperkuat fondasi SQL untuk data engineering:

• OLTP vs OLAP: beban transaksional dan analitik butuh database dan strategi berbeda.
• CTE memecah query kompleks menjadi blok bernama yang bisa diuji sendiri-sendiri.
• Window functions menghitung agregasi tanpa kehilangan detail per baris — senjata deduplikasi, ranking, dan tren.
• Index dan EXPLAIN ANALYZE adalah alat pertama optimasi query.

Di episode 4 selanjutnya kita akan membahas data modeling: star schema, snowflake schema, fact & dimension tables, serta Slowly Changing Dimensions (SCD) — cara mendesain warehouse yang benar-benar siap untuk analisis. Sampai jumpa di episode 4!