Problem
Gojek's fraud engine ran batch rules on a data warehouse. The warehouse is cold storage: data lands after minutes, and even a small query needs seconds to provision resources. Some fraud cases have to be caught before an order is completed, so batch rules react too late.
The analysts who write the rules asked for a store with SQL, queries under 1 second, clear error messages, joins of streams with millions of rows against reference data with hundreds of millions of rows, and execution every few seconds to a minute.
Example from the article: find accounts created in the last 30 minutes that have concurrent bookings, meaning a booking that overlaps another booking.
Solution in brief
Replicate the Kafka streams into ClickHouse and run the analysts' SQL rules there on a schedule. A custom ingestor handles Protobuf schemas, table creation and batching. A cluster with separate insert, production-read and playground nodes keeps ingestion and experiments from slowing the rules.
Questions this case answers
- How to execute analyst-written SQL rules over multiple event streams every few seconds with sub-second latency when the batch warehouse refreshes only every few minutes
- How to ingest evolving schema-defined message streams into a columnar store while keeping ingestion, production queries and ad-hoc analysis isolated
- How to run fraud rules written by analysts in SQL every few seconds on streaming data
- How to join live event streams with large static reference tables in under a second
Tools and methods
- ClickHouse — columnar OLAP database that runs the near real-time SQL rules
- Apache Kafka — source streams of account and booking events
- Custom Go ingestor — consumes Kafka, buffers, and batch-inserts into ClickHouse
- ReplicatedMergeTree with ZooKeeper — replicates data from insertion nodes to read nodes
- Rule-based heuristics — fraud rules written by analysts as SQL queries
- CI test of ingestion — each deployment migrates the schema and checks ingestion on a test cluster
Google or a one-time email code. No password.