roadmap
- 01
- 02
- 03
- 04
- 05
- 06
- 07
- 08
Prepare for ClickHouse interview questions on how a columnar engine stores, indexes and queries data. Covers columnar storage, MergeTree engines, sorting keys, materialized views and query tuning.
79 of 79 questions in your plan: 0 known, 0 in progress, 0 due, 0 skipped, 79 new. 0 cut, outside your plan.
Expect ClickHouse where the job posting names it: data engineering loops, product teams that put analytics in front of their users, observability platforms that store logs in it. It is usually probed in a deep dive on a stack from your CV, or inside a design round about a dashboard that aggregates billions of events while the user waits.
The questions live in the gap between what was inserted and what the merges have produced so far. Why does a ReplacingMergeTree table still return two versions of a row an hour after the update? Why does a filter on a column outside the sort key read nearly every granule, while one on the leading column reads a handful? Why does a service that inserts one row per request end in a too-many-parts error? Why did a new materialized view miss every row already in its source, and why does it ignore a later delete?
Naming the rule is enough early on. From senior level up, interviewers want the design that follows from it: paying for FINAL at read time or taking argMax over a version column, a sort key derived from the filters the dashboard really runs, and a diagnosis of a GROUP BY that hits its memory limit.
The first module has no ClickHouse in it. Columnar storage is the general model behind analytical engines, and its ideas return under ClickHouse names: zone maps and pruning become the sparse primary index and its granules, immutable files and compaction become parts and background merges, and late materialization is what PREWHERE asks for explicitly. Encodings explain why the column order of a sort key changes the size on disk, and vectorized execution why a scan is affordable where a row store would want an index.
ClickHouse itself comes second, and it picks up on disk. The MergeTree family and the sort and partition keys come first because later answers lean on them; a partition key chosen too finely is a parts problem before it is a query problem. The SQL dialect follows, including the -State and -Merge combinators that materialized views use to store partial aggregates, then ingestion, where batch size decides whether merges keep pace. Query performance closes the module, since explaining a slow query draws on all of it at once: which granules the index skipped, how many parts were opened, what the right side of a join cost in memory.
The last module steps back out of the product. Rollups and their refresh pose the general question that insert-triggered views answer one particular way: when a stored aggregate beats a scan, and what becomes of it when rows arrive late or change. Approximate aggregation ends the plan on the trade a dashboard makes when an exact distinct count costs too much memory: uniq against uniqExact, sketches and sampling, and the error you accept with each.
Coming from PostgreSQL or MySQL, change study order from by frequency to by topics so the plan opens on the first module, and give that module real time: B-tree lookups and updates in place are the habits it replaces, and they produce many wrong ClickHouse answers. If the interview is days away instead, keep by frequency, drop should know and nice to know from importance, bring the top of the level range down to the seniority you are applying for, and spend those days on the Core Engine module before the other two. Engineers who will own the cluster rather than write to it should go through everything and give query performance the most time, because the system tables and the query log are where the diagnosis questions get settled. The plan ends at the table. Where the rows come from, the scheduler that runs backfills and the modeling of a warehouse fed by many sources are ground for the [Data Engineer roadmap](/roadmaps/data-engineer). If the feed is Kafka, partitions, consumer groups and offsets belong to the [Apache Kafka roadmap](/roadmaps/kafka).
Coming from PostgreSQL or MySQL, change study order from by frequency to by topics so the plan opens on the first module, and give that module real time: B-tree lookups and updates in place are the habits it replaces, and they produce many wrong ClickHouse answers. If the interview is days away instead, keep by frequency, drop should know and nice to know from importance, bring the top of the level range down to the seniority you are applying for, and spend those days on the Core Engine module before the other two. Engineers who will own the cluster rather than write to it should go through everything and give query performance the most time, because the system tables and the query log are where the diagnosis questions get settled.
The plan ends at the table. Where the rows come from, the scheduler that runs backfills and the modeling of a warehouse fed by many sources are ground for the Data Engineer roadmap. If the feed is Kafka, partitions, consumer groups and offsets belong to the Apache Kafka roadmap.