ClickHouse-Kafka Integration: Production Reality Guide

Executive Summary

Three integration methods exist with dramatically different operational characteristics. ClickPipes costs $0.30 per million events but works reliably. Kafka Connect is "free" but requires 3+ weeks setup time for exactly-once semantics. Table Engine offers 5ms latency but fails silently without warnings.

Critical Reality Check: Production throughput is 50-70% lower than published benchmarks due to real message sizes and network conditions.

Integration Methods Comparison

Method	Real Throughput	Latency	Failure Mode	Cost Model
ClickPipes	40-70k events/sec	15ms	Retries automatically, loses data during outages	$0.30/million events + compute ($2,100→$8,300/month typical)
Kafka Connect	20-45k events/sec	20-30ms	Backs up entire pipeline on failures	Infrastructure only (~$500-1000/month for cluster)
Table Engine	50-120k events/sec	5ms	Silent failures, no error reporting	ClickHouse/Kafka costs only

ClickPipes (Managed Connector)

Configuration Reality:

• Actually works out of box (rare in this space)
• Schema mapping through UI
• No field-level encryption support
• AWS IAM requires additional trust policies not in docs

Production Characteristics:

• Handles 40-60k events/sec sustained
• Cost scales linearly with volume
• No replay mechanism during outages
• CFO complaints about 300%+ cost increases

Kafka Connect (Open Source)

Configuration Reality:

• Requires 3 weeks for exactly-once semantics
• Version 7.2.x has memory leak with large consumer groups
• Silent message dropping on schema changes
• Endpoint verification too strict by default

Critical Settings:

{
  "errors.tolerance": "all",
  "errors.deadletterqueue.topic.name": "clickhouse-connect-errors",
  "errors.deadletterqueue.context.headers.enable": true,
  "batch.size": 5000,
  "ssl.endpoint.identification.algorithm": ""
}

Production Characteristics:

• 25-35k events/sec with proper tuning
• OOM crashes with batch.size >50,000 for 2KB messages
• Consumer lag monitoring essential
• Backs up 6-hour queues from 20-minute outages

Table Engine (Native ClickHouse)

Configuration Reality:

• Fastest option when working
• Restarts break materialized views silently
• ClickHouse 23.8+ added error logging (earlier versions fail silently)
• kafka_max_block_size defaults (65,536) cause memory issues with multiple partitions

Critical Settings:

CREATE TABLE kafka_queue (...) ENGINE = Kafka
SETTINGS kafka_broker_list = 'localhost:9092',
         kafka_max_block_size = 10000,    -- Lower from default
         kafka_poll_timeout_ms = 7000;    -- Higher than default

Production Characteristics:

• Sub-10ms latency when operational
• Silent failures on service restarts
• Requires manual materialized view recreation
• Performance degrades with small parts (<10,000 rows)

Critical Failure Scenarios

Schema Evolution Disasters

What Breaks:

• ClickHouse rejects nullable field additions without defaults
• Connect silently inserts NULL for new fields
• Field type changes require full reprocessing
• Schema registry integration lies about compatibility

Consequence: 3-week data gaps discovered during board meetings with revenue chart discrepancies

Prevention:

• Always use nullable fields in ClickHouse
• Handle schema evolution at application level
• Monitor consumer lag for silent drops

Memory Exhaustion Patterns

Failure Scenario 1: kafka_max_block_size × partition_count = memory explosion

• 100 partitions × 64K rows = 6.4M rows buffered in memory
• Causes OOM before disk flush

Failure Scenario 2: Kafka Connect batch sizing

• 50,000 message batches × 2KB average = OOM with 32GB heaps
• GC overhead limit exceeded errors

Production Solution:

• Partition count = CPU core count for optimal throughput
• batch.size ≤ 5,000 for 2KB+ messages
• Monitor memory usage >80% as critical threshold

Network Partition Recovery

ClickPipes: Automatic recovery, data loss during outage window
Connect: Infinite retries, pipeline backup, offset commit failures
Table Engine: Silent stop, materialized views run with zero input

Production Impact: 30-minute outages create 3-day processing backlogs

Performance Reality vs Benchmarks

Published vs Actual Throughput:

• Benchmarks use tiny JSON messages (misleading)
• 2KB real messages reduce throughput 60-70%
• Network conditions create additional 10-20% degradation

Hardware Impact Factors:

• Message size matters more than CPU/memory
• Disk I/O becomes bottleneck at scale
• Network latency compounds with volume

Partition Strategy:

• Optimal: partition_count = CPU_cores
• Example: 3→32 partitions increased throughput 20k→70k events/sec
• Over-partitioning creates small parts problem

Security Implementation Reality

SSL/TLS Configuration

Common Failure: Default endpoint verification too strict

• javax.net.ssl.SSLHandshakeException: PKIX path building failed
• Solution: Disable endpoint verification for internal networks

Field-Level Encryption

Reality Check:

• Only Connect supports proper field-level encryption
• Performance impact significant (encrypt minimum viable PII only)
• Application-level encryption before Kafka recommended
• Compliance nightmare if analytics database gets dumped to S3

