Building a Real-Time Data Pipeline with Apache Kafka and ClickHouse

When your analytics queries go from “how did we do last week?” to “what’s happening right now?”, your data warehouse needs to evolve. Here’s how we built a real-time pipeline handling 500K events/second with sub-second query latency.

Architecture Overview

┌──────────┐     ┌──────────┐     ┌────────────┐     ┌──────────┐
│ Services │────▶│  Kafka   │────▶│ ClickHouse │────▶│ Grafana  │
│ (protobuf)│    │ (Avro)   │     │ (MergeTree)│     │ Dashboards│
└──────────┘     └──────────┘     └────────────┘     └──────────┘
                       │
                       ▼
                 ┌──────────┐
                 │  Schema   │
                 │ Registry  │
                 └──────────┘

Why ClickHouse Over Traditional OLAP?

ClickHouse is a columnar database built for real-time analytics. Unlike Snowflake or BigQuery:

• No cold storage penalty: All data is queryable immediately
• Vectorized execution: Queries process data in blocks using SIMD instructions
• Materialized views with TO: Incrementally updated aggregations

The Kafka Connect Bridge

We used ClickHouse’s native Kafka engine for ingestion:

CREATE TABLE events_queue (
    event_time DateTime,
    event_type LowCardinality(String),
    user_id UInt64,
    properties Nested(
        key String,
        value String
    )
) ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka:9092',
         kafka_topic_list = 'raw-events',
         kafka_group_name = 'clickhouse-consumer',
         kafka_format = 'AvroConfluent',
         kafka_schema_registry_url = 'http://schema-registry:8081';

Then a materialized view to persist and transform:

CREATE MATERIALIZED VIEW events_mv TO events_store AS
SELECT
    event_time,
    event_type,
    user_id,
    visitParamExtractString(properties, 'browser') AS browser,
    visitParamExtractString(properties, 'page') AS page
FROM events_queue;

Query Performance

With a 7-day window of data (~300B rows), here are real query times:

-- Count distinct users per event type, last 1 hour
SELECT event_type, uniq(user_id) AS users
FROM events_store
WHERE event_time > now() - INTERVAL 1 HOUR
GROUP BY event_type
ORDER BY users DESC;

-- Result: ~120ms on 2M rows

Cost Comparison

Running this on our own hardware (3-node ClickHouse cluster + 3-node Kafka) costs ~$1,200/month versus ~$8,000/month for the equivalent BigQuery + Dataflow setup. The operational burden is higher, but for our scale, the tradeoff makes sense.