Data Warehousing on AWS Training Data Warehousing on AWS: Trends, Network And Critical Thinking 5 — Questions and Answers
Question 1: A Redshift cluster's query performance degrades over time as rows are deleted and updated. Which maintenance command reclaims storage and re-sorts data?
- ANALYZE
- VACUUM (Correct answer)
- DEEP COPY
- ALTER TABLE REPACK
Correct answer: VACUUM
VACUUM reclaims space from deleted rows and re-sorts data in sort key order, restoring query performance that degrades due to unsorted or ghost rows.
Question 2: Which Redshift security feature provides an additional layer of control by restricting which IP address ranges can connect to the cluster?
- KMS encryption keys
- Cluster Security Groups / VPC Security Groups (Correct answer)
- IAM permission boundaries
- Redshift audit logging
Correct answer: Cluster Security Groups / VPC Security Groups
VPC Security Groups act as virtual firewalls that control inbound and outbound network traffic to the Redshift cluster based on IP ranges and ports.
Question 3: An analytics team sees that their Redshift queries frequently spill to disk. What is the most likely root cause?
- The cluster has too many nodes
- Queries are processing more data than available node memory, causing intermediate results to overflow (Correct answer)
- The S3 Enhanced VPC Routing is misconfigured
- The cluster distribution style is set to ALL instead of KEY
Correct answer: Queries are processing more data than available node memory, causing intermediate results to overflow
Disk spill occurs when query intermediate results exceed available memory, forcing Redshift to write temporary data to disk, which dramatically slows execution.
Question 4: A company wants real-time data streaming into their Redshift data warehouse from application event logs. Which AWS service provides the most direct streaming ingestion path?
- AWS Database Migration Service (DMS)
- Amazon Kinesis Data Firehose with Redshift as the destination (Correct answer)
- AWS Glue batch ETL jobs running every 5 minutes
- Amazon SQS queue consumed by a Lambda function writing to Redshift
Correct answer: Amazon Kinesis Data Firehose with Redshift as the destination
Kinesis Data Firehose natively supports Redshift as a delivery destination, buffering and loading streaming records directly into Redshift with no custom code.
Question 5: Which distribution style should be chosen for a large fact table in Redshift to optimize JOIN performance with multiple dimension tables?
- EVEN distribution
- ALL distribution
- KEY distribution on the most common JOIN column (Correct answer)
- AUTO distribution and let Redshift decide automatically
Correct answer: KEY distribution on the most common JOIN column
Distributing a large fact table by KEY on the column most frequently used in JOINs co-locates matching rows on the same slice, minimizing data redistribution during query execution.
Question 6: A critical thinking exercise: A company is deciding between a traditional on-premises data warehouse and Amazon Redshift. Which factor most strongly favors Redshift from a total cost of ownership perspective?
- Redshift always costs less per terabyte than on-premises hardware
- Redshift eliminates upfront capital expenditure and scales pay-as-you-go, converting CapEx to OpEx (Correct answer)
- Redshift requires no database administration expertise whatsoever
- Redshift guarantees faster query performance for all workload types
Correct answer: Redshift eliminates upfront capital expenditure and scales pay-as-you-go, converting CapEx to OpEx
Redshift's cloud model eliminates large upfront hardware purchases, converting capital expenditure to operational expenditure with usage-based scaling, which improves cash flow and reduces financial risk.
Question 7: Which emerging AWS capability allows machine learning models trained in Amazon SageMaker to be invoked directly within Redshift SQL queries?
- Redshift ML using Amazon SageMaker Autopilot integration (Correct answer)
- Amazon QuickSight Q natural language queries
- AWS Glue ML Transforms applied during ETL
- Amazon Comprehend called via Lambda from Redshift stored procedures
Correct answer: Redshift ML using Amazon SageMaker Autopilot integration
Redshift ML allows users to create, train, and invoke machine learning models using SQL commands, with SageMaker Autopilot automatically training the model in the background.
Data Warehousing on AWS Training Data Warehousing on AWS: Trends, Network And Critical Thinking 5
A Redshift cluster's query performance degrades over time as rows are deleted and updated.
Which maintenance command reclaims storage and re-sorts data?