Download 02-sinks-errors-and-dead-letters.ipynb
Open it in JupyterLab or VS Code from the lab folder to run it. This page is a read-only view.
Sinks, errors and dead letters in Connect — practical
Finance wants Brightlane's tariffs in its reporting database. A CDC connector reads them from the pricing database, a JDBC sink connector writes them into the reporting database. Then a bad record stops the sink, and you make it park such records instead, and operate the connectors through their REST API.
Lab services: kafka-1..3, schema-registry, postgres, connect (docker compose --profile connect up -d --wait)
· Lesson: modules/06-connect-and-cdc/02-sinks-errors-and-dead-letters.md
# Setup: find the lab's shared helpers and check the lab is running import sys, pathlib lab_root = next(p for p in [pathlib.Path.cwd(), *pathlib.Path.cwd().parents] if (p / "common").is_dir()) sys.path.insert(0, str(lab_root)) import json, time from decimal import Decimal from common import settings, lab lab.require_kafka() for name, url in [("Schema Registry", settings.SCHEMA_REGISTRY_URL + "/subjects"), ("Kafka Connect", settings.CONNECT_URL)]: lab.require_http(name, url, start_hint="docker compose --profile connect up -d --wait") lab.print_versions()
Step 1: The source: tariff changes from the pricing database
The same CDC connector as in lesson 6.1, with its own names.
lab.remove_connectors("m06l02-") lab.reset_pricing_db() lab.pg("DROP TABLE IF EXISTS tariffs", db="reporting") TOPIC = "m06l02_pricing.public.tariffs" lab.connect_api("PUT", "/connectors/m06l02-pricing-cdc/config", { "connector.class": "io.debezium.connector.postgresql.PostgresConnector", "plugin.name": "pgoutput", "database.hostname": "postgres", "database.port": "5432", "database.dbname": "pricing", "database.user": "brightlane", "database.password": "brightlane-lab", "topic.prefix": "m06l02_pricing", "table.include.list": "public.tariffs", "slot.name": "m06l02_pricing_cdc", "publication.name": "m06l02_pricing_cdc", "publication.autocreate.mode": "filtered", "time.precision.mode": "connect", "topic.creation.default.replication.factor": "3", "topic.creation.default.partitions": "1"}) print("source:", lab.wait_connector("m06l02-pricing-cdc")["connector"]["state"])
Step 2: The sink: a JDBC connector into the reporting database
The Debezium JDBC sink understands change events directly: it upserts each after row by the
record's key, and deletes the row for a delete event. schema.evolution=basic lets it create the
table from the Avro schema.
sink = { "connector.class": "io.debezium.connector.jdbc.JdbcSinkConnector", "topics": TOPIC, "connection.url": "jdbc:postgresql://postgres:5432/reporting", "connection.username": "brightlane", "connection.password": "brightlane-lab", "insert.mode": "upsert", # insert a new key, update an existing one "delete.enabled": "true", # a delete event deletes the row "primary.key.mode": "record_key", # the record key (tariff_id) is the primary key "schema.evolution": "basic", # create the table, add new columns "collection.name.format": "tariffs", # the table's name } lab.connect_api("PUT", "/connectors/m06l02-reporting-tariffs/config", sink) lab.wait_connector("m06l02-reporting-tariffs") count = lambda db: lab.pg("SELECT count(*) FROM tariffs", db=db)[0][0] lab.wait_until(lambda: lab.pg("SELECT to_regclass('tariffs')", db="reporting")[0][0] and count("reporting") == 36, timeout=60, what="the reporting table") print("reporting columns:", lab.pg("SELECT column_name, data_type FROM information_schema.columns " "WHERE table_name = 'tariffs' ORDER BY ordinal_position", db="reporting")) print("rows: pricing", count("pricing"), "| reporting", count("reporting"))
Step 3: A price change, end to end
How long does a change take from the pricing database to the reporting database?
def reporting_price(tariff_id): rows = lab.pg("SELECT price_per_kwh FROM tariffs WHERE tariff_id = %s", (tariff_id,), db="reporting") return rows[0][0] if rows else None started = time.monotonic() lab.pg("UPDATE tariffs SET price_per_kwh = 0.5800 WHERE tariff_id = 'tar-site-C05-1'") lab.wait_until(lambda: reporting_price("tar-site-C05-1") == Decimal("0.5800"), timeout=60, interval=0.1, what="the new price in reporting") print(f"price visible in reporting after {time.monotonic() - started:.1f} s") lab.pg("DELETE FROM tariffs WHERE tariff_id = 'tar-site-C06-1'") lab.wait_until(lambda: count("reporting") == 35, timeout=60, what="the delete") print("rows: pricing", count("pricing"), "| reporting", count("reporting"))
Break it: a record the sink can't read
A quick script writes one tariff as plain JSON into the topic, without the Avro serializer (as in lesson 5.1). Predict first: what does the sink do, and what happens to the next real price change?
from confluent_kafka import Producer producer = Producer(settings.PRODUCER_DEFAULTS) producer.produce(TOPIC, key=b"tar-site-N01-1", value=b'{"tariff_id": "tar-site-N01-1", "price_per_kwh": 0.39}') producer.flush(10) time.sleep(5) lab.pg("UPDATE tariffs SET price_per_kwh = 0.5000 WHERE tariff_id = 'tar-site-N01-1'") time.sleep(10) status = lab.connect_api("GET", "/connectors/m06l02-reporting-tariffs/status") print("connector:", status["connector"]["state"], "| task:", status["tasks"][0]["state"]) print("\n".join(line for line in status["tasks"][0]["trace"].splitlines() if line.startswith("Caused by"))[:400]) print("tar-site-N01-1 in reporting:", reporting_price("tar-site-N01-1"), "(pricing has 0.5000)")
The task stopped at the bad record, and every change after it waits. That's Connect's default,
errors.tolerance=none.
Step 4: Tolerate bad records, park them in a dead-letter topic
Update the sink's configuration, then restart the failed task.
DLQ = "m06l02-reporting-tariffs.dlq" sink.update({ "errors.tolerance": "all", # don't stop on a bad record ... "errors.deadletterqueue.topic.name": DLQ, # ... park it here "errors.deadletterqueue.topic.replication.factor": "3", "errors.deadletterqueue.context.headers.enable": "true", # with headers saying why "errors.log.enable": "true"}) lab.connect_api("PUT", "/connectors/m06l02-reporting-tariffs/config", sink) lab.connect_api("POST", "/connectors/m06l02-reporting-tariffs/restart?includeTasks=true&onlyFailed=true") lab.wait_connector("m06l02-reporting-tariffs") lab.wait_until(lambda: reporting_price("tar-site-N01-1") == Decimal("0.5000"), timeout=60, what="the waiting change") print("tar-site-N01-1 in reporting:", reporting_price("tar-site-N01-1"))
The waiting price change arrived. The bad record is in the dead-letter topic, with Connect's headers:
from confluent_kafka import Consumer, TopicPartition consumer = Consumer({"bootstrap.servers": settings.KAFKA_BOOTSTRAP, "group.id": "m06l02-inspect", "enable.auto.commit": False}) consumer.assign([TopicPartition(DLQ, 0, 0)]) parked = consumer.poll(10) consumer.close() print("value:", parked.value()) for key, value in parked.headers(): if "stacktrace" not in key: print(f"{key:<42} {value.decode()[:70]}")
Step 5: Operate the connectors
The REST API shows both connectors at once, their positions, and lets you stop and resume them.
time.sleep(6) # Connect saves positions every 5 s in the lab for name, info in lab.connect_api("GET", "/connectors?expand=status").items(): s = info["status"] print(f"{name:<26} {s['type']:<7} {s['connector']['state']:<8} tasks: {[t['state'] for t in s['tasks']]}") print(json.dumps(lab.connect_api("GET", "/connectors/m06l02-pricing-cdc/offsets")["offsets"][0]["offset"])) print(json.dumps(lab.connect_api("GET", "/connectors/m06l02-reporting-tariffs/offsets")["offsets"][0]))
The source connector's position is a place in PostgreSQL's log (lsn); the sink's is an offset in
the topic. stop shuts a connector down but keeps its configuration and position; resume
starts it again from there:
lab.connect_api("PUT", "/connectors/m06l02-reporting-tariffs/stop") lab.wait_until(lambda: lab.connect_api("GET", "/connectors/m06l02-reporting-tariffs/status", echo=False) ["connector"]["state"] == "STOPPED", timeout=30, what="the stop") lab.pg("UPDATE tariffs SET price_per_kwh = 0.4800 WHERE tariff_id = 'tar-site-N01-1'") time.sleep(5) print("while stopped:", reporting_price("tar-site-N01-1")) lab.connect_api("PUT", "/connectors/m06l02-reporting-tariffs/resume") lab.wait_until(lambda: reporting_price("tar-site-N01-1") == Decimal("0.4800"), timeout=60, what="the catch-up") print("after resume:", reporting_price("tar-site-N01-1"))
Clean up
Delete the connectors, slot and publication, the topics and schemas, the reporting table, and
reset the pricing database. If anything gets stuck, docker compose down -v in the code/ folder
resets the lab.
from confluent_kafka.admin import AdminClient from confluent_kafka.schema_registry import SchemaRegistryClient lab.remove_connectors("m06l02-") admin = AdminClient({"bootstrap.servers": settings.KAFKA_BOOTSTRAP}) lab.delete_topics(admin, [t for t in admin.list_topics(timeout=10).topics if t.startswith("m06l02")]) admin.delete_consumer_groups(["m06l02-inspect", "connect-m06l02-reporting-tariffs"]) registry = SchemaRegistryClient({"url": settings.SCHEMA_REGISTRY_URL}) for subject in [s for s in registry.get_subjects() if s.startswith("m06l02")]: registry.delete_subject(subject) registry.delete_subject(subject, permanent=True) lab.pg("DROP TABLE IF EXISTS tariffs", db="reporting") lab.reset_pricing_db() print("cleaned up")