Sun King uses Amazon Redshift data sharing to accelerate data analytics and improve user experience

This post is co-authored with Guillaume Saint-Martin at Sun King. 

Sun King is the world’s leading off-grid solar energy company, and is on a mission to power access to brighter lives through off-grid solar. Sun King designs, distributes, installs, and finances solar home energy products for people currently living without reliable energy access. It serves over 100 million users in 65 countries across the world.

Over 26,000 agents across Africa today help local families get access to Sun King off-grid products to have more productive lives. These agents are informed in near-real time to find the right geographical areas and families who do not have access to low cost power. Sun King is driven by data for analyzing areas of growth across thousands of miles using a dashboards that are powered by Amazon Redshift.

In this post, we share how Sun King uses Amazon Redshift and Redshift’s features like Data Sharing capabilities to improve the performance of queries in Looker for over 1,000 of our staff.

Amazon Redshift is a fully managed, scalable cloud data warehouse that accelerates your time to insights with fast, easy, and secure analytics at scale. Tens of thousands of customers rely on Amazon Redshift to analyze exabytes of data and run complex analytical queries, making it a widely used cloud data warehouse. You can run and scale analytics in seconds on all your data without having to manage your data warehouse infrastructure.

Use case

Sun King uses a Redshift provisioned cluster to run its extract, transform and load (ETL) and analytics processes to source and transform data from various sources. It then provides access to this data for business users through Looker. Amazon Redshift currently manages varied consumption requirements for Looker users across the globe

Amazon Redshift is used to clean and aggregate data into pre-processed tables, execute Sun King’s ETL pipelines, and process Looker “persistent derived tables” (PDTs) scheduled at an hourly frequency or less. These ETLs pipelines and PDTs were competing workloads and sometimes ran into read/write conflicts.

As data-driven company continues expanding, Sun King needed a solution that does the following:

• Allows hundreds of queries to run in parallel with desired query throughput.
• Optimize workload management to enable ETL, business intelligence (BI4) and Looker workloads to run simultaneously without impacting each other.
• Seamlessly scale capacity with the increase in user base and maintain cost efficiency.

Solution overview

As the data volumes, query counts, and users continue to grow, Sun King decided to move from a single cluster to a multi-cluster architecture with data sharing to take advantage of workload isolation and separate ETL and analytics workloads across different clusters while still using a single copy of the data.

The solution at Sun King is comprised of multiple Redshift provisioned clusters and an Amazon Elastic Compute Cloud (EC2) Network Load Balancer, using the data sharing capability in Amazon Redshift.

Amazon Redshift Data Sharing enables data access across Redshift clusters without having to copy or move data. Therefore, when a workload is moved from one Redshift cluster to another, the workload can continue to access data in the initial Redshift cluster. For more information, refer to Sharing Amazon Redshift data securely across Amazon Redshift clusters for workload isolation.

The solution consists of the following key components:

• Core ETL cluster: A core ETL producer cluster (8 ra3.xlplus nodes) with data share.
• Looker cluster: A producer/consumer cluster (8 ra3.4xlarge nodes) with data share to run the following:
  • Large ETL processes
  • Looker initiated ETL processes (PDTs)
  • Data team workloads
• BI clusters: This consists of four large consumer clusters (6 ra3.4xlarge nodes each):
  • Three clusters using reserved instances (RIs) that are on 24/7
  • One on-demand cluster turned on for six hours every weekday
• Network Load Balancer: The network load balancer distributes queries originating from Looker between the consumer clusters
• Concurrency scaling free tier: Each of the three clusters using reserved instances (RIs) produces one hour of concurrency scaling credits per day, which are used on Mondays, while the on-demand cluster produces four hours of concurrency scaling credits keeping the concurrency scaling cost under free tier.

The following diagram shows the solution and workflow steps

Results

Sun King saw the following improvements with this solution:

• Performance – The improvement in performance was drastic and immediate after implementing the distributed producer/consumer architecture. Most queries (95%) that used to take between 50-90 seconds to complete before now take at most 40 seconds, 75% of queries used to take up to five seconds before now take less than one second. Additionally, the number of queries run (Amazon Redshift Adoption) increased by 40%, driven by a greater utilization of Looker following the architecture change.
• Workload management – After this architectural change, queries don’t spend a long time queued anymore. The following chart illustrates queued vs running queries on one of the clusters before and after the modernization engagement.
• Scalability – With this Redshift data share enabled architecture, the Sun King data team was able to bring back an acceptable performance to its users, leading to renewed engagement , measured with the doubling of the number of monthly queries over the following few month, thus increasing adoption of Amazon Redshift across the company.

Sun King costs are estimated to only increase by 35%, by reserving most instances used for three years (26 ra3.4xlarge and 8 ra3.xlplus) and relying on the concurrency scaling free tier for a boost of performance on the day of highest utilization. This is compared to the smaller number of reserved clusters (8 ra3.4xlarge) and a much larger utilization of concurrency scaling (two concurrency scaling clusters, nearly always on). This modernization increased the productivity of the agents by providing them faster and near real time access to areas that need access to low cost power.

Conclusion:

In this post, we discussed how Sun King used Amazon Redshift data sharing capabilities to distribute workload and scale Amazon Redshift to address end-user performance requirements from Looker and keep control over the cost of Amazon Redshift consumption. Try the approaches discussed in this post and let us know your feedback in the comments.

About the authors

Guillaume Saint-Martin leads the Data and Analytics team at Sun King. With 10 years of experience in the data and development sectors, he manages a team of over 30 analysts, data engineers, and data scientists to support Sun King long term modeling and trend analysis.

Aaber Jah is a Senior Analytics Specialist at AWS based in Chicago, Illinois. He focuses on driving and maintaining AWS Data Analytics business value for customers.

Rohit Vashishtha is a Senior Analytics Specialist Solutions Architect at AWS based in Dallas, Texas. He has over 17 years of experience architecting, building, leading, and maintaining big data platforms. Rohit helps customers modernize their analytic workloads using the breadth of AWS services and ensures that customers get the best price/performance with utmost security and data governance.