Skip to content
Tutorials Center
My learning

Topics

Lesson 35 of 35 ยท Projects and Practice

Practice Questions and Interview Prep

Test your ClickHouse knowledge with hands-on exercises, design scenarios and common interview questions on MergeTree, indexing, replication, rollups and operations.

You have covered the core of ClickHouse: how it stores data, how to query and aggregate it, how to make it fast, how to scale and operate it. This closing lesson is a workout. It has SQL exercises you can run, design scenarios to reason about and a list of questions that interviewers actually ask, each with a short model answer.

ClickHouse cannot run on this site, so exercises use the portable SQL stand-ins and name the ClickHouse function you would use in the real database. Return to the track page /clickhouse for the full list of lessons.

Part 1: SQL exercises

The sample table below is generated by a recursive query. Attempt each exercise before opening the hint.

SQL Exercise setup and warm-up

Press Run (or Ctrl + Enter) to see the result here.

Exercise 2. For each category, return the customer with the highest total spend. In ClickHouse you would use argMax(customer_id, total) over a subquery. Try it with a window function:

SQL Exercise 2: top customer per category

Press Run (or Ctrl + Enter) to see the result here.

Exercise 3. Compute a running total of daily revenue and the share of the month. Use sum(...) OVER (ORDER BY day), available in ClickHouse too.

SQL Exercise 3: running total and share

Press Run (or Ctrl + Enter) to see the result here.

Exercise 4 (no runner). Write the ClickHouse query for the 95th percentile of amount per category, then explain whether you would use quantile, quantileExact or quantileTDigest on a 10-billion-row table. (Answer: quantile(0.95)(amount) or quantileTDigest; exact only for small data.)

Part 2: design scenarios

Write your answer in a few sentences before reading the notes.

  1. Clickstream, 5 billion rows a month, most queries filter one site and a date range. Notes: MergeTree, ORDER BY (site_id, toDate(ts), ...), monthly partitions, LowCardinality columns, a daily rollup via materialized view into AggregatingMergeTree.
  2. A table of user profiles that changes constantly. Notes: ClickHouse is not a transactional store; use ReplacingMergeTree with a version column and query with argMax or FINAL, or keep profiles in Postgres and use a dictionary.
  3. Dashboard queries on a column not in the sorting key. Notes: first try a projection with another order or a skipping index; if it is a main access path, reconsider the key. See skipping indexes.
  4. Inserts failing with "Too many parts". Notes: batch inserts, enable async inserts, fix over-partitioning, check merge backlog and disk speed.
  5. Join between a 20-billion-row fact table and a 2-million-row dimension on a cluster. Notes: dictionary or GLOBAL JOIN with the small table on the right, co-locate if possible. See sharding.
  6. A team asks to delete one customer's rows for privacy. Notes: lightweight DELETE FROM or a mutation ALTER TABLE ... DELETE, understand that parts are rewritten; design partitions or a ReplacingMergeTree for frequent deletes.

Part 3: interview questions with model answers

Question Short answer
Why is ClickHouse fast for analytics? Column storage reads only needed columns, vectorized execution, compression, sparse index skipping, parallelism across cores and shards
What is a granule? The smallest block read from disk, 8192 rows by default; the primary index has one entry per granule
ORDER BY vs PRIMARY KEY? ORDER BY defines the physical sort and default primary key; PRIMARY KEY may be a prefix to save index memory
What does a MergeTree merge do? Combines small parts into bigger sorted ones; special engines also collapse rows (sum, aggregate, replace)
Is a ClickHouse primary key unique? No. It is a sort and index key without uniqueness
How do materialized views work? They are insert triggers: the SELECT runs on each inserted block and writes into a target table
Why not use UPDATE often? Mutations rewrite whole parts; prefer append and replace patterns
Difference between replication and sharding? Replication copies the same data for availability; sharding splits data for scale
What does Keeper do? Coordinates replicas: stores the replication log and metadata
Is replication a backup? No; mistakes replicate. Use BACKUP and test restores
When use a dictionary? Small, key-based lookups that would otherwise be joins
uniq vs uniqExact? uniq is approximate and cheap; uniqExact is exact and memory-hungry
Why can SELECT * be slow? It reads every column file
How to see if an index is used? EXPLAIN indexes = 1, compare selected and total granules
How to deduplicate? Insert dedup of identical blocks, ReplacingMergeTree, partition replacement, tokens
What is FINAL and its cost? Applies merge logic at read time; costly on big tables

Part 4: a self-check routine

Before a production launch, walk this list:

  • Sorting key chosen from the real top-5 queries, and verified with EXPLAIN indexes = 1.
  • Types minimized, LowCardinality where useful, codecs on time series.
  • Inserts batched, part counts monitored.
  • Rollups for hot dashboards, raw data retained by TTL.
  • Replication (two replicas) and a tested backup/restore.
  • Users with least privilege, quotas, settings profiles and no exposed default user.
  • Alerts for disk, parts, replication, memory and stuck mutations.

Check your understanding

Quick check ยท 5 questions

of 5 answered You scored / 5 ยท perfect!

  1. 1. A teammate says a ClickHouse primary key guarantees one row per key. What is the correct response?

    ClickHouse primary keys are not unique; deduplication needs engines like ReplacingMergeTree or modelling choices.
  2. 2. Which design best suits constantly changing user profiles?

    Mutations are heavy; versioned inserts or an external source with a dictionary fit better.
  3. 3. A query returns 12 rows but reads 3 billion. What do you check first?

    A high selected-to-total granule ratio means the filter is not pruning via the key or an index.
  4. 4. Which combination gives exact unique counts that merge across rollups?

    Distinct counts are not additive; store states and merge them (use the matching -State and -Merge pair).
  5. 5. What is the most important property of a good sharding key?

    Even spread avoids hot shards, and alignment allows local aggregation and joins.

Summary

  • Practice by writing the ClickHouse function name for each portable SQL exercise.
  • For every design question, start from the access pattern, then key, types, rollups and retention.
  • Know the short answers: granules, merges, materialized views as triggers, replication versus sharding, backups.
  • Always verify with EXPLAIN, system.query_log and system.parts, and test restores.

Next: Back to the ClickHouse track