Any Pipeline Tool for Clickhouse, Similar to Snowflake's Dynamic Tables

1. Insert Performance Degradation

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