Reduce Athena scans without mistaking alerts for limits
On this page
Unneeded scans raise Athena costs. Filter queries to the partitions you need and set a per-query scan limit, because alerts notify you but do not stop a query. Compare DataScannedInBytes, and reuse results only when older data is acceptable.
What you need first
- Tool
- Use the Athena query editor, the browser interface for running SQL in Amazon Athena. Start with an existing workgroup and a database containing a partitioned table. The example uses AWS's documented impressions table.
- Access
- Ask an authorized Athena colleague for read-only query statistics and current controls for the intended workgroup. Result reuse also requires access to referenced tables and Amazon S3 read access to the previous output file.
- If you do not use that tool
- Give the Athena workload owner the workgroup, database, table and required period. Ask them to run the read-only SELECT and return DataScannedInBytes, ResultReuseInformation, the per-query limit and hourly or daily alert thresholds.
Why this is worth a look
Skipping a partition filter can scan data outside the period you need and increase billed scan volume. Athena scans only the specified partition when the partition appears in the WHERE clause. DataScannedInBytes reports the bytes queried, so use it to compare scan volume, not as a currency total.
Start with this query
SQLRun this teaching example in the Athena query editor only when the selected database has the documented impressions table and loaded partitions. Record DataScannedInBytes and reuse status from execution statistics.
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;How to confirm it
- 01
Record scan volume for one workload
Choose one workgroup and a representative period. Ask the workload owner for DataScannedInBytes from existing SELECT executions, the current per-query limit and aggregate alert thresholds. Record ResultReuseInformation so reused results are not mistaken for partition-filter improvements.
- 02
Restrict the query to required partitions
Add the table's partition key to the WHERE clause. Use the example only with its documented table, or have the table owner adapt it to the schema and required period. Compare bytes scanned with the existing query and confirm the filtered result covers the business need.
- 03
Set the limit that cancels queries
Ask the workgroup owner to choose a per-query limit that fits the workload. In the Athena console, choose Workgroups, the workgroup name, Execution controls, then Edit controls. Set Data scanned limit and save. The range is 10 MB to 7 EB. One limit applies to every query in that workgroup, and queries exceeding it are canceled.
- 04
Use aggregate thresholds as alerts
Ask the workgroup owner to set hourly or daily scan thresholds and name a notification recipient. These alerts use Amazon CloudWatch alarms and Amazon Simple Notification Service (SNS). Multiple thresholds can cover the workgroup, but reaching one does not automatically cancel queries.
- 05
Reuse results only within an agreed age
Agree how old results may be before enabling Reuse query results in the Athena query editor. Set the reuse time to that age, up to seven days. Check eligibility: matching query, database, catalog and result configuration in the same workgroup, supported tables, and access to stored results. Managed query results are not supported. Confirm reuse through ResultReuseInformation after a repeated run.
Before making changes
Canceled queries are still charged and may leave partial results in Amazon S3, so a scan limit is not a zero-cost guarantee. Result reuse can return stale data because Athena does not check source changes until the maximum reuse age expires. Assume the example table and columns exist, dt is a string partition key, and partitions are loaded. This is a teaching example, not a query against your account. Ask an authorized colleague to confirm execution access before running it.