How the Debezium JDBC sink writes Kafka topics into your database
The Debezium JDBC sink is a Kafka Connect sink connector that runs on Connect workers and writes records from Kafka topics into a relational database such as PostgreSQL or MySQL. For change events, it turns inserts, updates and deletes into SQL statements against the target tables, so the database ends up mirroring the source system. Whether that mirror stays correct depends on design choices you make before the first record is written: the upsert mode, the primary key mode, how deletes are handled, how schema changes are allowed to flow through, and where failed records are routed. Each of these is a configuration decision, not a default you can accept without review.
Start by mapping each source topic to a target table and deciding how the primary key is derived. The primary key mode determines which fields identify a row, and an error there produces duplicates or overwrites that are hard to detect later. Next, decide whether writes are idempotent upserts so that replaying a topic after a restart does not create duplicate rows. Then decide how delete events are treated: removing the row, keeping a tombstone-style marker, or ignoring deletes entirely. Each choice changes what downstream readers see. Confirm the exact configuration keys, their defaults and the supported database versions against the Debezium documentation for the connector release you run, because these settings change between releases.
Schema evolution needs the same care. Adding a column in the source is not automatically safe for the target, and type changes or dropped fields can stop the connector or silently alter data. Plan which schema changes you will allow and who approves them, and set up error routing so that malformed or rejected records are isolated instead of blocking the topic. Test these paths in a non-production environment with realistic change traffic before you rely on the pipeline.
What your Kafka, Connect, database and network must support first
A Debezium JDBC sink is only as stable as the platform under it. The cards below cover the Kafka and Connect versions, the target database, the account it writes with, the network path between workers and the database, and the shape of the source topics. Confirm each one before you choose a sink mode.
Kafka and Connect versions
Kafka 4.x runs Connect workers on Java 17 or later, and the supported Kafka releases are 4.3.1, 4.2.2 and 4.1.2. Check that your Connect workers, brokers and tooling meet these requirements, and that your Kafka cluster runs KRaft mode, since 4.0 removed ZooKeeper. Then confirm the Debezium JDBC sink release you plan to deploy supports your Connect version, because connector releases are tied to specific Connect runtimes.
Target database version and support window
Choose a database major version that will still receive fixes for the life of the pipeline. For PostgreSQL, versions 14 through 18 are currently supported, with 14 ending in November 2026 and 18 running to November 2030. For MySQL, weigh the LTS track against the Innovation track, where Innovation releases are supported only until the next Innovation release. Plan the database upgrade path before the sink goes live.
Sink database account and privileges
Create a dedicated account for the sink rather than reusing an administrator login. The account needs rights to create or alter target tables when schema evolution is enabled, plus insert, update and delete on the tables it writes. Restrict it to the target schema and database, and confirm which DDL rights your chosen configuration actually requires. Record the grants so a reviewer can compare them with the configuration.
Network path and TLS from workers
Connect workers open JDBC connections to the database, so the firewall rules, routing and DNS must allow that path from every worker host or pod. Decide whether the JDBC connection uses TLS, and confirm that the database server certificate is trusted by the worker truststore. Test the path from the actual worker runtime, including any Kubernetes network policy, because a test from a laptop does not prove the workers can connect.
Source topic key and value format
The sink maps record keys and values to table columns, so the format of the source topics determines what the sink can do. Confirm the converter used for keys and values, typically Avro, JSON with schemas or Protobuf, and confirm that keys carry the fields you plan to use as primary keys. Check whether the topics come from Debezium source connectors, because change event envelopes need a different unwrapping setup than plain records.
Configuring the Debezium JDBC sink connector in sequence
This sequence takes a team from a Kafka Connect worker with the sink installed to a verified connector that writes change events into a target table. Each step closes with a check the platform or data team can run before moving to the next one.
Confirm the plugin path and Java version
The Connect platform team checks that each worker runs Java 17, which Kafka 4.x requires for Connect, and that
plugin.pathpoints to a directory containing the Debezium JDBC sink and the JDBC driver for your database. Confirm the Kafka release you run against the Kafka downloads page, since 4.3.1, 4.2.2 and 4.1.2 are the supported releases. Done whenGET /connector-pluginson every worker lists the JDBC sink class and all workers report the same version.Create a database role with minimal rights
The database administrator creates a dedicated login role for the sink, with no superuser or ownership rights. It needs connect rights on the database, usage on the target schema, and insert, update and delete on the tables it writes to, plus schema creation only if you let the connector create tables. Confirm the database version is still supported, because PostgreSQL 14 support ends November 12, 2026. Done when the role can write to the test table and cannot read other schemas.
Create the target schema and test table
The database owner creates a dedicated schema and a test table whose primary key columns match the key fields in your Kafka messages. Keep the table definition under version control so later changes are reviewed. Check the definition with the database's describe command and confirm that the primary key constraint exists on the expected columns. Done when a query against the table returns zero rows and the key constraint is listed.
Map each topic to a target table
The data owner and the platform team write a mapping sheet that lists each source topic, its target schema and table, the key columns, and whether schema changes are allowed. If topic names differ from table names, record the routing transform you will use. Confirm that no two topics write to the same table unless their keys are designed for it. Done when each topic has exactly one target and the data owner has signed off on the sheet.
Set connection and converter settings
The connector operator sets
connection.url, the database user and a password reference from your secrets provider, not a plain-text value. Choose key and value converters that match the producer format, and add the schema registry URL if the messages use Avro, Protobuf or schema-registered JSON. Check the connector against the Connect REST validation endpoint, and confirm the settings match the connector release documentation. Done when validation returns no errors for required fields.Set upsert and primary key modes
The operator sets the insert mode to upsert so repeated delivery of the same event updates the existing row instead of failing or duplicating it. Set the primary key mode to take key fields from the Kafka record key, and enable delete handling only if your producers send tombstones and the business accepts row removal. Check exact option names and allowed values in the documentation for your connector release. Done when a test event sent twice produces one row with the latest values.
Start the connector in a non-production environment
The operator deploys the connector config to a non-production Connect cluster that reads only the test topics. Watch the connector and task states through the Connect status endpoint until the connector and all tasks report RUNNING. Review worker logs for errors on the first records, and confirm that the consumer group for the sink has advanced. Done when the status shows no failed tasks and the consumer group offset moves as messages arrive.
Verify rows against source events
The data owner compares the target table with the source topic. Run a key uniqueness query on the primary key columns, which should return no duplicate groups, and compare the row count with the number of distinct keys produced in the test window. Send an update and a delete event to confirm the row changes and disappears as expected. Done when counts match, the uniqueness query is clean, and the error handling topic or logs contain no unexpected records.
Choosing upsert mode and primary key modes for idempotent writes
Choosing the primary key mode is the decision that determines whether replayed or redelivered events update the same row or create a new one, so it should be settled before the sink writes anything. Kafka Connect sink connectors, including the Debezium JDBC sink, deliver records at least once in normal operation, and a task restart, rebalance or retry can send the same record again. With upsert insert.mode, a repeated event for an existing key becomes an update to that row, which keeps the target table consistent. With plain inserts, the same event becomes a duplicate row or a constraint violation. Confirm in the connector documentation which insert modes exist for your connector version and how each handles conflicts on the primary key.
The primary key mode decides which columns identify a row in the target table. One option takes the key from the Kafka record key, which suits events partitioned and keyed by the entity they describe, such as an order ID. The other takes the key from named fields in the record value payload, which suits cases where the record key is absent, carries a different meaning, or differs between producers. The trade-off is that record-key identity depends on producers keeping keys stable across topics, while payload-field identity depends on the schema staying compatible. Composite keys add a further decision: every column in the key must be present and non-null in each event, and their order and types must match the target primary key. Test these choices on a staging table with deliberately duplicated and out-of-order events, and confirm the exact key mode names and their behavior in the Debezium documentation for your version. Fix the key choice before any backfill, because rewriting keys on a populated table means rebuilding rows that downstream systems already read.
How change event fields map to database columns and keys
This table compares each part of a Debezium change event with the database element it lands in, along with the decision you make and the check to run before go-live. Read it top to bottom while drafting your sink configuration, then confirm each PostgreSQL and MySQL type mapping against the connector documentation for your connector version.
| Source element | Target element | Decision | Check |
|---|---|---|---|
| Record key fields | Primary key columns | Choose the primary key mode (record_key or record_value) and which key fields become the primary key | Insert a test event and confirm one row per business key, with no duplicate rows after a repeated delivery |
| Payload fields (after image) | Typed table columns | Decide which fields to map and whether unlisted fields are dropped or rejected | Compare column types in PostgreSQL and MySQL with the connector type mapping table for your version |
| Source nullable flags | Column nullability | Decide whether the target column allows nulls where the source does not guarantee a value | Send an event with a null value and confirm the insert or update succeeds without weakening constraints you need |
| Source default values | Column defaults | Decide whether the sink or the source owns default values for new columns | Add a column in the source during a test run and confirm how the target column is created or left unset |
| Kafka topic name | Target table name | Set table naming through the topic-to-table routing setting, one table per topic or a routed name | Check that topic names containing dots, dashes or prefixes produce the table names your schema expects |
| Operation type field (op) | Insert, update or delete action | Decide whether delete events remove rows or are written as tombstone-style records, based on your retention needs | Replay a create, update and delete sequence and confirm the final table state matches the source |
| Source metadata (ts_ms, source block) | Audit or lineage columns, or none | Decide whether to store source position or timestamps in the target, and whether they count as personal data in your scope | Confirm the columns you add are populated and do not break upserts when a field is absent |
| Schema changes in the source | Target column additions or type changes | Decide whether schema evolution is allowed automatically or handled by a planned migration | Test one added column and one type change in staging and record which ones the sink accepts |
Delete handling and schema evolution limits to plan for
Change events carry more than new rows. They carry deletes, column changes, type changes and key changes, and each one reaches the target table through different rules. The cards below cover the four cases where a sink can silently diverge from the source or stop writing, and the decision each one needs written down before the first production topic is connected.
Delete semantics and tombstones
A delete in the source arrives as a change event with a null value for the same key, which Kafka calls a tombstone. Decide whether the target should remove the row (hard delete) or keep it and mark it deleted (soft delete), since reporting and audit queries depend on that choice. Confirm in the connector documentation how delete events and tombstones are handled for your chosen primary key mode before you rely on either behavior. Document who owns the decision and how deleted rows are reconciled.
New and dropped source columns
When a source table gains or loses a column, the change event shape changes, and the sink must decide whether to add the column to the target. Check whether your connector configuration allows automatic column creation and whether dropped columns are removed or left in place. Unmanaged drift can leave target tables with stale columns that break downstream queries. Document the approval step for DDL changes and who reviews them before the sink picks them up.
Incompatible type changes that stop writes
A source type change, such as a text column becoming an integer, can produce values the existing target column cannot store. In that case the sink task fails and stops writing that topic until someone acts. Test type changes in a non-production environment with the actual target database version, and decide in advance whether the response is a schema migration on the target, a rollback on the source, or a dead letter route for the affected records. Name the on-call owner for these failures.
Table renames and key changes
Renaming a source table or changing its primary key alters the topic name or the key used for upserts, so the sink may write to a new target table or match rows incorrectly. Changing the key on an existing table can create duplicates or overwrite the wrong rows. Plan the sequence explicitly: pause the connector, migrate target data, update the topic and table mapping, then resume with a recorded offset. Treat any key change as a migration with its own test run and rollback plan.
Testing the sink before cutover and after schema changes
Treat the Debezium JDBC sink as untested until a fixed set of change events produces the same end state in a staging database that you expect in production. Build the test plan around one known replay set: a recorded batch of change events from the source topics that includes inserts, updates, deletes and at least one event for a row that is later changed again. Replay it into a staging PostgreSQL or MySQL schema created with the same primary key mode, delete handling and insert.mode settings as production, then compare the resulting rows against a hand-written expected state. Check each operation type separately, because an insert that lands correctly can still hide a delete that was ignored or an update that wrote a stale value.
Next, prove that the sink is idempotent and can resume. Force a duplicate delivery by replaying a slice of the same events a second time, or by resetting the consumer offset of the sink connector to an earlier point, and confirm that the row count and values do not change. Then stop the Connect worker in the middle of the replay, restart it, and confirm that it resumes from the committed offset without gaps or double-applied deletes. Confirm the exact offset storage and replay controls available in your Kafka Connect version and deployment model before you rely on them, and check the current upgrade and connector documentation for the Debezium release you run. Rerun the full suite after every change to the source table, including added, renamed or dropped columns and changed primary keys, because schema evolution limits are where upserts most often break silently.
Keep the evidence from every run: the replay set identifier, connector configuration with secrets removed, Kafka Connect and Debezium versions, the expected and actual end-state queries with their results, the offsets before and after each restart, and the dead letter topic contents for any rejected records. Store these with the change request so that an auditor or incident responder can reconstruct what was tested. Make a failed test block promotion to production by default, and define in advance who may approve an exception and what compensating check is required. A failure in the duplicate or restart tests should stop cutover even when the happy-path replay passes, because those failures usually appear only under real operating conditions.
Failures in the Debezium JDBC sink and their fixes
This table maps common symptoms of a Debezium JDBC sink to their likely causes, the fix to try first and the check that confirms the fix worked. Use it as a triage list, and verify each fix against the connector release you run, because option names and defaults change between releases. The main takeaway is that most failures trace back to three settings: primary key mode, topic and key configuration, and delete handling.
| Symptom | Likely cause | Fix | Check |
|---|---|---|---|
| Duplicate rows in the target table | Insert mode used instead of upsert, or primary key mode does not match the key fields of the source topic | Set insert.mode to upsert, and set primary.key.mode and primary.key.fields to match the record key shape | Replay a small set of updated keys and confirm the target row count stays constant |
| Missing rows in the target table | Source topic not listed in the connector topics setting, or records arrive with a null record key so no primary key can be built | Add the topic to the connector configuration, and confirm the producer or source connector sets a key on every record | Compare offsets for the topic against the row count, and inspect a sample of records for null keys |
| Connector task in a failed state | Column type mismatch between the event and the target column, or the database role lacks insert, update, delete or DDL permission | Adjust the target column type or type mapping, and grant the connector role the privileges it needs on the target schema | Check task status through the Kafka Connect REST API and review the task trace for the first exception |
| Deletes not applied to the target table | Delete handling not enabled, or the source does not emit tombstone records for deleted keys | Enable delete handling in the sink, and confirm the source emits deletes with tombstones for compacted topics | Delete a test key at the source and confirm the matching row is removed in the target |
| Slow writes or lag growing on the sink | Batch size too small for the write volume, or the database connection pool and max connections are exhausted | Increase batch size within what the database handles, and cap tasks so connections stay under the server limit | Track consumer lag and database connection counts during a sustained load test |
| Records landing in the error topic | Schema or type conversion failure, constraint violation or unsupported event shape, with error tolerance set to route failures | Read the error headers to find the failing field, then fix the mapping or target constraint and reprocess the records | Confirm new records no longer reach the error topic and that reprocessed records appear once in the target |
Questions teams ask before building a Debezium JDBC sink
How much more effort is a multi-table pipeline than a single-table sink?
A single-table sink is usually the smaller job, because the work centers on one topic, one target table, one key rule and one set of delete and error behaviors. A multi-table pipeline multiplies those decisions. Each table needs its own key mapping, type mapping, topic routing and tests, and the tables may share foreign-key order, backfills and cutover steps. Effort grows with the number of tables, the number of distinct source schemas, the number of target databases and the amount of transformation between the topic and the table. Ask for sizing by table count and by the number of rules that differ between tables. The effort itself depends on your environment and should come from a written scope.
What does our team need to provide before a sink build starts?
The engagement needs three groups of owners. Kafka owners provide cluster access, topic names, retention settings, the Connect deployment model and the credentials the connector will use. Database owners provide the target schema, a database account with the permissions the sink needs, backup and restore arrangements, and their view of how replicas and maintenance windows affect writes. You also need a non-production environment that matches production in database and Kafka versions, with representative data volumes and a way to replay events. Someone with authority must sign off on primary key modes and delete handling, because those rules decide whether the target matches the source.
Can a capable internal team build a Debezium JDBC sink alone?
Yes, a team with Kafka Connect and database experience can build a single sink, and many do. The hard parts are the rules more than the connector setup: which key identifies a row, what a delete means in the target, how type conversions behave and how failed records are routed. Those decisions need review by the people who own the data, and the tests should replay duplicate, out-of-order and deleted events. Check whether your team has run Connect in production and who will carry the pipeline after launch. Confirm the Java runtime your Connect workers need, since the Kafka upgrade notes list Java 17 for Connect since Kafka 4.0. An external design review can close gaps without handing over the whole build.
How do schema changes affect the timing of a sink project?
Schema changes affect timing because a change to a source table can require a matching change in the target before events land cleanly. Adding a column is often straightforward, while renaming a column, changing a type or changing a key can break upsert logic and require a coordinated cutover. Plan a change process that covers the source, the Connect configuration and the target DDL, and test each change type in staging before production. Confirm how the sink handles schema evolution in the mode you choose, since the documented limits matter as much as the normal case. Include time for these tests in the schedule instead of assuming the team will absorb it later.
What drives the cost of a Debezium JDBC sink up or down?
Cost is mostly the time of the engineers who design, build and test the pipeline, plus the environments they need. The largest drivers are the number of tables and key rules, the number of source schemas and target databases, the amount of transformation and error handling, the depth of testing for replay, duplicates and deletes, and whether a backfill from existing data is needed. Recurring costs include running Kafka Connect, running the database, monitoring, and the on-call and upgrade work that follows. Costs fall when the data model is stable, the target database version is supported and your team already operates Connect. Ask which parts are one-time build work and which are ongoing support.
What should we ask for in a written quote for a database sink?
A written quote should follow from a written scope. Ask for the list of tables and topics, the key and delete rules each one needs, the environments that will be built and tested, and the test cases for duplicate, out-of-order and deleted events. Ask who performs each task, which tasks your team performs and what you must provide and by when. Confirm the acceptance criteria, the documentation and runbooks delivered, and how a schema change requested mid-project is handled. If you want post-launch help, ask how that support is structured. DeployOpen works on fixed-scope engagements agreed in writing before work starts, and ongoing support is available as a retainer.
When should we hire a partner for review instead of a full build?
Choose a review when your team can build the sink but wants an independent check of the design before production. A review fits key mode choices, delete semantics, error routing, the schema change process and test coverage. A full build fits when the team lacks Kafka Connect or database capacity, or when several pipelines must be delivered together. A review usually ends with a list of decisions and risks that your team implements, while a build delivers the working pipeline and its runbooks. If the data is regulated, ask the review to cover how the pipeline shows accurate writes. DeployOpen prepares systems and evidence for compliance assessments but does not issue audit reports or certifications.
Does the target database version affect how long the sink will last?
Yes, the target database version sets how long the pipeline can run without a forced upgrade. PostgreSQL publishes a support window for each major version, and PostgreSQL 14 is supported until November 12, 2026, while PostgreSQL 18 is supported until November 14, 2030. Check the PostgreSQL versioning policy before you choose a version. MySQL offers an LTS track and an Innovation track, and Innovation releases are supported only until the next Innovation release, so the LTS line usually suits a long-lived sink target. Confirm the version your database team will run, because type mapping and the upgrade plan depend on it.
How DeployOpen scopes and delivers a Debezium JDBC sink
DeployOpen plans, builds, tests and documents Debezium JDBC sink pipelines inside your own infrastructure, from the topic and key design through to upsert, delete and error routing behavior. Engagements are fixed-scope, with deliverables and timelines agreed in writing before work starts.
How we scope
- Inventory the source topics, change event format and primary key candidates for each target table before any connector is configured.
- Agree in writing which primary key modes, delete handling and schema evolution behavior the pipeline will use, and which are out of scope.
- Confirm the target PostgreSQL or MySQL versions and the Kafka Connect runtime you operate, since these set what you must verify in testing.
How we build
- Configure the JDBC sink with upsert mode and tested primary key settings, and check idempotent behavior by replaying the same events.
- Set up error routing with a dead letter queue topic and context headers, so rejected records can be inspected and reprocessed.
- Test delete handling and schema changes against a non-production copy of the target database before any cutover.
How we hand over
- Deliver connector configuration, topic and key mappings, database grants and the test results as versioned artifacts in your repository.
- Document the runbook for pausing, restarting and reprocessing the sink, along with known schema evolution limits and their workarounds.
- Walk your database and platform teams through monitoring signals, such as connector task state and dead letter queue volume.
How we support
- Ongoing support, upgrades and continuous testing are available as a retainer, covering connector and Kafka Connect version changes.
- Re-test the sink when the target database major version changes, since the write path and permissions depend on the database release.
- Confirm with your database owners the backup, point-in-time recovery and replica strategy that protects the tables the sink writes to.
