Designing a Real-Time Analytics Stack: Kafka → ClickHouse with Exactly-Once Semantics

The problem

Dashboards lag, incidents hide in the noise, and an OLTP store handles ad-hoc scans poorly. Millisecond analytics over streaming events need to arrive without duplicate rows or silent data loss when brokers hiccup. This covers how to wire Kafka → ClickHouse and choose the right ingestion option (and config) to hit exactly-once where it's actually guaranteed.

The three ways to ingest Kafka into ClickHouse

Option	Where it runs	Semantics	When to choose
ClickPipes for Kafka	ClickHouse Cloud (managed)	At-least-once	Lowest ops, cloud-native; de-dup happens downstream. ClickHouse
Kafka Connect Sink (official)	Any Kafka Connect	Exactly-once (EOS) with built-in state store	EOS plus rich connector controls. ClickHouse
Kafka Table Engine + Materialized Views	Inside ClickHouse	At-least-once	Self-host, simple, push & pull; pair with dedup engines if needed. ClickHouse

ClickHouse's "Integrating Kafka" page summarizes the semantics: ClickPipes = at-least-once, Kafka Connect Sink = exactly-once, Kafka table engine = at-least-once. ClickHouse

Architecture at a glance

• Producers write to Kafka with transactions (idempotence + transactional.id).
• Sink consumes with read_committed (no uncommitted/aborted records).
• ClickHouse stores to a MergeTree family table (often ReplicatedMergeTree for HA). Apache Kafka

Path 1 - Exactly-once with the ClickHouse Kafka Connect Sink

This is the straightest line to EOS: the official ClickHouse Kafka Connect Sink ships with EOS support. It uses a state store (KeeperMap) to coordinate and avoid duplicates; enable exactlyOnce=true. ClickHouse

Minimal connector config (JSON):

{
  "name": "clickhouse-connect",
  "config": {
    "connector.class": "com.clickhouse.kafka.connect.ClickHouseSinkConnector",
    "tasks.max": "1",
    "topics": "events",
    "hostname": "my-clickhouse.example",
    "database": "analytics",
    "username": "default",
    "password": "<SECRET>",
    "ssl": "true",
    "exactlyOnce": "true",
    "value.converter": "org.apache.kafka.connect.json.JsonConverter",
    "value.converter.schemas.enable": "false"
  }
}

EOS is a main feature of the connector, powered by KeeperMap; other state stores are also selectable. ClickHouse

Producer & consumer prerequisites for EOS:

• Producer: set a transactional.id (enables idempotence automatically) and wrap sends in a transaction. Apache Kafka
• Consumer (used by Connect): must read committed only (isolation.level=read_committed). Apache Kafka

Java producer snippet (transactions):

Properties p = new Properties();
p.put("bootstrap.servers", "broker:9092");
p.put("enable.idempotence", "true");        // implied when transactional.id set
p.put("acks", "all");
p.put("transactional.id", "orders-producer-1");

KafkaProducer<String, byte[]> producer = new KafkaProducer<>(p);
producer.initTransactions();
try {
  producer.beginTransaction();
  producer.send(new ProducerRecord<>("events", key, value));
  // ...batch more sends...
  producer.commitTransaction();
} catch (Exception e) {
  producer.abortTransaction();
}

This setup, plus the Sink's EOS, gives the strongest duplicate-free path end-to-end. Apache Kafka

Path 2 - ClickPipes (managed) for speed to value

On ClickHouse Cloud, ClickPipes is a few clicks: it scales, supports common formats, has built-in retries & error tables, and simple controls. It's at-least-once (design for dedup downstream if dupes can't be tolerated). ClickHouse

Where it fits: lowest ops, quick wins, multi-source onboarding, Terraform/API automation. Where it doesn't: strict EOS guarantees - use the Kafka Connect Sink for those. ClickHouse

Path 3 - Kafka Table Engine + Materialized Views (self-hosted simple)

The Kafka table engine lets ClickHouse consume from topics; materialized views trigger on incoming batches to write into a target MergeTree table. Simple and battle-tested, but at-least-once. ClickHouse

DDL blueprint:

