Meltano

is there a way to keep values of deleted rows with any target-postgres (LOG_BASED)?

I’ve tested only Transferwise variant and found it puts `NULL`s instead of previous values. It uses csv-file to push values with `COPY` into tmp table and `UPDATE` with all columns from schema.

So, I’ve experimented a bit and altered the `UPDATE` statement with `COALESCE` if new value of `sdc_deleted_at IS NOT NULL`. It works fine, but doesn’t cover corner case with `UPDATE` and `DELETE` of the same row in one batch.

Transferwise variant stores records in key based dict, that means that only the last record would be written to the csv file. So the fix for the corner case is to update the last updated value before deletion with deletion record (i.e., `last_record_before_deletion.update(deletion_record)` ) in the dict.

Interesting, seems like a good feature for soft deletes on the target postgres for meltano labs. If something's being deleted the value from the last record should stay.

Another way to quick workaround this would be to do append only (get rid of the primary key)