Real-Time Analytics for Consumer Goods: 4 Proven ClickHouse + Kafka Patterns

Real-time analytics for consumer goods is now a two-component problem: Apache Kafka 4.3 as the event backbone and ClickHouse 26.8 LTS as the serving engine. This paper is the design we put in front of CIOs, CTOs, platform architects and data scientists at consumer packaged goods (CPG) companies who need sell-through, promotion lift and out-of-stock risk visible within seconds of a till transaction, not the next morning. Every DDL and query below was executed against a ClickHouse 26.7 engine build in our lab; the output shown is real, the data is synthetic and labelled as such.

The short version for the executive reader: the platform is open source end to end, it fits on commodity hardware, and the operational risk is concentrated in three places you can measure: part counts on the raw table, consumer lag in system.kafka_consumers, and the merge backlog in system.merges. Everything else in real-time analytics for consumer goods is schema design, and schema design is where most real-time analytics programmes in this sector go wrong.

Why real-time analytics for consumer goods is a different workload

A consumer goods manufacturer rarely owns the point of sale. Sell-through data arrives from dozens of retailers, in different cadences (EDI 852 batches from one, near-real-time APIs from another), keyed by retailer-specific store and item identifiers. Direct-to-consumer channels add clickstream and orders. The ERP produces shipments and inventory movements. Trade promotion management emits promotion windows and funding. The real-time analytics questions cut across all of it: is the BOGO in this retailer’s north-east region actually lifting units, which stores will be out of stock of a hero SKU before the next delivery, and what is the realised discount versus list price at the moment of sale.

Three properties make real-time analytics for consumer goods harder than a generic event pipeline. First, the event volume is bursty and seasonal; a promotion week or a heat wave can multiply the POS event rate by an order of magnitude with no warning. Second, the data is late-arriving and revisable: retailers restate, returns flow days later, and the CDC stream from the ERP corrects inventory positions constantly. Third, the consumers are mixed: dashboards that need sub-second response for a category manager, embedded portals exposed to retailer partners under quotas, and data scientists who want raw events in a notebook without an extract job.

This shapes the engine choice for real-time analytics. The serving layer must absorb high insert rates while keeping query latency stable, handle upserts on inventory state without a locking update path, and expose a SQL surface rich enough for data science. ClickHouse’s MergeTree family does exactly this, and since 26.x the gaps that used to hurt consumer goods real-time analytics teams (JSON handling, atomic materialized-view backfill, lightweight updates) have closed.

Reference architecture for real-time analytics for consumer goods

The real-time analytics architecture in Figure 1 is deliberately boring. Kafka is the single ingress. ClickHouse is the single serving store. There is no separate stream processor between them for the base real-time analytics use case; the aggregation that a Flink job would do is done by ClickHouse materialized views at insert time, which is cheaper to operate and keeps one fewer system in the on-call rotation.

We add Flink or Kafka Streams only when a customer needs stateful joins across topics before the data lands, for example enriching POS events with a promotion calendar that changes intra-day. Kafka Streams in 4.3 uses the server-side rebalance protocol (KIP-1071, GA in 4.2), which removes most of the rebalance storms that made Streams painful on large store fleets.

Version choices and why they matter

Community versus vendor note for real-time analytics buyers: everything above is Apache-licensed or Apache-2.0 open source and runs self-managed on any cloud or on premises. ClickHouse Cloud offers ClickPipes as a managed Kafka ingestion path with its own SharedMergeTree semantics; that is a legitimate option, but it is not what this paper describes and several settings below (the Kafka table engine itself, kafka_num_consumers) do not apply there.

Kafka 4.3 topic design for consumer goods real-time analytics streams

Topic partitioning is the first decision that determines whether a real-time analytics platform scales. For POS events we key by store_id so that a store’s events stay ordered within a partition and a materialized view computing per-store running totals sees them in sequence. For inventory CDC we key by the (store, SKU) pair. Partition counts are set for the peak, not the average: a 12-partition topic that runs at 15 percent utilisation in a normal week is the correct size if promotion weeks hit 100 percent.

