20 Advanced ClickHouse Interview Questions for Experienced Developers

The **MergeTree** family is the foundation of ClickHouse, designed for inserting large batches of data that are written to disk as immutable “parts” and merged in the background.

Common variations include:

• `MergeTree`: The base engine for a single node.
• `ReplicatedMergeTree`: Adds replication and data deduplication capabilities, essential for fault tolerance in a cluster.
• `SummingMergeTree`: Automatically summarizes data during the merge process by summing numeric columns that are not part of the primary key. Great for counters.
• `AggregatingMergeTree`: Applies aggregate functions to rows with the same primary key, storing the intermediate state. This is powerful for incremental aggregations.
• `ReplacingMergeTree`: Removes duplicate rows based on the sorting key during merges, keeping only the latest version.

In a columnar database, values for the same column are stored together contiguously on disk. This provides several key benefits for analytical (OLAP) queries:

• I/O Reduction: Queries typically only need to read a small subset of columns. A columnar store only reads the data for the columns it needs, dramatically reducing disk I/O compared to a row-based store which would have to read every entire row.
• Better Compression: Data within a single column is often highly uniform (e.g., all integers or all strings from a limited set), which leads to much higher data compression ratios.
• Vectorized Execution: Because data is stored in contiguous blocks, modern CPUs can use SIMD instructions to perform operations on entire “vectors” of column data at once, which is significantly faster than processing row by row.

In ClickHouse, the **primary key** (defined by `PRIMARY KEY`) is not a uniqueness constraint like in SQL databases. Its sole purpose is to create a sparse index that accelerates data lookups. It points to “marks” (blocks of rows) rather than individual rows.

The **sorting key** (defined by `ORDER BY`) determines the physical order in which data is stored on disk within each data part. By default, the sorting key is the same as the primary key.

Choosing a good sorting key is critical for performance, as well-sorted data can be read much faster and compresses better. The primary key should be a prefix of the sorting key.

When you `INSERT` data into a MergeTree table, ClickHouse writes it to a new, immutable directory called a **data part**. Each insert creates one or more parts. Over time, having many small parts can degrade query performance.

To combat this, a background process continuously **merges** smaller parts into larger, more efficient ones. During a merge, it takes several source parts, combines their sorted data into a single new part, and then deletes the old parts. This process also performs the special logic of engines like `SummingMergeTree` or `ReplacingMergeTree`.

The `LowCardinality(T)` data type is a dictionary-based encoding for columns that have a small number of distinct values (low cardinality) relative to the total number of rows. It stores the unique values in a shared dictionary and then represents the data in the column as integer indices into that dictionary.

It should be used for columns like status codes, country codes, or event types. This can dramatically reduce memory usage and increase the speed of queries that group by or filter on that column.

ClickHouse is designed for extremely fast scans over large datasets but has very expensive `JOIN` operations. A `JOIN` in a distributed system requires sending large amounts of data between nodes, which is a major performance bottleneck.

Denormalization—storing related data together in a single, wide table—avoids the need for joins at query time. By pre-joining data during ingestion, you trade increased storage for vastly superior query performance, which is the primary goal of an analytical database like ClickHouse.

A **View** is simply a stored query. It doesn’t store any data itself; when you query the view, you are executing the underlying query.

A **Materialized View** is a more powerful feature that physically stores data. It has a `SELECT` query and a target table. When data is inserted into the source table of the query, the transformation is applied, and the result is inserted into the target table. This provides a way to build real-time data rollups and aggregations automatically. It’s a trigger for populating another table.

While ClickHouse is not a graph database, you can model hierarchies effectively. A common approach is to use an array of parent IDs to represent the entire path from the root to the current node.

For example, a `categories` table could have `category_id`, `parent_id`, and an array column `category_path`. For a sub-category, the path might be `[root_id, parent_id, current_id]`. This allows you to find all descendants of a node with a simple `has(category_path, node_id)` query, which is very efficient.

`PREWHERE` is a ClickHouse-specific optimization. It allows you to specify a filtering condition that is applied *before* reading the full data for the columns involved. ClickHouse will first read only the columns necessary for the `PREWHERE` condition, filter out the rows, and then read the remaining columns needed by the `SELECT` list for only the matching rows.

This can provide a significant performance boost if the `PREWHERE` condition is highly selective and filters out a large portion of the data, especially when operating on large columns. A good rule of thumb is to put the most selective filter on a column with the smallest size into `PREWHERE`.

The `FINAL` modifier forces ClickHouse to perform a final merge of data parts for engines like `ReplacingMergeTree` or `CollapsingMergeTree` at query time, guaranteeing that you see the final, collapsed results.

