A company has collected more than 100 TB of log files in the last 24 months. The files are stored as raw text in a dedicated Amazon S3 bucket. Each object has a key of the form year-month…

While Apache Avro is a good data format, Apache Parquet (columnar) is generally more efficient for analytical queries in Athena, especially when querying a subset of columns, offering superior performance and cost benefits.

Adding a key prefix of the form date=year-month-day/ to S3 objects effectively partitions the data, allowing Athena to perform partition pruning and scan only relevant data, significantly reducing data scanned and query costs.

Converting log files to Apache Parquet format, a columnar storage format, dramatically reduces the amount of data Athena needs to scan when querying a subset of columns, leading to lower costs and faster query execution.

Adding a key prefix of the form year-month-day/ is a valid partitioning strategy, but using the 'key=value/' format (e.g., date=year-month-day/) is the standard for `MSCK REPAIR TABLE` to automatically discover partitions; without it, manual partition addition might be needed, increasing maintenance.

Using ALTER TABLE ADD PARTITION requires manually defining each partition, which becomes high-maintenance for daily log files over 24 months. MSCK REPAIR TABLE (Option F) is the preferred low-maintenance approach for adding many partitions.

After partitioning data in S3, dropping and recreating the Athena table with a PARTITIONED BY clause and then running MSCK REPAIR TABLE automatically discovers and registers new partitions, providing a low-maintenance way to update the table metadata.