I85PREP
⌘ K
ClickHouse Certified study materials
ClickHouse Certified study resources

ClickHouse Certified practice questions

One question at a time. Build your ClickHouse Certified understanding.

First 50 of 200 questions. Each one lists the choices, the correct answer, and an explanation. The checker at the top of this page saves progress in this browser when it is available.

Continue in the practice tool above to load more questions, 50 at a time.

Question 1ClickHouse fundamentals and OLAP concepts

In ClickHouse, which MergeTree-family engine stores data sorted by the primary key and enforces no uniqueness constraint on that key?

  1. ReplacingMergeTree
  2. SummingMergeTree
  3. MergeTree Correct answer.
  4. CollapsingMergeTree

Explanation

Plain MergeTree sorts rows on disk by the ORDER BY key but permits duplicate key values; deduplication is not part of its contract. The other engines are variants that add background merge-time behavior such as replacing, collapsing, or summing rows.

Question 2ClickHouse fundamentals and OLAP concepts

What is the primary purpose of the ORDER BY clause when defining a MergeTree table in ClickHouse?

  1. It sets the physical insertion order of incoming rows
  2. It defines the sort key stored in the sparse primary index Correct answer.
  3. It controls which columns are compressed on disk
  4. It enforces a unique constraint across all rows

Explanation

ORDER BY specifies the sorting key of each part, and ClickHouse builds a sparse primary index over that key to skip granules during reads. It does not enforce uniqueness and does not determine compression choices.

Question 3ClickHouse fundamentals and OLAP concepts

A ClickHouse table uses MergeTree with PARTITION BY toYYYYMM(event_date). What is the main benefit of this partitioning for typical analytical queries?

  1. It removes the need for a primary key or sorting key
  2. It converts the table into an in-memory dictionary for faster lookups
  3. It lets the query engine skip entire partitions whose date range cannot match the filter Correct answer.
  4. It guarantees that rows within each partition are unique

Explanation

Partition pruning lets ClickHouse read only the partitions whose key range overlaps the query predicate, sharply reducing scanned data. Partitions are independent directories of parts and are pruned by min/max metadata rather than by uniqueness.

Question 4ClickHouse fundamentals and OLAP concepts

Which ClickHouse data type is the most appropriate for storing a fixed-length numeric identifier that never exceeds 4,294,967,295 and is never negative?

  1. Int64
  2. UInt32 Correct answer.
  3. Float64
  4. String

Explanation

UInt32 is an unsigned 32-bit integer covering 0 to 4,294,967,295, exactly the stated range, using four bytes per value. Wider or floating types waste space or introduce precision concerns for integer identifiers.

Question 5ClickHouse fundamentals and OLAP concepts

In ClickHouse, what does a materialized view defined with TO a target table and a SELECT over a source table actually do?

  1. It re-runs the SELECT on every query that references the view name
  2. It creates a logical alias that resolves to the source table
  3. It stores the query result at insert time into the target table Correct answer.
  4. It caches results only for the first query and then expires

Explanation

A materialized view acts as an insert trigger: when rows land in the source table, the view's SELECT runs on that block and writes results to the target table. Reads of the view name then query the stored target data, not a live recomputation.

Question 6ClickHouse fundamentals and OLAP concepts

Which statement best describes ClickHouse's column-oriented storage model?

  1. Values of each column are stored together, so a query reads only referenced columns Correct answer.
  2. All columns are compressed into a single blob per partition
  3. Each row is stored contiguously so single-row lookups read one block
  4. Rows are stored in a B-tree keyed by the primary key

Explanation

Columnar storage keeps values of the same column adjacent, so a scan touching a few columns avoids reading unrelated ones. This underlies the compression and vectorized execution advantages for analytical workloads.

Question 7ClickHouse fundamentals and OLAP concepts

