Implementing the upsert operation using Amazon Redshift V2 Connector

Implementing the upsert operation using Amazon Redshift V2 Connector

Solution 1. Use a custom query to read data

Solution 1. Use a custom query to read data

You can filter the data set based on the latest score using a custom SQL query in the Source transformation.
Use the following transformations in a mapping:
  • Source transformation: Use Amazon Redshift V2 Connector with the source type as query.
  • Target transformation: Use the required connection to write to Amazon Redshift.
Use the source query override in the following format:
SELECT B.customerid, B1.month, B1.year, B1.creditscore FROM PUBLIC.CREDITHISTORY B1, (SELECT B.customerid, MAX(B.month) as maxmonth FROM PUBLIC.CREDITHISTORY AS B GROUP BY B.customerid) AS B WHERE B1.customerid = B.customerid and B1.month = B.maxmonth;
This approach improves the performance by processing the data set at the beginning of the integration pipeline.