We built a complex data ingestion, manipulation and storage pipeline that enabled reliability reading in data and querying it under a second.
To make sure we could reliably make many thousands of API calls every week, we used Google Cloud Task queues that allow endlessly retrying API calls with exponential backoff until they pass.
We then had a lot of heavy data manipulation to do to calculate market shares. Initially we did this in BigQuery, but quickly realised that the number of writes would cost too much. Therefore we did the data manipulation in a managed PostgreSQL instance, which was quick and low cost.
However, the results would need to be queriable from the UI with sub-1-second waiting times. To achieve this, we pushed our big data output into BigQuery, which is optimised for fast parallel querying.
Finally, we built the filterable Business Dashboards using Looker Studio, which integrates with BigQuery. This allowed fast prototyping of report formats customers wanted to see.
To make the experience look customised and to enable a paywall, we then embedded the Looker Studio reports in a web application for the final product.