What is a granule in ClickHouse MergeTree storage?

  1. A single compressed block holding one column of one row
  2. A partition directory containing all parts of a month
  3. A group of consecutive rows that is the unit the sparse primary index points to Correct answer.
  4. A background merge job scheduled by the server

Explanation

A granule is a fixed number of consecutive rows (set by index_granularity) and is the smallest unit the primary index addresses. The index stores one mark per granule, allowing the engine to skip granules that cannot match.

Question 8ClickHouse fundamentals and OLAP concepts

Which query pattern is best served by ClickHouse's design?

  1. Large scans that aggregate a few columns over billions of rows Correct answer.
  2. Frequent single-row updates by primary key
  3. Long multi-statement transactions with row-level locking
  4. Heavy foreign-key join enforcement on every insert

Explanation

ClickHouse is built for high-throughput analytical scans and aggregations over wide tables, using columnar reads and vectorized execution. It favors batch inserts and avoids per-row update and transactional locking patterns.

Question 9ClickHouse fundamentals and OLAP concepts

A MergeTree table is defined with ORDER BY (user_id, event_time). A query filters WHERE user_id = 42 AND event_time >= '2024-01-01'. Why can ClickHouse read far fewer granules than a full scan?

  1. Because the query planner rewrites the filter into a partition drop
  2. Because user_id is a unique key and returns one row
  3. Because the primary index stores per-granule min/max of event_time within each user_id prefix Correct answer.
  4. Because event_time is automatically a partition key

Explanation

The sorting key is a prefix (user_id, event_time), so the sparse index can locate the user_id=42 range and then use the event_time bounds recorded per granule to skip older data. This prefix-aware pruning is what reduces granules read.

Question 10ClickHouse fundamentals and OLAP concepts

A table has PARTITION BY toYYYYMM(ts) and rows spanning three years. A query filters ts BETWEEN '2024-03-01' and '2024-03-15'. What does ClickHouse do first to limit data read?

  1. It prunes partitions whose month range cannot overlap March 2024 Correct answer.
  2. It builds a temporary hash index on ts for the query
  3. It merges all partitions into one before scanning
  4. It converts the range filter into an exact equality lookup

Explanation

Partition pruning uses partition key ranges to discard whole partitions that cannot contain matching rows, so only the 2024-03 partition is considered. Within that partition, granule-level index pruning then narrows the scan further.

Question 11ClickHouse fundamentals and OLAP concepts

Why does ClickHouse generally discourage using very high-cardinality expressions such as a raw UUID column as the partition key?

  1. Because too many small partitions increase metadata and merge overhead Correct answer.
  2. Because UUID columns cannot be sorted within partitions
  3. Because partitioning disables compression for that column
  4. Because partition keys cannot contain UUID values

Explanation

Each distinct partition key value creates a separate partition directory and its own parts, so high cardinality yields many tiny partitions and excessive file and merge overhead. Partition keys should have modest cardinality, such as months or days.

Question 12ClickHouse fundamentals and OLAP concepts

A ReplacingMergeTree table is declared with ORDER BY id and no version column. Duplicate rows with the same id exist across parts. What should a query do to reliably return only one row per id?

  1. Add a WHERE clause on the sign column
  2. Rely on the engine to have already removed duplicates before any query runs
  3. Convert the table to a Distributed engine
  4. Use FINAL or aggregate with GROUP BY to deduplicate at query time Correct answer.

Explanation

ReplacingMergeTree removes duplicates only during background merges, which are not guaranteed to have happened, so duplicates may still be visible. FINAL or an explicit aggregation enforces deduplication at read time.

Question 13ClickHouse fundamentals and OLAP concepts

An AggregatingMergeTree table stores AggregateFunction columns such as sumState(value). How must a query read those columns to get correct totals?

  1. Use toString then parse the number
  2. Use sum(value) on the raw state column
  3. Use the matching -Merge combinator, for example sumMerge(value) Correct answer.
  4. Use any() to pick one state per group

Explanation