IAM for AWS ClickPipes

Missing from Docs:

{
  "Effect": "Allow",
  "Action": [
    "kafka:DescribeCluster",
    "kafka:GetBootstrapBrokers", 
    "kafka:ReadData"
  ],
  "Resource": "arn:aws:kafka:region:account:cluster/*"
}

Plus assumable role trust policy (commonly missed).

Production Incident Playbook

Consumer Lag Explosion

1. Check ClickHouse responsiveness: SELECT 1
2. Verify disk space: 90% full = system death
3. Identify small parts: SELECT count() FROM system.parts WHERE rows < 1000
4. Optimize if needed: OPTIMIZE TABLE your_table FINAL (long operation)
5. Scale partitions if persistent lag

Silent Data Stoppage (Table Engine)

1. Verify materialized view: SHOW CREATE TABLE your_mv
2. Check consumer status: SELECT * FROM system.kafka_consumers
3. Look for exceptions: exceptions_count > 0 indicates failure
4. Nuclear option: Drop/recreate kafka table
5. Last resort: Restart ClickHouse service

Connect Task Failures

1. Check logs: docker logs connect-worker-1
2. OOM indicator: "Connector task is being killed"
3. Reduce batch.size by 50%
4. Rolling restart workers

Nuclear Recovery Option

# When everything is broken
docker-compose down -v
kafka-consumer-groups --bootstrap-server localhost:9092 \
  --reset-offsets --group your-consumer-group \
  --to-latest --all-topics --execute
docker-compose up -d

Trade-off: Data loss vs operational recovery (acceptable at 3 AM).

Resource Requirements & Costs

Infrastructure Sizing

Minimum Viable:

• Connect workers: 2 CPU, 4GB RAM each
• ClickHouse: 4 CPU, 16GB RAM minimum
• Kafka: Match partition count to CPU cores

Scaling Indicators:

• CPU utilization >50% consistently = scale up needed
• Memory usage >80% = immediate scaling required
• Consumer lag >10,000 messages = capacity issue

Cost Optimization

Compression Impact:

• Enable LZ4 compression everywhere
• 30-40% storage savings
• Sometimes improves performance due to reduced I/O

Right-Sizing Warnings:

• Don't over-optimize for average load
• Black Friday traffic can spike 300% unexpectedly
• $200/month savings not worth outage during peak sales

Monitoring & Alerting

Critical Metrics

Consumer Lag: >10,000 messages (adjust for volume)
Insert Rate: <50% of baseline for >5 minutes
Error Rate: >1% (0.1% too sensitive, causes false alarms)
Memory Usage: >80% on workers

ClickHouse-Specific Queries

-- Table Engine consumer health
SELECT table, partition_id, current_offset, exceptions_count, last_exception_time
FROM system.kafka_consumers 
WHERE table = 'your_kafka_table'
ORDER BY last_exception_time DESC;

-- Small parts detection (performance killer)
SELECT table, count() as parts_count, avg(rows) as avg_rows_per_part
FROM system.parts 
WHERE active = 1 
GROUP BY table 
HAVING avg_rows_per_part < 10000;

Decision Framework

When to Choose ClickPipes

• Company values time over money
• Already on ClickHouse Cloud
• Team lacks dedicated DevOps expertise
• Budget >$5k/month for data pipeline acceptable

When to Choose Kafka Connect

• Need exactly-once semantics
• Require data transformations
• Have experienced operations team
• Multiple destination systems planned

When to Choose Table Engine

• Sub-10ms latency requirement critical
• Team understands ClickHouse internals deeply
• Can maintain custom monitoring/alerting
• Acceptable to trade reliability for performance

Disaster Recovery

Backup Strategy

ClickHouse: Fast native backups
Critical Gap: Kafka consumer offset management during restore
Recovery Process:

1. Restore ClickHouse from backup
2. Reset Kafka offsets to backup timestamp or latest
3. Accept data gap or reprocess (choose based on impact)

Multi-Region Reality

MirrorMaker2: Budget 2-3 weeks for tuning
Common Failure: Replicates internal topics creating data loops
Recommendation: Application-level replication simpler than Kafka-level

Tested Recovery Procedures

Warning: Backup scripts that work in staging often fail in production due to path differences
Requirement: Regular disaster recovery testing (not just backup verification)
Reality: "Tested" backups missing materialized views common failure mode

Common Implementation Mistakes

Schema Design

Wrong: Trust timestamp ordering for deduplication
Right: Use proper sequence IDs with ReplacingMergeTree
Reason: Kafka delivery timestamps unreliable under load

Performance Assumptions

Wrong: Rely on benchmark throughput numbers
Right: Test with actual message sizes and network conditions
Reality: 2KB messages vs 10-byte test payloads = 60-70% throughput difference

Monitoring Blind Spots

Wrong: Only monitor successful inserts
Right: Monitor consumer lag, error rates, and silent failures
Critical: Table Engine fails silently - requires proactive monitoring

Resource Planning

Wrong: Size for average load
Right: Plan for 3x traffic spikes during events
Example: Black Friday analytics outage due to "optimized" instance sizing