I always found it ironic that you get most of this for free if you design your sql updates and save/query the transaction log and/or history. A lot of relational dbs have functionality for that.
And if you don't want to use that, there's also products for this specifically, such as event store.
because things can and will fail inbetween updating the db and publishing the event leading to inconsistency between your database and kafka. a better approach would be to update the db and then use something like debezium or maxwell to pull the updates from the db's bin log and publish them to kafka.
It can make sense to do both, so to de-couple internal table structures from externally exposed events. Debezium supports you safely doing this via the transactional outbox pattern [1]. Events are inserts into an outbox table in the same database as the actual business data, both updated in one transaction. Debezium then can be used to capture the outbox events from the logs and send the events to Kafka. To me this hits the sweetspot for many use cases, as you avoid the potential inconsistencies of dual writes which you mention while still not exposing internal structures to external consumers (for other use cases, like updating a cache or search index on the contrary, CDC-ing the actual tables may be preferable).
thanks for your work on debezium! cdc to kafka has been a game changer for our data pipelines, so much easier to make generalized tooling at this level. our old pipeline requires either immutable rows with autoincrement primary keys or last modified dates with a secondary index on every table. it doesn't capture all the changes for example if a row is modified twice before the pipeline sees it the first update won't be recorded in the history. it also doesn't propagate deletes. debezium solves both of those issues plus doesn't require the application developers to do anything special for their data to get into the analytics systems. now instead of sounding like a broken record telling people to add last modified dates to all their tables i can sound like a broken record telling people to stop violating single source of truth by dual writing (which is a much better problem to have as before i had given up on single source of truth even being possible in a large organization)
This is a neat pattern, but it honestly just seems like you’re trading where you want the complexity. The outbox pattern would force devs to not use SQL directly for mutations - in so much as they can’t directly modify records like they normally would. I can see this being ok if you had someone with strong DBA skills implementing the abstraction.
Devs modify records just as they normally would. Only, in addition, they produce a record in the outbox table, representing the change to the outside world. I.e. instead of writing to the DB and Kafka, both changes are done to the database, and then events from the outbox table are CDC-ed to Kafka.
We do this now and make publishing to Kafka a requirement of transaction success, rolling back the DB transaction if publishing fails. The dual write is much more straightforward in my opinion and the pattern works as long as the primary database supports transactions or some other form of consistency guarantee.
I still can’t get behind using CDC via a DB watching tool unless I have little control of the system writing to the DB. There is so much context lost interpreting the change at the DB (who was the user that made this change, etc.) — unless you are sending this data to the DB (then yikes your DB schema is much more complex).
> rolling back the DB transaction if publishing fails.
If your DB transaction fails after the Kafka message has been sent, you'll end up with inconsistencies between your local database state and what has been published externally via Kafka. That's exactly the kind of dual write issues the outbox pattern suggested in my other comment avoids.
> unless you are sending this data to the DB
You actually can do this, and still keep this metadata separately from your domain tables. The idea is to add this metadata into a separate table, keyed by transaction id, and then use downstream stream processing to enrich the actual change events with that metadata. I've blogged about an approach for this a while ago here: https://debezium.io/blog/2019/10/01/audit-logs-with-change-d....
We don’t use auto commits with Kafka so it’s not a problem for us. Of course, this does reduce our publishing throughput - though that’s not a problem for us at our current scale.
Either way, I’m intrigued by the outbox pattern. As long as it is transparent to developers, it does seem like an ideal CDC mechanism.
I always found it ironic that you get most of this for free if you design your sql updates and save/query the transaction log and/or history. A lot of relational dbs have functionality for that
Being able to elegantly and efficiently do "AS OF" queries is still a hard problem, I don't see that KTables really solve it. Hate to say it but Oracle's solution to this is still unmatched. https://oracle-base.com/articles/10g/flashback-query-10g#fla...
And if you don't want to use that, there's also products for this specifically, such as event store.