AggregateFunction columns hold serialized intermediate states, so they must be finalized with the matching Merge combinator, such as sumMerge, to produce the final aggregate. Reading them as plain values yields meaningless bytes.

Question 14ClickHouse fundamentals and OLAP concepts

A materialized view targets a SummingMergeTree table and aggregates by (day, site). What happens to rows inserted with the same (day, site) into different parts?

  1. They are summed immediately at insert into a single row
  2. They are rejected as duplicate primary keys
  3. They may remain separate until a background merge combines them Correct answer.
  4. They overwrite each other, keeping only the newest

Explanation

SummingMergeTree combines rows sharing the sorting key only during merges, so separate parts can temporarily hold partial sums. Queries should use SUM with GROUP BY to get correct totals before merges occur.

Question 15ClickHouse fundamentals and OLAP concepts

Which scenario is the strongest reason to prefer a ClickHouse dictionary over a JOIN against a small lookup table?

  1. The lookup table is updated with high-frequency single-row writes
  2. The lookup requires multi-statement transactional consistency
  3. The lookup data is small, changes rarely, and is needed for per-row enrichment in large scans Correct answer.
  4. The join must enforce referential integrity on inserts

Explanation

Dictionaries load compact key-value data into memory and allow fast per-row lookups during large scans without a join. They suit slowly changing reference data, not high-frequency single-row updates or transactional constraints.

Question 16ClickHouse fundamentals and OLAP concepts

A query filters on a non-primary-key column that appears late in the sorting key, such as the third column of ORDER BY (a, b, c). Why might the filter read many more granules than expected?

  1. Because the filter column is automatically converted to String
  2. Because ClickHouse ignores WHERE clauses on non-first columns
  3. Because the sparse index can only prune effectively on a leading prefix of the sorting key Correct answer.
  4. Because non-key columns are not compressed and must be fully decompressed

Explanation

The primary index is ordered by the full sorting key, so pruning works best when the predicate constrains a leading prefix such as a or (a, b). Filtering only on a later column like c cannot narrow the index range and may scan most granules.

Question 17ClickHouse fundamentals and OLAP concepts

What is the effect of setting a low index_granularity value, such as 256, on a MergeTree table?

  1. Smaller granules, a larger primary index, and finer pruning Correct answer.
  2. Automatic conversion of the sorting key to a partition key
  3. Reduced compression because granules are compressed separately
  4. Larger index marks and fewer granules per part

Explanation

index_granularity sets rows per granule, so a smaller value yields finer-grained marks and more index entries, improving pruning precision at the cost of a larger index. It does not alter partitioning or the sorting key definition.

Question 18ClickHouse fundamentals and OLAP concepts

A team inserts one row per HTTP request into a MergeTree table using many small INSERT statements. Why is this inefficient compared with batching?

  1. Single-row inserts are rejected unless a version column exists
  2. Each insert forces a full table re-sort and rewrite
  3. Each insert creates a new part, and many small parts increase merge pressure and file overhead Correct answer.
  4. Small inserts bypass the sorting key and store rows unsorted

Explanation

Every INSERT produces at least one new part, and numerous tiny parts burden the background merge process and metadata. Batching rows into larger inserts reduces part count and improves throughput.

Question 19ClickHouse fundamentals and OLAP concepts

A table uses ORDER BY (tenant_id, ts) and a query filters only WHERE ts >= now() - INTERVAL 1 DAY across all tenants. What is the likely performance characteristic?

  1. Automatic rewrite to a partition-pruned scan on ts
  2. Immediate rejection because ts is not indexed
  3. Poor pruning because ts is not a leading prefix of the sorting key Correct answer.
  4. Excellent pruning because ts is part of the sorting key

Explanation

Since tenant_id leads the sorting key, a predicate on ts alone cannot use the index range effectively and may scan most granules. Reordering the key or partitioning by time would help such queries.

Question 20ClickHouse fundamentals and OLAP concepts