However, this comes at a significant performance cost. The query must read all data parts that have the same primary key, merge them in memory, and then return the final result. This can make the query much slower and less scalable. It’s often better to design applications to handle the intermediate, un-merged data and avoid using `FINAL` in performance-critical queries.

Aggregate function combinators are suffixes that modify the behavior of aggregate functions. They provide a powerful way to perform conditional or parametric aggregations.

The **`-If`** combinator is one of the most useful. It makes an aggregate function accept an additional argument: a condition. The function will only process values from rows where the condition is true. For example, to count the number of errors and warnings in a single pass, you could write:

SELECT countIf(level = 'error') AS errors, countIf(level = 'warning') AS warnings FROM logs

Both are used in distributed queries to handle joins where the right-hand table is on a different node.

• `GLOBAL IN / JOIN`: The coordinator node first executes the subquery for the right side of the clause, collects the results, and then broadcasts these results to all the shards. Each shard then uses this broadcasted data to perform the join locally.

You should use `GLOBAL IN` when the result of the subquery is a single column. Use `GLOBAL JOIN` when the subquery returns multiple columns needed for the join. This approach is efficient only when the result set of the right-hand subquery is small enough to fit in memory and be broadcasted over the network.

When you insert a block of data into a `ReplicatedMergeTree` table, ClickHouse computes a hash of the block. This hash is stored in ClickHouse Keeper (or ZooKeeper). Before writing a new block, a node checks with Keeper to see if a block with the same hash has already been committed by another replica.

If the hash already exists, the block is discarded as a duplicate. If not, the block is written, and its hash is registered. This provides exactly-once delivery semantics for inserted blocks, ensuring that even if a client sends the same batch of data multiple times due to retries, it will only be stored once.

A **Distributed** table engine does not store any data itself. It acts as a distributed “view” or a proxy over a set of local tables on different shards in a cluster. When you query the Distributed table, it rewrites the query and sends it to all the underlying shards. It then gathers the partial results from each shard and merges them on the coordinator node before returning the final result to the client.

Similarly, when you `INSERT` into a Distributed table, it determines which shard a row belongs to based on a sharding key and forwards the write to the correct node.

ClickHouse Keeper (a custom coordination system compatible with ZooKeeper) is essential for managing a replicated cluster. Its main responsibilities include:

• Leader Election: Electing a leader replica for each shard to coordinate writes.
• Metadata Storage: Storing the replication queue (log of operations to be performed) and tracking which replicas have executed which tasks.
• Data Deduplication: Storing hashes of inserted blocks to prevent duplicate writes.
• Synchronization: Coordinating actions like schema changes (`ALTER`) across all replicas.

You execute the `ALTER` statement on just one of the nodes in the cluster. ClickHouse automatically propagates the schema change to all other replicas in the same shard using ClickHouse Keeper. The query is placed on a replication queue, and each replica will apply it. You don’t need to run the `ALTER` command on every node manually. To apply it to the entire cluster (all shards), you would use the `ON CLUSTER` clause.

Dictionaries are in-memory key-value stores that are used to enrich data at query time, providing a highly efficient alternative to traditional `JOIN`s for dimension tables. You can load data into a dictionary from various sources (a ClickHouse table, a MySQL database, a file, etc.). At query time, you can use the `dictGet` function to look up values from the dictionary based on a key from your fact table. This lookup is extremely fast as it happens entirely in memory.

The `ARRAY JOIN` clause is used to “un-nest” or “explode” an array. For each row in the source table, it creates new rows for each element in the specified array column. This is useful when you need to perform aggregations or filtering on individual array elements. For example, if you have a row with a `tags` column `[‘a’, ‘b’]`, an `ARRAY JOIN tags` would produce two separate rows, one with tag ‘a’ and one with tag ‘b’, with all other column values duplicated.

• A **Tuple** is a fixed-size, ordered collection of elements that can have different types, similar to a struct. Example: `Tuple(String, Int32)`.
• A **Nested** data structure is used to represent multiple related arrays of the same length within a single row. For example, `Nested(event_name String, event_time DateTime)`. This is essentially syntactic sugar for two separate array columns (`event_name Array(String)`, `event_time Array(DateTime)`) where ClickHouse ensures they have the same number of elements.

Quotas are a way to manage resource usage on a per-user or per-role basis. They allow you to set limits on various metrics over a specific time period. You can define quotas for:

• Query Complexity: Number of queries, errors, or result rows.
• Resource Usage: Execution time or number of read rows.
• Network Usage: Amount of data transferred.

This is crucial for multi-tenant environments to ensure that no single user can monopolize the cluster’s resources.