Engineering Sub-Second Analytics Across 100 Million Events with ClickHouse, Kafka, and dbt

-- 1. Raw Kafka Streaming Engine Table
CREATE TABLE default.kafka_telemetry_stream (
    event_id UUID,
    tenant_id UUID,
    event_type String,
    duration_ms UInt32,
    timestamp DateTime64(3, 'UTC')
) ENGINE = Kafka
SETTINGS kafka_broker_list = 'kafka-cluster:9092',
         kafka_topic_list = 'telemetry_raw',
         kafka_group_name = 'clickhouse_telemetry_consumer',
         kafka_format = 'JSONEachRow';

-- 2. Target Columnar MergeTree Table Optimized for Aggregations
CREATE TABLE default.telemetry_events (
    tenant_id UUID,
    event_type LowCardinality(String),
    event_id UUID,
    duration_ms UInt32,
    timestamp DateTime64(3, 'UTC'),
    date Date DEFAULT toDate(timestamp)
) ENGINE = MergeTree()
PARTITION BY toYYYYMM(date)
ORDER BY (tenant_id, event_type, timestamp, event_id);

-- 3. Materialized View Bridging Kafka Stream into Columnar Storage
CREATE MATERIALIZED VIEW default.mv_kafka_telemetry TO default.telemetry_events AS
SELECT tenant_id, event_type, event_id, duration_ms, timestamp, toDate(timestamp) as date
FROM default.kafka_telemetry_stream;

SELECT
    tenant_id,
    event_type,
    count() AS total_events,
    quantile(0.50)(duration_ms) AS p50_latency,
    quantile(0.95)(duration_ms) AS p95_latency,
    quantile(0.99)(duration_ms) AS p99_latency
FROM default.telemetry_events
WHERE tenant_id = 'a3b8c1d2-4e5f-6a7b-8c9d-0e1f2a3b4c5d'
  AND timestamp >= NOW() - INTERVAL 7 DAY
GROUP BY tenant_id, event_type
ORDER BY total_events DESC;