Which ClickHouse feature most directly supports approximate distinct counting over billions of rows with bounded memory?

  1. count(DISTINCT) with a full sort
  2. groupArray
  3. uniq (HyperLogLog-based) Correct answer.
  4. uniqExact

Explanation

The uniq function uses an adaptive HyperLogLog-style sketch to estimate distinct counts with bounded memory and small error. uniqExact is precise but requires memory proportional to the number of distinct values.

Question 21ClickHouse fundamentals and OLAP concepts

A materialized view with TO target is created over a source table, but rows inserted into the source before the view existed are missing from the target. What explains this?

  1. Materialized views only process blocks inserted after they are created Correct answer.
  2. The target table rejected the historical rows
  3. The view silently dropped rows due to a type mismatch
  4. Materialized views backfill history automatically on creation

Explanation

A materialized view is an insert trigger and does not backfill existing data; only inserts that occur after creation are transformed and written to the target. Historical rows must be loaded separately, for example with an INSERT SELECT.

Question 22ClickHouse fundamentals and OLAP concepts

A query on a SummingMergeTree table returns two rows for the same (day, site) key. Which explanation is most consistent with SummingMergeTree semantics?

  1. The ORDER BY clause was omitted from the table definition
  2. SummingMergeTree never combines rows with identical keys
  3. The table has a corrupted primary index
  4. The rows live in separate parts that have not yet been merged Correct answer.

Explanation

SummingMergeTree collapses rows with equal sorting keys only during background merges, so unmerged parts can expose duplicate keys. Aggregating with SUM and GROUP BY in the query guarantees a single combined row.

Question 23ClickHouse fundamentals and OLAP concepts

A table is created with PARTITION BY toYYYYMM(ts) and ORDER BY id. A query filters WHERE id = 1000 with no time predicate. What limits partition pruning here?

  1. ClickHouse prunes partitions only for equality on String columns
  2. The partition key is ts, so an id-only filter cannot exclude partitions by key range Correct answer.
  3. The sorting key id disables partition pruning entirely
  4. Partition pruning requires an ORDER BY on the partition key

Explanation

Pruning relies on partition key ranges, and with partitioning by month of ts, an id-only predicate gives no month bounds to prune on. The query may therefore examine parts in every partition, though the id index still helps within each part.

Question 24ClickHouse fundamentals and OLAP concepts

A team wants per-row enrichment of a large fact table with a slowly changing lookup of about 50,000 rows, refreshed hourly, and wants to avoid a join in every query. Which approach best fits ClickHouse capabilities?

  1. Use a Distributed table to broadcast the lookup to all nodes
  2. Define a dictionary over the lookup and call dictGet in the query Correct answer.
  3. Add the lookup columns to the sorting key and rely on deduplication
  4. Create a ReplacingMergeTree and rely on FINAL for enrichment

Explanation

A dictionary loads the small, slowly changing lookup into memory and enables fast per-row enrichment via dictGet without a join. Hourly refresh suits dictionaries, which support periodic reloading.

Question 25ClickHouse fundamentals and OLAP concepts

A MergeTree table stores event_time as DateTime and is partitioned by day. A dashboard queries a single day repeatedly with different filters. Which design choice most improves those repeated scans?

  1. Remove partitioning so all days share one part
  2. Replace MergeTree with a Memory engine for durability
  3. Convert event_time to String to reduce storage
  4. Order the sorting key so the frequently filtered dimension leads within the day Correct answer.

Explanation

Within the already-pruned day partition, a sorting key led by the commonly filtered dimension lets the sparse index skip granules for each filter. This compounds partition pruning with granule-level pruning for repeated queries.

Question 26ClickHouse fundamentals and OLAP concepts

In ClickHouse, which engine family stores data in immutable parts that are periodically merged in the background while supporting primary-key indexing and partitioning?

  1. Kafka engine
  2. Memory engine
  3. MergeTree family Correct answer.
  4. Distributed engine

