Do ClickHouse Materialized Views store data?

Yes. Unlike standard SQL views, ClickHouse Materialized Views act as insert triggers that process incoming data blocks and store the results permanently in a separate target table.

Can I backfill a Materialized View with existing data?

Materialized Views only process data inserted after their creation. To include historical data, you must manually perform an INSERT INTO target_table SELECT ... FROM source_table for the existing time range.

What happens when I chain multiple Materialized Views?

Downstream MVs see the pre-aggregated block produced by the upstream MV's SELECT query, not the fully merged state of the table. You must use -MergeState functions to handle these partial states correctly in a chain.

Why use AggregatingMergeTree instead of SummingMergeTree?

Use AggregatingMergeTree for non-additive metrics like unique counts (uniq) or quantiles. It uses -State and -Merge functions to ensure mathematical accuracy when merging partially aggregated data blocks.

ClickHouse Materialized Views

Name: ClickHouse Materialized Views
Author: obsessiondb

byobsessiondb

•

データベース管理

Implements high-performance ClickHouse Materialized Views for real-time data aggregation, ETL pipelines, and query optimization using proven architectural patterns.

This skill provides comprehensive guidance and 10+ implementation patterns for ClickHouse Materialized Views, helping developers build efficient real-time analytics systems. It covers critical aspects such as selecting the right engine (SummingMergeTree vs. AggregatingMergeTree), implementing complex aggregates with -State and -Merge functions, and managing data transformation pipelines. By following these best practices, users can avoid common pitfalls like incorrect aggregation logic in chained MVs or missing historical data, ensuring scalable and performant database architectures tailored for high-volume data ingestion.

主な機能

01Operational workflows for backfilling, pausing, and modifying existing views

02Real-time aggregation patterns using SummingMergeTree and AggregatingMergeTree

03Advanced state management for non-additive metrics like unique counts and quantiles

04Automated data transformation and JSON parsing pipelines for raw logs

05Best practices for multi-level MV chaining and pre-aggregated block processing

0612 GitHub stars

ユースケース

01Reducing query latency for large-scale analytical dashboards by pre-calculating metrics

02Building real-time ETL pipelines to transform raw unstructured data into structured tables

03Implementing incremental unique user counting and high-precision percentile tracking

What are Skills?·How to Install

Install with 🐟 Skill.Fish

npx skillfish add obsessiondb/clickhouse-skills clickhouse-materialized-views

For use in Claude.ai and ChatGPT

Download Skill