Exact event loading with Redpanda Connect and kafka-python

Route and prerequisites

The reference is Redpanda Connect4.113.0: real kafka_franz input → sql_raw output → fallback Kafka DLQ, with actual kafka-python producer/consumer APIs. Checks covered primary-key idempotence, deletes, restart, offline changes and coordinated added columns. This does not establish automatic DDL or cross-system exactly-once.

Complete the shared connection. Prepare accessible input topic lake-events and DLQ lake-dlq, recording partitions/retention, and install Redpanda Connect plus kafka-python. This page writes lake_demo using example key 501; do not replay into a business database. Inject MO_DSN from a secret manager using your MySQL driver’s DSN format (shape user:password@tcp(host:6001)/doc_lake) and check special-character handling separately.

Runtime configuration

Save as redpanda.yaml. Represent 64-bit integers/decimals as strings and binary as hex; NULL remains JSON null. Bind SQL parameters rather than concatenating values. Start with one thread/in-flight request; same-key multiple partitions or threads need separate ordering checks.

input:
  kafka_franz:
    seed_brokers: ["${KAFKA_BOOTSTRAP}"]
    topics: [lake-events]
    consumer_group: lake-demo-sink
    start_offset: earliest
    commit_period: 1s
    checkpoint_limit: 1
pipeline:
  threads: 1
output:
  fallback:
    - switch:
        cases:
          - check: 'this.op == "delete"'
            output:
              sql_raw:
                driver: mysql
                dsn: "${MO_DSN}"
                query: 'DELETE FROM lake_demo WHERE id=?'
                args_mapping: 'root = [this.id]'
                max_in_flight: 1
          - output:
              sql_raw:
                driver: mysql
                dsn: "${MO_DSN}"
                query: 'INSERT INTO lake_demo(id,amount,payload,label,rev) VALUES(?,?,UNHEX(?),?,?) ON DUPLICATE KEY UPDATE amount=VALUES(amount),payload=VALUES(payload),label=VALUES(label),rev=VALUES(rev)'
                args_mapping: 'root = [this.id,this.amount,this.binary_hex,this.label,this.rev]'
                max_in_flight: 1
    - kafka_franz:
        seed_brokers: ["${KAFKA_BOOTSTRAP}"]
        topic: lake-dlq
        key: '${! this.id }'
shutdown_timeout: 5s
redpanda-connect lint redpanda.yaml
redpanda-connect run redpanda.yaml

Send with kafka-python and verify

Run the producer in another terminal. Expect id 501, amount -1e-18, binary 00FF80 and the Unicode label. Resend the same keyed event and expect one row; sending {"op":"delete","id":"501"} should remove it. Retain original topic events and send receipts.

import os, json
from kafka import KafkaProducer

p = KafkaProducer(bootstrap_servers=os.environ["KAFKA_BOOTSTRAP"],acks="all")
try:
    event = {"op":"upsert","id":"501","amount":"-0.000000000000000001",
             "binary_hex":"00ff80","label":"Kafka😀","rev":"1"}
    receipt = p.send("lake-events",key=event["id"].encode(),
                     value=json.dumps(event,ensure_ascii=False).encode()).get(timeout=10)
    print(receipt.topic,receipt.partition,receipt.offset)
    p.flush()
finally:
    p.close()

DLQ, schema and recovery

Send an overlong label as an invalid record, followed by a valid one. Check the complete original record in DLQ, valid-record progress, absence of the bad SQL row and committed next offset. Fallback acceptance into DLQ can advance offsets, so a committed offset does not mean every row reached SQL. Audit and repair/replay the DLQ separately.

Commit new events while the component is stopped and verify offline catch-up after resume. Migrate SQL DDL before updating args_mapping, event schema and old-event compatibility. Protect replay with version conditions or event deduplication; final-value UPSERT alone does not protect against reordering. A custom kafka-python SQL consumer must implement commit/offset ordering, rebalance, DLQ and deduplication itself. Producer acks=all is not an external database exactly-once guarantee.