Use the following transformations in a mapping:

Source transformation: Use three Source transformations that read the credit card history and the customer.
Aggregator transformation. The Aggregator transformation aggregates the data based on the customer ID and defines the output field
MAX(month)
.
Joiner transformation: Use two joiners.
Target transformation: Use Amazon Redshift V2 Connector with the upsert operation.

The following 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 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.