ClickHouse Analytics skill for Claude Code

A ClickHouse table is designed well when the sorting key matches the filters of the dominant queries (so they read a small fraction of granules), partitions are coarse, inserts arrive in large batches, and expensive aggregations are precomputed by materialized views. ClickHouse is not Postgres: no row-level updates in hot paths, no uniqueness enforcement, and joins are something to design around.

Workflow

- [ ] 1. Write down the top queries: filters, group-bys, time ranges, freshness needs
- [ ] 2. Choose the engine
- [ ] 3. Choose ORDER BY (sorting key), PARTITION BY, and TTL
- [ ] 4. Choose column types and codecs
- [ ] 5. Design ingestion (batch size, async inserts, dedup)
- [ ] 6. Add materialized views for rollups
- [ ] 7. Verify granules read and parts health

2. Engine

Data	Engine
Append-only events/logs	MergeTree
Latest version of an entity (upserts by key)	ReplacingMergeTree(version) - dedup happens at merge time, not insert
Pre-aggregated rollups with arbitrary aggregates (uniq, quantiles)	AggregatingMergeTree fed by a materialized view
Rollups that are only sums/counts	SummingMergeTree
Cancel/replace rows by sign	CollapsingMergeTree / VersionedCollapsingMergeTree (rarely needed now)
Replicated self-managed cluster	Replicated*MergeTree variants (ClickHouse Cloud uses SharedMergeTree transparently)

3. Sorting key, partitioning, TTL

CREATE TABLE events
(
    tenant_id    UInt32,
    event_date   Date DEFAULT toDate(event_time),
    event_time   DateTime64(3, 'UTC'),
    event_type   LowCardinality(String),
    user_id      UInt64,
    session_id   UUID,
    country      LowCardinality(String),
    revenue      Decimal(18, 4) DEFAULT 0,
    props        String CODEC(ZSTD(3))
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_type, event_date, user_id)
TTL event_date + INTERVAL 13 MONTH DELETE;

ORDER BY rules:

• It defines the sparse primary index (one mark per granule, 8192 rows by default). A query can skip granules only on a prefix of the key.
• Put columns used in almost every filter first, generally from low to high cardinality: tenant_id, then categorical filters, then time, then a high-cardinality column if point lookups matter.
• Do not lead with a high-cardinality column (UUID, raw timestamp) unless queries filter on it by equality; it makes the remaining key columns useless for pruning.
• PRIMARY KEY can be a prefix of ORDER BY when you want a longer sort for compression but a smaller index.
• The sorting key is effectively fixed. Changing it means creating a new table and INSERT INTO new SELECT * FROM old (rehearse; it rewrites everything).

Partitioning rules:

• Partition for data lifecycle (dropping old months), not for query speed. Monthly (toYYYYMM) is the common default; daily only for very high volume with short retention.
• Keep total partitions in the low hundreds or fewer. High-cardinality partition keys (per tenant, per day for years) create too many parts and slow everything.
• Drop old data with ALTER TABLE events DROP PARTITION 202401 (instant) or TTL. Dropping partitions is destructive; confirm with the user.

4. Types and codecs

Data	Type
Strings with < ~10k distinct values (country, event type, status)	LowCardinality(String)
Timestamps	DateTime (seconds) or DateTime64(3) with explicit time zone
Money	Decimal(18, 4) or integer minor units
Flags	Bool / UInt8
Identifiers	UInt64 / UUID (not String)
Optional values	Avoid Nullable (extra column, slower); use defaults ('', 0) unless NULL semantics are needed
Semi-structured	JSON type (production-ready in recent releases, 25.3+), or String + JSONExtract*; promote hot keys to real columns

Codecs: Delta/DoubleDelta + ZSTD for monotonic timestamps and counters, Gorilla for float gauges, ZSTD(1-3) for large strings. Check effect in system.columns (data_compressed_bytes vs data_uncompressed_bytes).

5. Ingestion

• Each INSERT creates at least one part per partition touched. Many tiny inserts cause “Too many parts” errors and merge storms.
• Target batches of 10k-100k+ rows and at most about one insert per second per table.
• If clients cannot batch (many producers, small events), enable async inserts so the server buffers:

INSERT INTO events SETTINGS async_insert = 1, wait_for_async_insert = 1 VALUES (...);

wait_for_async_insert = 1 returns only after data is flushed; setting it to 0 risks silent loss on errors.

• Kafka/streams: use a buffering consumer (ClickPipes, Kafka table engine + MV, Vector, or the client library batching) rather than per-message inserts.
• Inserts into a partition are not idempotent by default across retries. With ReplicatedMergeTree/Cloud, identical retried blocks are deduplicated (insert_deduplicate), but only if the batch is byte-identical; build retries to resend the same batch.

Deduplication and updates

• ReplacingMergeTree(updated_at) keeps the latest row per sorting key after merges. Before merges, duplicates are visible. Query correctly with either:

