Kickdrum helps digital marketing client realize 100x performance gain

When a digital marketing company wanted to migrate their customer segmentation database to a cloud architecture, the Kickdrum team recognized an opportunity to refactor the existing technology stack to support marketer functionality that was previously impossible.

From experience with a prior client, the team recognized that providing marketers with feedback about segment sizes in real time was a difference maker in the digital marketing space. In legacy systems, creating segments was often a guessing game – but with properly optimized databases, a user interface could guide marketers with information about cohorts and constituent groups to investigate, with the segmentation user interface acting as a guide to find interesting groups to target. All the Kickdrum team needed to do was dramatically improve performance relative to the existing Oracle database.

To begin the optimization and evaluation process, Kickdrum first obtained representative queries along with observed performance details of those queries. These poor performing queries could take minutes or sometimes hours to execute on the existing Oracle database. While that poor performance was masked from end users due to the scheduled nature of their use case, an updated user interface with real-time feedback on segment size would not have been possible without dramatically improved response times. Kickdrum evaluated these queries and grouped them into 6 different classes based on the data sources and complexity of the filtering and clauses involved. Testing would need to validate performance of each class of query, as well as when different clauses were executed concurrently.

Three candidate technologies were selected for comparison to determine the best price-performance for the segmentation use case: AWS Redshift, Snowflake, and Presto on EMR. However, the Oracle database was built for specific operational activities, not for fast querying of the large data sets involved in segmentation. So Kickdrum reviewed the queries and designed a new schema optimized for performance against the very large volumes – around 8 billion rows of data - found in the real-world application. Once the new schema design was complete, data from the production database could be loaded into each candidate platform to evaluate query performance.

To thoroughly evaluate candidate technologies for real-world performance, multiple configurations were tested. The first candidate platform, AWS Redshift, was tested across 515 permutations of cluster size, node count, concurrency scaling, cache vs. no cache, and other Redshift parameters. These permutations were then tested on each query class, and to recreate real world conditions multiple runs were executed with varying concurrent loads – in total, over 1.5 million queries were executed with over 7 million measurements collected and charted.

Fortunately, some learnings from Redshift could be applied when implementing Snowflake, reducing the number of configurations to 160 permutations of cluster size, data warehouse count, and cache. Another 390 Presto on EMR configurations were evaluated with permutations for partition count, cache, threading, concurrency, and buffer sizes. In total, nearly 10 million measurements were collected from over 2 million queries across the more than one thousand permutations of candidate platforms.

Ultimately, for the real-world scenario being evaluated, we found that Presto on EMR was not a viable candidate. While suitable for many of the single queries, performance quickly degraded when concurrent queries were executed. A number of caching options were included in the permutations evaluated, but all struggled to handle concurrency at the scale being tested.

Ultimately, Redshift and Snowflake both dramatically outperformed the legacy Oracle database. However, while Snowflake performance was usually acceptable for user experience, Redshift provided better performance in nearly all scenarios evaluated. Queries that ran for hours in the legacy database executed in seconds on both platforms, with 90% executing in under 100ms on Redshift. In addition, the price performance of the optimal Redshift configuration was nearly 40% lower than the comparably performing Snowflake configuration across all query patterns.

The final platform architecture achieved performance 100x that of the original platform, and as a result delivered marketers real-time insights into segment sizes that was not previously possible. Further, additional segmentation queries showed the largest sub-groups within defined segments, empowering marketers to discover new groups for more precise and impactful targeting. By evaluating multiple platforms and identifying the optimal price-performance platform for real-world requirements, Kickdrum defined a migration path based on not just cost savings or cloud scalability, but on value-creation, delivering previously impossible capabilities.

About Kickdrum

Kickdrum partners with technology investors, boards, and executives to accelerate enterprise value. To learn more about Kickdrum, please visit https://kickdrum.com/.