Optimize OLAP Database

Use this skill when designing, tuning, or troubleshooting high-throughput columnar databases (ClickHouse, DuckDB, Apache Iceberg v3). For ad-hoc exploratory querying and cross-source joins, use query-analytical-engine instead.

When to Use

• designing columnar database schemas and primary sorting keys for ClickHouse, DuckDB, or Apache Iceberg v3
• configuring sparse primary index granules (8192-row alignment) to minimize index RAM footprint
• governing partition key cardinality (PARTITION BY toYYYYMM) to prevent the "Too many parts" error
• adding secondary data skipping indexes (minmax, set, bloom filter, tokenbf_v1) for non-prefix filter columns
• accelerating dimension lookups via LowCardinality dictionary encoding and in-memory external dictionaries
• designing streaming micro-batch ingestion pipelines (Buffer engine, Kafka MV, 256MB Parquet blocks)
• profiling query performance bottlenecks using EXPLAIN PIPELINE, EXPLAIN ESTIMATE, and EXPLAIN PLAN
• configuring automated TTL lifecycle policies for data expiration and tiered storage migration to S3
• deploying specialized MergeTree engines (ReplacingMergeTree, SummingMergeTree, AggregatingMergeTree)
• implementing native table encryption (KMS) for Iceberg and Delta Lake tables
• preparing for Spark 4.2+ and Flink 2.3 compatibility

Core Rules