Data platform optimisation
A sports tech startup ran a data platform centered around Amazon Redshift, enhanced by Amazon Glue and PySpark, with ETL processes managed by Airflow. The data warehouse was utilized for both internal business intelligence analytics and to supply analytical data for one of the company’s digital products. However, as data volumes increased, performance issues became apparent. The small but ambitious data team did not have enough personnel or specialized technical skills to address these long-term issues. We discovered several weaknesses in the current data infrastructure, resulting in both poor performance and excessive operational costs. After a thorough review of the data and common queries, we implemented a new data partitioning strategy that improved data distribution, minimized skew, and enhanced cluster performance. Additionally, improved ETL processes lessened the load on the cluster, leading to a 2-3x reduction in average query execution times and nearly eliminating excessively long query durations. We then recommended alterations to the data architecture that could boost system performance as data volumes continued to grow, while also achieving significant cost savings by reducing the Redshift cluster size and transferring much of the workload to PostgreSQL, which better fit the observed usage patterns. An MVP was developed to illustrate the operating principles and showcase the solution's performance and scalability potential, after which it was handed over to the internal team for further development.