Data Warehousing on AWS Training Data Warehousing on AWS: Trends, Network And Critical Thinking 5

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?

Accepted Answer

VACUUM

Answer

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?

Accepted Answer

Cluster Security Groups / VPC Security Groups

Answer

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?

Accepted Answer

Queries are processing more data than available node memory, causing intermediate results to overflow

Answer

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?

Accepted Answer

Amazon Kinesis Data Firehose with Redshift as the destination

Answer

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?

Accepted Answer

KEY distribution on the most common JOIN column

Answer

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.

Data Warehousing on AWS Training Practice Test

Data Warehousing on AWS Training Practice Test

Data Warehousing on AWS Training Data Warehousing on AWS: Trends, Network And Critical Thinking 5