BigQuery Cost Intelligence: Optimizing Staging Pipelines and Dataform Incremental ETL

Ringkasan Eksekutif

Seiring dengan meningkatnya volume data transaksi bulanan di AstraPay, biaya komputasi Google Cloud BigQuery melonjak secara signifikan. Kendala utama kami adalah ketiadaan visibilitas mengenai query mana yang paling memakan biaya, serta adanya pemborosan resource akibat pipeline ETL BigQuery-to-BigQuery lama yang masih menggunakan metode Full Load (membaca ulang seluruh data historis dan menimpa tabel target setiap hari).

Untuk mengatasi tantangan efisiensi ini, saya merancang dan menerapkan solusi dua arah:

1. Membangun dasbor BigQuery Cost Monitoring terpusat dengan memanfaatkan audit log metadata INFORMATION_SCHEMA untuk melacak konsumsi biaya secara real-time.
2. Merekayasa ulang pipeline transformasi internal BigQuery-to-BigQuery menggunakan Dataform dengan Incremental Mode, yang mengubah model ingesti CDC (Change Data Capture) dari yang semula berbiaya Merge tinggi menjadi model Append-Only Staging yang efisien.

Hasilnya, kami berhasil meminimalkan volume pemindaian data secara signifikan, menurunkan tagihan bulanan pergudangan data GCP, serta mempercepat waktu pemuatan laporan analitik bisnis di Looker Studio.

---

Audit Biaya & Deteksi Anomali Query via INFORMATION_SCHEMA

Sebelum melakukan optimasi teknis pada pipeline, kami memerlukan visibilitas penuh terhadap konsumsi biaya saat ini. Mengidentifikasi “query termahal” dan anomali query ad-hoc yang tidak efisien adalah langkah awal yang krusial.

Saya membangun sistem pelacakan biaya otomatis dengan melakukan query berkala terhadap metadata internal Google Cloud BigQuery. Query di bawah ini mengaudit aktivitas pekerjaan (jobs) selama 7 hari terakhir dari INFORMATION_SCHEMA.JOBS_BY_PROJECT untuk memetakan konsumsi volume data berdasarkan email pengguna, dataset, dan query SQL terkait:

SELECT
  project_id,
  user_email,
  job_id,
  DATE(creation_time, "Asia/Jakarta") AS query_date,
  -- Mengonversi ukuran bytes yang dipindai ke unit Terabytes (TB)
  ROUND(total_bytes_billed / POWER(1024, 4), 3) AS data_scanned_tb,
  -- Estimasi biaya berdasarkan model On-Demand BigQuery ($5.00 per TB)
  ROUND((total_bytes_billed / POWER(1024, 4)) * 5.0, 2) AS estimated_cost_usd,
  -- Mengambil 100 karakter pertama dari query untuk keperluan identifikasi ringkas
  SUBSTR(query, 1, 100) AS query_snippet
FROM
  `region-asia-southeast2`.INFORMATION_SCHEMA.JOBS_BY_PROJECT
WHERE
  creation_time >= TIMESTAMP_SUB(CURRENT_TIMESTAMP(), INTERVAL 7 DAY)
  AND total_bytes_billed IS NOT NULL
  AND job_type = "QUERY"
ORDER BY
  total_bytes_billed DESC
LIMIT 10;

Data hasil query audit ini dialirkan secara otomatis ke Looker Studio Cost Dashboard. Dasbor ini memvisualisasikan:

• Top 10 Expensive Queries: Query spesifik yang memindai data paling banyak beserta pembuatnya.
• Cost by User/Service Account: Mendeteksi jika ada akun sistem (service account) atau anggota tim analitik yang memicu query tanpa batasan filter partisi.
• Cost Trends by Dataset: Mengidentifikasi dataset tabel staging mana yang paling boros akibat ketiadaan partisi dan clustering yang optimal.

---

Desain Arsitektur: Transisi ke Append-Only Staging & Incremental ETL

