Exam AWS Certified Data Analytics - Specialty topic 1 question 65 discussion

D :can save from 30% to 90% on your per-query costs and get better performance by compressing, partitioning, and converting your data into columnar formats. B: For partition

B and D are the right answers. Some background: Snappy compresses the data to help with I/O, it roughly does the same level of compression for both parquet and AVRO. AVRO stores the data in row format and does not compresses the data. However, Parquet is a columnar store (without any additional compression algorithm like snappy applied), it natively compresses the data by 2X to 5X on average. A) Since Parquet does a better job in compression, this option is incorrect B) This is correct since data is partitioned with keys (year, month, day) with medium cardinality. C) Even though ORC and Parquet are both columnar storage formats and both supported by Athena, Since no compression is used in this option, we can safely ignore this. D) Parquet with Snappy is a better choice than ORC with no compression, so this is correct. E) Adding sensor(ID) to the partition creates high cardinality on the partitions and may lead to multiple small files under each partition which will slow down performance. So, B is a better option as you can keep all 50 sensor data in a single file for a day.

In the question it is mentioned that "Athena to observe changes in a captured metric over time, such as water temperature or acidity." No signs of using a specific sensor and then observe metrics. So if we introduce sensor in partition and not filter it in the query you are introducing an additional partition to search. The request of the question it makes sense that we use B

BD : Makes the most sense

DE: I passed the test

Partitioning by sensor, year, month, and day (option E) would likely increase costs as compared to partitioning by only year, month, and day (option B) because it would create a larger number of smaller partitions. Each partition would contain data from a single sensor for a given date range, resulting in more small files that would need to be scanned by Athena for each query. So B is better answer than E

partition by sensor and then by year/month/day make sense, parquet with snappy gives best compressions

B partition strategy better than E. D for sure.

D is an obvious choice. E has the highest potential to save costs also for queries that filter the sensor and the task is to find the solution with the most cost savings

Correct answers are B & D Option B as the data can be partitions by year, month, and day as it needs to be analyzed using captured metrics over time and not specific to any sensor. Option D as columnar data format helps to improve query performance. Options A & C are wrong as Avro and ORC without compression would not provide query performance similar to parquet with compression. Option E is wrong as the data needs to be analyzed as per the metrics and not specific to a particular sensor.

B and D. They are will query by time, not sensor id.

If possible, avoid having a large number of small files – Amazon S3 has a limit of 5500 requests per second. Athena queries share the same limit. https://docs.aws.amazon.com/athena/latest/ug/performance-tuning.html

Because more and optimal number of partitions can be done through the option E. Snappy compression with Parquet format, allows easy integration and maximum storage saving.

Answer is B & D

B and D are the right answers.

The metrics of temperature and acidity may be varying between different locations of the lake, students may want to see if any issues at a particular location level based on metrics. So its advisable to partition by Sensor

I vote A & E. Vote for A because "gather JSON-formatted batches of water quality values in Amazon S3" is the requirement. We can't compress the Json format file using Parquet or ORC.