Explanation

The MergeTree family is the core storage engine family in ClickHouse. Data is written into immutable parts, and a background process merges parts to keep the number of files and read amplification bounded. MergeTree also provides the sparse primary-key index and partition pruning that most analytical workloads rely on.

Question 27ClickHouse fundamentals and OLAP concepts

A ClickHouse table is created with ORDER BY (tenant_id, event_time). Which statement correctly describes what the sorting key determines?

  1. The partition boundaries used to split data on disk
  2. The physical order of rows inside each part and the sparse primary index used for skipping granules Correct answer.
  3. The uniqueness constraint enforced on every inserted row
  4. The set of columns that are compressed with a dedicated codec

Explanation

The ORDER BY clause of a MergeTree table defines the sorting key. Rows are stored sorted by that key within each part, and the same key drives the sparse primary index that lets the reader skip granules. It does not enforce uniqueness, and partitioning is controlled by a separate PARTITION BY clause.

Question 28ClickHouse fundamentals and OLAP concepts

Which ClickHouse data type is designed to store a value that is the result of an aggregate function, such as a partially aggregated state, so it can be merged later?

  1. LowCardinality(String)
  2. Nullable(String)
  3. AggregateFunction Correct answer.
  4. Decimal(18,4)

Explanation

AggregateFunction is a parametric type that stores the intermediate state of an aggregate, for example sumState or uniqState output. These states can be combined with the matching -Merge combinator, which is what makes AggregatingMergeTree and incremental materialized views work. Ordinary scalar types cannot hold such mergeable states.

Question 29ClickHouse fundamentals and OLAP concepts

What is the primary purpose of a projection in ClickHouse?

  1. To replicate data to another shard automatically
  2. To store an alternative sorted representation of the data that the optimizer can substitute for the base table scan Correct answer.
  3. To enforce referential integrity between two tables
  4. To define a scheduled cron job that refreshes a table

Explanation

A projection stores rows in a different order or pre-aggregated form, and the query planner can read it instead of the base table when it estimates a lower cost. This gives a way to accelerate queries whose filters do not match the table's primary sorting key. Projections are not constraints, replication mechanisms, or schedulers.

Question 30ClickHouse fundamentals and OLAP concepts

A MergeTree table has ORDER BY (country, city) and contains 500 million rows. A query filters WHERE city = 'Osaka' without constraining country. What is the expected read behavior?

  1. The query will fail because city is not the first column of the sorting key
  2. The engine will use city as an implicit secondary index and read only matching granules
  3. The sparse index cannot prune granules effectively because city is not the leading key column, so most granules may be read Correct answer.
  4. The engine will automatically rewrite the table so that city becomes the leading key

Explanation

The sparse primary index can only skip granules using a prefix of the sorting key. Because country is the leading column, a filter on city alone provides no usable prefix, so the scan may touch most of the table. To fix this, teams reorder the key, add a projection, or add a skip index.

Question 31ClickHouse fundamentals and OLAP concepts

A developer wants to keep only the latest row per order_id in a MergeTree table, using a version column updated_at. Which engine configuration achieves this after background merges?

  1. MergeTree with ORDER BY order_id and a TTL on updated_at
  2. SummingMergeTree with ORDER BY order_id
  3. ReplacingMergeTree(updated_at) with ORDER BY order_id Correct answer.
  4. AggregatingMergeTree with ORDER BY order_id

Explanation

ReplacingMergeTree with a version column keeps the row with the highest version value when rows sharing the sorting key are merged. Deduplication is eventual, so queries that need exact results should still use FINAL or aggregate explicitly. SummingMergeTree adds numeric columns, which is not the desired behavior here.

Question 32ClickHouse fundamentals and OLAP concepts

