Question

A data engineer stores clickstream data in S3 under the prefix s3://analytics-lake/events/year=2024/month=03/day=15/ using Hive-style partition folders. A Glue Data Catalog table was created by a crawler two months ago. New daily folders are being written continuously by a Firehose stream, but Athena queries that filter on recent dates return zero rows even though the files exist in S3. The engineer confirms the schema and file format are correct. What is the MOST cost-effective way to ensure Athena can query the newly added partitions going forward with minimal ongoing operational effort?

Accepted Answer

Enable partition projection on the table by defining the partition key ranges and formats in the table properties, so Athena calculates partitions at query time.

Answer

Run the Glue crawler on a manual schedule every hour to detect and register the new partition folders in the Data Catalog.

Answer

Run MSCK REPAIR TABLE in Athena after each new file is delivered by Firehose to synchronize the partition metadata.

Answer

Recreate the table as an unpartitioned external table so Athena scans all files and never misses new data.