Data as a service: Fast, user-friendly reporting for hundreds of billions of records

Our client had a reporting interface for its customers that allowed them to analyze ad campaign performance. The challenge was the underlying data’s massive size: ten billion rows per day, representing second-by-second activity across many dimensions. This challenge was further compounded by the interface’s flexibility, which allowed customers to create arbitrary queries against the data.

The existing system, built on Snowflake, was able to run the queries, but they often took several minutes to complete. The system needed to be able to return results from as recent as five minutes before the query in half a second or less and with large numbers of concurrent users.In order to support such query capability, we also needed to update the operational data processing pipeline to effectively pre-aggregate the data and efficiently stream it to a scalable back end.

Usually, in big data applications, it is common to use append-only data storage. But because the near-real-time reporting needs were typically focused on the most recent activity, the current months, days, and hours needed to be constantly updated. Tuning an analytical cluster to handle this kind of data update load was a novel and difficult problem.

We built a cloud-based data pipeline using Lambda architecture to optimize the most common types of user queries. The pipeline feeds raw data into a ClickHouse database and maintains the data that has been pre-aggregated through Apache Spark. This new process created a massive database of every event while keeping yearly, monthly, daily, and hourly aggregates of the data in the 10 dimensions most often queried. We also rebuilt the reporting system interface to leverage this new back end and provide a highly responsive user experience.

Rebuilding the system involved first doing an in-depth analysis of usage patterns to understand the best candidates of data to pre-aggregate. For this analysis, we evaluated several data storage engines and built proofs of concept with two of them (ElasticSearch and ClickHouse).

We tested dozens of potential use cases and traffic patterns. The results of this analysis and load testing informed the final architecture.We also implemented monitoring dashboards that provide overall statistics as well as alerts and indicators of potential performance issues requiring further tuning.