Which statement about the difference between a ClickHouse dictionary and a regular JOIN with a small lookup table is accurate?

  1. A dictionary replaces the need for a primary key on the fact table
  2. A dictionary stores its data only on disk and must scan the file for every lookup
  3. A dictionary loads the lookup into memory in a specialized structure and can answer key lookups without building a hash table per query Correct answer.
  4. A dictionary can only be used with the Memory table engine on the right side of a JOIN

Explanation

Dictionaries are preloaded into memory structures optimized for key-to-attribute lookups, so each query avoids rebuilding a hash table for the right side of a join. This makes them attractive for frequent enrichment against small or medium reference data. They do not change the fact table's key requirements.

Question 33ClickHouse fundamentals and OLAP concepts

A team runs thousands of single-row INSERT statements per minute into a MergeTree table. Why is this pattern problematic?

  1. Each INSERT forces a full recompression of every column in the table
  2. Each INSERT rewrites all existing partitions from scratch
  3. Each INSERT locks the whole table until the next merge completes
  4. Each INSERT creates a new part, and too many small parts trigger excessive background merges and 'too many parts' errors Correct answer.

Explanation

Every INSERT into a MergeTree table produces at least one new part on disk. Many tiny inserts create many small parts, and the background merge process cannot keep up, eventually raising a 'too many parts' error. The recommended fix is to batch rows client-side or use an asynchronous buffering mechanism.

Question 34ClickHouse fundamentals and OLAP concepts

A query uses APPROX_COUNT_DISTINCT or uniq() instead of uniqExact() on a column with billions of distinct values. What is the main trade-off?

  1. Guaranteed identical results to uniqExact with no approximation
  2. Exact results with no memory savings but faster I/O
  3. Bounded memory and much faster execution in exchange for a small, statistically bounded estimation error Correct answer.
  4. Loss of the ability to filter the query by any other column

Explanation

uniq and its variants use adaptive sampling and sketches that keep memory bounded regardless of cardinality, at the cost of a small estimation error. uniqExact is precise but its memory grows with the number of distinct values. The approximate functions are the standard choice for large-scale distinct counting.

Question 35ClickHouse fundamentals and OLAP concepts

A table uses PARTITION BY toYYYYMM(ts) and contains three years of data. A query filters only on a non-partition column, customer_id. What does partition pruning do for this query?

  1. It automatically creates a partition per distinct customer_id
  2. It prunes partitions using min/max statistics of customer_id stored per partition
  3. It converts the query into a projection-based lookup
  4. Nothing, because the filter does not reference the partition expression, so all partitions may be scanned Correct answer.

Explanation

Partition pruning works by evaluating the partition expression against the query predicates. If the filter does not constrain ts, ClickHouse cannot exclude any partition and may read all of them. Partitioning by a high-cardinality column such as customer_id would create far too many partitions.

Question 36ClickHouse fundamentals and OLAP concepts

Which ClickHouse mechanism is specifically designed to accelerate a filter on a column that is not part of the sorting key, such as a tag column, by storing per-granule metadata?

  1. TTL expression
  2. Data skipping index Correct answer.
  3. ReplacingMergeTree version column
  4. LowCardinality encoding

Explanation

A data skipping index stores compact per-granule information, such as min/max, bloom filter, or set values, for a column. During a scan the reader consults it to decide whether a granule can be skipped entirely. TTL, version columns, and LowCardinality affect retention, deduplication, and encoding rather than granule skipping.

Question 37ClickHouse fundamentals and OLAP concepts

A materialized view with a TO target table is created over a source table. What happens to rows that were inserted into the source before the view was created?

  1. They are not present in the target table, because the view only processes rows inserted after its creation Correct answer.
  2. They cause the view creation to fail unless the source table is empty
  3. They are backfilled the next time the target table is merged
  4. They are backfilled automatically when the view is created

Explanation

A materialized view in ClickHouse acts as an insert trigger: it transforms rows as they arrive in the source table. Rows that already existed before the view was created are never processed, so the target table starts empty or partial. Teams backfill historical data with a separate INSERT ... SELECT.

