Data Warehousing on AWS Training Performance Optimization 1 — Questions and Answers
Question 1: Which Amazon Redshift feature automatically sorts and distributes data to optimize query performance?
- Automatic Table Optimization (ATO) (Correct answer)
- Manual VACUUM
- COPY command
- UNLOAD command
Correct answer: Automatic Table Optimization (ATO)
Automatic Table Optimization (ATO) in Redshift automatically applies sort keys and distribution keys based on query patterns.
Question 2: What is the purpose of the VACUUM command in Amazon Redshift?
- Delete unused columns
- Reclaim storage and resort rows (Correct answer)
- Compress data blocks
- Rebuild indexes
Correct answer: Reclaim storage and resort rows
VACUUM reclaims storage from deleted rows and re-sorts rows to maintain query performance.
Question 3: Which distribution style should you choose for a large fact table that is frequently joined with dimension tables?
- ALL
- EVEN
- KEY (Correct answer)
- AUTO
Correct answer: KEY
KEY distribution places matching values on the same node slice, reducing data movement during joins.
Question 4: What does the ANALYZE command do in Amazon Redshift?
- Compresses columnar data
- Updates table statistics for the query planner (Correct answer)
- Sorts table rows
- Encrypts table data
Correct answer: Updates table statistics for the query planner
ANALYZE collects statistics about table data so the query optimizer can generate efficient execution plans.
Question 5: Which Redshift feature allows you to cache the results of repeated queries in memory?
- Materialized Views
- Result Caching (Correct answer)
- Workload Manager
- Spectrum Cache
Correct answer: Result Caching
Result caching stores query results in memory so identical subsequent queries are returned instantly without re-execution.
Question 6: What is a sort key in Amazon Redshift and why is it important for performance?
- A primary key that enforces uniqueness
- A column used to physically order data on disk for faster range scans (Correct answer)
- An index created on frequently queried columns
- A partition key that distributes data across nodes
Correct answer: A column used to physically order data on disk for faster range scans
Sort keys physically order data on disk, allowing Redshift to skip blocks that don't match filter conditions (zone maps).
Data Warehousing on AWS Training Performance Optimization 1
Which Amazon Redshift feature automatically sorts and distributes data to optimize query performance?