clickhouse-core-workflow-a

name	clickhouse-core-workflow-a
description	Design ClickHouse schemas with MergeTree engines, ORDER BY keys, and partitioning. Use when creating new tables, choosing engines, designing sort keys, or modeling data for analytical workloads. Trigger: "clickhouse schema design", "clickhouse table design", "clickhouse ORDER BY", "clickhouse partitioning", "MergeTree table".
allowed-tools	Read, Write, Edit, Bash(npm:*), Grep
version	1.0.0
license	MIT
author	Jeremy Longshore <[e-mail]>
tags	["saas","database","analytics","clickhouse","olap"]
compatible-with	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.

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:

1. Put low-cardinality filter columns first (event_type, status)
2. Then high-cardinality columns you filter on (user_id, tenant_id)
3. End with a time column if you use range filters (created_at)
4. Do NOT put high-cardinality columns you never filter on in ORDER BY