Migrating A Contact Centre Analytics Platform From Elasticsearch To ClickHouse Cloud

The Challenge

Our client operates a SaaS platform designed to help businesses motivate, support, and improve workforce performance. Their data intensive application integrates thousands of metrics from across an organisation, providing frontline staff and team leaders with insights into their performance relative to KPIs and peers.

The system is most often used by large contact centres in sectors including telecommunications and insurance, and processes millions of data points each day.

The application was based on Postgres and Elasticsearch. However, with a growing customer base, increasingly granular data, and more complex query patterns, they wanted to migrate parts of their application to ClickHouse to improve query response times and user experience.

Customer Pain Points

The customer were experiencing the following challenges prior to the engagement:

Scalability concerns related to Postgres as data volumes increase and more detailed low level transactional data is captured.
A long-running multi-hour ETL pipeline in place to precompute analytics leading to a slow lead time for users.
Developer productivity issues due to the need to re-ingest and precompute aggregates.
Overprovisioned and expensive infrastructure largely to support batch ingestion processes.
The JSON-based API of Elasticsearch made querying and application logic difficult compared to a SQL native approach.

Our Technical Approach

We took the following approach to this project:

Proposed a modern architecture using ClickHouse Cloud.
The key focus was to calculate user facing analytics on page load, rather than needing to pre-compute analytics.
Leveraged ClickPipes MySQL CDC connector to replicate core tables directly to ClickHouse.
Created materialized views in ClickHouse to dynamically compute and aggregate metrics, simplifying application logic.
Developed PoC of the solution which involved migrating the entire web application to operate against ClickHouse Cloud.
Developed an approach for a safe and progressive migration of users between the two databases.
Conducted performance and load testing of the solution using realistic data and concurrent user loads.

Outcomes

Key outcomes of the project included:

Secured ~50% reduction in database costs compared with existing Elasticsearch solution.
Deprecation of a multi-hour batch ETL ingestion process previously required for pre-computing analytics in favour of computing results at query time.
Foundations put into place to migrate from periodic batch ingestion to real-time, intraday analytics.
Significant increase in developer productivity and simplification of estate.
End-to-end migration of application completed in under 3 weeks to dev/test level.