DEA-C01 Question 271
Select 2You are designing a data lake architecture for an e-commerce company on Amazon S3. The company frequently queries large datasets stored in S3 using Amazon Athena. To improve query performance and reduce costs, which of the following are best practices you should implement?
- A
Partition the data in S3 based on common query filters such as date or region.
- B
Use row-based storage formats like CSV for optimal query performance.
- C
Compress the data using columnar compression formats such as Parquet or ORC.
- D
Index the S3 data using Amazon S3’s native indexing service.
- E
Avoid partitioning the data as it increases query complexity.
Show answer and explanation
Correct answers: A, C
Explanation
To optimize query performance and reduce costs when querying data in Amazon S3 with Amazon Athena, it is best practice to partition the data based on common query patterns (e.g., date or region) and use columnar formats like Parquet or ORC with compression. These techniques reduce the amount of data Athena needs to scan, improving performance and lowering costs. Avoiding partitioning or relying on row-based formats like CSV would lead to inefficient queries, while indexing is not directly supported in S3 and is managed at the query engine or format level.
- A. Correct.
Partitioning data based on common query filters (e.g., date or region) allows Athena to scan only the relevant partitions, significantly improving query performance and reducing costs.
- B. Incorrect.
Row-based storage formats like CSV are not optimized for analytical queries. Columnar formats like Parquet or ORC are more efficient for queries involving large datasets.
- C. Correct.
Compressing data using columnar formats like Parquet or ORC not only reduces storage costs but also improves query performance by reducing the amount of data that needs to be scanned.
- D. Incorrect.
Amazon S3 does not have a native indexing service. Indexing in the context of data optimization is usually handled by the query engine, such as Athena, and the data format (e.g., Parquet).
- E. Incorrect.
Avoiding partitioning is not a best practice. Partitioning is crucial for optimizing query performance and cost, especially when dealing with large datasets.