Data Warehousing on AWS Training Performance Optimization 2 — Questions and Answers
Question 1: Which type of Redshift sort key uses multiple columns and is best when queries frequently filter on those columns together?
- Interleaved sort key
- Compound sort key (Correct answer)
- Single sort key
- Distribution sort key
Correct answer: Compound sort key
Compound sort keys order data by the first column, then second, etc., and work best when queries filter on leading columns in order.
Question 2: What is an interleaved sort key best suited for in Amazon Redshift?
- Tables with a single high-cardinality filter column
- Queries that filter on any subset of sort key columns with equal weight (Correct answer)
- Large dimension tables with ALL distribution
- Tables loaded via the COPY command
Correct answer: Queries that filter on any subset of sort key columns with equal weight
Interleaved sort keys give equal weight to all sort key columns, making them useful when different queries filter on different subsets.
Question 3: How does Amazon Redshift Spectrum improve query performance for large datasets stored in S3?
- It caches S3 data in Redshift local storage
- It pushes query computation down to the Spectrum layer, parallelizing across thousands of nodes (Correct answer)
- It converts S3 files to columnar format automatically
- It replicates S3 data across Availability Zones
Correct answer: It pushes query computation down to the Spectrum layer, parallelizing across thousands of nodes
Redshift Spectrum executes queries directly against S3 data using a massively parallel processing layer, reducing data transfer.
Question 4: What is the recommended maximum number of columns for a Redshift sort key to maintain performance?
- 2
- 4 (Correct answer)
- 6
- 8
Correct answer: 4
Redshift recommends limiting sort keys to no more than 4 columns to avoid excessive overhead during data loading and vacuuming.
Question 5: Which Redshift feature allows you to pause and resume a cluster to save costs during idle periods while retaining data?
- Elastic Resize
- Cluster Pause/Resume (Correct answer)
- Snapshot Restore
- RA3 Node Tiering
Correct answer: Cluster Pause/Resume
Cluster Pause/Resume stops compute billing while preserving all data, ideal for development or infrequent workloads.
Question 6: What does the STL_QUERY view in Amazon Redshift contain?
- Active session information
- Historical query execution records (Correct answer)
- Node health metrics
- Cluster configuration details
Correct answer: Historical query execution records
STL_QUERY stores historical records of queries executed on the cluster, including execution time and status.
Data Warehousing on AWS Training Performance Optimization 2
Which type of Redshift sort key uses multiple columns and is best when queries frequently filter on those columns together?