DAS-C01 · Question #35
A company has a business unit uploading .csv files to an Amazon S3 bucket. The company's data platform team has set up an AWS Glue crawler to do discovery, and create tables and schemas. An AWS Glue…
The correct answer is A. Modify the AWS Glue job to copy the rows into a staging table. The standard upsert pattern for Amazon Redshift (which does not natively support UPSERT/MERGE in all scenarios) is: (1) load incoming data into a staging table, (2) DELETE rows from the target table where the primary key matches rows in the staging table, then (3) INSERT all…
Question
A company has a business unit uploading .csv files to an Amazon S3 bucket. The company's data platform team has set up an AWS Glue crawler to do discovery, and create tables and schemas. An AWS Glue job writes processed data from the created tables to an Amazon Redshift database. The AWS Glue job handles column mapping and creating the Amazon Redshift table appropriately. When the AWS Glue job is rerun for any reason in a day, duplicate records are introduced into the Amazon Redshift table. Which solution will update the Redshift table without duplicates when jobs are rerun?
Options
- AModify the AWS Glue job to copy the rows into a staging table.
- BLoad the previously inserted data into a MySQL database in the AWS Glue job.
- CUse Apache Spark's DataFrame dropDuplicates() API to eliminate duplicates and then write the
- DUse the AWS Glue ResolveChoice built-in transform to select the most recent value of the
How the community answered
(51 responses)- A71% (36)
- B16% (8)
- C10% (5)
- D4% (2)
Explanation
The standard upsert pattern for Amazon Redshift (which does not natively support UPSERT/MERGE in all scenarios) is: (1) load incoming data into a staging table, (2) DELETE rows from the target table where the primary key matches rows in the staging table, then (3) INSERT all rows from the staging table into the target table. This ensures idempotency - rerunning the job will remove previously inserted duplicates before reinserting, resulting in no net duplicate records. Option C (dropDuplicates) only deduplicates within the incoming batch, not against already-persisted rows in Redshift. Option D (ResolveChoice) handles schema ambiguity, not deduplication. Option B introduces an unnecessary MySQL dependency.
Topics
Community Discussion
No community discussion yet for this question.