Originally published at: Zepto TechXPress
About Zepto
Zepto is an Indian quick-commerce platform. Partner brands are central to its business: their products make up a catalog sold across many cities, and every day that catalog produces millions of data points, from individual product views to sales totals per city.
A transactional database was enough for the first version of Brand Analytics. Once tables passed 200 million rows and external users expected fast, joined queries over fresh data, the dashboards needed a purpose-built OLAP engine.
Key takeaway · Outgrowing an OLTP database for analytics
The original post was written by Tejan Hede on Zepto TechXPress, Zepto's engineering blog.
To help those brands, Zepto built Brand Analytics, a dashboard where a partner brand can follow its sales trends, inventory levels, search trends, user conversions and how each subcategory performs. The team set itself one question: how to give brands insights they can act on in real time, without long waits and without reducing the data to something too coarse to be useful.
The challenge
The first version on PostgreSQL
The MVP ran on PostgreSQL and showed GMV and unit sales at the brand, city and product level. At launch, the core sales table held only a few million rows.
Zepto's analytics team works in Databricks on Delta Lake. Source tables such as orders, impressions, offers and product information were joined there into one unified sales table, close to a star schema, and a cron job copied it into PostgreSQL once a day. Serving the dashboard straight from Databricks was ruled out early: response times were too slow for end users, and concurrency was limited.
Growth outran the database
Adoption grew quickly. More brands signed on, they used the dashboard heavily, and new SKUs and categories kept arriving. The transactional data passed 200 million rows and was still growing. PostgreSQL is built for OLTP, and it had trouble with large, complex analytical queries at this size. The team concluded it needed an OLAP database designed for this workload, with three requirements:
- Complex joins, so the data would not have to be fully denormalized.
- Sub-second responses, because the dashboards are used by external customers.
- Integration with the existing upstream pipelines in Kafka and Databricks.
Evaluating OLAP engines
Zepto benchmarked three engines. ClickHouse was fast but had difficulty with joins. Apache Pinot handled low-latency ingestion well but fell short on the complex queries Zepto runs. StarRocks met all three requirements.
Three points decided it. Joins performed well. In benchmarks that simulated Zepto's current use cases, P99 latency stayed under 500 ms on more than 300 million rows. And StarRocks could ingest natively from Kafka through Routine Load and from Parquet files on S3, so the team could move from prototype to production with few changes to its pipelines.
The solution
Shared-nothing deployment
StarRocks can run in shared-data mode, querying data in object storage, or in shared-nothing mode, where it keeps data on local storage. Zepto chose shared-nothing. Latency matters most for a dashboard that external users open, and with data volumes under tens of terabytes, local storage raised no storage or scaling concerns.
Batch data from Databricks with Pipe
Databricks writes its tables as Parquet files in S3 folders. Zepto creates a StarRocks Pipe with AUTO_INGEST = TRUE on those folders, so each new Parquet file is loaded as soon as it appears. The team noted two lessons from running it:
- Pipe does not propagate deletes. Zepto marks removed rows with an
is_deletedflag instead. - Primary Key tables apply upserts, so the choice of primary key needs care.
The primary sales table now holds more than 300 million rows, and most dashboard queries join two or three tables.
Real-time data from Kafka with Routine Load
Brands also wanted to see today's sales and impressions up to the current moment, not only up to yesterday. For this Zepto uses Kafka Routine Load, which gives exactly-once ingestion, needs little configuration, and requires no third-party connector. One StarRocks table alone receives more than 30 million rows a day through Routine Load, and it is one of several tables with high volume.
The real-time pipeline has four stages:
- More than 60,000 events per second arrive from several Kafka topics. Impressions are by far the largest, followed by product events and order delivery events.
- Apache Flink jobs drop the columns that are not needed, aggregate metrics over a 5-minute window, and write the results to destination Kafka topics.
- Routine Load ingests those topics into StarRocks, where the data can be queried within seconds.
- The Brand Analytics Service queries StarRocks across SKUs, brands and regions to serve the brands' dashboards.
The results
Brand Analytics moved from a PostgreSQL MVP to a production analytics stack that keeps pace with Zepto's growth in data and users:
- Query speed: external, user-facing dashboards return results in under a second.
- Fresh data: brands see same-day sales and impressions, where the MVP showed data refreshed once a day.
- Ingestion at volume: Pipe keeps StarRocks in sync with Databricks through S3, and Routine Load streams more than 30 million rows per day from Kafka into a single table.
- Low latency by design: the shared-nothing deployment keeps data on local storage, close to the queries.
