DEA-C01 Question 272
Select 3You are tasked with optimizing a large Amazon Redshift table that stores sales transaction data. This table is queried frequently for reports that filter by region and transaction date. The table is updated daily with new data. What combination of strategies would best optimize query performance and storage efficiency for this table?
- A
Use a compound sort key on the 'region' and 'transaction_date' columns.
- B
Partition the table by 'region' using Redshift's partitioning feature.
- C
Compress the data using column encoding based on the data type in each column.
- D
Use a distribution key on the 'region' column to evenly distribute data across nodes.
- E
Convert the table into Parquet format for better storage optimization.
Show answer and explanation
Correct answers: A, C, D
Explanation
The best practices for optimizing performance and storage in Amazon Redshift include using compound sort keys for query filtering, applying columnar compression to reduce storage usage, and choosing an appropriate distribution key to balance data across nodes. Partitioning and the Parquet format are not applicable to Amazon Redshift in this scenario.
- A. Correct.
Using a compound sort key on 'region' and 'transaction_date' ensures that the table is optimized for queries filtering by these columns. Redshift uses sort keys to improve query performance for range-restricted scans.
- B. Incorrect.
Amazon Redshift does not support traditional table partitioning like some other databases. Instead, it relies on sort keys and distribution styles for optimization.
- C. Correct.
Applying columnar compression (column encoding) based on data type reduces storage requirements and improves query performance by minimizing the amount of data read from disk.
- D. Correct.
Setting a distribution key on 'region' helps evenly distribute the data across nodes, reducing data movement during query execution and improving performance for region-based queries.
- E. Incorrect.
Parquet is a columnar storage format used primarily with Amazon S3 and Athena, not Amazon Redshift. Redshift uses its native storage and optimization mechanisms.