Question 38ClickHouse fundamentals and OLAP concepts

A query against a SummingMergeTree table returns two rows with the same (day, site) key. Which explanation is most consistent with SummingMergeTree semantics?

  1. The engine stores each key twice by design to support read parallelism
  2. The rows were inserted with different data types, which prevents summation
  3. The table silently rejects the duplicate key at insert time, so the two rows must come from different tables
  4. The rows live in parts that have not yet been merged, and summation only happens for rows within the same part during merges Correct answer.

Explanation

SummingMergeTree collapses rows with the same sorting key only when parts are merged. Until a merge combines the relevant parts, queries can see multiple rows per key. Queries that need collapsed results should aggregate with SUM and GROUP BY, or use FINAL.

Question 39ClickHouse fundamentals and OLAP concepts

A fact table has ORDER BY (tenant_id, ts) and is partitioned by day. A dashboard runs WHERE ts >= now() - INTERVAL 1 DAY across all tenants every few seconds. Which change most directly improves this workload?

  1. Replace the DateTime type of ts with String
  2. Remove the partition by day to reduce the number of parts
  3. Increase index_granularity to a very large value such as 8192
  4. Add a projection or secondary table sorted by ts so the time-range scan reads a contiguous, pruned range Correct answer.

Explanation

Because ts is not the leading key column, a global time-range filter cannot use the primary index prefix and must scan broadly. A projection or a companion table sorted by ts gives the planner a structure where the time range is contiguous and prunable. Enlarging granules or changing the column type does not fix the key-prefix mismatch.

Question 40ClickHouse fundamentals and OLAP concepts

A team needs per-row enrichment of a large fact table with a 50,000-row lookup that is refreshed hourly. Which approach best fits ClickHouse's execution model?

  1. Load the lookup into a dictionary refreshed on a schedule and use dictGet in the SELECT list Correct answer.
  2. Run a LEFT JOIN against the lookup table for every query and rely on the join cache
  3. Denormalize by running an UPDATE on the fact table each hour
  4. Store the lookup in a Memory table and recreate it on every query

Explanation

A dictionary preloads the small lookup into an in-memory structure and supports fast keyed access through dictGet, which avoids rebuilding a join hash table per query. Scheduled refresh keeps the lookup current without touching the fact table. Mutating the large fact table hourly would be expensive and unnecessary.

Question 41Data modeling and table engines

Which ClickHouse table engine is the standard choice for a fact table that must hold every inserted row without deduplication?

  1. CollapsingMergeTree
  2. AggregatingMergeTree
  3. MergeTree Correct answer.
  4. ReplacingMergeTree

Explanation

MergeTree is the base engine of the family and stores every inserted part without any row-level deduplication or collapsing logic. The other engines add merge-time behavior such as version-based replacement, aggregate-state merging, or sign-based collapsing on top of the same storage layout.

Question 42Data modeling and table engines

In a MergeTree table definition, what does the ORDER BY clause specify?

  1. The physical column order inside each compressed data file part
  2. The columns projected into the skip indexes for faster filtering
  3. The columns used as the sorting key that determines the on-disk order and the primary index granularity Correct answer.
  4. The columns whose values are used to break ties when two rows have identical timestamps

Explanation

The ORDER BY expression of a MergeTree defines the sorting key; data inside each part is stored sorted by it, and it also drives the sparse primary index. Choosing it well is the single most important modeling decision for MergeTree tables.

Question 43Data modeling and table engines

A table is created with ENGINE = ReplacingMergeTree(ver). Two rows share the same sorting key with ver values 3 and 7. After a merge completes, which row remains?

  1. Neither row, because conflicting versions are dropped
  2. The row with ver = 7 Correct answer.
  3. Both rows, because deduplication only happens at query time
  4. The row with ver = 3

Explanation

ReplacingMergeTree keeps the row with the maximum value in the version column when rows with equal sorting keys meet during a merge. The older row (ver = 3) is discarded at merge time, not at query time.

