ClickHouse Interview Questions for Data Engineers: Sorting Keys, Deduplication, and Materialized Views

A ClickHouse query can run quickly and return the wrong revenue. Insert an order worth 100, correct it to 120, and replay an older message: which amount should the dashboard show? The answer depends on the sorting key, the replacement rule, and whether the dashboard reads current rows or an aggregate of every inserted version.

These ClickHouse interview questions for data engineers follow that problem through a runnable example. The central skill is separating physical access order, logical identity, and aggregate maintenance, then proving that all three match the business definition.

Evidence boundary: Official facts below come from ClickHouse documentation checked September 8, 2026. The questions, design judgments, and dataset are original PracHub practice, not candidate-reported questions or a description of hiring at ClickHouse. No candidate reports are used. We executed the SQL locally with chDB 4.3.0, embedding ClickHouse 26.7.2.1; this establishes the illustrated results, not production throughput or replicated-cluster behavior.

How should you choose a sorting key?

Practice question: A multi-tenant order dashboard usually filters one tenant and a creation-date range. Orders arrive through change data capture. What belongs in ORDER BY?

For the B-tree comparison, use PracHub’s database indexing guide; do not transfer uniqueness assumptions from an OLTP primary key.

Start with two requirements: which queries need efficient reads, and which fields remain unchanged across versions of an order. A useful access pattern can become an incorrect replacement identity if you include mutable fields.

Official fact: MergeTree stores sorted data in parts and uses a sparse primary index to skip ranges. Its primary key does not enforce row uniqueness. If specified separately, PRIMARY KEY must be a prefix of the sorting key. PARTITION BY divides data into partitions; it is a different design choice from sorting rows within parts. See the MergeTree reference.

Design inference for this workload: Consider (tenant_id, created_date, order_id) only if creation date is immutable and consistently reproduced in every update. Leading with tenant aligns with tenant-scoped reads; the date supports time filtering; the order ID distinguishes orders. If creation date can change, keep it out of the replacement identity and evaluate another access strategy.

The official primary-key guidance prioritizes common filters and data ordering that helps compression. Treat that as a starting point for measurement, not a rule that the lowest-cardinality column always goes first.

That table is an original schema review, not a universal ranking. If the interviewer changes the workload to global order lookups, revisit the access order while preserving identity.

Does ReplacingMergeTree deduplicate immediately?

Practice question: An insert succeeded. Can you now count the table to obtain the number of current orders?

Official fact: ReplacingMergeTree identifies duplicates by the complete ORDER BY tuple. With a version column, replacement retains the highest version. Background merges have no guaranteed completion time, and duplicate versions may remain visible. Query-time FINAL applies replacement semantics when reading. It does not repair an incorrectly chosen key. See the ReplacingMergeTree reference.

A strong answer states the business contract before naming the engine: one current row per tenant/order, the greatest source version wins, and duplicate deliveries must not change current revenue. Source version should represent business ordering. An ingestion timestamp alone can make a delayed old update look newest.

Also distinguish two meanings of “deduplication.” Official fact: Insert-retry deduplication uses a finite deduplication log and depends on engine and settings. It prevents recognized repeated inserts; it is separate from selecting the latest business record. The retry documentation describes its limits. Do not promise permanent exactly-once behavior merely because a retry returned success.

Preparation inference: Ask what happens after the retry window expires, when a replay uses different batches, and when two messages carry the same version but conflicting payloads. Define a producer contract that makes version ties identical, or reject conflicting ties upstream. Avoid making arrival order the business policy by accident.

Run the five-row correctness test

This original exercise models current order value, not revenue from an immutable payment ledger. Tenant 7 has two orders. Order 101 changes from 100 to 120; order 102 remains 30. The correct current total is therefore 150.

Run the following statements in order in a fresh, disposable database. The table names must be unused. SYSTEM STOP MERGES deliberately freezes the source’s background replacement so that the first result is reproducible; it is a test control, not an application design. A restricted hosted account may not permit that command.

CREATE TABLE orders
(
    tenant_id UInt32,
    order_id UInt64,
    version UInt64,
    amount Int64
)
ENGINE = ReplacingMergeTree(version)
ORDER BY (tenant_id, order_id);

CREATE TABLE totals
(
    tenant_id UInt32,
    amount Int64
)
ENGINE = SummingMergeTree
ORDER BY tenant_id;

CREATE MATERIALIZED VIEW totals_mv TO totals AS
SELECT tenant_id, sum(amount) AS amount
FROM orders
GROUP BY tenant_id;

SYSTEM STOP MERGES orders;

INSERT INTO orders VALUES (7, 101, 1, 100), (7, 102, 1, 30);
INSERT INTO orders VALUES (7, 101, 2, 120);
INSERT INTO orders VALUES (7, 101, 1, 100);
INSERT INTO orders VALUES (7, 102, 1, 30);