# Kafka 4.3.x, KRaft mode. Topics for the real-time analytics pipeline. --bootstrap-server is the standardised argument across tools (KIP-1147).
bin/kafka-topics.sh --bootstrap-server ${KAFKA_BOOTSTRAP} --create \
  --topic pos-sales \
  --partitions 12 \
  --replication-factor 3 \
  --config min.insync.replicas=2 \
  --config retention.ms=604800000 \
  --config compression.type=zstd \
  --config cleanup.policy=delete

bin/kafka-topics.sh --bootstrap-server ${KAFKA_BOOTSTRAP} --create \
  --topic inventory-cdc \
  --partitions 12 \
  --replication-factor 3 \
  --config min.insync.replicas=2 \
  --config cleanup.policy=compact \
  --config min.compaction.lag.ms=3600000

# Verify
bin/kafka-topics.sh --bootstrap-server ${KAFKA_BOOTSTRAP} --describe --topic pos-sales

Seven days of retention on pos-sales is the real-time analytics replay window. If ClickHouse has to be rebuilt or a materialized view redefined, seven days of raw events can be re-consumed from Kafka with a new consumer group name rather than re-requested from retailers. Log compaction on inventory-cdc keeps the latest state per (store, SKU) key indefinitely, which is what a ReplacingMergeTree consumer needs on a cold start.

The event contract is plain JSON, one object per line. Avro or Protobuf with a schema registry is the right choice once more than two teams produce into the topic; ClickHouse’s Kafka engine reads AvroConfluent and ProtobufSingle natively, so the choice does not change the ClickHouse side of the design.

{
  "event_id":   "9f1c2b7e-3d1a-4f0b-9a2f-7c1e5b6d8a90",
  "event_time": "2026-09-04T08:21:44.019Z",
  "retailer_id": "tesco",
  "store_id":   "s239",
  "sku":        "SKU42",
  "quantity":   2,
  "unit_price": 2.70,
  "promo_id":   "PROMO-Q3-BOGO",
  "channel":    "grocery"
}

# Producer sketch for the real-time analytics feed (confluent-kafka 2.x against Kafka 4.3). Idempotent producer, zstd, keyed by store_id.
from confluent_kafka import Producer
import json, os

p = Producer({
    "bootstrap.servers": os.environ["KAFKA_BOOTSTRAP"],
    "enable.idempotence": True,
    "acks": "all",
    "compression.type": "zstd",
    "linger.ms": 20,
    "batch.size": 262144,
})

def emit(event: dict) -> None:
    p.produce(
        topic="pos-sales",
        key=event["store_id"].encode(),
        value=json.dumps(event, separators=(",", ":")).encode(),
        on_delivery=lambda err, msg: err and print("delivery failed:", err),
    )
    p.poll(0)

# ... emit() per POS transaction, then p.flush() on shutdown

ClickHouse 26.8 schema for real-time analytics for consumer goods

The Kafka table engine is the real-time analytics consumer. It holds no data; it exposes the topic as a stream that materialized views read from. Every parameter is declared explicitly because defaults have changed across releases and a customer-facing design should not depend on them.

CREATE TABLE cpg.pos_sales_kafka
(
    event_id      String,
    event_time    DateTime64(3, 'UTC'),
    retailer_id   LowCardinality(String),
    store_id      String,
    sku           String,
    quantity      Int32,
    unit_price    Decimal(10, 2),
    promo_id      Nullable(String),
    channel       LowCardinality(String)
)
ENGINE = Kafka
SETTINGS
    kafka_broker_list        = '${KAFKA_BOOTSTRAP}',
    kafka_topic_list         = 'pos-sales',
    kafka_group_name         = 'clickhouse-pos-sales-v1',
    kafka_format             = 'JSONEachRow',
    kafka_num_consumers      = 4,
    kafka_thread_per_consumer = 1,
    kafka_max_block_size     = 65536,
    kafka_flush_interval_ms  = 1000,
    kafka_poll_timeout_ms    = 500,
    kafka_handle_error_mode  = 'stream',
    kafka_skip_broken_messages = 0;

