Designing an IoT Data Pipeline with Kafka

The problem and the reader

A portfolio of buildings produces telemetry continuously: AHU supply-air temperatures and fan status every few seconds, chiller kW every minute, zone CO2 from hundreds of sensors, electricity and water meters every 15 minutes. Several systems want the same data: a time-series database for trends and dashboards, an alarm service that raises CMMS work orders, an energy analytics job in the warehouse, and perhaps a digital-twin model. Writing every reading from every gateway into each of those systems directly creates a web of point-to-point integrations that is hard to change and loses data whenever one target is down.

This guide is for data engineers and solution architects designing that pipeline. It assumes you know what Apache Kafka is (topics, partitions, offsets, consumer groups) and focuses on the IoT-specific design decisions. The protocol-level comparison is in Kafka vs MQTT; making the database writes safe under redelivery is covered in Kafka Consumer Idempotency for Sensor Streams.

Reference architecture: devices to database

1. Devices and controllers on field protocols (BACnet, Modbus) or MQTT-capable sensors.
2. Edge gateway per site: polls or subscribes to devices, maps them to asset and point IDs, records a collection time for each reading (keeping the source's event time where the device supplies one), buffers to local disk when the uplink fails, and publishes over MQTT with TLS.
3. MQTT broker: terminates thousands of device and gateway connections, authenticates them and routes by topic. MQTT is designed for constrained devices and unreliable networks; the EMQX documentation notes that Kafka clients, by contrast, need a stable network connection and more resources, which is why devices rarely talk to Kafka directly.
4. Bridge or connector: subscribes to MQTT topics and produces records to Kafka, setting the Kafka key.
5. Kafka: the durable, partitioned log. It absorbs bursts, keeps data for a retention window and lets each downstream system read at its own pace.
6. Sinks and consumers: a sink connector or consumer service per target (time-series database, alarm and rules engine, object storage or warehouse loader), each in its own consumer group.
7. Analytics: the warehouse or lakehouse joins long history with CAFM and CMMS data (assets, work orders, contracts).

Streaming (MQTT, Kafka) moves and buffers; the database stores and serves queries; analytics platforms handle long-range questions. Keep business records such as work orders in the CMMS database; if other systems need to react to them, publish change events to Kafka rather than moving the records themselves into Kafka.

Message envelope and timestamps

Agree a message envelope before you build the bridge, and version it. A practical telemetry record carries:

• Identity: site, asset tag (as used in the CAFM or CMMS asset register) and point name, or a stable point ID.
• Measurement time from the device or gateway, in UTC with an explicit offset.
• Value, unit and quality code (good, estimated, out of range, communication failure).
• Gateway ID and a gateway sequence number, which make gaps and duplicates detectable downstream.
• Schema version, or a version in the topic name such as bms.telemetry.v1.

Kafka records also have their own timestamp; the topic setting message.timestamp.type decides whether it is the producer's create time or the broker's log append time. Neither is the moment the sensor measured the value, especially after a gateway replays a buffer, so sinks should use the payload timestamp as the reading time and treat the Kafka timestamp as arrival metadata.

{
  "v": 1,
  "site": "HQ",
  "asset": "AHU-01",
  "point": "supply_air_temp",
  "ts": "2026-10-27T09:01:00Z",
  "value": 13.6,
  "unit": "degC",
  "quality": 0,
  "gw": "gw-hq-01",
  "seq": 884213
}

Topic and key design per asset

Topics by data class, not by asset. Create a small number of topics whose records share a schema, retention and set of consumers: for example bms.telemetry.v1 (high volume; 7 to 14 days of retention is an illustrative assumption, to be set from your worst sink outage and rebuild time), bms.alarms.v1 (low volume, longer retention), meters.readings.v1 and cmms.workorder-events.v1. A topic per asset or per point multiplies partitions and broker metadata for no benefit, and each new asset would need a new topic and new consumer configuration.

Key by the series whose order matters. Kafka sends records with the same key to the same partition and preserves their order there. Keying telemetry by asset tag (or by point ID) keeps each AHU's readings in sequence for consumers that compute state, such as "fan has been off for 10 minutes". Keying by site would also preserve order but concentrates a large site in one partition; keying randomly balances load but loses per-series order. Broker-side integrations expose this choice directly: the EMQX Kafka sink, for example, has a message key setting used for partitioning and message ordering, which can be built from message fields.

Partition count. Partitions cap how many consumers in one group can work in parallel. Choose enough for expected growth at creation, because adding partitions later changes which partition a key maps to with the default partitioner, and existing data is not moved, so a key's old and new records end up in different partitions.

Reference data. Point metadata (unit, asset, alarm limits) changes rarely and is needed by every consumer. A compacted topic keyed by point ID keeps the latest version of each point and lets a new consumer load the full current list before it starts reading telemetry.

Ordering from MQTT through Kafka

Ordering has to survive several hops, and each hop has its own rules:

• MQTT. The MQTT 5.0 standard requires a server to forward messages on a topic in the order it received them from a given client, at the same QoS, on non-shared subscriptions. With QoS 1, a resend after a network break can deliver a duplicate after later messages: the standard's own example is a subscriber receiving 1, 2, 3, 2, 3, 4. Shared subscriptions, used to load-balance a bridge, spread messages over several subscribers and give up that per-topic order.
• Bridge. If the bridge runs several workers, all messages for one asset must go through the same Kafka key so they meet again in one partition.
• Kafka. Order is preserved within a partition only. Without producer idempotence, retries after broker failures can write duplicates; the Java producer enables idempotence by default unless conflicting settings are made, and with it message ordering is preserved. Check the equivalent setting in your bridge's client library.
• Gateways. After an outage, a gateway publishes its buffer, so readings from yesterday arrive after readings from a minute ago.

The practical conclusion is that sinks should not assume arrival order equals measurement order. Write readings keyed on series and measurement time, so a late or repeated reading lands in the right place, and handle late data explicitly in roll-ups; see Late-Arriving and Out-of-Order Sensor Events.

Getting MQTT data into Kafka

There are three common ways to move messages from the broker into Kafka. Whichever you choose, check its buffering behaviour, because that is where data is lost during a Kafka outage.

The EMQX documentation is a useful example of what to look for: its Kafka sink can buffer in memory, on disk or both, memory-buffered messages are lost if the EMQX node restarts, and when the per-partition buffer limit is reached older messages are discarded to make room. Size disk buffers for the longest Kafka outage you plan to ride through, and alert on buffer use.

A note on licences

The licence and trial statements on this page (the Confluent MQTT source connector's 30-day trial and subscription requirement, the JDBC sink connector's availability under the Confluent Community License) are quoted from each vendor's documentation as published on 27 October 2026; read the linked licence or connector page itself for the terms. This is not legal advice: read the licence and take advice for your situation before deploying a connector or broker feature.

Sinks: time-series database, alarms and warehouse

Give each target its own consumer group so that a slow warehouse load does not hold back the alarm consumer, which reads at its own pace. Typical sinks:

• Relational or time-series database through a JDBC sink connector or a small consumer service that batches inserts. Confluent's JDBC sink documents at-least-once delivery and recommends its upsert mode so that reprocessed records do not create duplicates or constraint violations; the same page states that the connector is available under the Confluent Community License (see the licence note above).
• ClickHouse through the ClickHouse Kafka Connect sink, which its documentation describes as shipping with exactly-once semantics, with the caveat that its buffering option is incompatible with exactly-once mode. That is the vendor's statement about its own connector; it was not verified here, so check the conditions its documentation attaches before relying on it.
• Alarm and rules service as a Kafka Streams or plain consumer application that evaluates thresholds per asset and raises CMMS work orders through the CMMS API.
• Object storage or warehouse for long history, loaded in batches.

Configure a dead letter queue topic for records that fail conversion, and alert on it: a malformed payload from one gateway should not stop the whole sink. In the database, keep the Kafka coordinates with each row (or at least per batch). They cost a few bytes per row and answer "how far has this table been loaded from each partition?", which is exactly what you need before a replay. Committing offsets in the same database transaction as the readings, so the database becomes the record of the sink's position, is covered in the idempotent consumer guide.

Offset commits in short. A sink consumer restarts from its last committed offset. If it commits after the database write, a crash between the two delivers the last batch again (at least once); if it commits before, a crash loses it (at most once). Auto-commit, the client default, commits on a timer rather than after your database commit, and a rebalance (for example after max.poll.interval.ms is exceeded) hands the partition to another member that starts from the last committed offset. Producer idempotence prevents duplicates from producer retries only. Kafka transactions do not include the database, so the sink is at-least-once with idempotent writes; it should only be described as exactly once when offsets are stored in the same transaction as the data and a fencing check rejects writes from a consumer that has lost its partitions. The full treatment, with a failure-recovery table and a fencing example, is in how Kafka offset commits work.

CREATE TABLE reading_landing (
    asset_tag        text             NOT NULL,
    point_name       text             NOT NULL,
    ts               timestamptz      NOT NULL,
    value            double precision,
    kafka_topic      text             NOT NULL,
    kafka_partition  integer          NOT NULL,
    kafka_offset     bigint           NOT NULL,
    loaded_at        timestamptz      NOT NULL DEFAULT now()
);

INSERT INTO reading_landing (asset_tag, point_name, ts, value, kafka_topic, kafka_partition, kafka_offset) VALUES
    ('AHU-01', 'supply_air_temp', '2026-10-27 09:00:00+00', 13.4, 'bms.telemetry.v1', 0, 1201),
    ('AHU-01', 'supply_air_temp', '2026-10-27 09:01:00+00', 13.6, 'bms.telemetry.v1', 0, 1202),
    ('CH-01',  'power_kw',        '2026-10-27 09:00:00+00', 212.0, 'bms.telemetry.v1', 2, 877),
    ('CH-01',  'power_kw',        '2026-10-27 09:01:00+00', 215.5, 'bms.telemetry.v1', 2, 878),
    ('AHU-02', 'zone_co2',        '2026-10-27 08:59:00+00', 640,   'bms.telemetry.v1', 1, 455);

SELECT kafka_topic,
       kafka_partition,
       max(kafka_offset)  AS last_loaded_offset,
       max(ts)            AS newest_reading,
       count(*)           AS rows_loaded
FROM reading_landing
GROUP BY kafka_topic, kafka_partition
ORDER BY kafka_partition;

   kafka_topic    | kafka_partition | last_loaded_offset |     newest_reading     | rows_loaded
------------------+-----------------+--------------------+------------------------+-------------
 bms.telemetry.v1 |               0 |               1202 | 2026-10-27 09:01:00+00 |           2
 bms.telemetry.v1 |               1 |                455 | 2026-10-27 08:59:00+00 |           1
 bms.telemetry.v1 |               2 |                878 | 2026-10-27 09:01:00+00 |           2

Replay and backfill

Replay is the main reason to put Kafka between the broker and the database. Kafka consumers can rewind to an older offset and read again, as long as the data is still within retention. Typical cases are a sink bug that wrote wrong values, a database restore to a point in time, or a new consumer (for example a new analytics table) that needs the last two weeks.

1. Set telemetry retention longer than the worst sink outage you plan for plus the time to detect it and rebuild. If that is a long weekend plus two working days, seven days is too short.
2. For a rebuild, stop the sink, reset its consumer group to a date and time (or to the offsets recorded in the landing table), and restart it. The offset tool only works on inactive groups.
3. For a backfill alongside live processing, use a separate consumer group writing to a new table, then swap tables, so the live sink is not disturbed.
4. Make sure the target tolerates rows it already has, because a replay re-delivers them; the design is in Kafka Consumer Idempotency for Sensor Streams.

bin/kafka-consumer-groups.sh --bootstrap-server kafka1:9092 \
    --reset-offsets --group tsdb-sink --topic bms.telemetry.v1 \
    --to-datetime 2026-10-26T00:00:00.000 --execute

Sizing, security and operations

• Sizing. Work it out per topic: records per second, bytes per record, replication factor and retention. For example, 50 buildings with 400 points each at one reading a minute is 20,000 points, or about 333 records per second on average; at an assumed 200 bytes per record that is about 5.8 GB a day before compression and replication. Plan for bursts when gateways reconnect and replay their buffers, which can be many times the average.
• Security. Authenticate every gateway to the MQTT broker with its own credentials or certificate and restrict which topics it may publish to. In Kafka, give the bridge, each sink and each consumer service its own principal with ACLs limited to the topics they need, and use TLS on every hop. Keep the operational technology network separated from the corporate network, with the broker or bridge as the controlled crossing point.
• Monitoring. Track MQTT connection counts and dropped messages, bridge buffer use, Kafka consumer lag per group, dead letter queue volume, and the gap between measurement time and load time in the database.
• Schema change. Add fields compatibly; for breaking changes, publish to a new versioned topic and run old and new consumers side by side until the old one can be retired.

When not to put Kafka in the pipeline

• One consumer. If the only destination is one time-series database, a gateway or broker integration writing straight into it, with the gateway buffering during outages, is simpler; see MQTT to TimescaleDB.
• Low volume. A few hundred points every 15 minutes does not justify running a replicated Kafka cluster.
• No operations capacity. Kafka adds brokers, controllers, connectors and their monitoring. A managed service reduces this but adds cost; compare pricing models on the vendors' own pages.
• Device-to-device control. Commands and acknowledgements between devices and controllers belong on MQTT or the field protocol, not on a log built for analytics fan-out.

For the wider storage picture, see How to Store IoT Sensor Data in a Database and the time-series overview.

Frequently asked questions

Sources

• Apache Kafka 4.4 documentation: Introduction
• Apache Kafka 4.4 documentation: Design
• Apache Kafka 4.4 documentation: Topic Configs
• Apache Kafka 4.4 documentation: Basic Kafka Operations
• Apache Kafka 4.4 documentation: Producer Configs
• Apache Kafka 4.4 documentation: Kafka Connect user guide
• Apache Kafka 4.4 javadoc: ConsumerRebalanceListener (partitions reassigned before revocation)
• Apache Kafka 4.4 javadoc: KafkaConsumer (offsets, commits, rebalances)
• OASIS: MQTT Version 5.0 standard
• EMQX documentation: Stream MQTT Data into Apache Kafka
• Confluent documentation: MQTT Source Connector
• Confluent documentation: JDBC Sink Connector
• ClickHouse documentation: ClickHouse Kafka Connect Sink

Checked 27 October 2026.

How we research these articles: our editorial method. Research-based from official documentation; SQL examples are run on a local database where the page says so. CodeWithSQL earns nothing from the vendors mentioned.