skills.rest
ivanshamaev
ivanshamaevCOMMUNITY
Explore all skills by this author →
WebsiteGitHub

clickhouse-olap

Design ClickHouse OLAP table engines and query patterns for analytics.

14|1|Updated May 5, 2026
Search Tags:#query optimization#clickhouse#materialized views#mergetree#olap modeling#data skipping indexes#ttl tiered storage
One-click install
npx skills add https://github.com/ivanshamaev/de-agent-skills --skill clickhouse-olap
Or copy as Structured Prompt for Agent▼
Please help me install this Agent Skill.
Skill: clickhouse-olap
Source: https://github.com/ivanshamaev/de-agent-skills/tree/main/skills/clickhouse_olap
Command: npx skills add https://github.com/ivanshamaev/de-agent-skills --skill clickhouse-olap

SYSTEM DOCUMENTATION & REQUIREMENTS

What problem does it solve?

This Skill helps you design ClickHouse OLAP schemas and queries that stay fast under real analytical workloads by choosing the right MergeTree engine, sorting/indexing strategy, and lifecycle settings.

Core Features & Use Cases

  • MergeTree engine selection for OLAP semantics: model append-only facts, upserts/deduplication (ReplacingMergeTree), pre-aggregation storage (AggregatingMergeTree), simple numeric rollups (SummingMergeTree), and sign-based mutability (CollapsingMergeTree).
  • Performance-oriented table design: implement effective ORDER BY / PARTITION BY / PRIMARY KEY rules, add data skipping indexes (minmax/set/bloom_filter), and use FINAL/argMax patterns appropriately.
  • Operationalizing ingestion and lifecycle: configure TTL for tiered storage, build real-time materialized views and aggregations, optimize scans using PREWHERE, and support batch inserts and common streaming patterns via Kafka engine and Python clients.

Quick Start

Tell me how to design a ClickHouse OLAP table for user event analytics that supports fast filtering by event type and user id, uses an appropriate MergeTree engine, includes data skipping indexes, and adds a materialized view for real-time minute-level aggregates.

Frequently Asked Questions about clickhouse-olap

High-intent search queries and answers about installing and using this skill.

FAQPage Schema
How do I design a ClickHouse OLAP table for high-performance analytics?▼

Design fast ClickHouse OLAP tables by selecting the right MergeTree engine, defining an effective ORDER BY and PARTITION BY strategy, and adding data skipping indexes to optimize analytical query performance.

When should I use ReplacingMergeTree versus CollapsingMergeTree in ClickHouse?▼

Use ReplacingMergeTree for upserts and deduplication, and CollapsingMergeTree for sign-based mutability. Choosing the correct MergeTree family engine ensures your ClickHouse OLAP schema matches your data mutation semantics.

How do I optimize ClickHouse queries using PREWHERE and data skipping indexes?▼

Optimize ClickHouse query scans by applying PREWHERE for early non-indexed filtering and adding minmax, set, or bloom_filter data skipping indexes based on your query predicates to reduce read volume.

What is the best way to handle real-time pre-aggregation in ClickHouse?▼

Handle real-time pre-aggregation in ClickHouse by building materialized views using AggregatingMergeTree and AggregateFunction types to automatically roll up streaming and batch workloads into query-ready summaries.

How do I configure TTL tiered storage in ClickHouse for lifecycle management?▼

Configure ClickHouse TTL tiered storage by defining TTL rules on your MergeTree tables to automatically move or delete aging data, ensuring efficient lifecycle management and storage cost control.

Does this ClickHouse OLAP modeling approach support Kafka engine ingestion?▼

Yes, this ClickHouse OLAP modeling approach supports streaming ingestion via the Kafka engine and Python clients, enabling real-time materialized views and continuous pre-aggregation for live workloads.

Related Skills

Explore More Data & Analytics Skills→
nazrulsoftwaredevnazrulsoftwaredev

clickhouse-io

Optimize ClickHouse schemas and analytical queries with MergeTree patterns.

Community
Advanced
archibatearchibate
6

clickhouse-io

Provide ClickHouse schema and query optimization patterns for analytical workloads.

Community
Intermediate
j7-devj7-dev
19

clickhouse-io

Optimize ClickHouse table design, queries, ingestion, and materialized views.

Community
Intermediate
kouisokouiso

clickhouse-io

Optimize ClickHouse databases with MergeTree engines and query techniques.

Community
Intermediate