Pada arsitektur lama kami, data dari database transaksional Cloud SQL PostgreSQL direplikasi secara real-time menggunakan GCP Datastream. Datastream langsung menjalankan perintah MERGE (upsert dinamis) ke tabel BigQuery target. Metode ini sangat berat karena BigQuery harus memindai dan menulis ulang partisi secara terus-menerus untuk setiap baris data yang berubah.

Saya merancang ulang alur data dengan membagi proses menjadi dua tahap (Decoupled Ingestion):

1. Append-Only Ingestion: Datastream dikonfigurasi untuk hanya menulis semua perubahan data sebagai entri baru (append) ke dalam tabel staging BigQuery. Ini menghindari beban komputasi MERGE yang mahal pada proses ingesti real-time.
2. Incremental Reconciliation via Dataform: Dataform menjalankan job terjadwal secara periodik untuk memproses rekonsiliasi data baru (delta) dari tabel staging ke tabel produksi secara inkremental.

Alur pemrosesan ini digambarkan secara visual pada diagram di bawah ini:

graph TD
    subgraph PostgreSQL [Cloud SQL Operational DB]
        DB[(Transaction Data)]
    end

    subgraph Staging_Layer [BigQuery Staging Layer]
        DB -->|1. CDC Stream via GCP Datastream| BQ_Stg[BigQuery Staging <br> Append-Only Table]
    end

    subgraph Transform_Layer [Dataform Engine]
        BQ_Stg -->|2. Scheduled Incremental Job| DF[Dataform SQLX Compiler]
        DF -->|3. Calculate dynamic lookback & upsert delta| BQ_Prod[(BigQuery Production <br> Partitioned & Clustered)]
    end

    subgraph Serving_Layer [Analytics & BI]
        BQ_Prod -->|4. Efficient Query Billed| Looker[Looker Studio Dashboard]
    end

    style BQ_Stg fill:#f9f,stroke:#333,stroke-width:1px
    style DF fill:#bbf,stroke:#333,stroke-width:1px
    style BQ_Prod fill:#bfb,stroke:#333,stroke-width:1px

---

Rekayasa Pipeline SQLX Dataform Incremental

Dalam merekayasa ulang pipeline transformasi di Dataform, tantangan utamanya adalah mengantisipasi data yang datang terlambat (late-arriving data atau delay sinkronisasi CDC). Jika kami hanya menyaring data yang dibuat persis pada hari berjalan (CURRENT_DATE), data transaksi yang terlambat disinkronkan beberapa jam/hari oleh Datastream akan terlewat dan tidak masuk ke database produksi.

Untuk mengatasi ini, saya mengimplementasikan Dynamic Lookback Window di dalam konfigurasi SQLX Dataform menggunakan blok pre_operations.

Berikut adalah implementasi riil model SQLX summary_daily yang mengonsolidasikan data transaksi QRIS merchant secara harian:

config {
  type: "incremental",
  schema: "merchant_analytics",
  name: "summary_daily",
  description: "Daily QRIS transaction aggregations per merchant. Incremental: first run loads all data, subsequent runs upsert from the latest processed date.",
  uniqueKey: ["id"],
  assertions: {
    uniqueKey: ["id"]
  },
  bigquery: {
    partitionBy: "DATE(updated_at)",
    clusterBy: ["merchant_id"]
  },
  tags: [
    "merchant_analytics",
    "merchant_analytics_daily"
  ]
}

-- PRE_OPERATIONS: Menentukan rentang tanggal lookback secara dinamis sebelum query utama berjalan
pre_operations {
  DECLARE lookback_start_date DATE DEFAULT (
    ${when(
      incremental(),
      -- Pada eksekusi inkremental, ambil tanggal maksimum yang sudah diproses saat ini, lalu lakukan lookback
      `COALESCE((SELECT MAX(date) FROM ${self()}), DATE('2026-01-01'))`,
      -- Jika full run/initial load, ambil tanggal statis awal projek
      `DATE('2026-01-01')`
    )}
  );
}

