What about introducing conflict detection methods for JDBC producer?
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