Implementing the upsert operation using Amazon Redshift V2 Connector

Back Next

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.

Solutions

Download Guide

Watch

Comments

Cloud Connectivity Homepage

Communities

Knowledge Base

Success Portal