SELECT count(), sum(amount) FROM orders;
SELECT count(), sum(amount) FROM orders FINAL;
SELECT sum(amount) FROM totals;

Observed results on the stated local engine: the three queries return (5, 380), (2, 150), and 380, respectively. The replayed version 1 of order 101 does not displace version 2 in the FINAL result. The repeated row for order 102 also leaves its current value unchanged.

The first total, 380, is the sum of inserted values: 100 + 30 + 120 + 100 + 30. It answers a different question from “What is the latest value of each order?” The plain non-replicated table in this fixture has no retry-deduplication window configured; this exercise is not a test of replicated insert retries.

Before moving on, predict what happens if you put version in the sorting key. Versions 1 and 2 would no longer compete as the same identity. That schema error survives FINAL; faster merging cannot correct it.

Why does the materialized view still show 380?

Practice question: The source is a ReplacingMergeTree table. Why does a materialized view not automatically inherit its corrected total?

Official fact: An incremental materialized view executes against newly inserted blocks and writes results to its target. It does not continuously recompute the full source-table query whenever the source changes. The incremental materialized-view documentation explains this insert-trigger behavior.

In our experiment, the target received contributions from every inserted version. Source replacement cannot retrospectively turn those contributions into one latest value per order. Nor does querying the aggregate target with FINAL reconstruct the order identities that aggregation discarded.

Prove that the discrepancy survives a source merge:

SYSTEM START MERGES orders;
OPTIMIZE TABLE orders FINAL;
SELECT count(), sum(amount) FROM orders;
SELECT sum(amount) FROM totals;

Observed results: the source now returns (2, 150), while the target still returns 380. The forced merge is used only to demonstrate the failure. It is not a recommended recurring fix for dashboard correctness.

Design inference: Putting a deduplication-looking expression inside the insert-trigger query is not enough. Ask whether it can see earlier versions and retract their previous contributions. Inserting 120 after 100 requires replacing 100, or adding a correctly derived delta of 20. Simply adding 120 implements neither operation.

Choose a maintenance strategy from the metric’s contract:

Official fact: Refreshable materialized views periodically execute their query over the dataset. In replacement mode, refreshed results replace the target result atomically; APPEND has different semantics. See refreshable materialized views. Our proposed refresh query must still select current rows correctly. A refresh of the wrong query only produces a newer wrong answer.

Which edge cases should you defend next?

The five-row test proves one narrow contract. Extend it with changes that challenge that contract, rather than adding random volume and declaring success.

First, change a mutable field. Move an order from pending to paid while keeping its identity and increasing its version. The surviving order count must remain one. Repeat with a proposed status-based sorting key to expose the mistake.

Second, challenge partition stability. Official guidance for ReplacingMergeTree recommends keeping all versions of a row in the same partition; background merging operates within partitions. For this order model, partitioning by ingestion month can scatter versions of a long-lived order. Define an immutable partition value and ensure every correction reproduces it. Do not assume adding FINAL makes every partition-related setting harmless.

Third, add deletions. Specify whether cancellation is an amount change, a status change, or removal from the metric. These are different business events. The four-column fixture intentionally has no delete protocol. A production extension needs an explicit versioned deletion policy and a test that late older messages do not resurrect removed orders.

Fourth, rebuild the aggregate. Creating an incremental view after historical data exists does not itself solve historical coverage. Establish a cutoff or pause writes, backfill the intended range, and prove there is neither a gap nor an overlap with live ingestion. For our case, compare the rebuilt total with the current-state source total, not the sum of raw versions.

Finally, evaluate performance separately. Preparation inference: compare candidate layouts using representative tenant sizes, date ranges, part counts, and update frequency. Inspect index pruning and measured rows/bytes read. A tiny correctness fixture cannot substantiate a latency claim, and a warm-cache timing alone cannot justify a sorting key.

Practice the surrounding data-engineering decisions

These verified PracHub questions exercise adjacent skills. They are not presented as ClickHouse-specific candidate reports. Re-answer each using the order fixture so that your explanation includes a concrete failure and a checkable invariant.

For more practice, use the Data Engineer question collection. Your answer is ready when you can defend the key, predict all five observed query results, and explain why merging the source leaves the materialized-view total unchanged.

Sources and Further Reading

• ClickHouse: MergeTree reference
• ClickHouse: Choosing a primary key
• ClickHouse: ReplacingMergeTree reference
• ClickHouse: Working with ReplacingMergeTree
• ClickHouse: Deduplicating inserts on retries
• ClickHouse: Incremental materialized views
• ClickHouse: Refreshable materialized views