Four settings carry the throughput and the safety of the real-time analytics design. kafka_num_consumers = 4 with kafka_thread_per_consumer = 1 gives four independent consumer threads per ClickHouse node; across three nodes that is twelve consumers for twelve partitions, which is the ceiling (more consumers than partitions sit idle).

kafka_max_block_size and kafka_flush_interval_ms together bound the insert block: whichever is hit first flushes, so a quiet night still lands rows within a second and a promotion peak forms 64K-row blocks that MergeTree handles efficiently. kafka_handle_error_mode = 'stream' is the difference between a poison message stalling the consumer group and the message being routed to a dead-letter table via the _error and _raw_message virtual columns.

The raw landing table for real-time analytics persists the Kafka coordinates. We have used _partition and _offset more than once to prove to a retailer’s data team exactly which message a disputed figure came from.

CREATE TABLE cpg.pos_sales
(
    event_id        String,
    event_time      DateTime64(3, 'UTC'),
    event_date      Date MATERIALIZED toDate(event_time),
    retailer_id     LowCardinality(String),
    store_id        String,
    sku             String,
    quantity        Int32,
    unit_price      Decimal(10, 2),
    gross_value     Decimal(14, 2) MATERIALIZED quantity * unit_price,
    promo_id        Nullable(String),
    channel         LowCardinality(String),
    kafka_topic     LowCardinality(String),
    kafka_partition UInt32,
    kafka_offset    UInt64,
    INDEX idx_sku sku TYPE bloom_filter(0.01) GRANULARITY 4
)
ENGINE = MergeTree
PARTITION BY toYYYYMM(event_date)
ORDER BY (retailer_id, store_id, sku, event_time)
TTL event_date + INTERVAL 730 DAY
SETTINGS index_granularity = 8192;

CREATE MATERIALIZED VIEW cpg.mv_pos_sales_raw TO cpg.pos_sales AS
SELECT
    event_id,
    event_time,
    retailer_id,
    store_id,
    sku,
    quantity,
    unit_price,
    promo_id,
    channel,
    _topic     AS kafka_topic,
    _partition AS kafka_partition,
    _offset    AS kafka_offset
FROM cpg.pos_sales_kafka;

In a production real-time analytics cluster this table is ReplicatedMergeTree('/clickhouse/tables/{shard}/cpg/pos_sales', '{replica}') with the Kafka engine table created on every replica; each replica’s consumers join the same consumer group and Kafka assigns them disjoint partitions, so ingestion parallelises across the cluster without a Distributed table in the write path. The sort key leads with retailer_id and store_id because the dominant query patterns filter on them; sku gets a bloom filter skip index for the queries that filter on SKU alone.

Pre-aggregation with AggregatingMergeTree

Hourly sell-through by retailer, SKU and promotion is the real-time analytics table every dashboard hits. Computing it at insert time via a second materialized view chained off cpg.pos_sales means the query reads tens of thousands of rows instead of hundreds of millions. Note the coalesce(promo_id, ''): a nullable column cannot sit in a MergeTree sort key without allow_nullable_key, and we would rather encode the absence of a promotion explicitly. The Decimal accumulator must be Decimal(38, 2) because sum() over Decimal(10, 2) widens to 38 digits; ClickHouse rejects the DDL otherwise, which is the kind of thing a lab pass catches before a customer does.

