ETL vs ELT

Dua pola utama dalam memindahkan data dari sumber ke tujuan. Perbedaannya terletak pada kapan transformasi dilakukan.

ETL (Extract → Transform → Load)

Pola tradisional di mana data ditransformasi sebelum dimuat ke data warehouse.

# ETL Flow
Source DB ──Extract──→ Staging Server ──Transform──→ Data Warehouse
                            │
                     (clean, filter,
                      aggregate, join)

# Contoh: transformasi di Python sebelum load
import pandas as pd

# Extract
raw = pd.read_csv("sales_raw.csv")

# Transform
cleaned = raw.dropna(subset=["amount"])
cleaned["amount"] = cleaned["amount"].astype(float)
cleaned["date"] = pd.to_datetime(cleaned["date"])
monthly = cleaned.groupby(cleaned["date"].dt.to_period("M"))["amount"].sum()

# Load
monthly.to_sql("monthly_sales", engine, if_exists="replace")

ELT (Extract → Load → Transform)

Pola modern di mana data dimuat mentah ke data warehouse, lalu ditransformasi di sana menggunakan SQL.

-- ELT Flow
-- Source DB ──Extract──→ Data Warehouse ──Transform (SQL)──→ Analytics Tables
--                              │
--                     (raw data landing zone)

-- Transform di warehouse menggunakan SQL (misal dbt)
WITH raw_sales AS (
    SELECT * FROM raw.sales_events
    WHERE amount IS NOT NULL
),
cleaned AS (
    SELECT
        CAST(amount AS DECIMAL(10,2)) AS amount,
        DATE_TRUNC('month', event_date) AS month
    FROM raw_sales
)
SELECT month, SUM(amount) AS total_sales
FROM cleaned
GROUP BY month

Perbandingan

Tren Saat Ini

• ELT dominan di ekosistem modern karena cloud warehouse sangat powerful
• dbt menjadi standar de facto untuk transformasi di warehouse
• ETL masih relevan untuk data sensitif yang perlu di-filter sebelum masuk warehouse
• Banyak pipeline menggunakan hybrid — extract, light transform, load, heavy transform