How Critical Manufacturing uses ClickHouse to bring real-time intelligence to the factory floor

Summary

Critical Manufacturing faced performance and scaling bottlenecks while processing millions of factory floor events using a legacy Microsoft SQL Server analytics stack. Increasing data volumes caused historical queries to take hours, straining real-time dashboards and escalating operational maintenance. The engineering team replaced this architecture by migrating analytics workloads to ClickHouse connected directly to streaming Kafka topics. They implemented an ELT pattern alongside ReplacingMergeTree engines to deduplicate late-arriving sensor records and utilized TTL policies for automated lifecycle management. The resulting platform delivers sub-second analytical queries across historical and real-time operational data, expands into OpenTelemetry observability storage, and maintains embeddings for retrieval-augmented generation workloads.

Context

Critical Manufacturing's legacy Microsoft SQL Server analytics infrastructure struggled to scale as factory floor events grew from millions to billions of records. Historical queries took hours, real-time dashboards lagged, and index maintenance created substantial operational overhead.

Approach / What changed

The team adopted ClickHouse with native Kafka ingestion, switching from ETL to ELT to run transformations at query time. They utilized ReplacingMergeTree engines for deduplicating bursty or late-arriving events, denormalized data by embedding relational context in JSON fields, and configured TTL-based data lifecycle rules.

Takeaways

• Transitioning from ETL to ELT allowed transformations to occur within ClickHouse at query time, preventing the need to re-run hours of aggregation pipelines when metric definitions changed.
• ReplacingMergeTree engines with custom ordering keys enabled automated deduplication and separated current entity states from historical audit logs during offline device reconnect bursts.
• Denormalizing relational datasets by embedding contextual metadata inside JSON fields bypassed complex multi-table joins and boosted columnar query execution.

Related reading