WITH impacted_keys AS (
  SELECT DISTINCT
    stg.date,
    stg.merchant_id
  FROM ${ref("staging_qris_raw_daily")} stg
  -- Filter hanya data staging yang masuk setelah batas lookback untuk meminimalkan data scan
  WHERE ${when(incremental(), `stg.date >= lookback_start_date`, `TRUE`)}
),

qris_transactions_d1 AS (
  SELECT
    stg.date,
    stg.merchant_id,
    stg.status,
    stg.success_amount
  FROM ${ref("staging_qris_raw_daily")} stg
  JOIN impacted_keys k
    ON stg.date = k.date
    AND stg.merchant_id = k.merchant_id
)

SELECT
  -- Hasilkan ID unik deterministik untuk mekanisme merge uniqueKey di Dataform
  (CAST(FORMAT_DATE('%Y%m%d', date) AS INT64) * 100000000) + CAST(merchant_id AS INT64) AS id,
  date,
  merchant_id,
  COUNT(*) AS total_trx,
  SUM(CASE WHEN status = 'SUCCESS' THEN 1 ELSE 0 END) AS success_trx,
  SUM(CASE WHEN status IN ('FAILED', 'VOID') THEN 1 ELSE 0 END) AS failed_trx,
  SUM(success_amount) AS total_gtv,
  CURRENT_TIMESTAMP() AS created_at,
  CURRENT_TIMESTAMP() AS updated_at
FROM qris_transactions_d1
GROUP BY date, merchant_id

Cara Kerja Pola SQLX Di Atas:

1. Konfigurasi Partisi dan Cluster: Tabel produksi dipartisi berdasarkan DATE(updated_at) dan dicluster berdasarkan merchant_id. Struktur ini memastikan bahwa visualisasi Looker Studio yang sering menyaring data berdasarkan merchant tertentu hanya memindai partisi yang relevan, memotong biaya scan hingga 90% untuk ad-hoc query.
2. Kondisional when(incremental(), ...): Sintaks Dataform compile-time ini secara dinamis menghasilkan query SQL yang berbeda tergantung pada status eksekusi job. Saat dijalankan secara inkremental, filter date >= lookback_start_date aktif, membatasi data staging yang dibaca.
3. Mekanisme uniqueKey: Dataform secara otomatis menangani operasi MERGE di belakang layar menggunakan kunci id. Jika baris dengan id yang sama sudah ada di tabel produksi (misal ada transaksi baru masuk di tanggal yang sama), baris tersebut akan diperbarui (updated). Jika belum ada, baris baru akan ditambahkan (inserted).

---

Dampak Bisnis & Hasil Kerja

Penerapan audit biaya terpadu dan refaktorisasi ETL Dataform Inkremental memberikan dampak langsung yang terukur pada efisiensi infrastruktur cloud perusahaan:

Metrik Evaluasi	Sebelum Optimasi (Full Load ETL)	Setelah Optimasi (Incremental Dataform)	Hasil / Penghematan
Volume Scan Data (Harian)	~1.8 TB per hari	~85 GB per hari	Turun ~95.2%
Durasi Eksekusi Job ETL	~42 menit	~4 menit	Lebih Cepat 10x Lipat
Latensi Pemuatan Dasbor	>15 detik (sering timeout)	<2 detik (instan)	Rendering Instan
Biaya Bulanan BigQuery	Tinggi (karena full scan TB harian)	Rendah (hanya scan data delta harian)	Pemotongan Tagihan Signifikan

Melalui inisiatif ini, tim data dapat beroperasi dengan lebih lincah tanpa khawatir memicu peningkatan pengeluaran komputasi GCP yang tidak terkendali. Strategi ini membuktikan bahwa optimasi alur kerja data yang tepat dapat secara langsung mengurangi pengeluaran operasional perusahaan (OPEX) secara signifikan.