Data Warehousing on AWS Training Data Lake Integration 1 — Questions and Answers
Question 1: What AWS service allows Amazon Redshift to query data directly in Amazon S3 without loading it into Redshift?
- AWS Glue
- Redshift Spectrum (Correct answer)
- Amazon Athena
- AWS Data Pipeline
Correct answer: Redshift Spectrum
Redshift Spectrum extends Redshift's SQL capabilities to query data stored in S3 using external tables, without requiring data to be loaded into Redshift.
Question 2: When using Redshift Spectrum, where are external table definitions stored?
- Amazon DynamoDB
- Amazon RDS
- AWS Glue Data Catalog or Amazon Athena metastore (Correct answer)
- Amazon ElastiCache
Correct answer: AWS Glue Data Catalog or Amazon Athena metastore
Redshift Spectrum uses the AWS Glue Data Catalog or the Amazon Athena metastore as the external metadata repository to define schemas for external tables.
Question 3: Which file format is NOT natively supported for querying with Redshift Spectrum?
- Parquet
- ORC
- JSON
- Microsoft Excel (.xlsx) (Correct answer)
Correct answer: Microsoft Excel (.xlsx)
Redshift Spectrum supports open formats such as Parquet, ORC, JSON, CSV, and Avro, but does not support proprietary binary formats like Excel.
Question 4: What is the primary performance advantage of using columnar file formats like Parquet with Redshift Spectrum?
- They support more complex nested data types
- They reduce the amount of data scanned, lowering costs and improving query speed (Correct answer)
- They automatically eliminate duplicate rows
- They enable real-time streaming ingestion into Redshift
Correct answer: They reduce the amount of data scanned, lowering costs and improving query speed
Columnar formats like Parquet allow Spectrum to read only the columns referenced in a query, drastically reducing the data scanned and the per-byte query cost.
Question 5: In Redshift Spectrum, external tables referencing S3 data must be created inside which type of schema?
- Internal schema
- Public schema
- External schema (Correct answer)
- Temporary schema
Correct answer: External schema
Redshift Spectrum external tables must reside in an external schema that is mapped to the AWS Glue Data Catalog or Athena, signaling that data lives outside Redshift.
Question 6: Which statement best describes a data lake architecture on AWS?
- Data is always stored in a normalized relational format in Amazon RDS
- All data must be transformed before it is stored
- Raw data in multiple formats is stored in Amazon S3, serving as a centralized repository (Correct answer)
- Data lakes require Redshift as the primary storage layer
Correct answer: Raw data in multiple formats is stored in Amazon S3, serving as a centralized repository
A data lake stores raw data in its native format—structured, semi-structured, and unstructured—in Amazon S3, enabling diverse analytics workloads with schema-on-read.
Question 7: What IAM configuration is required for Redshift Spectrum to access data in S3?
- An IAM role with S3 read and Glue catalog permissions must be attached to the Redshift cluster (Correct answer)
- Root account credentials must be embedded in the CREATE EXTERNAL SCHEMA statement
- S3 buckets must be configured with public read access
- No IAM configuration is required; Redshift has implicit access to all S3 buckets
Correct answer: An IAM role with S3 read and Glue catalog permissions must be attached to the Redshift cluster
Redshift Spectrum requires an IAM role with s3:GetObject and glue:GetTable (or equivalent) permissions attached to the cluster to access external data sources.
Data Warehousing on AWS Training Data Lake Integration 1
What AWS service allows Amazon Redshift to query data directly in Amazon S3 without loading it into Redshift?