How do you ingest a Kafka topic into ClickHouse using the Kafka table engine?

## The three-object pattern ClickHouse consumes Kafka with a pipeline of three schema objects, and getting the roles right is most of the interview answer. ```sql -- 1. the consumer: stores nothing CREATE TABLE events_queue ( event_time DateTime, user_id UInt64, action String ) ENGINE = Kafka SETTINGS kafka_broker_list = 'broker:9092', kafka_topic_list = 'events', kafka_group_name = 'clickhouse_events', kafka_format = 'JSONEachRow', kafka_num_consumers = 4; -- 2. the storage CREATE TABLE events ( event_time DateTime, user_id UInt64, action LowCardinality(String) ) ENGINE = MergeTree PARTITION BY toYYYYMM(event_time) ORDER BY (user_id, event_time); -- 3. the pump CREATE MATERIALIZED VIEW events_mv TO events AS SELECT event_time, user_id, action FROM events_queue; ``` **The Kafka engine table holds no data.** It is a cursor over a topic. Running `SELECT * FROM events_queue` by hand pulls messages and moves the consumer forward, which means an innocent debugging query silently steals rows from the pipeline. This is the single most common mistake and the thing to say out loud in an interview. **The materialized view is an insert trigger,** not a cached result. Each block the consumer reads is passed through the view's `SELECT` and inserted into the target table. That is where you do light transformation: type casting, flattening, dropping fields, deriving columns. ## Batching, and why it is still the whole game Every block the view pushes becomes a data part, so the Kafka path is subject to exactly the same part-explosion pressure as any other write path. Two settings govern the block size: `kafka_max_block_size` (rows collected before a flush) and the Kafka flush interval (time before an incomplete block is flushed anyway). A low-volume topic with an aggressive flush interval will happily produce a part every few hundred milliseconds. Size these so each part carries a meaningful number of rows. ## Parallelism `kafka_num_consumers` sets how many consumers the table runs; it cannot usefully exceed the topic's partition count, since each partition is consumed by at most one consumer in the group. `kafka_thread_per_consumer` gives each consumer its own thread so they do not share one polling thread. Across a replicated ClickHouse cluster you typically create the Kafka table on several nodes with the *same* consumer group name and let the group split partitions between them, with each node writing into its local shard or replica. ## Delivery semantics The pipeline is **at-least-once**. Offsets advance after a block has been written; a failure between the write and the commit, or a consumer rebalance mid-flight, can replay messages that were already stored. Plan for it rather than hoping: - store into a `ReplacingMergeTree` keyed on a business key so duplicates collapse on merge (with `FINAL` or aggregation at read time until they do), - or deduplicate downstream in the query, - or make the payload naturally idempotent. Do not claim exactly-once. An interviewer who works with this stack will notice. ## Malformed messages One unparseable message can otherwise stall the whole consumer. `kafka_skip_broken_messages` allows a bounded number of parse failures per block to be skipped. `kafka_handle_error_mode = 'stream'` instead exposes failures through the `_error` and `_raw_message` virtual columns, so a second materialized view can route them into a dead-letter table while the main view keeps only clean rows. Virtual columns also give you provenance — `_topic`, `_partition`, `_offset`, `_key`, `_timestamp` — and storing `_partition` and `_offset` in the target table makes duplicate hunting and replay auditing straightforward. ## Operating it `system.kafka_consumers` shows consumer state, assignments and last exceptions — the first place to look when rows stop arriving. Server logs carry rebalance and parse errors. `DETACH TABLE` / `ATTACH TABLE` on the materialized view is the way to pause and resume ingestion (for example while doing maintenance on the target) without dropping the consumer group. ## Scope note: Kinesis There is no Kinesis table engine in the open-source ClickHouse server the way there is for Kafka. Ingesting from Kinesis means either ClickPipes on ClickHouse Cloud, which offers managed connectors for streaming sources, or an external process that reads the stream and inserts in batches. Saying "same as Kafka but with a Kinesis engine" is a factual error. ## When not to use the engine at all The built-in consumer is attractive because it needs no extra service, but it couples ingestion lifecycle to database DDL, offers limited transformation, and gives you at-least-once with manual error routing. Many teams prefer an external consumer or a managed connector when they need richer transformation, schema-registry integration, or independent scaling of the ingest tier. The engine shines for straightforward, high-volume, low-transformation feeds.