Data Warehousing on AWS Training Data Warehousing on AWS: Architecture Design 4 — Questions and Answers
Question 1: A retail company needs near-real-time data availability in their Redshift warehouse. Which ingestion pattern best meets this requirement?
- Daily batch COPY from S3
- Amazon Kinesis Data Firehose streaming into Redshift (Correct answer)
- Weekly AWS Snowball Edge import
- Monthly RDS snapshot restore
Correct answer: Amazon Kinesis Data Firehose streaming into Redshift
Kinesis Data Firehose can buffer and load streaming data into Redshift in near real-time with minimal latency.
Question 2: What is the primary benefit of using columnar storage in Amazon Redshift compared to row-based storage?
- Faster single-row lookups by primary key
- Better compression and reduced I/O for analytical queries that scan few columns (Correct answer)
- Support for ACID transactions on individual rows
- Lower write latency for OLTP workloads
Correct answer: Better compression and reduced I/O for analytical queries that scan few columns
Columnar storage groups values of the same column together, enabling high compression and limiting I/O to only the columns needed by a query.
Question 3: Which architectural pattern uses Amazon Redshift as the serving layer while keeping raw data in S3 and using AWS Glue for transformation?
- Lambda architecture
- Kappa architecture
- Lake house architecture (Correct answer)
- Federated architecture
Correct answer: Lake house architecture
The lake house architecture integrates a data lake (S3) with a data warehouse (Redshift) using Glue for transformation and Spectrum for in-place querying.
Question 4: In Redshift WLM (Workload Management), what happens to a query when all slots in a queue are occupied?
- The query is immediately cancelled with an error
- The query waits in the queue until a slot becomes available (Correct answer)
- The query is automatically routed to a concurrency scaling cluster
- The query is downgraded to run on the leader node only
Correct answer: The query waits in the queue until a slot becomes available
Queries that exceed the queue's concurrency limit wait in the queue until a slot frees up, unless the queue timeout is reached.
Question 5: A data engineer notices that a Redshift table has significant data skew. What is the most likely cause?
- The table uses ZSTD compression encoding
- The distribution key has low cardinality, concentrating data on few slices (Correct answer)
- The table has too many sort key columns defined
- Vacuum has not been run on the table recently
Correct answer: The distribution key has low cardinality, concentrating data on few slices
Choosing a distribution key with low cardinality or many repeated values causes rows to pile up on the same slice, creating skew.
Question 6: Which Amazon Redshift feature enables querying data in operational databases like Aurora or RDS without extracting it first?
- Redshift Spectrum
- Federated Query (Correct answer)
- Concurrency Scaling
- Data Sharing
Correct answer: Federated Query
Redshift Federated Query lets you query live data in Aurora PostgreSQL, Aurora MySQL, or RDS without moving it into Redshift.
Question 7: For a data warehouse supporting thousands of concurrent BI users, which Redshift scaling strategy is most appropriate?
- Increase the number of nodes using classic resize
- Use Redshift Serverless with auto-scaling RPUs
- Enable concurrency scaling and configure WLM queues appropriately (Correct answer)
- Deploy multiple independent clusters behind Route 53
Correct answer: Enable concurrency scaling and configure WLM queues appropriately
Concurrency Scaling with well-tuned WLM queues is the standard approach for handling high concurrency in provisioned Redshift clusters.
Data Warehousing on AWS Training Data Warehousing on AWS: Architecture Design 4
A retail company needs near-real-time data availability in their Redshift warehouse.
Which ingestion pattern best meets this requirement?