From Bottleneck to Breakthrough: Inside Fetch’s Real-Time Analytics Overhaul
Session title: Flexible Forecasting and Insights with Apache Pinot
Fetch, the popular rewards app with more than 13 million daily active users, found itself grappling with a challenge familiar to many data-driven companies: Its internal sales forecasting tools couldn’t keep pace with the business.
Fetch users scan over 11 million receipts daily, generating between 30 and 40 million line items. These transactions feed proprietary forecasting models that help sales teams determine which promotional offers to present to users. They can range from a small reward for buying soda to large point bonuses for trying a meal kit subscription. The goal is to tailor offers precisely so that users are more likely to act on them, increasing reward redemption and sponsor satisfaction.
But as data volumes grew, forecasting became a problem. The models relied on vast amounts of transaction data hosted on a relational database. Performance was dragging. Queries that required filtering, grouping, and counting distinct values with millions of potential groupings took minutes to return. That slowed down internal sales and marketing teams, who rely on the tool to quickly iterate through what-if scenarios. And with the database operating under a credit-based pricing model, the company was not only wasting time but racking up unnecessary costs.
“What should have been a fast, exploratory process turned into a grind,” said Brendan Sigale, Sales Intelligence Platform Lead at Fetch, in a presentation at the Real-Time Analytics Summit hosted by StarTree.
Pinot with a twist
To address its scale and performance challenges, Fetch turned to StarTree Cloud’s managed Pinot service. Although Pinot is best known for high-throughput, real-time analytics with sub-second latency and streaming ingestion, Fetch saw a potential use to accelerate batch-style queries that were laboring on the legacy system. The goal wasn’t to support millions of queries per second but to make a relatively small number of high-value queries run much faster.
“Our highest usage table peaks at around 20 queries per second,” Sigale said. “It’s an internal tool, so we only see traffic when our internal teams are using it.”
Early results were promising, but not good enough. The pipeline exported data from the relational database into Parquet files stored on Amazon S3, which Pinot then ingested. However, there was no organization to how the data was stored. Rows from different months were scattered across all segments, meaning that a query for a single month’s data required scanning every segment. Pinot’s default behavior of ingesting files based purely on size and order, rather than understanding the data inside, compounded the problem.
“Pinot isn’t that smart when it ingests data from S3,” Sigale said. “It just grabs a Parquet file, splits it into segments based on how big the file is and how big the desired segments are, builds the segments, and then moves on to the next Parquet file on the list.”
Cardinality curse
Segment size was another issue. The initial pipeline produced thousands of tiny files, resulting in a fragmented data landscape that slowed query execution due to constant I/O overhead. Fetch tried to speed things up using StarTree indexes, but the high cardinality of the data, consisting of dozens of dimensions with thousands or tens of thousands of distinct values each, caused the indexes to consume massive amounts of disk space with little performance benefit.
The team went back to the drawing board. Their second iteration brought substantial improvements. They introduced data locality by modifying the export process to sort data by receipt purchase date before writing to S3. Now, Pinot could ingest segments that contained logically grouped rows, making it much faster to retrieve data within specific time windows. Segment sizes were also increased significantly, cutting down the total number of files and further boosting performance.
“We basically forced it to be organized,” Sigale said.
The second version also dropped the StarTree indexes in favor of traditional inverted indexes and bloom filters, which proved more effective given the nature of the data. Fetch moved to NVMe solid state storage to replace the slower hard drives previously used. Queries that had once timed out after 60 seconds now executed in one-third of a second.
In the third optimization phase, Fetch refined its query patterns even further. Sorting data during export based on business relevance, such as prioritizing frequently filtered columns over high-cardinality fields, enhancing segment efficiency. Bloom filters could now be applied to properly organize data, reducing query times to one-tenth of a second.
Hint of a solution
A particularly stubborn challenge was handling COUNT DISTINCT queries across high-cardinality user data. When asked a question like “How many users who spent over $50 on soda last month also bought my brand this week?” the multi-stage query engine in Pinot initially struggled. The default behavior – grouping by user ID at both the leaf and intermediate stages – wasted compute resources and often caused the system to run out of memory.
Fetch resolved this by applying a query hint – or predefined query execution instruction – that disabled the initial GROUP BY step. This allowed raw records to flow through to the intermediate stage, which handled the aggregation more efficiently. The change eliminated server crashes and cut query COUNT DISTINCT times from over a minute to just a few seconds.
To increase reliability, Fetch implemented a blue-green deployment model for data ingestion. Two copies of each table – one labeled “blue” and one “green” – are maintained in Pinot. Each night, one is updated while the other remains live, ensuring that users always have access to a stable dataset.
The results have been striking. Apache Pinot delivered a 30-fold performance improvement over Fetch’s previous relational database, with some queries running more than 100 times faster. While Pinot’s cost was roughly double that of the legacy setup, the value in improved speed and flexibility was exponentially greater.
“Teams can now explore data interactively, test hypotheses on the fly, and even iterate live in meetings,” Sigale said.
Fetch plans to integrate Pinot more deeply into its B2B platform, bringing the speed and power of its forecasting tools directly to its business partners. Such an expansion wouldn’t be feasible without the improvements Pinot enabled.
StarTree Pinot wasn’t just a faster database; it unlocked a more agile, responsive, and scalable approach to personalized rewards.