I85PREP
⌘ K
ClickHouse Certified study materials
ClickHouse Certified study resources

ClickHouse Certified: Data Modeling and Table Engines

ClickHouse Certified · Work through your exam, one topic at a time.

All guide sections

Data Modeling and Table Engines

This domain covers the decisions that fix a table's performance for its lifetime: engine family, sorting key, partitioning, data types and codecs, plus the replication/distribution topology. Questions typically present a workload - deduplicating events, additive metrics, update streams - and ask which engine or key design fits.

MergeTree clause structure

ENGINE = MergeTree() PARTITION BY ... ORDER BY ... PRIMARY KEY ... SAMPLE BY ... TTL ... SETTINGS ...

  • ORDER BY is mandatory and defines the physical sort order; PRIMARY KEY is optional and must be a prefix of the sorting key.
  • PARTITION BY should be low cardinality (e.g. toYYYYMM(ts)). Each partition multiplies parts and open files; thousands of partitions cause "too many parts" and slow inserts.

Choosing an engine

  • MergeTree - append-only facts, no post-insert semantics.
  • ReplacingMergeTree([ver]) - keeps one row per sorting key after merge, latest by ver.
  • SummingMergeTree - sums numeric non-key columns; non-summed columns keep arbitrary values.
  • AggregatingMergeTree - stores aggregate-function states, read back with -Merge combinators.
  • CollapsingMergeTree(sign) and VersionedCollapsingMergeTree(sign, ver) - update/delete streams; the versioned variant tolerates out-of-order inserts.
  • Replicated* keeps identical data on each replica of a shard; a Distributed table shards a logical table across the cluster and fans out queries. Typical layout: local ReplicatedMergeTree plus a Distributed table on top.

Types and codecs

  • LowCardinality(String) for up to roughly 10k distinct values cuts storage and speeds filters.
  • Nullable(T) adds a separate null mask and disables some optimizations; prefer DEFAULT values.
  • Decimal for money, DateTime64(3) for milliseconds, sized integers instead of strings for IDs.
  • CODEC(ZSTD(3)) as a general choice; Delta/DoubleDelta for monotonic timestamps, Gorilla for gauges.

Worked example

Events may arrive twice and you must keep the latest per event_id. Model: ReplacingMergeTree(updated_at) ORDER BY event_id. Inserts stay cheap, but a query one second after insert may see both rows, so read with FINAL for single-key lookups or SELECT event_id, argMax(payload, updated_at) ... GROUP BY event_id for reports. Do not partition by event_id.

Traps that cost marks

  • Deduplication is per partition: partitioning by date breaks ReplacingMergeTree dedup across days.
  • SummingMergeTree never sums sorting-key columns.
  • High-cardinality PARTITION BY is the classic production incident.