Real-time revenue analytics over millions of transactions/day: how?

Question

IT Interview Practice · Accepted Answer

**Stream the transactions through Kafka into a stream processor that pre-aggregates into an OLAP store, so dashboards read tiny pre-computed rollups instead of scanning raw events.** At millions of txns/day you never run `SUM()` over the raw table on each page load — you aggregate once, on write.

## Architecture

```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)
```

## How it works

- **Kafka** is the durable log. Every payment publishes an event (`amount`, `currency`, `ts`, `merchant`). It buffers spikes, decouples producers from consumers, and lets you **replay** history to rebuild aggregates.
- **Stream processor (Flink / Kafka Streams)** keeps **tumbling/sliding windows** (per minute/hour) and folds each event into a running total in its **state store**. Output is a pre-aggregated row per (minute, dimension).
- **OLAP store (ClickHouse, Druid, Pinot)** stores those rollups columnar. A dashboard querying "revenue last 24h" scans ~1,440 rows, not millions — sub-second.
- **Materialized views** in ClickHouse can do the same rollup inside the DB (`AggregatingMergeTree`) if you want fewer moving parts.

## Interview perspective

The interviewer probes whether you **pre-aggregate on write vs scan on read**, and whether you understand **exactly-once**. Good answers name Kafka + OLAP; great answers explain **why late/duplicate events corrupt totals** and how to fix them: idempotent keys, Flink **checkpointing + transactional sink** for exactly-once, and **watermarks** to close windows despite late arrivals. A common trap is proposing to `SELECT SUM()` on Postgres — fine at thousands/day, it collapses at millions.

## Trade-offs & failure modes

- **Lambda vs Kappa.** Lambda runs a batch layer (nightly Spark reconciles the source of truth) beside the stream layer (fast but approximate) — accurate but two codebases. **Kappa** uses only the stream layer and **replays Kafka** to fix bugs — simpler, one codebase, now the common default.
- **Exactly-once isn't free.** Transactional sinks add latency and throughput cost; many teams accept **at-least-once + idempotent upserts** keyed by `txn_id` instead.
- **Late data.** A payment settling 2h late lands after its window closed. Watermarks + allowed-lateness re-open windows, or a nightly batch job corrects yesterday's numbers.
- **Multi-currency / refunds** must be modeled as signed events, else "revenue" drifts.
- **When not to:** a few thousand txns/day needs none of this — a Postgres rollup table updated by a cron beats a Kafka+Flink cluster you have to operate.

Architecture

How it works

Interview perspective

Trade-offs & failure modes

Related questions