Ship clickhouse integration confluent cloud with less lag

Confluent Cloud gives you managed Kafka: brokers, ACLs, Schema Registry, and a bill that scales with throughput. ClickHouse^® 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:

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.

bootstrap.servers = pkc-xxxxx.region.aws.confluent.cloud:9092
security.protocol = SASL_SSL
sasl.mechanisms   = PLAIN
sasl.username     = <API_KEY>
sasl.password     = <API_SECRET>

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

Principal: User:<api-key>
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.

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:

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

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):

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:

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, 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:

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:

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 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®.