Implementing the upsert operation using Amazon Redshift V2 Connector

Implementing the upsert operation using Amazon Redshift V2 Connector

Solution 2. Use an Aggregator transformation in a mapping

Solution 2. Use an Aggregator transformation in a mapping

Use the following transformations in a mapping:
  1. Source transformation: Use three Source transformations that read the credit card history and the customer.
  2. Aggregator transformation. The Aggregator transformation aggregates the data based on the customer ID and defines the output field
    MAX(month)
    .
  3. Joiner transformation: Use two joiners.
  4. Target transformation: Use Amazon Redshift V2 Connector with the upsert operation.
The following image shows the mapping solution:
The image shows the mapping solution.
The first source is from the
credithistory
table.
The second and third sources are
customer
and
credithistory
tables that are joined in the joiner transformation using condition
customerid
.
Another normal join is defined between the upstream data using the
customerid
condition and the month.
The following image shows the join condition:
The fields with prefix
agg_
are added from upstream in the Aggregator transformation.
The prefix
agg_
is added as a part of the naming convention in the incoming field rules.
The Target transformation must point to the object, for example,
customercredithistory
and implement an upsert operation.
The advantage of this approach is that it is a complete mapping solution. Also, if you parameterize the source and target connections and objects, you can use this solution for any upsert operation for objects of a similar use case.