Reduce Athena scan costs with documented query controls

Why this is worth a look

Athena documents that a partition in a WHERE clause limits scanning to that partition. QueryExecutionStatistics reports DataScannedInBytes, the number of bytes in the queried data. A workgroup per-query limit cancels queries that exceed its threshold, while aggregate workgroup thresholds send alerts instead. Eligible result reuse can bypass execution within the same workgroup.

A read-only Athena SELECT based on the documented impressions example. Run it in the Athena query editor against a workgroup and table where impressions and dt exist, then inspect DataScannedInBytes in the query execution statistics. The one-hour string range is the example time window.

SELECT dt, impressionid
FROM impressions
WHERE dt < '2009-04-12-14-00'
  AND dt >= '2009-04-12-13-00'
ORDER BY dt DESC
LIMIT 100;

-- After execution, inspect DataScannedInBytes in the query execution statistics.

How to confirm it

1. 01

   Measure a representative period

   Run the existing read-only SELECT statements in their normal Athena workgroup and inspect DataScannedInBytes in each query's execution statistics. Use query history for a representative period to identify repeated high-scan patterns. Keep the workgroup and time window consistent when comparing results.

2. 02

   Add a partition predicate

   For a partitioned table, add the partition key to the WHERE clause so Athena scans only the specified partition. Use the documented dt example as a syntax reference. Hive-style paths can be loaded with MSCK REPAIR TABLE; non-Hive layouts require ALTER TABLE ADD PARTITION.

3. 03

   Set one per-query limit

   In the workgroup execution controls, set the Data scanned limit for the workload. Athena allows one per-query limit per workgroup, applies it to each query in that workgroup, and cancels a query that exceeds it. The documented range is 10 MB through 7 EB.

4. 04

   Add aggregate alerts

   For aggregate usage, configure multiple hourly or daily workgroup thresholds with CloudWatch alarms and Amazon SNS notifications. These thresholds apply to all queries in the workgroup and do not automatically cancel queries. Separate workloads into workgroups when they need different per-query limits.

5. 05

   Enable reuse selectively

   Set a maximum reuse age only when the permitted result age matches the workload. Athena can bypass execution for a matching prior result in the same workgroup when the documented query, catalog, database, result configuration, access, and storage conditions are met. Check DataScannedInBytes after a repeated run.

Primary sources