CREATE TABLE cpg.sell_through_hourly
(
    hour          DateTime('UTC'),
    retailer_id   LowCardinality(String),
    sku           String,
    promo_id      LowCardinality(String),
    units         SimpleAggregateFunction(sum, Int64),
    gross_value   SimpleAggregateFunction(sum, Decimal(38, 2)),
    stores        AggregateFunction(uniq, String),
    baskets       AggregateFunction(uniq, String)
)
ENGINE = AggregatingMergeTree
PARTITION BY toYYYYMM(hour)
ORDER BY (retailer_id, sku, promo_id, hour)
SETTINGS index_granularity = 8192;

CREATE MATERIALIZED VIEW cpg.mv_sell_through_hourly TO cpg.sell_through_hourly AS
SELECT
    toStartOfHour(event_time)                        AS hour,
    retailer_id,
    sku,
    coalesce(promo_id, '')                           AS promo_id,
    toInt64(sum(quantity))                           AS units,
    toDecimal128(sum(quantity * unit_price), 2)      AS gross_value,
    uniqState(store_id)                              AS stores,
    uniqState(event_id)                              AS baskets
FROM cpg.pos_sales
GROUP BY hour, retailer_id, sku, promo_id;

Since 26.8, CREATE MATERIALIZED VIEW ... POPULATE is atomic, a real-time analytics operability gain, with respect to concurrent inserts, so backfilling this aggregate from existing raw data no longer requires pausing the Kafka consumers. Before 26.8 the safe procedure was detach the Kafka table, create the view with POPULATE, re-attach; keep that procedure in your runbook if any cluster in the estate is still on 25.x.

Inventory state and price history

Inventory in real-time analytics is state, not events. The Debezium CDC stream from the ERP lands in a ReplacingMergeTree keyed by (store, SKU) with the CDC timestamp as the version column; reads use FINAL or the argMax pattern. Price history is a slowly changing dimension consumed with ASOF JOIN.

CREATE TABLE cpg.store_inventory
(
    store_id      String,
    sku           String,
    on_hand       Int32,
    updated_at    DateTime64(3, 'UTC'),
    source        LowCardinality(String)
)
ENGINE = ReplacingMergeTree(updated_at)
ORDER BY (store_id, sku)
SETTINGS index_granularity = 8192;

CREATE TABLE cpg.sku_price_history
(
    retailer_id   LowCardinality(String),
    sku           String,
    valid_from    DateTime('UTC'),
    list_price    Decimal(10, 2)
)
ENGINE = MergeTree
ORDER BY (retailer_id, sku, valid_from);

For entity state that arrives as partial JSON patches rather than full rows (a common shape from product-information systems), 26.8 adds mergedJSONPatch(), an aggregate that folds RFC 7396 patches into the current document inside an AggregatingMergeTree. We have not yet run it under production load, so it is noted here as a candidate rather than a recommendation.

Kafka Connect sink as the alternative real-time analytics ingestion path

The Kafka table engine puts the real-time analytics consumer inside the database, which is operationally simple but couples consumer scaling to ClickHouse node count and makes exactly-once delivery your problem to reason about (the engine is at-least-once; the materialized view’s insert deduplication handles retries of identical blocks). The official clickhouse-kafka-connect sink moves the consumer into Kafka Connect, adds exactly-once semantics backed by a KeeperMap state table, and since 1.3.10 propagates schema changes across a cluster with clusterName. Choose it when the Kafka platform team owns ingestion, when exactly-once is a contractual requirement, or when ClickHouse nodes should not carry consumer threads.

{
  "name": "clickhouse-pos-sales-sink",
  "config": {
    "connector.class": "com.clickhouse.kafka.connect.ClickHouseSinkConnector",
    "tasks.max": "12",
    "topics": "pos-sales",
    "hostname": "${CH_HOST}",
    "port": "8443",
    "ssl": "true",
    "database": "cpg",
    "username": "${CH_USER}",
    "password": "${CH_PASSWORD}",
    "topic2TableMap": "pos-sales=pos_sales",
    "exactlyOnce": "true",
    "clusterName": "cpg_cluster",
    "clickhouseClientInsertTimeoutMs": "60000",
    "errors.tolerance": "all",
    "errors.deadletterqueue.topic.name": "pos-sales-dlq",
    "value.converter": "org.apache.kafka.connect.json.JsonConverter",
    "value.converter.schemas.enable": "false",
    "key.converter": "org.apache.kafka.connect.storage.StringConverter"
  }
}