SELECT * FROM users FINAL WHERE tenant_id = 42;          -- correct, costs more (improved in recent versions)
SELECT argMax(email, updated_at) AS email FROM users WHERE tenant_id = 42 GROUP BY user_id;

• Never rely on OPTIMIZE TABLE ... FINAL in application flow; it rewrites data and is for maintenance only.
• Deletes: lightweight DELETE FROM t WHERE ... marks rows (cheap-ish, cleaned at merge). ALTER TABLE t UPDATE/DELETE are mutations that rewrite parts; use rarely and in bulk. Both are destructive; confirm first.

6. Materialized views

Incremental MVs are insert triggers: they transform each inserted block and write to a target table. They do not see existing data (backfill separately) and do not react to deletes or updates in the source.

CREATE TABLE events_daily
(
    tenant_id  UInt32,
    event_date Date,
    event_type LowCardinality(String),
    events     AggregateFunction(count),
    users      AggregateFunction(uniq, UInt64),
    revenue    AggregateFunction(sum, Decimal(18, 4))
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (tenant_id, event_date, event_type);

CREATE MATERIALIZED VIEW events_daily_mv TO events_daily AS
SELECT tenant_id, event_date, event_type,
       countState()        AS events,
       uniqState(user_id)  AS users,
       sumState(revenue)   AS revenue
FROM events
GROUP BY tenant_id, event_date, event_type;

-- Query with -Merge combinators and GROUP BY (rows are partial aggregates)
SELECT event_date, countMerge(events) AS events, uniqMerge(users) AS users
FROM events_daily
WHERE tenant_id = 42 AND event_date >= today() - 30
GROUP BY event_date ORDER BY event_date;

• Always use TO <table> so the target is a normal table you control.
• Backfill: create the MV first, then INSERT INTO events_daily SELECT ...State() ... FROM events WHERE event_date < <mv start> to avoid gaps or double counting.
• Refreshable materialized views (REFRESH EVERY 1 HOUR) recompute a full query on a schedule; use them for joins and logic that incremental MVs cannot express.

7. Verify and diagnose

EXPLAIN indexes = 1
SELECT count() FROM events WHERE tenant_id = 42 AND event_date >= today() - 7;
-- Look at "Granules: X/Y" per index: X should be a small fraction of Y.

SELECT query_duration_ms, read_rows, read_bytes, memory_usage, query
FROM system.query_log
WHERE type = 'QueryFinish' AND event_time > now() - INTERVAL 1 HOUR
ORDER BY read_bytes DESC LIMIT 20;

SELECT table, partition, count() AS parts, sum(rows) AS rows
FROM system.parts
WHERE active AND database = currentDatabase()
GROUP BY table, partition
ORDER BY parts DESC LIMIT 20;      -- hundreds of active parts per partition = insert problem

Query guidance

• Filter on sorting-key prefixes; add a data-skipping index (minmax, set, bloom_filter) only for selective columns outside the key, and confirm with EXPLAIN indexes = 1 that it skips granules.
• Projections can provide an alternate sort order for a second access pattern at the cost of storage and insert work.
• Joins: the right-hand table is loaded into memory by default. Put the smaller table on the right, filter both sides first, or replace dimension joins with dictionaries (dictGet). Prefer denormalizing dimensions into the event table at ingest.
• Use approximate functions where acceptable: uniq instead of uniqExact, quantileTDigest instead of exact quantiles.
• SELECT * on wide tables reads every column file; select only what you need.
• Use LIMIT n BY key for top-N per group.

Gotchas

• No enforced uniqueness or foreign keys; the sorting key is not a unique key.
• FINAL and argMax patterns are needed with ReplacingMergeTree; dashboards that forget them double count.
• Materialized views fire on the inserted block only; a GROUP BY in the MV aggregates within that block, which is why the target must be Aggregating/Summing MergeTree and queries must re-aggregate.
• Nullable in the sorting key requires allow_nullable_key and hurts performance.
• Time zones: DateTime without a zone uses the server zone at display; set explicit 'UTC' on columns.
• Memory errors on GROUP BY/JOIN: set max_bytes_before_external_group_by / max_bytes_before_external_sort to spill, or reduce cardinality first.

Output: table design note

**Table:** events (MergeTree), ~2B rows/month, 13-month TTL
**Top queries:** per-tenant funnels by event_type over 30 days; per-user timelines.
**ORDER BY (tenant_id, event_type, event_date, user_id)** - tenant and type filters prune to <1% of granules.
**Partition:** monthly; ~13 active partitions.
**Ingestion:** Kafka -> batches of 50k rows every 2 s; async_insert off.
**Rollup:** events_daily (AggregatingMergeTree) via MV; dashboards read rollup, 40x fewer rows.
**Evidence:** EXPLAIN indexes=1 shows Granules 212/486,113 for the funnel query.