Quest1
Case Study · Healthcare · ClickHouse Design Validation

A ClickHouse Design Validation Engagement: Stabilizing a Production Analytics Pipeline at Scale

How an EHR practice-management platform serving tens of thousands of independent medical practices brought in dedicated ClickHouse expertise to optimize its production analytics pipeline — cutting memory consumption 5–10× on the most expensive queries, halving the size of the ClickHouse Cloud instance, and equipping the in-house team to diagnose the next wave of issues themselves.

$ cat case-study/details.yaml
Industry
Healthcare
Service
ClickHouse Design Validation
Partner
ClickHouse
$ read /case-study/01-the-problem.md

The Problem.

The client had made a deliberate strategic move to ClickHouse for their analytics workload, migrating off MS SQL Server. The decision was the right one: their reporting and analytics workloads are OLAP-shaped, their data volumes were growing in step with their customer base, and ClickHouse is purpose-built for the access pattern.

Getting the platform stood up and running on ClickHouse was the first phase of that move, and the team executed it. But ClickHouse is a different kind of analytical engine than the OLTP-derived databases most engineering teams build on. Its strengths — columnar storage, vectorized execution, materialized views as a first-class compute primitive, sort-key-driven physical layout — depend on a set of design choices that don't have direct equivalents in the RDBMS world. Translating an existing analytics workload to play to those strengths is a domain in its own right, and it's where dedicated ClickHouse expertise earns its keep.

In production, the pattern was visible in a way that's familiar to any team that's run an analytical platform at scale. Some queries were efficient. Others — particularly the ones built on chained materialized views — were generating memory pressure, occasional spikes, and crashes when load increased. As the platform onboarded more customers, the worst cases were getting worse. The client wanted to stop the immediate pain and, more importantly, get a clear-eyed view of what to redesign for the longer term.

The client engaged Quest1's ClickHouse Design Validation service for exactly that: short-term stabilization of the production environment, and a longer-term roadmap for the data-model and pipeline redesign work that comes next.

$ read /case-study/02-what-we-built.md

What we built.

Quest1 ran a multi-week ClickHouse Design Validation engagement, structured to deliver immediate stabilization and a longer-term redesign roadmap in parallel. The work was co-delivered with ClickHouse Support — Quest1 anchored the design and architecture review, ClickHouse Support contributed deep platform-level diagnostics — and the combination is what made the root cause findable in the time available.

A full review of the existing system, queries, and data flow pipelines.
The first weeks went into understanding the production environment as it actually ran: what queries were firing, against what models, through what pipelines, and where the memory pressure was concentrating. The data flow surfaces — Kafka for ingest, Airflow for orchestration, ClickHouse on GCP as the analytical store — were each examined for the shape of the load they were placing on ClickHouse, not just the shape of the code that ran on them.
A documented optimization report.
The output of the review was a structured report mapping each significant ClickHouse-specific optimization opportunity in the current system to a concrete recommendation. The report wasn't a list of generic best practices; every finding was tied to a specific query, table, or pipeline in the client's environment, with the underlying ClickHouse mechanics explained. This is the artifact that makes the ClickHouse Design Validation service durable — the client owns it, refers back to it, and can extend it as the system evolves.
Trace-driven root cause diagnosis with ClickHouse Support.
For the production crashes specifically, Quest1 worked alongside ClickHouse Support to enable trace-level diagnostics and follow the memory pressure to its source. The root cause turned out to be the materialized view topology — large MV chains both deep (cascading transforms) and wide (many MVs from the same source), where the subqueries defining the views weren't filtering down before the join phase. Each refresh was effectively reading entire source tables and performing multiple joins against them. That access pattern is expensive in any database; in ClickHouse it's particularly punishing, because a single broad MV refresh can dominate cluster memory for the duration of the read.
A short-term filter-pushdown remediation.
With the root cause identified, the immediate stabilization move was straightforward: introduce filter criteria inside the subqueries that defined the materialized views, so each refresh reads only the rows it actually needs. This was the lever that took the worst memory spikes from production-breaking down to manageable, without requiring a full data-model redesign first.
A redesign roadmap for long-term scale.
The MV remediation was a tourniquet, not a cure. The longer-term recommendation set covers redesigning the data models to play to ClickHouse's strengths (sort key choice, MergeTree engine selection, MV topology rationalization, schema denormalization where appropriate), and restructuring the pipelines to feed those models efficiently. The roadmap is sequenced so the client can make the changes incrementally, in priority order, while the system keeps running.
A trace-and-diagnose capability the client now owns.
Through the engagement, Quest1 walked the client's development team through the trace-enabling and root-cause-diagnosis approach used during the engagement. The team replicated the technique successfully on their own queries and used it to surface and fix additional performance issues without external help. Knowledge transfer wasn't a side effect — it was a deliverable.

The stack: ClickHouse on GCP, Kafka for ingest, Airflow for orchestration. Engagement length: about four weeks.

$ read /case-study/03-the-impact.md

The Impact.

The immediate production picture changed inside the engagement window. After the filter-pushdown remediations landed, memory consumption on the most expensive queries dropped by a factor of 5–10×. Query runtimes on the same surfaces improved 2–3×. The crashes stopped being a regular event. None of this required a redesign — it came from making the existing materialized views read fewer rows.

The cost outcome followed from the memory outcome. With memory pressure under control, the client was able to halve their ClickHouse Cloud instance from 128 GB to 64 GB without compromising stability — a direct, measurable infrastructure cost reduction with no offsetting downside.

The structural outcome may be the most durable one. The client's development team now has the optimization report as a reference, the trace-and-diagnose technique as a working capability, and a sequenced roadmap for the longer redesign work. They went from being inside an opaque production problem they couldn't solve themselves to being able to investigate, isolate, and remediate ClickHouse performance issues on their own terms.

  • A documented ClickHouse optimization report tied to specific queries, models, and pipelines in the client's environment.
  • A stabilized production analytics pipeline, with worst-case memory spikes reduced to a fraction of their prior levels.
  • A halved ClickHouse Cloud footprint (128 GB → 64 GB) and the cost benefit that comes with it.
  • A long-term redesign roadmap for the data models, materialized view topology, and pipeline architecture, sequenced for incremental adoption.
  • An in-house trace-and-diagnose capability the client's development team can apply to future ClickHouse performance issues.
$ cat metrics.summary
on the most expensive queries
5–10× less memory
on the same query surfaces
2–3× faster queries
halved ClickHouse Cloud footprint
128 GB → 64 GB
$ ls case-studies/related/

Related case studies.

From Months to Days: A Self-Service Migration Pipeline Between Two SaaS Products

How a practice-management software provider serving the elective medical industry rebuilt customer migration between two of its products — replacing months of engineering-led work with a self-serve, AWS-native pipeline that the service team runs in days, with two-hour turnarounds in sight.

read case study →

From Hours to Minutes: An AWS-Native CI/CD Pipeline for a Multi-Sided TMS Platform

How G2Mint, a transport management SaaS connecting brokers, shippers, and carriers, replaced manual build-deploy cycles with end-to-end automated application and infrastructure pipelines on AWS — and cut deployment time from hours to minutes in the process.

read case study →