Implementing the upsert operation using Amazon Redshift V2 Connector

Implementing the upsert operation using Amazon Redshift V2 Connector

Solution 3. Use a post-SQL query in the Target transformation

Solution 3. Use a post-SQL query in the Target transformation

You can write duplicate data from the source as is and then configure a post-SQL query with Amazon Redshift v2 Connector to clean the written data set.
Use the following transformations in the mapping:
  1. Source transformation: Use Amazon Redshift V2 Connector with the source type as single object or multiple objects.
  2. Target transformation: Use Amazon Redshift V2 Connector and select the upsert operation.
Configure a post-SQL query for the Target transformation in the advanced properties and use the following command to include only the latest updated credit card scores using the following SQL query:
DELETE from PUBLIC.CUSTOMERCREDITHISTORY WHERE idenid NOT IN (SELECT MAX(B.idenid) FROM PUBLIC.CUSTOMERCREDITHISTORY AS B GROUP BY customerid);
The advantage of this solution is that you do not need to make changes to the mapping solution. The data is cleaned in the Amazon Redshift target table based on the configured post-SQL statement after the write operation completes.