Data Warehousing on AWS Training ETL and Data Integration 2 — Questions and Answers
Question 1: What is the purpose of Redshift's STG (staging) table pattern in ETL workflows?
- To store query results temporarily in memory
- To load incoming data before merging it into production tables using UPSERT logic (Correct answer)
- To cache materialized view results
- To store rejected rows from COPY operations
Correct answer: To load incoming data before merging it into production tables using UPSERT logic
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?
- Use the REPLACE INTO statement
- Load into a staging table, DELETE matching rows from target, then INSERT from staging (Correct answer)
- Use ON CONFLICT DO UPDATE syntax
- Use the UPSERT keyword directly
Correct answer: Load into a staging table, DELETE matching rows from target, then INSERT from staging
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?
- Glue Studio
- Glue Crawler (Correct answer)
- Glue Trigger
- Glue Job
Correct answer: Glue Crawler
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?
- It provides real-time streaming into Redshift
- It orchestrates and schedules data movement and transformation workflows between AWS services (Correct answer)
- It replaces Glue for serverless ETL
- It manages Redshift cluster snapshots
Correct answer: It orchestrates and schedules data movement and transformation workflows between AWS services
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?
- COMPUPDATE ON
- GZIP (Correct answer)
- BZIP2
- LZOP
Correct answer: GZIP
Specifying GZIP in the COPY command tells Redshift to decompress the files on the fly during the load operation.
Question 6: What is Amazon Aurora Zero-ETL integration with Redshift used for?
- Replicating Redshift data back to Aurora
- Near real-time replication of transactional data from Aurora to Redshift without building ETL pipelines (Correct answer)
- Migrating Aurora schemas to Redshift format
- Running SQL queries that span both Aurora and Redshift
Correct answer: Near real-time replication of transactional data from Aurora to Redshift without building ETL pipelines
Zero-ETL integration automatically replicates Aurora transactional data into Redshift in near real-time, eliminating custom ETL pipeline development.
Data Warehousing on AWS Training ETL and Data Integration 2
What is the purpose of Redshift's STG (staging) table pattern in ETL workflows?