SAP-C02 Practice Question 10: Athena Data Lake Query Remediation
AWS Certified Solutions Architect – Professional (SAP-C02) · Test 1 · question 10 of 10
Analysts complain that ad hoc queries over an existing clickstream data lake have become unusable. Amazon Data Firehose lands about 12 TB of gzipped JSON per day into s3://logs-lake/clicks/YYYY/MM/DD/HH/, and the bucket now holds three years of history. An AWS Glue Data Catalog table points at s3://logs-lake/clicks/ with no partition columns defined. Analysts query the table in Amazon Athena, almost always with a predicate on a single day or a small date range, plus a filter on two of the table's ninety columns.
Queries that used to finish in seconds now run for tens of minutes, and the Athena bill is dominated by bytes scanned. The company wants query runtime and scanned bytes cut dramatically; analysts must keep using standard SQL in Athena rather than a different query tool; and no recurring job may be required to register each new hour of data as it arrives.
What should the solutions architect do?
Show answer and explanation
Answer:
Athena charges and spends time on bytes scanned, so the fix has to reduce what is read, and this option attacks every dimension of the problem at once. Partitioning by date lets Athena prune everything outside the queried day instead of reading three years of prefixes. Parquet is columnar, so a query touching 2 of 90 columns reads only those column chunks rather than parsing whole JSON records, and Snappy keeps it splittable for parallel reads. Partition projection then calculates partition values from the configured range and path template at query time instead of calling GetPartitions against the Data Catalog, which both removes the metadata-lookup cost on a highly partitioned table and, as the documentation puts it, "eliminates the need to specify partitions manually", so no crawler or ALTER TABLE ADD PARTITION job is needed as new hours arrive. Firehose supports record format conversion to Parquet natively, so new data lands in the optimized layout. Analysts keep writing ordinary Athena SQL.
Option A changes the pricing model, not the physics. Athena provisioned capacity buys dedicated processing capacity billed per DPU-hour and helps with concurrency and cost predictability, but the engine still reads every gzipped JSON object in the bucket for each query. Runtime stays in the tens of minutes and total cost most likely rises.
Option C is a partial fix that fails a stated constraint. An hourly crawler is precisely the recurring registration job the company excluded, and it adds cost and catalog churn. Partitioning alone also leaves the data as row-oriented gzipped JSON, so a single-day query still parses all 90 columns of roughly 12 TB. Query result reuse only helps when an identical query is re-run inside the reuse window, which is not how ad hoc analysis works.
Option D forces the analysts onto a different query tool and a cluster to size and operate, which the stem rules out, and it means continuously loading 12 TB per day into Redshift. If Redshift were desired, Redshift Spectrum over the same S3 data would still need the Parquet and partitioning work in option B to perform well; the storage layout is the real problem.
Reference: Use partition projection with Amazon Athena