Makes sense if you prefer scanning the whole picture first. Whole-to-parts vs. parts-to-whole is a different mindset. I personally like fade-in in some cases—it helps me focus on one layer at a time and build up the full context without getting overwhelmed. That said, efficiency can be an issue, so each viz is situational and easy to overuse :-)
All streaming processors face the same fundamental problem:
Streaming joins require maintaining state for both sides of the join
High-cardinality data (millions of unique keys) means huge state sizes
Traditional approach: Keep everything in memory will make memory exhausted
The high-cardinality join memory problem isn't unique to Timeplus. Apache Flink also uses hybrid hash joins that spill to disk (RocksDB) when memory fills, Materialize shares indexed state across multiple queries (but still requires keeping full datasets in memory), and RisingWave stores state in cloud object storage (S3/GCS) with LRU caching for hot data. What makes Timeplus different is its purpose-built optimization for the Pareto Principle, where a tiny fraction of data generates the vast majority of activity - keeping hot data in memory and cold data on disk for dramatic memory savings.
Is there a native SQL pipeline tool for ClickHouse that processes real-time data incrementally, with low latency, large throughput and high efficiency, similar to Snowflake’s Dynamic Tables?
Dynamic Tables are interesting for declarative streaming. In the ClickHouse ecosystem, you might want to look at materialized views combined with streaming engines.
For real-time transformations, there are a few approaches:
- Native ClickHouse MaterializedViews with AggregatingMergeTree
- Stream processors that write to ClickHouse (Flink, Spark Streaming)
- Streaming SQL engines that can read/write ClickHouse
We've been working on streaming SQL at Proton (github.com/timeplus-io/proton) which handles similar use cases - continuous queries that maintain state and can write results back to ClickHouse. The key difference from Dynamic Tables is handling unbounded streams vs micro-batches.
What's your specific use case? Happy to discuss the tradeoffs.
Consistently we heard about ClickHouse has very limited materialized views that can't handle real-time pipeline fast efficiently enough. would love to see more comments here.
Users frequently complain that materialized views significantly slow down insert performance, especially when having multiple MVs on a single table.
2. Streaming Data Patterns
This is critical for ClickHouse materialized views. Streaming data often arrives in frequent, small batches, but ClickHouse performs best when ingesting data in larger batches. The materialized views' transformation query runs synchronously within the INSERT transaction for every single batch, making the fixed overhead disproportionately large for small batches
3. Block-Level Processing Limitations
MVs in ClickHouse operate only on the data blocks being inserted at that moment. When performing aggregation, a single group from the original dataset may have multiple entries in the target table since grouping is applied only to the current insert block.
4. JOIN Limitations and Memory Issues
Materialized views with JOINs are problematic because MVs only trigger on the left-most table. It's also inefficient to update the view upon the right join table since it needs to recreate a hash table each time, or else keeping a large hash table and consuming a lot of memory.
5. Reprocessing historical data requires manual ALTER TABLE operations.
6. Each materialized view will create a new part from the block over which it runs - potentially causing the "Too Many Parts" issue
The tests didn't show much of a performance difference between SSE and WS across these scenarios, Both technologies perform similarly in most use cases.
WS has a lower client CPU utilization compared to SSE, meaning WebSocket can better leverage available CPU resources.
For OCaml users interested in data streaming processing (similar to Flink or Spark), but looking for a faster and more efficient option, check out this OCaml plugin Timeplus Proton. Concise, safe, highly performant and fun
-> Streaming Queries - Process large datasets with constant memory usage
-> Async Inserts - High-throughput data ingestion with automatic batching
-> Compression - LZ4 and ZSTD support for reduced network overhead
-> TLS Security - Secure connections with certificate validation
-> Connection Pooling - Efficient resource management for high-concurrency applications
-> Rich Data Types - Full support for #ClickHouse types including Arrays, Maps, Enums, DateTime64
-> Idiomatic OCaml - Functional API leveraging OCaml's strengths