Serving queries: promotion lift, out-of-stock risk, realised price

These four real-time analytics queries are the ones a category manager, a supply planner and a revenue-management analyst actually run. Output below is from the lab run against 2,000,000 synthetic POS events (four retailers, 500 stores, 200 SKUs, one promotion); the shape of the answer is what matters, the numbers are synthetic.

Real-time analytics query 1: promotion lift in the last 24 hours

SELECT
    sku,
    sumIf(units, promo_id = 'PROMO-Q3-BOGO')        AS promo_units,
    sumIf(units, promo_id = '')                     AS base_units,
    round(promo_units / nullIf(base_units, 0), 2)   AS lift_ratio,
    uniqMerge(stores)                               AS stores_selling
FROM cpg.sell_through_hourly
WHERE hour >= now() - INTERVAL 24 HOUR
GROUP BY sku
ORDER BY lift_ratio DESC
LIMIT 5;

┌─sku────┬─promo_units─┬─base_units─┬─lift_ratio─┬─stores_selling─┐
1. │ SKU89  │       15002 │      19088 │       0.79 │            500 │
2. │ SKU152 │       15064 │      19178 │       0.79 │            500 │
3. │ SKU178 │       15120 │      19462 │       0.78 │            500 │
4. │ SKU34  │       14828 │      19095 │       0.78 │            500 │
5. │ SKU75  │       15062 │      19245 │       0.78 │            500 │
   └────────┴─────────────┴────────────┴────────────┴────────────────┘

The real-time analytics query touches 40,000 pre-aggregated rows, not two million raw events, and uniqMerge finalises the HyperLogLog state that the materialized view accumulated. On a real estate the lift ratio would be normalised against a pre-promotion baseline window; the synthetic generator here applied a flat one-third promotion share, hence the uniform ratios.

Real-time analytics query 2: out-of-stock risk from live velocity and CDC inventory

WITH velocity AS
(
    SELECT
        store_id,
        sku,
        sum(quantity) / 4 AS units_per_hour
    FROM cpg.pos_sales
    WHERE event_time >= now() - INTERVAL 4 HOUR
    GROUP BY store_id, sku
)
SELECT
    v.store_id,
    v.sku,
    i.on_hand,
    round(v.units_per_hour, 2)                          AS units_per_hour,
    round(i.on_hand / nullIf(v.units_per_hour, 0), 1)   AS hours_of_cover
FROM velocity AS v
INNER JOIN cpg.store_inventory AS i FINAL
    ON i.store_id = v.store_id AND i.sku = v.sku
WHERE hours_of_cover < 6
ORDER BY hours_of_cover ASC
LIMIT 5;

┌─store_id─┬─sku────┬─on_hand─┬─units_per_hour─┬─hours_of_cover─┐
1. │ s212     │ SKU196 │       0 │           2.25 │              0 │
2. │ s404     │ SKU85  │       0 │              6 │              0 │
3. │ s154     │ SKU188 │       0 │            2.5 │              0 │
4. │ s11      │ SKU178 │       0 │            5.5 │              0 │
5. │ s475     │ SKU0   │       0 │            3.5 │              0 │
   └──────────┴────────┴─────────┴────────────────┴────────────────┘

FINAL on a ReplacingMergeTree forces de-duplication at read time; with a (store, SKU) key of 100,000 rows this is cheap. On a 50-million-row inventory table it is not, and the correct pattern is argMax(on_hand, updated_at) with GROUP BY store_id, sku, or scheduling OPTIMIZE ... FINAL on the partition during a quiet window. This query is the seed of the alerting consumer in Figure 1: run it every minute, write breaches to a Kafka topic consumed by a share group (KIP-932) so several replenishment workers can acknowledge alerts individually.

