Analitik revenue real-time marang jutaan transaksi/dina: kepiye?

Question

IT Interview Practice · Accepted Answer

**Aliran transaksi liwat Kafka menyang stream processor sing nge-agregat luwih dhisik menyang OLAP store, supaya dashboard maca rollup cilik sing wis diitung tinimbang nge-scan event mentahan.** Ing jutaan transaksi/dina sampeyan ora tau nglakoni `SUM()` marang tabel mentahan saben kaca dimuat — sampeyan agregat sepisan, nalika nulis.

## Arsitektur

```text
Payment svc ──▶ Kafka topic ──▶ Flink/Kafka Streams ──┬─▶ OLAP (ClickHouse/Druid)  ◀── Dashboard
 (txn events)   (buffer,replay)   (windowed pre-agg)    │      (per-minute rollups)     (read)
                                                         └─▶ S3/Parquet (raw, replay/audit)
                                     state store ◀──────────┘  (exactly-once checkpoints)
```

## Cara kerjane

- **Kafka** minangka log sing tahan lama. Saben pembayaran ngublikasi event (`amount`, `currency`, `ts`, `merchant`). Iki nge-buffer lonjakan, misahake produser saka consumer, lan ngidini sampeyan **replay** sejarah kanggo mbangun maneh agregat.
- **Stream processor (Flink / Kafka Streams)** njaga **tumbling/sliding window** (saben menit/jam) lan nglempakake saben event menyang total mlaku ing **state store**-ne. Output-e siji row wis-diagregat saben (menit, dimensi).
- **OLAP store (ClickHouse, Druid, Pinot)** nyimpen rollup kuwi kanthi columnar. Dashboard sing nge-query "revenue 24 jam pungkasan" nge-scan ~1.440 row, dudu jutaan — sangisore detik.
- **Materialized view** ing ClickHouse bisa nindakake rollup sing padha ing sajroning DB (`AggregatingMergeTree`) yen sampeyan pengin luwih sithik bagian gerak.

## Sudut pandang wawancara

Pewawancara nyoba niteni apa sampeyan **agregat nalika nulis vs scan nalika maca**, lan apa sampeyan ngerti **exactly-once**. Jawaban apik nyebutake Kafka + OLAP; jawaban apik banget nerangake **kenapa event telat/duplikat ngrusak total** lan kepiye ndandani: idempotent key, **checkpointing + transactional sink** Flink kanggo exactly-once, lan **watermark** kanggo nutup window sanajan ana sing telat teka. Jebakan umum yaiku ngusulake `SELECT SUM()` ing Postgres — apik ing ewonan/dina, lebur ing jutaan.

## Trade-off & kegagalan

- **Lambda vs Kappa.** Lambda mlaku lapisan batch (Spark saben wengi ngrekonsiliasi sumber kebeneran) ing sandhinge lapisan stream (cepet nanging perkiraan) — akurat nanging rong basis kode. **Kappa** mung nganggo lapisan stream lan **replay Kafka** kanggo ndandani bug — luwih prasaja, siji basis kode, saiki dadi standar umum.
- **Exactly-once ora gratis.** Transactional sink nambah latency lan biaya throughput; akeh tim nampa **at-least-once + idempotent upsert** sing dikunci `txn_id` malah.
- **Data telat.** Pembayaran sing rampung 2 jam telat teka sawise window-e ditutup. Watermark + allowed-lateness mbukak maneh window, utawa job batch saben wengi mbenerake angka wingi.
- **Multi-currency / refund** kudu dimodelke minangka event bertanda, yen ora "revenue" bakal nyimpang.
- **Nalika ora usah:** sawetara ewu transaksi/dina ora butuh iki babar pisan — tabel rollup Postgres sing diupdate cron ngalahake cluster Kafka+Flink sing kudu sampeyan operasi.

Arsitektur

Cara kerjane

Sudut pandang wawancara

Trade-off & kegagalan

Pitakon sing gegandhengan