ClickHouse Certified common mistakes
ClickHouse Certified · Understand the traps and how to avoid them.
Treating ClickHouse like a row-store OLTP database and designing tables around third normal form with many small normalized tables joined at query time, instead of denormalizing into wide tables with pre-joined or pre-aggregated columns.
Candidates who come from PostgreSQL or MySQL habits assume joins are cheap and normalization is always correct. ClickHouse is a columnar OLAP engine where large distributed joins are expensive, and the exam tests whether you understand that wide, denormalized tables and pre-aggregation are the idiomatic pattern.
What to do instead
Before answering any modeling question, ask 'is this a scan-heavy analytical workload?' If yes, prefer a single wide table, use dictionaries or JOIN engines only for small dimension lookups, and use AggregatingMergeTree or materialized views to pre-aggregate. Practice rewriting a normalized schema into a denormalized ClickHouse schema and justify the tradeoff.
Choosing MergeTree ordering keys (ORDER BY) carelessly, for example ordering by a high-cardinality column like a UUID or by a column that is never used in WHERE clauses, and then expecting the primary index to speed up all queries.
The sparse primary index only helps when the query filters on a prefix of the ORDER BY key. Candidates memorize 'ORDER BY is the primary key' without internalizing that it defines the sort order and granularity skipping, so they pick keys by intuition rather than by query patterns.
What to do instead
Learn the rule: put low-to-medium cardinality columns that appear in WHERE filters first, in the order of most selective to least. Explain how the sparse index stores one mark per 8192 rows and how granule skipping works. For each practice table, list the top queries and derive the ORDER BY from their filter columns.
Confusing the roles of PARTITION BY and ORDER BY, using PARTITION BY on a high-cardinality column (such as a user ID or a timestamp with second precision), which creates thousands of parts and degrades merges and query planning.
Partitioning looks like an indexing tool to candidates from other databases. In ClickHouse, partitions are physical directories and each partition should hold a manageable number of parts; over-partitioning causes 'too many parts' errors and slow inserts.
What to do instead
Memorize the guidance: partition by month (toYYYYMM(event_date)) or by a low-cardinality category, keep partition count in the hundreds at most, and rely on ORDER BY for fine-grained pruning. Be able to explain the difference between partition pruning and primary index granule skipping, and when partitioning is not needed at all.
Writing queries that use SELECT * on wide tables, apply functions to the filtered column (for example WHERE toDate(ts) = today()), or join large tables without filtering, and then blaming ClickHouse for being slow.
Candidates carry SQL habits that are harmless in row stores. In a columnar engine, SELECT * reads every column, and wrapping a column in a function prevents index and partition pruning because the stored value no longer matches the predicate.
What to do instead
Always project only the needed columns. Rewrite function-wrapped predicates into range predicates on the raw column (ts >= today() AND ts < today() + 1). Filter and aggregate before joining, and prefer IN or JOIN with the smaller table on the right. Practice reading EXPLAIN and the query log to confirm that granules and partitions are being skipped.
Assuming ReplacingMergeTree or CollapsingMergeTree deduplicates rows immediately on insert, and then reporting wrong counts because duplicates are still visible until background merges run.
The engine names suggest immediate replacement, but deduplication is eventual and only guaranteed within a partition after merges. Candidates also forget that ReplacingMergeTree keeps the row with the highest version only after merge, and that FINAL forces merge-time semantics at a cost.
What to do instead
State clearly that these engines provide eventual deduplication. Use SELECT ... FINAL or GROUP BY with argMax when exact results are required, use ReplacingMergeTree(version) with a monotonic version column, or switch to a design that deduplicates at insert time. Know the cost of FINAL and when it is acceptable.
Ignoring asynchronous inserts, batching, and part-size guidance during ingestion design, sending one row per INSERT or many tiny inserts, and expecting high throughput.
Each INSERT creates a part, and too many small parts trigger merge pressure and the 'too many parts' error. Candidates who test ingestion with single-row inserts see poor performance and misdiagnose the engine rather than the insert pattern.
What to do instead
Batch inserts into blocks of roughly 10k to 100k rows, use the native protocol or clickhouse-client with stdin for bulk loads, use async_insert for streaming sources, and prefer Kafka or ClickHouse Cloud's built-in ingestion for continuous streams. Be able to explain why part count matters and how merges work.
Confusing materialized views with ordinary views: expecting a MATERIALIZED VIEW to reflect later changes to the source table, or expecting a normal VIEW to store data.
In ClickHouse a materialized view is an insert trigger that writes transformed rows into a target table at insert time; it does not re-read historical data and is not a cached query. Candidates from other systems assume refresh-on-read semantics.
What to do instead
Memorize: VIEW is a stored query with no data; MATERIALIZED VIEW transforms incoming inserts into a target table. To backfill, insert the historical SELECT into the source or use POPULATE only with the documented caveats. Use AggregatingMergeTree target tables with -State and -Merge combinators for incremental aggregation.
Misreading system tables and monitoring, for example querying system.parts or system.merges without filtering by active parts or database, or treating system.query_log as always populated.
System tables are large and contain inactive parts, replicated queue entries, and log rows controlled by settings such as log_queries. Candidates run naive queries against them, get confusing numbers, and draw wrong conclusions about table health.
What to do instead
Learn the key filters: system.parts WHERE active = 1, system.replicas for replication lag and errors, system.merges for current merges, system.query_log filtered by event_time and type. Know that query_log is flushed periodically and can be disabled. Practice a health-check query set you can recall under exam pressure.
Treating ClickHouse Cloud as identical to self-managed ClickHouse, for example assuming you can tune low-level server settings, run arbitrary system commands, or manage ZooKeeper/Keeper directly.
Cloud abstracts away infrastructure: compute and storage are separated, scaling is via replicas and compute size, and many server-level settings are managed. Candidates who only studied self-managed deployments miss the operational differences the exam tests.
What to do instead
Study the Cloud model: shared storage, compute separated from storage, automatic backups, vertical and horizontal scaling, and the settings exposed to users. Know which operations are self-service (users, roles, quotas, warehouses) versus platform-managed, and how private networking and IP access lists work.
Applying generic performance tuning advice (more memory, more threads, bigger max_memory_usage) before diagnosing the actual bottleneck, and ignoring settings like max_threads, max_block_size, and the effect of the query plan.
Candidates memorize setting names without understanding which layer each affects. Raising limits can mask a bad schema or a non-sargable predicate, and some settings interact (for example threads versus available cores and memory per thread).
What to do instead
Adopt a diagnostic order: read EXPLAIN and the query plan, check whether partitions and granules are pruned, verify column projection, then tune settings. Know the meaning of max_threads, max_memory_usage, max_block_size, and background_pool_size, and be able to explain the tradeoff of each. Use system.query_log and system.query_thread_log to find the real cost.