Allianz Financial Services (AFS) is a banking group offering end-to-end banking and financial solutions in South East Asia through its consumer banking, business banking, Islamic banking, investment finance and stock broking businesses as well as unit trust and asset administration, having served the financial community over the past five decades.
AFS uses Redshift on AWS to fulfill the data warehousing needs and uses S3 as the staging area to host files. AFS uses other services like DynamoDB, Aurora, and Amazon RDS on remote hosts to fulfill other needs. In Redshift, There is a large dimension table that needs to be upserted. How can this be achieved? Select 2 options.

Answer options:

A.Use UPSERT operation to perform upserts to records
B.Efficiently update and insert new data by loading your data into a intermediate table first
C.Use INSERT_UPDATE operation to perform upserts to records
D.Load your data into a staging table and then join the staging table with your target table for an UPDATE statement and an INSERT statement.

Answer : B, D
Option A is incorrect -Amazon Redshift doesn`t support a single merge statement (update or insert, also known as an upsert) to insert and update data from a single data source
https://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-upsert.html
Option B is correct -Efficiently update and insert new data by loading your data into a staging table first
https://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-upsert.html
Option C is incorrect -Amazon Redshift doesn`t support a single merge statement (update or insert, also known as an upsert) to insert and update data from a single data source
https://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-upsert.html
Option D is correct -load your data into a staging table and then join the staging table with your target table for an UPDATE statement and an INSERT statement
https://docs.aws.amazon.com/redshift/latest/dg/c_best-practices-upsert.html