DEA-C01 Question 273
Select 3You are working on a data pipeline that ingests large volumes of time-series data into Amazon Redshift. The data is frequently queried for specific time ranges, and query performance is critical. Which combination of strategies would optimize storage and query performance for this use case?
- A
Partition the data by time ranges and use a sort key on the timestamp column.
- B
Enable automatic compression and manually choose the Zstandard compression algorithm for all columns.
- C
Use DISTSTYLE ALL for all tables to ensure data is replicated across all nodes.
- D
Use columnar storage and apply compression based on data type for individual columns.
- E
Define a compound sort key with the timestamp column as the first key.
Show answer and explanation
Correct answers: A, D, E
Explanation
For time-series data in Amazon Redshift, partitioning by time and using appropriate sort keys significantly improve query performance by reducing the data scanned. Columnar storage and tailored compression further enhance storage efficiency and query speed. Combining these strategies ensures optimal performance for time-range queries while maintaining efficient storage utilization.
- A. Correct.
Partitioning the data by time ranges and using a sort key on the timestamp column helps improve query performance when filtering for specific time ranges. This reduces the amount of data that needs to be scanned.
- B. Incorrect.
While enabling automatic compression can be useful, manually choosing a single compression algorithm like Zstandard for all columns is not optimal. Compression should be chosen based on the data type of each column.
- C. Incorrect.
Using DISTSTYLE ALL replicates the entire table to all nodes, which is only optimal for small tables. For large datasets, this approach can lead to inefficient storage and increased maintenance costs.
- D. Correct.
Columnar storage is a core feature of Amazon Redshift that optimizes storage and query performance. Applying compression based on data type for individual columns is a best practice to reduce storage size and improve query efficiency.
- E. Correct.
Defining a compound sort key with the timestamp column as the first key ensures that queries filtering by time ranges benefit from sorted data, improving query performance.