Question 44Data modeling and table engines

Which statement about the primary key of a MergeTree table is accurate?

  1. It must be unique across all rows in the table
  2. It can contain columns that do not appear in the sorting key in any position
  3. It is stored as a separate hash table for point lookups
  4. It must be a prefix of the sorting key and is used to build the sparse index Correct answer.

Explanation

In MergeTree the primary key must be a prefix of the ORDER BY expression, and it defines the sparse index marks stored in memory. Uniqueness is never enforced, and there is no separate hash structure for the primary key.

Question 45Data modeling and table engines

You need a table where each entity keeps only its latest state, and you want stale versions physically removed over time without writing custom merge logic. Which engine fits best?

  1. Log engine
  2. ReplacingMergeTree with a version column Correct answer.
  3. SummingMergeTree with a numeric version column
  4. MergeTree with a TTL on the version column

Explanation

ReplacingMergeTree is designed exactly for keeping the latest row per sorting key, using a version column to decide which row wins during merges. TTL deletes by age, SummingMergeTree adds numbers, and Log engines offer no deduplication at all.

Question 46Data modeling and table engines

What is the role of the sign column in a CollapsingMergeTree table?

  1. It marks rows as state additions (1) or cancellations (-1) so matching pairs collapse during merges Correct answer.
  2. It records the number of times a row has been merged
  3. It flags rows that should be skipped by the primary index
  4. It stores the replication version used to resolve conflicts between replicas

Explanation

CollapsingMergeTree uses a sign column of 1 or -1; when rows with identical sorting keys and opposite signs meet, they cancel each other during merges. This models mutable state as an append-only stream of additions and retractions.

Question 47Data modeling and table engines

A raw event table stores user_id, event_time, and payload. You want to expose a per-user daily event count that stays cheap to query as data grows. Which modeling approach is most idiomatic in ClickHouse?

  1. Create a ReplacingMergeTree keyed by user_id and compute counts at query time
  2. Create a MergeTree table and rely on a materialized view that stores plain integer counts updated in place
  3. Create a Memory engine table refreshed nightly by an external job
  4. Create an AggregatingMergeTree table keyed by (user_id, day) holding countState() values and query it with countMerge() Correct answer.

Explanation

AggregatingMergeTree stores intermediate aggregate states and merges them by sorting key, so pre-aggregated results stay compact and correct across merges. Querying with countMerge() finalizes the stored states.

Question 48Data modeling and table engines

Which engine should you choose for a small lookup table of country codes that is read constantly and never updated?

  1. Join Correct answer.
  2. Buffer
  3. Kafka
  4. MergeTree

Explanation

The Join engine keeps the table fully in memory as a hash map optimized for joins against the right side, which suits small, static dictionaries. It is not intended for large tables or frequent mutations.

Question 49Data modeling and table engines

How does SummingMergeTree treat rows that share the same sorting key when they are merged?

  1. It sums all numeric columns not part of the sorting key into a single row Correct answer.
  2. It concatenates string columns and sums numeric columns
  3. It discards all but the first inserted row
  4. It keeps the row with the largest value in each numeric column

Explanation

During merges, SummingMergeTree replaces groups of rows with equal sorting keys by one row whose non-key numeric columns hold the sums. Non-numeric columns keep an arbitrary value from one of the merged rows.

Question 50Data modeling and table engines

A table uses PARTITION BY toYYYYMM(event_date). What is the main benefit of this choice for a time-series workload?

  1. It makes the primary index unnecessary
  2. Queries filtered by month can skip entire partitions, and old months can be dropped cheaply Correct answer.
  3. It guarantees that rows within a month are uniquely keyed
  4. It compresses each month with a different codec automatically

Explanation

Partitioning splits data into independent parts directories, so partition pruning lets queries read only relevant months and DROP PARTITION removes old data instantly. Partitioning does not enforce uniqueness or replace the primary index.