Data Warehousing on AWS Training ETL and Data Integration 2

Question 1

What is the purpose of Redshift's STG (staging) table pattern in ETL workflows?

Accepted Answer

To load incoming data before merging it into production tables using UPSERT logic

Answer

Staging tables hold incoming data temporarily, allowing ETL processes to perform MERGE/UPSERT operations into the final target table safely.

Question 2

How do you perform an UPSERT (insert or update) in Amazon Redshift, which lacks native MERGE support in older versions?

Accepted Answer

Load into a staging table, DELETE matching rows from target, then INSERT from staging

Answer

Redshift's classic UPSERT pattern loads new data into a staging table, deletes conflicting rows from the target, then inserts all staging rows.

Question 3

Which AWS Glue component automatically discovers and catalogs metadata from data sources like S3 and RDS?

Accepted Answer

Glue Crawler

Answer

Glue Crawlers scan data sources, infer schemas, and register table metadata in the AWS Glue Data Catalog.

Question 4

What is the role of AWS Data Pipeline in a Redshift ETL architecture?

Accepted Answer

It orchestrates and schedules data movement and transformation workflows between AWS services

Answer

AWS Data Pipeline orchestrates scheduled data movement and transformation jobs across AWS services like S3, RDS, DynamoDB, and Redshift.

Question 5

Which Redshift COPY option should you use when loading data files that were compressed with gzip?

Accepted Answer

GZIP

Answer

Specifying GZIP in the COPY command tells Redshift to decompress the files on the fly during the load operation.

Data Warehousing on AWS Training Practice Test