| name | clickhouse-core-workflow-a |
| description | Design ClickHouse schemas with MergeTree engines, ORDER BY keys, and
partitioning.
Use when creating new tables, choosing an engine, designing sort keys, or
modeling data for analytical workloads on ClickHouse or ClickHouse Cloud.
Trigger with "clickhouse schema design", "clickhouse table design",
"clickhouse ORDER BY", "clickhouse partitioning", "MergeTree table".
|
| allowed-tools | Read, Write, Edit, Bash(npm:*), Grep |
| version | 1.7.0 |
| license | MIT |
| author | Jeremy Longshore <jeremy@intentsolutions.io> |
| tags | ["saas","database","analytics","clickhouse","olap"] |
| compatibility | Designed for Claude Code |
ClickHouse Schema Design (Core Workflow A)
Overview
Design ClickHouse tables with correct engine selection, ORDER BY keys,
partitioning, and codec choices for analytical workloads. This skill covers the
four schema decisions that determine query speed and storage cost — engine,
sort key, partition expression, and column codecs — then points to
references/ for full DDL and the programmatic apply path.
Prerequisites
@clickhouse/client connected (see clickhouse-install-auth)- Understanding of your query patterns (what you filter and group on)
Instructions
Step 1: Choose the Right Engine
| Engine | Best For | Dedup? | Example |
|---|
MergeTree | General analytics, append-only logs | No | Clickstream, IoT |
ReplacingMergeTree | Mutable rows (upserts) | Yes (on merge) | User profiles, state |
SummingMergeTree | Pre-aggregated counters | Sums numerics | Page view counts |
AggregatingMergeTree | Materialized view targets | Merges states | Dashboards |
CollapsingMergeTree | Stateful row updates | Collapses +-1 | Shopping carts |
ClickHouse Cloud uses SharedMergeTree — it is a drop-in replacement for
MergeTree on Cloud. You do not need to change your DDL.
Step 2: Design the ORDER BY (Sort Key)
The ORDER BY clause is the single most important schema decision. It defines:
- Primary index — sparse index over sort-key granules (8192 rows default)
- Data layout on disk — rows sorted physically by these columns
- Query speed — queries filtering on ORDER BY prefix columns hit fewer granules
Rules of thumb:
- Put low-cardinality filter columns first (
event_type, status)
- Then high-cardinality columns you filter on (
user_id, )
