Back to case studies
Automotive/Automotive Data & Analytics Provider

Migrating An Automotive Analytics Platform From SQL Server To ClickHouse

Replatforming a dealer management analytics product from SQL Server to ClickHouse, delivering observed cost savings of 50%+.

The challenge

Our client is a well known automotive data and analytics provider that delivers insights and dealer management tooling to automotive manufacturers, retailers and lenders across North America.

Their existing analytics platform is built on Microsoft SQL Server. As data volumes and query complexity have grown, the cost of running SQL Server has become a major commercial pressure with significant annual database licensing and infrastructure spend.

The team chose ClickHouse as the replacement for SQL Server based on its compression, query performance and operational economics, with a clear commercial mandate to materially reduce database costs whilst supporting growing analytics use cases.

As well as performing the data migration itself, the team also had to migrate their application onto ClickHouse and safely cut over each of their single tenant deployments, without disrupting the dealers and manufacturers who rely on the product every day.

Customer pain points

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

  • SQL Server licensing and infrastructure costs running at a high annual level with no clear ceiling as data volumes continue to grow.
  • Slow application performance, with analytical workloads outgrowing the SQL Server row store engine and long running queries impacting dealer facing analytics products.
  • Limited prior ClickHouse experience within the engineering team, requiring schema and migration guidance.
  • A need to migrate the application layer away from SQL Server alongside the data, and to safely cut over each single tenant deployment without disruption to customers.

Our technical approach

We took the following approach to this project:

  • Designed a ClickHouse target schema mapped from the existing SQL Server model, including sort key selection and type optimisation for compression.
  • Built and validated the ingestion pipeline to replicate core tables from SQL Server into ClickHouse, with full historical backfill and ongoing CDC. This used Supermetal, a vendor tool for change data capture replication, rather than building custom pipelines.
  • Developed bespoke tooling to validate and manage the CDC process, checking that data in ClickHouse stayed consistent with SQL Server and giving the team control over the replication during migration and cutover.
  • Validated query parity for the most important analytics workloads, rewriting SQL Server specific patterns into idiomatic ClickHouse SQL.
  • Stood up the SQL Server to ClickHouse data migration well ahead of cutover, allowing application teams to integrate against a stable target environment.
  • Provided enablement and pairing sessions to upskill the in-house engineering team on ClickHouse internals and operations.

Outcomes

Key outcomes of the project included:

  • Data platform fully migrated from SQL Server to ClickHouse and ready for production cutover, with the only remaining work being internal application porting on the customer side.
  • Observed database cost savings of 50%+ compared with the previous SQL Server spend.
  • Improved query performance on key dealer analytics workloads compared with the SQL Server baseline.
  • Upskilled customer engineers on ClickHouse schema design, ingestion and operations, including how it differs from SQL Server, to support long term ownership.
Case ID: automotive-dealer-managementReturn to case studies