clickhouse-materialized-views_skill

Overview

This skill provides 10+ proven patterns for ClickHouse Materialized Views (MVs) to build real-time aggregation, transformation, and query-optimization pipelines. It highlights which MergeTree engines to use—SummingMergeTree, AggregatingMergeTree, ReplacingMergeTree—and warns about common pitfalls like missing backfills and wrong ORDER BY choices. Practical examples and troubleshooting guidance help you design safe, performant MVs.

How this skill works

Materialized Views run the MV SELECT on each INSERT batch into the source table and write results into a target table; they do not read historical data. Use -State functions in MVs to store intermediate aggregate states and -Merge functions at query time to complete aggregation for AggregatingMergeTree. Choose SummingMergeTree for additive counters and AggregatingMergeTree for non-additive aggregates like uniq or quantiles.

When to use it

• Create real-time counters and pre-aggregations to speed heavy queries
• Parse or enrich incoming raw data for downstream consumers
• Maintain latest-per-key state (last seen, last action) with ReplacingMergeTree
• Build multi-level aggregation (hourly → daily) to reduce query cost
• When queries are slow and data can be incrementally aggregated at insert time

Best practices

• Treat MVs as INSERT-triggered processing, not caches of query results
• Pick the correct engine: SummingMergeTree for sums/counts, AggregatingMergeTree for uniq/quantiles, ReplacingMergeTree for last-value
• Match target ORDER BY to the MV GROUP BY for proper merges and space efficiency
• Backfill historical data manually when adding an MV; MVs do not process existing rows
• Avoid many MVs on a single source table to prevent insert latency; consider async or consolidated pipelines

Example use cases

• Daily event counters using SummingMergeTree for fast dashboard metrics
• Accurate unique user and p95 latency using AggregatingMergeTree with -State/-Merge
• Parse JSON logs into structured tables on insert for simpler downstream queries
• Track latest user state with ReplacingMergeTree and argMax patterns
• Multi-level aggregation: hourly AggregatingMergeTree feeding daily AggregatingMergeTree for rollups