"insert.mode=insert" also manages updates

[rlebreto]

Hi

I find that it is very difficult to sink data to redshift using RedshiftSinkConnector.
My use case is to replicate data from postgresql to redshift with debezium.

Because PK are not enforced on redshift, if I propagate an insert and then an update I will have 2 rows in redshift (????)

postgresql :

insert into x_catalog.instalment values (100003, now(), 9999, 0, 0, 'test debezium', 19.99, 1);
update x_catalog.instalment set duration=2 where id = 100003;

result in redshift :

dev=# select * from debezium.x_catalog_instalment;
   id   |       insert_date       | phase_id | inst_index | max_index |  description  | amount_tax_incl | duration
--------+-------------------------+----------+------------+-----------+---------------+-----------------+----------
 100003 | 2024-09-03 08:56:29.312 |     9999 |          0 |         0 | test debezium |           19.99 |        1
 100003 | 2024-09-03 08:56:29.312 |     9999 |          0 |         0 | test debezium |           19.99 |        2

I read that some people propose to use 2 flows (one for insert and one for update), but if the one with insert.mode=insert creates 2 rows I will never be able to have at the end one single row in redshift.

If to manage correctly inserts and updates in redshift db we need to use 2 sinks, is there a solution to not manage updates at all with the insert.mode=insert flow ?