to be able to detect conflicts when using SDC for database replication where JDBC producer is used it would be needed to check the outcome of an insert, update, delete at the destination database.
Whenever we implement database replication with cdc-enabled sources and JDBC producer as destination at the moment we have the following situation regarding conflicts which might happen: - insert or uniqueness conflict: a row with the same PK already exists at target, SDC JDBC producer throws an error, this is fine, but - update and delete conflict: a row with the same PK does not exist at target, SDC JDBC producer just goes on without any warning, thus no chance of noticing this.
I would recommend to add some additional and optional parameters to SDC JDBC producer, with the option to notice data out-of-sync scenarios. If an update from a cdc-enabled source does not find exactly one row at the target site when using the PK, we have a conflict. How to implement? In the source code, in class com/streamsets/pipeline/lib/jdbc/JdbcMultiRowRecordWriter.java resp. com/streamsets/pipeline/lib/jdbc/JdbcGenericRecordWriter.java you use "statement.executeUpdate();"
This is actually a function, returning number of changed rows, if this is not 1 in a scenario as described above, I would wish to have an option to generate some events or errors.
Thanks for your comments Mathias Zarick