Modernizing a Healthcare Data Pipeline: Why We Chose ClickHouse Over Cassandra for Real-Time Analytics
Lessons from designing a modern streaming architecture for real-time healthcare inventory visibility.
Background
A healthcare organization operating across Indonesia needed real-time visibility into medicine and medical supply inventory across hospitals, pharmacies, and distribution centers.
The existing reporting platform relied on traditional database reporting, resulting in delayed inventory insights and slower decision-making during high-demand periods.
The primary objectives were to:
- Provide near real-time inventory visibility
- Enable fast analytical dashboards
- Support growing transaction volumes
- Scale without impacting operational systems
Solution Architecture
Data Flow
Why This Architecture?
PostgreSQL serves as the operational database, storing inventory transactions generated by hospitals, pharmacies, and warehouse systems.
Apache Kafka acts as the event streaming platform, capturing every inventory change in real time, including:
- Stock received
- Stock issued
- Transfers
- Returns
- Expiry updates
- Inventory adjustments
Kafka ensures reliable, scalable event distribution without impacting operational applications.
Apache Flink — rather than loading raw events directly into the analytics database, Flink processes the data stream in real time by:
- Cleaning invalid records
- Removing duplicate events
- Applying business rules
- Enriching inventory information
- Performing real-time aggregations
- Generating analytics-ready datasets
This significantly improves data quality before storage.
ClickHouse was selected as the analytical database because the workload was dominated by large-scale analytical queries rather than transactional updates. Its columnar architecture provides:
These characteristics make it particularly well suited for operational analytics and reporting.
Web Reporting — business users access interactive dashboards to monitor current inventory levels, low-stock alerts, medicine consumption trends, warehouse utilization, regional inventory availability, and product expiry analysis. Because ClickHouse is optimized for analytical workloads, dashboards respond within seconds, even when processing millions of records.
Why Not Cassandra?
Cassandra excels in workloads requiring massive write throughput, global distribution, high availability, and operational transaction storage.
However, this project’s primary requirement was real-time analytical reporting, where users frequently execute aggregations, filtering, and trend analysis across large datasets. For these workloads, ClickHouse provides significantly better query performance due to its column-oriented storage and analytical query engine.
This does not make ClickHouse a replacement for Cassandra — it simply makes it a better fit for this specific use case.
Business Benefits
KEY TAKEAWAYS
- Select databases based on workload, not popularity.
- Use Kafka and Flink to build a robust real-time data pipeline.
- Separate operational databases from analytical databases.
- ClickHouse is an excellent choice when the primary goal is real-time analytics over large streaming datasets.
Final Thoughts
There is no universal “best” database. The right technology depends on the business problem you’re solving.
For organizations focused on real-time analytics, streaming data, and interactive dashboards, a modern architecture built on PostgreSQL → Kafka → Apache Flink → ClickHouse → Web Reporting provides a scalable, high-performance foundation for data-driven decision-making.