Real-time analytics query 3: realised discount versus list price at the moment of sale

SELECT
    s.retailer_id,
    s.sku,
    s.event_time,
    s.unit_price                                  AS sold_at,
    p.list_price,
    round(1 - s.unit_price / p.list_price, 3)     AS realised_discount
FROM cpg.pos_sales AS s
ASOF LEFT JOIN cpg.sku_price_history AS p
    ON p.retailer_id = s.retailer_id
   AND p.sku = s.sku
   AND p.valid_from <= toDateTime(s.event_time)
WHERE s.promo_id IS NOT NULL
ORDER BY s.event_time DESC
LIMIT 3;

┌─retailer_id─┬─sku────┬──────────────event_time─┬─sold_at─┬─list_price─┬─realised_discount─┐
1. │ target      │ SKU87  │ 2026-09-04 08:22:25.173 │     2.7 │       4.44 │               0.4 │
2. │ target      │ SKU37  │ 2026-09-04 08:22:24.110 │    2.29 │       3.44 │              0.34 │
3. │ target      │ SKU198 │ 2026-09-04 08:22:23.788 │    4.02 │       6.66 │               0.4 │
   └─────────────┴────────┴─────────────────────────┴─────────┴────────────┴───────────────────┘

ASOF JOIN is the single most under-used ClickHouse feature in consumer goods real-time analytics. It answers ‘what was the list price in force when this unit sold’ without a date-range self-join, and it is what makes realised-price and price-elasticity work tractable on raw events.

Real-time analytics query 4: top stores per retailer with LIMIT BY

SELECT
    retailer_id,
    store_id,
    sum(quantity) AS units
FROM cpg.pos_sales
WHERE event_date >= today() - 1
GROUP BY retailer_id, store_id
ORDER BY retailer_id, units DESC
LIMIT 2 BY retailer_id;

┌─retailer_id─┬─store_id─┬─units─┐
1. │ kroger      │ s431     │  3936 │
2. │ kroger      │ s414     │  3876 │
3. │ target      │ s490     │  3874 │
4. │ target      │ s195     │  3857 │
5. │ tesco       │ s87      │  3904 │
6. │ tesco       │ s55      │  3846 │
7. │ walmart     │ s17      │  3910 │
8. │ walmart     │ s50      │  3780 │
   └─────────────┴──────────┴───────┘

Proving the real-time analytics sort key with EXPLAIN

Every real-time analytics serving query in a customer design gets an EXPLAIN indexes = 1 before sign-off. This one shows the primary key pruning 245 granules to 63 on a (retailer, SKU) filter even though sku is third in the sort key; the bloom filter then confirms all 63 candidate granules. If the primary-key line had read 245/245, the sort key would be wrong for the workload and no amount of hardware would fix it.

EXPLAIN indexes = 1
SELECT sum(quantity)
FROM cpg.pos_sales
WHERE retailer_id = 'tesco' AND sku = 'SKU42';

Aggregating
└──ReadFromMergeTree (cpg.pos_sales)
      Parts: 2 | Granules: 63
      Prewhere filter column:  retailer_id = 'tesco' AND sku = 'SKU42'
      Indexes:
        PrimaryKey
          Keys: retailer_id, sku
          Condition: and((sku in ['SKU42', 'SKU42']), (retailer_id in ['tesco', 'tesco']))
          Parts: 2/2
          Granules: 63/245
          Search Algorithm: generic exclusion search
        Skip
          Name: idx_sku
          Description: bloom_filter GRANULARITY 4
          Parts: 2/2
          Granules: 63/63
... (Min-Max and Partition index lines trimmed; both matched all parts)

Operating real-time analytics for consumer goods: what to measure

