--- title: "Ship clickhouse integration confluent cloud with less lag" excerpt: "Connect Confluent Cloud to ClickHouse® via Kafka engine, Connect sink, or Tinybird. Auth and lag for clickhouse integration confluent cloud." authors: "Tinybird" categories: "AI Resources" createdOn: "2026-08-04 00:00:00" publishedOn: "2026-08-04 00:00:00" updatedOn: "2026-08-04 00:00:00" status: "published" --- Confluent Cloud gives you managed Kafka: brokers, ACLs, Schema Registry, and a bill that scales with throughput. ClickHouse{% sup %}®{% /sup %} wants a steady stream of inserts it can turn into parts. A **clickhouse integration confluent cloud** setup fails less often on SQL than on **SASL config, network reachability, and consumer lag math**. If you treat Confluent like "Kafka on someone else's laptop" and ClickHouse® like "just another JDBC sink," you will spend the first production week debugging TLS hostnames and empty materialized views. This guide covers the consumer choices, auth, schema strategy, lag SLAs, and when Tinybird should own the analytical side. ## **What "done" looks like** A healthy integration has all of these: 1. A dedicated Confluent principal that can only read the topics you intend 2. A consumer path whose network dependency you can draw on a whiteboard 3. Destination tables with `ORDER BY` that match real filters (event type + time, not ingest-only) 4. A written freshness budget (not "near real-time") 5. Alerts on consumer lag **and** insert/MV errors 6. A plan for poison messages (DLQ, quarantine table, or skip + metric) Missing any one of these is how "the pipeline is green" becomes "the dashboard is lying." ## **Decide who consumes the topic** Three consumer shapes dominate production: | Shape | Who pulls from Confluent | Who writes ClickHouse® | Ops surface | | --- | --- | --- | --- | | A. ClickHouse® Kafka engine | ClickHouse® | Materialized view → MergeTree | ClickHouse® logs / `system` tables | | B. Kafka Connect sink | Connect workers | Sink → HTTP/JDBC | Connect tasks + DLQ topics | | C. Tinybird Kafka connector | Tinybird | Managed ClickHouse® + Pipes | Tinybird + Confluent lag | Pick based on where your cluster lives and who on-calls the consumer. - **Same private network / self-managed ClickHouse®:** Kafka engine is the fewest moving parts. - **ClickHouse® Cloud or locked-down VPC:** Connect sink or Tinybird often wins. Brokers never need inbound paths from Cloud. - **You also need HTTP APIs on the stream:** Tinybird collapses consumer + serving. ### **Anti-pattern: two consumers "for HA"** Running Kafka engine **and** Connect against the same topic into the same table doubles events unless you designed for it. If you need HA, replicate ClickHouse® / run multiple Tinybird replicas with one consumer group, or use Connect tasks with proper rebalancing. Do not invent a second independent consumer "just in case." ## **Confluent auth that actually works** Confluent Cloud almost always means **SASL_SSL** with API keys. ```text bootstrap.servers = pkc-xxxxx.region.aws.confluent.cloud:9092 security.protocol = SASL_SSL sasl.mechanisms = PLAIN sasl.username = sasl.password = ``` Rules that prevent the classic "works in kcat, fails in ClickHouse®" loop: 1. Create a **dedicated API key** scoped to the topics you read. Do not reuse a UI admin key in the database. 2. Confirm the principal can **READ** the topic and **READ** the consumer group (or allow group create). 3. Prefer the **public bootstrap** only if your ClickHouse® side has egress. Private Link / VPC peering changes hostnames. Copy the pair that matches your network path. 4. Rotate secrets in the same place you rotate warehouse creds. Hard-coded passwords in `.xml` configs get committed. 5. Test with the **same bootstrap hostname** the database will use. `kcat` against public endpoints proves nothing about Private Link DNS. ### **ACL checklist** ```text Principal: User: Topic: product.events → READ Group: clickhouse-events → READ (and CREATE if your policy requires it) ``` If lag stays at zero forever and the destination is empty, ACLs and wrong topic names beat "ClickHouse® is slow" nine times out of ten. ## **Pattern A: Kafka engine inside ClickHouse®** Three objects. No external workers. ```sql CREATE TABLE raw.events_queue ( event_id String, user_id String, event_type LowCardinality(String), event_time DateTime64(3), payload String ) ENGINE = Kafka SETTINGS kafka_broker_list = 'pkc-xxxxx.region.aws.confluent.cloud:9092', kafka_topic_list = 'product.events', kafka_group_name = 'clickhouse-events', kafka_format = 'JSONEachRow', kafka_num_consumers = 2, kafka_skip_broken_messages = 100; CREATE TABLE analytics.events ( event_id String, user_id String, event_type LowCardinality(String), event_time DateTime64(3), payload String, _ingested_at DateTime DEFAULT now() ) ENGINE = MergeTree PARTITION BY toYYYYMM(event_time) ORDER BY (event_type, event_time, event_id); CREATE MATERIALIZED VIEW raw.events_mv TO analytics.events AS SELECT event_id, user_id, event_type, event_time, payload FROM raw.events_queue; ``` Wire SASL through ClickHouse® Kafka settings / named collections for your version. Keep passwords out of `SHOW CREATE TABLE` transcripts shared in tickets. **When this breaks:** - ClickHouse® cannot resolve or reach Confluent brokers - `kafka_num_consumers` exceeds partition count - JSON field types drift and poison the MV (one bad field type can stall progress) - You selected `event_time` from `now()` instead of the producer timestamp and every query partition-prunes wrong ### **Upsert streams** If the topic carries CDC-style upserts, land into `ReplacingMergeTree`: ```sql CREATE TABLE analytics.customers_cdc ( customer_id String, email String, plan LowCardinality(String), updated_at DateTime64(3) ) ENGINE = ReplacingMergeTree(updated_at) ORDER BY customer_id; ``` At-least-once delivery from Kafka plus `ReplacingMergeTree` is the usual analytical compromise. Exactly-once across Confluent + ClickHouse® is a research project disguised as a ticket. ## **Pattern B: Kafka Connect sink on Confluent** Run the ClickHouse® sink (or HTTP sink) in **Confluent Cloud Connect** / self-managed Connect that already has broker access. Workers push into ClickHouse® over HTTPS. Useful when: - ClickHouse® is only reachable outbound from Connect - You want DLQ topics and Connect-native restart semantics - Multiple sinks already share the same Connect cluster ### **Connect sizing notes** | Knob | Why it matters | | --- | --- | | Tasks max | Parallelism ≤ partitions for the topic | | Batch size / linger | Controls ClickHouse® insert size | | DLQ topic | Poison JSON should not block the partition forever | | Converter | JSON vs Avro decides whether Schema Registry is in path | Watch batch size and linger. Connect defaults tuned for warehouses can create tiny ClickHouse® inserts. Raise flush sizing until parts stay healthy under peak produce rate. Example HTTP-oriented mental model (even if you use a dedicated sink): ```text Flush when: 5_000 records OR 2 seconds Timeout: high enough for ClickHouse® GC blips On failure: retry with backoff, then DLQ Idempotency: event_id in ORDER BY / ReplacingMergeTree version ``` ## **Schema Registry is optional until it is not** JSONEachRow keeps demos simple. Avro/Protobuf with Schema Registry shows up the moment producers evolve fields. Options: 1. **Stay on JSON** with a tolerant schema (`payload String` + promoted columns) 2. **Decode in Connect** SMT / converter, land plain JSON in ClickHouse® 3. **Decode in Tinybird** after ingest if you use that path Do not let every nested Avro field become a ClickHouse® column on day one. Promote the filters you query; leave the rest in a JSON column. ### **Compatible evolution rules** - Adding an optional field: fine if ClickHouse® column is Nullable or you only read from JSON - Renaming a field: break unless you dual-write or map in Connect - Changing a type from string to int: will break Kafka engine MVs hard Keep a staging consumer that asserts schema compatibility before producers ship breaking changes. ## **Topic and partition design for analytics** Confluent partitioning that is perfect for microservice fan-out is not always perfect for ClickHouse®. Guidance: - Prefer keys that keep related events together when order matters per entity (`user_id`) - Do not over-partition "for speed" if you only run two consumers - Compacted topics for latest-state CDC are fine; still model `ReplacingMergeTree` on the ClickHouse® side - Retention must exceed your worst catch-up window (deploy + outage + backlog drain) If you need both operational log retention and cheap analytics storage, that is normal: Confluent keeps the log; ClickHouse® keeps the queryable projection. ## **Lag budgets, not vibes** Write the SLA before you tune `kafka_num_consumers`: ```text produce delay + consumer batching / Connect flush + ClickHouse® insert + merge visibility ≈ dashboard freshness ``` If product needs 5-second freshness, a 60-second Connect flush will never get there. If BI refreshes every 15 minutes, chase fewer, larger inserts instead of max concurrency. ### **What to monitor** - Confluent consumer lag for your group (per partition) - Time since last successful insert / MV write - ClickHouse® MV exceptions (`system.kafka_consumers` / error logs by version) - Parts count on the destination table after traffic spikes - DLQ depth (Connect path) Alert on lag **crossing a budget**, not on lag being non-zero. Streaming lag is usually non-zero. ## **Security and tenancy** - Separate API keys per environment (dev keys must not read prod topics) - Prefer Private Link when ClickHouse® and Confluent share a cloud footprint - Encrypt in transit everywhere (SASL_SSL / HTTPS). Do not "temporarily" disable SSL in staging configs that get copied. - Scope ClickHouse® writer users to target databases only - Redact PII in Connect SMTs or Tinybird landing schemas if topics are dirtier than analytics needs ## **Tinybird when the topic is product data** If the Confluent topic exists to power [user-facing analytics](https://www.tinybird.co/blog/user-facing-analytics), consuming into a bare cluster still leaves you building an API layer. Tinybird's Kafka connector reads Confluent Cloud (SASL_SSL), lands in managed ClickHouse®, and publishes SQL as HTTP: ```sql NODE events_by_type SQL > SELECT event_type, count() AS events, uniq(user_id) AS users FROM events WHERE event_time >= {{ DateTime(start_time, '2026-08-01 00:00:00') }} GROUP BY event_type ORDER BY events DESC TYPE endpoint ``` Example endpoint call: ```bash curl -s \ -H "Authorization: Bearer YOUR_TINYBIRD_TOKEN" \ "https://api.tinybird.co/v0/pipes/events_by_type.json?start_time=2026-08-01%2000:00:00" ``` Confluent stays the event backbone. Tinybird is the analytical consumer and serving path. That split matches teams that already standardized on Confluent for service traffic and do not want a second on-call for Kafka consumers inside the database. For [streaming data](https://www.ibm.com/think/topics/streaming-data) that also needs sub-100ms API reads, this is usually cheaper than Connect + self-managed ClickHouse® + a custom API tier. ## **Failure modes and fixes** 1. **Empty table, zero lag.** Wrong topic, wrong group ACL, or consumers not started (detached Kafka table / MV). 2. **Lag grows forever.** Undersized insert batches, MV errors on bad rows, or ClickHouse® disk pressure. Check error logs before adding consumers. 3. **Duplicate events after redeploy.** At-least-once plus missing idempotency. Add stable `event_id` to `ORDER BY` or use `ReplacingMergeTree`. 4. **Exploding parts.** Micro-batches from Connect. Increase linger/batch size. 5. **Schema poison pill.** One bad message stalls a partition. Enable skip/DLQ; fix the producer; backfill. ## **Bring-up checklist** - [ ] Dedicated Confluent API key with least privilege - [ ] Network path proven with a throwaway consumer before DDL - [ ] Partition count ≥ consumer concurrency - [ ] Destination `ORDER BY` uses event time, not ingest time alone - [ ] DLQ or error table for poison messages - [ ] Lag alert on the consumer group with a numeric budget - [ ] Secret rotation owner named - [ ] Staging load test at ≥ peak produce rate - [ ] Document who owns Confluent vs who owns ClickHouse® / Tinybird ## **Frequently Asked Questions (FAQ)** ### **Can ClickHouse® Cloud use the Kafka engine against Confluent?** Often the blocker is network/auth, not SQL. If Cloud cannot reach brokers privately, use Connect sink or Tinybird instead of forcing the Kafka engine. ### **How many consumers should I set?** Never above the topic partition count. Start at 1–2, raise only when lag is CPU/IO bound on the consumer, not when dashboards feel slow (that is usually query design). ### **Do I need exactly-once?** ClickHouse® analytical sinks are usually **at-least-once**. Design `ReplacingMergeTree` or idempotent event ids if producers redeliver. Chasing transactional sink semantics across Kafka + ClickHouse® is rarely worth it for product analytics. ### **Where should Schema Registry live in the path?** Decode before ClickHouse® if you want stable typed columns. Or store opaque payloads and promote columns as query needs appear. ### **Should I compact the topic if ClickHouse® stores the data?** Compaction is for latest-state consumers. Analytics often wants history. Keep retention long enough for reprocessing, and let ClickHouse® hold the historical projection you query. ### **What is the fastest path to APIs on Confluent data?** Tinybird Kafka connector + Pipe endpoints. You keep Confluent as the bus and skip building a custom query service on top of raw ClickHouse®.