I’ve configured a replication task that reads from a Log Stream and replicates data to a Snowflaketarget with Apply Changes Processing enabled, meaning that changes are applied directly to the target tables in Snowflake. The source of the Log Stream is an Oracle database.
Moreover, I’ve implemented a soft-delete logic in this task by adding a technical field defined as follows:
CASE ifnull(operation_indicator('DELETE','UPDATE','INSERT'),'null')
WHEN 'null' THEN 'INSERT'
ELSE operation_indicator('DELETE','UPDATE','INSERT') END
Occasionally, operations are performed on the source that modify primary key fields. I’d like to understand if it’s possible to transform the UPDATE into a DELETE of the record with the previous key, followed by an INSERT of the record with the new key.
As I understand it, the “DELETE and INSERT when updating a primary key column” option available in Apply Changes Processing cannot be used to achieve this because for the Snowflake target endpoint this option applies only to Change Tables, not to the actual target tables.
However, I noticed that there is also an “Apply changes using SQL MERGE” option. If I understand correctly, this option works as follows: “for a standard UPDATE, Qlik Replicate generates a MERGE INTO statement. However, if the UPDATE modifies primary key values, Qlik Replicate will not use MERGE INTO; instead, it will apply the change as a DELETE + INSERT pair for that row”.
If this is how SQL MERGE operates, would implementing a soft-delete logic mean that, when a primary key changes, the target table would contain the record with the old primary key marked as deleted (soft-deleted) and the record with the new primary key inserted as a new record?
