Migrating A Dealer Management Analytics Platform From SQL Server To ClickHouse

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 annual database licensing and infrastructure spend running at $300–400k.

The team chose ClickHouse as the target platform 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.

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

• SQL Server licensing and infrastructure costs running at $300–400k annually with no clear ceiling as data volumes continue to grow.
• Analytical workloads outgrowing the row store engine, with long running queries impacting dealer facing analytics products.
• Limited prior ClickHouse experience within the engineering team, requiring schema and migration guidance.
• A need to coordinate database migration with parallel work on the application layer that consumes the data.

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.
• Validated query parity for the most important analytics workloads, rewriting SQL Server specific patterns into idiomatic ClickHouse SQL.
• Stood up the data side of the 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.