Remediating A Healthcare SaaS Data Warehouse Suffering From Materialised View Sprawl

Our client operates a SaaS platform serving independent medical practices in the United States, providing practice management, billing and clinical workflow tools to thousands of providers.

Their ClickHouse based data warehouse underpins reporting and analytics across the product. Over time, the team had leaned heavily on materialised views as the primary mechanism for shaping data for downstream consumers.

Whilst materialised views are a powerful feature of ClickHouse, the platform had reached a point where the volume, depth and interdependency of those views was actively causing significant performance, reliability and operational issues.

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

• A proliferation of materialised views, including views built on views, leading to fragile and hard to reason about dependency chains.
• Significant write amplification and background load caused by cascading materialised view inserts.
• Slow and unpredictable query performance on customer facing analytics surfaces.
• Difficulty introducing schema changes or fixing data quality issues without breaking downstream consumers.
• Growing infrastructure cost driven by the storage and compute overhead of the materialised view estate.

We took the following approach to this project:

• Audited the existing materialised view estate to map dependencies, identify hotspots and classify which views were genuinely load bearing.
• Redesigned the data modelling approach to favour simpler aggregation patterns, refreshable materialised views and query time calculation where appropriate.
• Reworked the most problematic chains, replacing cascading materialised views with cleaner ingestion and transformation patterns.
• Tuned ingestion, merging and part management settings to reduce background load and stabilise the cluster under peak conditions.
• Documented updated guidelines for the engineering team on when and how to use materialised views safely.