ClickHouse Certified: ClickHouse Fundamentals and OLAP Concepts

This domain tests the mental model the rest of the exam rests on: how ClickHouse physically stores rows as columns, why its index only helps sorted data, how parts and merges behave, and which OLAP guarantees it does and does not give you. Expect questions that ask you to predict whether a query will be fast, or why a design falls over at scale.

Columnar storage and granules

• Every column lives in its own file inside a part, so a query reads only the columns it names - SELECT * on a 200-column table is an architectural error, not a style choice.
• Each column file is cut into granules of index_granularity rows (default 8192). The primary index stores one mark per granule: the sorting-key value of the granule's first row.
• The index is therefore sparse and small enough to stay in memory for billions of rows, but it can only prune contiguous ranges on a prefix of the sorting key. It is not a B-tree and cannot locate a single row by a non-key column.

Parts, merges, consistency

• Each INSERT writes one immutable part (a directory of column files plus index and mark files); a background merge collapses parts into larger ones.
• Merges are asynchronous. Engines that "deduplicate" or "sum" (ReplacingMergeTree, SummingMergeTree) only converge after merges, so results can be temporarily wrong unless you use FINAL or an aggregate.
• Rows inside one inserted block are written atomically; there is no cross-part ACID transaction. Atomic multi-table inserts exist only through the explicit transaction feature.

Execution model

• Execution is vectorized: column-at-a-time over blocks of up to 65,536 rows, with SIMD-friendly loops.
• Work is split across max_threads by granule ranges, so a single query consumes all cores - per-query latency and concurrency trade off directly.

Worked example

A 1-billion-row events table is defined ORDER BY (user_id, ts). The query WHERE ts > now() - INTERVAL 1 DAY reads every granule, because ts is the second key column and the sparse index cannot bound a range on it. Rewriting as WHERE user_id = 42 AND ts > ... lets ClickHouse binary-search the index to user 42 and read only that user's granules. Re-sorting by (ts, user_id) or adding a projection achieves the same for the time-range shape.

Traps that cost marks

• Treating ORDER BY / PRIMARY KEY as a uniqueness constraint - duplicates are allowed and common.
• Expecting SELECT without ORDER BY to return rows in sorting-key order; parts merge in the background, so order holds only with an explicit ORDER BY.
• Assuming ReplacingMergeTree removes duplicates at insert time.
• Believing a large part count is harmless.