A real-time analytics platform fails in predictable ways, and each has a system table that shows it before the dashboard does. These are the four checks in our managed-service alert set for this architecture.

-- Real-time analytics health checks, ClickHouse 26.8
-- 1. Consumer health per Kafka engine table (lag, errors, last poll)
SELECT
    database,
    table,
    consumer_id,
    assignments.partition_id   AS partitions,
    assignments.current_offset AS offsets,
    num_messages_read,
    last_poll_time,
    num_rebalance_revocations,
    exceptions.text            AS recent_exceptions
FROM system.kafka_consumers
WHERE database = 'cpg';

-- 2. Part pressure on the raw table (alert at 150 active parts per partition; parts_to_throw_insert default is 3000)
SELECT
    table,
    partition,
    count()                                   AS active_parts,
    sum(rows)                                 AS rows,
    formatReadableSize(sum(bytes_on_disk))    AS on_disk
FROM system.parts
WHERE database = 'cpg' AND active
GROUP BY table, partition
ORDER BY active_parts DESC
LIMIT 10;

-- 3. Merge backlog
SELECT
    table,
    count()                    AS running_merges,
    max(elapsed)               AS longest_seconds,
    sum(total_size_bytes_compressed) AS bytes_in_flight
FROM system.merges
WHERE database = 'cpg'
GROUP BY table;

-- 4. End-to-end real-time analytics lag: produce time in the event vs insert time in ClickHouse (p95 over 5 min)
SELECT
    quantile(0.95)(dateDiff('millisecond', event_time, _inserted_at)) AS p95_lag_ms
FROM
(
    SELECT event_time, now64(3) AS _inserted_at
    FROM cpg.pos_sales
    WHERE event_time >= now() - INTERVAL 5 MINUTE
);

The lag query above is the simplified form; in production we add an ingested_at DateTime64(3) DEFAULT now64(3) column to cpg.pos_sales so the measurement survives a restart. The lab output for check 2 after the two-million-row load was two active parts and 48.5 MiB on disk for pos_sales, one part each for the aggregate and state tables.

The 26.8 change that will bite: max_insert_threads = auto

ClickHouse 26.8 changes a default that matters for real-time analytics ingestion: max_insert_threads moves from single-threaded to auto. Each insert thread writes its own part, so an INSERT ... SELECT backfill that used to create one part per block now creates several. Kafka engine flushes are not affected (each consumer thread already writes its own part), but batch backfills and the Kafka Connect sink’s inserts can push part counts toward parts_to_delay_insert (default 1000) on a busy partition. Measure with check 2 above after upgrading; if merge throughput cannot keep up, pin the setting explicitly.

Strategy: what CIOs and CTOs should decide, and what they should not

The technical design for real-time analytics above is the easy part. The decisions that determine whether real-time analytics for consumer goods pays back are organisational, and we see the same four recur across engagements.

Own the event contract, not the vendor. The schema of pos-sales is the asset. It outlives the BI tool, the cloud provider and the analytics engine. Publish it with a schema registry, version it, and treat a change to it as a governed release. This is also the lock-in defence: Kafka and ClickHouse are both replaceable in principle because the contract is yours.

Set latency targets as SLOs with an error budget, not as aspirations. ‘Real-time analytics’ means nothing in a contract. ‘p95 event-to-dashboard lag under five seconds, measured from producer timestamp to ingested_at, 99.5 percent of five-minute windows per month’ is something engineering can build for and operations can be paged on. The measurement query is in the section above; the SLO belongs in the platform charter.

Decide the build-versus-managed line explicitly. Self-managed ClickHouse and Kafka on Kubernetes or bare metal is the lowest unit cost for real-time analytics at scale and the design in this paper assumes it. ClickHouse Cloud with ClickPipes, Confluent Cloud, or a managed-services partner running the open-source stack in your VPC each move the operational burden and the cost curve differently. What matters is that the decision is made once, with the SLO and the replay requirement in front of the people making it, rather than defaulting to whatever the pilot team used.

