Compliant BI Solution Enabling 30x Faster Reporting and a 30% Decrease in Cloud Costs
About Our Client
The Client is a UAE-based fintech startup that offers an innovative digital payment platform.
Heavy Analytics Queries Were Affecting Payment Platform Performance
The Client’s digital payment platform used Amazon Relational Database Service (RDS) as an operational database. The same RDS database served as the primary data source for internal analytics: the Client used AWS Glue to extract the platform’s transactional and customer data and display it in Excel. That way, the Client could compare revenue across different periods and get insights about the platform’s users. As the Client’s customer base grew and the data volume increased, the heavy analytics queries overloaded the database, which started affecting the payment platform’s performance. In addition, the analytics pipelines themselves were becoming slower and slower.
At the same time, the Client was planning to enter a new market in a geographic region with strict data protection laws, and AWS did not have local servers that were compliant with the regulations. The Client was also unhappy with the increasing AWS fees and wanted to find an alternative cloud solution with more attractive pricing.
To overcome all these challenges, the Client planned to migrate its digital payment platform to a fully compliant cloud environment that would also offer a better performance-to-cost ratio. Aiming to prove the feasibility of the full-scale migration in a pilot project, the Client decided to migrate its analytics pipelines first.
The Client had already worked with ScienceSoft on the development of a BNPL platform and was satisfied with our software development services. So, the company reached out to ScienceSoft for another collaboration.
Developing a Future-Proof Approach to Analytics
ScienceSoft started by auditing the Client’s data sources and analytics processes and concluded that the data model was efficient, but the SQL queries had multiple defects. To address the payment platform’s performance issues and improve reporting efficiency, our experts suggested building a comprehensive analytics solution instead of migrating the pipelines.
ScienceSoft offered to create a data warehouse (DWH) that would use data replication queries to pull data from the RDS database. Since these queries are more lightweight than the traditional reporting ones, this would significantly decrease the burden on the payment platform. The DWH would also allow the Client to implement a self-service business intelligence (BI) tool instead of the old Excel spreadsheets. With a BI solution, the Client’s employees would gain access to more insightful and flexible analytics, visualize the target metrics, and build ad hoc reports without IT assistance. ScienceSoft also offered to fix the Client’s SQL code since it was more time- and cost-efficient than developing new queries from scratch.
As the Client was planning to migrate the entire payment platform to a new cloud in the future, the RDS would eventually need to be replaced with a different data source. To avoid any connectivity issues down the line, ScienceSoft focused on ensuring full DWH interoperability with the new data source regardless of its type. We suggested using Airbyte as it is compatible with multiple data sources, can enable efficient data replication, and features a software development kit to create custom connectors if needed.
At the same time, ScienceSoft started looking for a compliant and cost-efficient cloud environment. We recommended Google Cloud Platform (GCP) as it covered the required geographical regions and could provide sufficient operational and analytics resources at a more affordable cost.
Design and Implementation of a GCP-Based BI Solution
Upon receiving the Client’s approval, ScienceSoft implemented the analytics system that enables the following functionality:
Raw data ingestion from Amazon RDS
The solution securely replicates data from the temporary source (Amazon RDS) using Airbyte. The data lands in a data lake based on Google Cloud Storage (GCS). When processing payment transaction data, the solution downloads the updates only, which optimizes resource consumption and minimizes the load on the RDS storage while delivering the most recent revenue insights for accurate reporting.
Data transformation and structuring
Next, the data gets deduplicated and adapted to the standard data model before landing in the Google BigQuery staging area. Since AWS RDS stores data in a tabular format and Google BigQuery is optimized for unstructured data, it was essential to ensure accurate data representation in the staging area. To do this, we created dedicated Google BigQuery tables that are structurally compatible with the source data schema.
To prepare the pre-cleaned data for analytics, the solution validates, enriches, aggregates, and uploads it to the data warehouse (DWH). The data movement between the layers is executed through SQL stored procedures that are triggered by Airflow. ScienceSoft also implemented Slack notifications on data management issues for the Client’s team.
Security enablement
ScienceSoft configured GCP security mechanisms, including identity and access management and data encryption. Access to the data is possible only from the predefined IPs.The solution utilizes the Client’s original SQL queries debugged by our team.
Cost-Effective and Compliant BI Solution Ready in 3 Months
Within just three months, the Client received a comprehensive analytics solution to replace the slow analytics pipelines that were affecting the performance of the company’s digital payment platform. The self-service BI system enabled 30x faster reporting compared to the old spreadsheet-based analytics processes, and the migration from AWS to Google Cloud Platform helped the Client reduce cloud costs by 30% while ensuring compliance with the data privacy regulations of its target market.
Satisfied with the capabilities and pricing of the new cloud environment, the Client plans to fully migrate its digital payment platform to GCP.
Technologies and Tools
Google Cloud Platform, Google Cloud Storage, Google Big Query, Looker.