-- Kafka reader (source)
CREATE TABLE src.kafka_events
(
  ts DateTime64(3),
  user_id UInt64,
  event_type LowCardinality(String),
  payload String
)
ENGINE = Kafka
SETTINGS
  kafka_broker_list = 'broker:9092',
  kafka_topic_list  = 'events',
  kafka_group_name  = 'ckh-events',
  kafka_format      = 'JSONEachRow',
  kafka_num_consumers = 4;

-- Target (replicated for HA)
CREATE TABLE dst.events
(
  ts DateTime64(3),
  user_id UInt64,
  event_type LowCardinality(String),
  payload String
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
ORDER BY (user_id, ts);

-- Incremental ingestion (MV)
CREATE MATERIALIZED VIEW pipe.kafka_to_events
TO dst.events AS
SELECT ts, user_id, event_type, payload
FROM src.kafka_events;

• Kafka engine consumes once and advances offsets; MVs persist data into MergeTree. ClickHouse
• Use ReplicatedMergeTree for durability/HA in production. ClickHouse

For stronger de-dup on this path: model with ReplacingMergeTree (ORDER BY the business key; optional version column). Dedup happens on background merges, so duplicates may persist briefly. ClickHouse

Exactly-once: what it really means here

• End-to-end EOS requires: transactional producers, a consumer reading only committed records, and a sink that writes idempotently and coordinates state. In practice, that's Kafka transactions + read_committed + ClickHouse Kafka Connect Sink with exactlyOnce=true. Apache Kafka
• ClickPipes and Kafka engine deliberately target at-least-once. Plan dedup or idempotent upserts when using them. ClickHouse

Performance checklist

• Batching: Produce in batches and let Connect control insert sizing; avoid tiny messages.
• Keys & partitions: Pick a key that balances partitions and preserves query locality (e.g., user_id for per-user reads).
• ORDER BY: Align ClickHouse ORDER BY with hot filters to minimize scanned bytes.
• Backpressure: Watch Connect consumer lag and ClickHouse system.parts growth.
• Schema: Prefer typed JSON/Avro/Protobuf; don't parse giant blobs in SELECT.
• HA: Use ReplicatedMergeTree and a multi-broker Kafka cluster.

Common pitfalls (and fixes)

• "EOS is set but dupes still appear." Verify producers actually use transactions, and the sink is read_committed and exactlyOnce=true. Otherwise the guarantee is only at-least-once. Apache Kafka
• Kafka engine floods CPU. Increase kafka_num_consumers only up to partition/core counts; do heavy transforms in the MV or downstream. ClickHouse
• ClickPipes surprises: It's managed and at-least-once - design schemas and queries for dedup (business keys + ReplacingMergeTree). ClickHouse
• Single replica writes: Non-replicated tables risk gaps on failover. Use ReplicatedMergeTree. ClickHouse

End-to-end example: producer → EOS sink → ClickHouse

1. Producer (transactions): set transactional.id, acks=all; wrap sends in beginTransaction/commitTransaction. Apache Kafka
2. Sink (Kafka Connect): deploy the ClickHouse Kafka Connect Sink, set exactlyOnce=true, point to an existing target table. ClickHouse
3. ClickHouse table (target):

CREATE TABLE analytics.events
(
  event_id UUID,
  ts       DateTime64(3),
  user_id  UInt64,
  type     LowCardinality(String),
  attrs    JSON
)
ENGINE = ReplicatedMergeTree('/clickhouse/tables/{shard}/events', '{replica}')
ORDER BY (event_id, ts);

4. Query safely: downstream readers see one row per committed event; duplicates are prevented by the sink's EOS logic at write time. ClickHouse

Reference docs

• Integrating Kafka with ClickHouse (options & semantics) - start here. ClickHouse
• ClickHouse Kafka Connect Sink (EOS + config) - reference & examples. ClickHouse
• ClickPipes for Kafka - managed ingestion in Cloud. ClickHouse
• Kafka table engine - settings, examples, MV wiring. ClickHouse
• ReplicatedMergeTree - replication and background fetches. ClickHouse
• Kafka transactions & read_committed - official API docs (Javadoc). Apache Kafka

Summary

• For exactly-once, use Kafka Connect Sink with transactions (transactional.id) and consumers on read_committed.
• For lowest ops in Cloud with at-least-once tolerance, use ClickPipes.
• For self-hosting with simple plumbing, use Kafka engine + MVs, but model for dedup.