DEA-C01 Question 275
Select 3A data engineering team is tasked with optimizing a 1 TB Amazon Redshift table used for analytical queries. The table contains billions of rows, and queries often filter by a 'region' column and perform aggregations on a 'sales_date' column. What combination of best practices should the team follow to improve query performance while minimizing storage costs?
- A
Partition the table by the 'region' column.
- B
Define a distribution key on the 'region' column.
- C
Sort the table by the 'sales_date' column.
- D
Compress the table using columnar encoding.
- E
Use a compound sort key with 'region' and 'sales_date' columns.
Show answer and explanation
Correct answers: B, C, D
Explanation
To optimize a large Amazon Redshift table for analytical queries, it is crucial to use a distribution key to minimize data movement, a sort key to enable efficient query scans, and compression to reduce storage and improve I/O performance. Partitioning is not a concept in Redshift, and while compound sort keys can be useful, they are not the best fit for this specific use case.
- A. Incorrect.
Partitioning is not directly supported in Amazon Redshift. Instead, distribution keys and sort keys are used for optimization. Therefore, this is not a valid option for Redshift.
- B. Correct.
Defining a distribution key on the 'region' column ensures that data is distributed across the cluster nodes based on this column, which can help reduce data movement and improve query performance for region-based filtering.
- C. Correct.
Sorting the table by the 'sales_date' column enables efficient range-based scans for queries involving date-based aggregations, improving query performance significantly.
- D. Correct.
Columnar encoding compresses data efficiently in Amazon Redshift, reducing storage costs and improving I/O performance. Redshift automatically applies the most appropriate compression algorithm when the 'COPY' command is used with a new table.
- E. Incorrect.
While a compound sort key is useful when the query filtering follows the order of the sort key columns, this scenario would benefit more from separate optimization strategies (distribution key and a single-column sort key).