Fund the data-science on-ramp from day one. The fastest value we have seen from real-time analytics in this sector did not come from dashboards; it came from a demand-planning team pointing a notebook at the raw events table within the first month. If the platform only serves BI, the business case is a reporting upgrade. If it serves forecasting, pricing and allocation models, it is a margin programme.

For data scientists: the same real-time analytics tables, no extract

ClickHouse’s HTTP and native interfaces, plus chDB (the embedded engine, available as a Python package and since 26.8 also in WebAssembly), mean a data scientist can query the real-time analytics serving tables from a notebook, or run the exact same SQL against a local Parquet export for offline experimentation. The lab in this paper was executed through chDB. A feature set for a per-SKU demand model is one query away:

import clickhouse_connect
import pandas as pd

client = clickhouse_connect.get_client(
    host=os.environ["CH_HOST"], port=8443, secure=True,
    username=os.environ["CH_USER"], password=os.environ["CH_PASSWORD"],
)

features = client.query_df('''
    SELECT
        toDate(hour)                                    AS day,
        retailer_id,
        sku,
        sum(units)                                      AS units,
        sum(gross_value)                                AS gross_value,
        uniqMerge(stores)                               AS stores_selling,
        countIf(promo_id != '')                         AS promo_hours,
        lagInFrame(sum(units), 7) OVER
            (PARTITION BY retailer_id, sku ORDER BY toDate(hour)) AS units_lag_7d
    FROM cpg.sell_through_hourly
    WHERE hour >= today() - INTERVAL 180 DAY
    GROUP BY day, retailer_id, sku
    ORDER BY day, retailer_id, sku
''')
# features: real-time analytics aggregates as a pandas DataFrame, ready for Prophet / LightGBM / whatever the team standardises on

Quotas and row policies on the ClickHouse side of the real-time analytics platform (CREATE QUOTA, CREATE ROW POLICY ... USING retailer_id = currentUser()) let the same tables serve an embedded retailer-facing portal without a second copy of the data, which is a governance point CIOs tend to care about more than the engineers expect.

Scope, version boundaries and what this paper does not claim

The lab that produced the output above ran on a ClickHouse 26.7.2.1 engine build embedded via chDB 4.3, not on a 26.8 server cluster, and the Kafka engine table was substituted with a Null engine of identical schema because the embedded engine does not carry librdkafka; every MergeTree, materialized view, aggregate and join shown is real and reproducible, the Kafka engine DDL is validated against the 26.8 documentation and our production estates but not executed in this lab.

No real-time analytics throughput or latency figures are claimed; the roadmap timings in Figure 3 are illustrative. The Kafka 4.3 and 4.2 features referenced are cited from the Apache release announcements. Test everything in a staging environment before applying it to production, and treat the Kafka retention window as part of your DR posture, because it is.

Working with ChistaDATA on real-time analytics for consumer goods

ChistaDATA designs, builds and operates real-time analytics for consumer goods companies on 100 percent open-source ClickHouse and Kafka, with 24×7 consultative support and managed services and no vendor lock-in. If you are evaluating this architecture, the useful first conversation is a two-hour review of your event sources and the three queries your business would run first; we can usually tell from that whether the Kafka engine or the Connect sink is right for your real-time analytics platform and what the sort keys should be. See our ClickHouse consulting, 24×7 ClickHouse support and ClickHouse managed services pages, or write to us directly.

References

ClickHouse 26.8 release call and changelog: presentations.clickhouse.com/2026-release-26.8. Apache Kafka 4.2.0 release announcement (share groups, KIP-1071 GA): kafka.apache.org. Kafka 4.3.1 status: Kafka Monthly Digest, June 2026. ClickHouse Kafka Connect sink releases: github.com/ClickHouse/clickhouse-kafka-connect. ClickHouse Kafka table engine documentation: clickhouse.com/docs.