Using Athena System Tables to Find Expensive Queries and Unused Work

An Athena environment can look healthy even when a lot of money and time is being wasted.

Queries may be running successfully. Users may not be seeing errors. Dashboards may still load.

But behind the scenes, you can have queries scanning far more data than necessary, users repeatedly running the same expensive SQL, workgroups spending most of their time waiting, or tables that were created months ago and are barely used.

Finding these problems usually requires more than looking at the Athena query editor.

Amazon Athena now provides system tables that make this type of investigation much easier. The query executions system table records completed Athena query activity and exposes information such as the query identity, workgroup, execution time, amount of data scanned, query text, and tables referenced by the query.

This gives developers and data teams a way to investigate Athena usage directly with SQL instead of building a separate query-history collection system.

The useful part is not simply seeing query history.

The real value comes from being able to turn that history into questions such as:

Which queries scanned the most data?
Which teams are generating the largest Athena workload?
Which tables are being queried regularly?
Which tables have little or no query activity?
Which queries are slow because of execution time?
Which queries spend too much time waiting?
Which users are repeatedly running expensive SQL?
Are failed queries consuming a significant amount of work?
Is an expensive workload actually being used by anyone?

That makes Athena system tables useful for performance reviews, cost investigations, workload cleanup, and capacity planning.

What the Athena Query Executions System Table Gives You

The query executions system table contains information about completed Athena queries.

Among other fields, it provides information about:

Information	Why it matters
Query execution ID	Identify a specific execution
Workgroup	Understand which workload generated the query
Principal ARN	Identify the IAM identity that submitted it
Query text	Understand what the user or application executed
Submission time	Find queries in a time window
Completion time	Measure when execution finished
Total execution duration	Find slow queries
Engine execution duration	Separate engine processing from other time
Query planning duration	Identify planning overhead
Queue time	Find workloads spending time waiting
Data scanned	Find expensive queries
Tables accessed	Understand table usage
Query state	Separate successful and failed work
Error information	Investigate recurring failures

The table is especially useful because all of this information can be analyzed together.

For example, finding a query that scanned 500 GB is interesting.

Finding that the same query ran 2,000 times during the last month is much more interesting.

Finding that it ran 2,000 times, scanned 500 GB each time, and is responsible for most of a team's Athena usage gives you a clear optimization target.

Start With the Largest Data Scans

One of the easiest ways to find expensive Athena queries is to look at the amount of data scanned.

A query that scans a large amount of data is not automatically badly designed. Some analytical queries genuinely need to process large datasets.

The problem is when a query repeatedly scans large amounts of data for a small result.

Start with a report like this:

SELECT
    query_execution_id,
    work_group_name,
    principal_arn,
    state_enum,
    data_scanned_size,
    total_execution_duration,
    submission_date_time
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
ORDER BY data_scanned_size DESC
LIMIT 50;

This gives you a starting list of the largest scans from the last seven days.

Do not immediately assume that the first query in the list needs to be rewritten.

First look at the context.

Ask:

Is this a scheduled batch job?
Is the query part of a reporting pipeline?
Does it run once a day or hundreds of times?
Is the scanned data expected?
Is the table partitioned?
Is the query filtering on the partition column?
Is the result actually used?

That last question is often overlooked.

A technically correct query can still be operationally wasteful if nobody uses its result.

Convert Scanned Bytes Into Something Easier to Read

The raw data scanned value is useful for machines but not particularly friendly when reviewing a report.

You can convert it into gigabytes or terabytes.

SELECT
    query_execution_id,
    work_group_name,
    principal_arn,
    ROUND(
        CAST(data_scanned_size AS DOUBLE) / POWER(1024, 3),
        2
    ) AS scanned_gb,
    ROUND(
        CAST(total_execution_duration AS DOUBLE) / 1000,
        2
    ) AS duration_seconds,
    submission_date_time
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
ORDER BY scanned_gb DESC
LIMIT 50;

Now a performance review can show something easier to understand:

Query A    842 GB
Query B    615 GB
Query C    490 GB
Query D    301 GB

This makes it easier to identify the queries worth investigating first.

Look for Repeated Expensive Queries

A single large query might be acceptable.

A large query executed hundreds of times is a different problem.

Group the query history by the identity that submitted the query.

SELECT
    principal_arn,
    COUNT(*) AS query_count,
    SUM(data_scanned_size) / POWER(1024.0, 4) AS scanned_tb
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
  AND state_enum = 'SUCCEEDED'
GROUP BY principal_arn
ORDER BY scanned_tb DESC;

This tells you which identities generated the largest amount of scanned data.

For example, you might discover:

ReportingRole      18.7 TB
AnalyticsRole       9.4 TB
DataScienceRole     5.2 TB
ApplicationRole     1.8 TB

That immediately gives you a direction for investigation.

The next question is not simply "Why is ReportingRole expensive?"

It is:

"Which queries from ReportingRole are responsible for the usage?"

That leads to a more detailed query review.

Find the Worst Queries From One Identity

Suppose the reporting role is responsible for most of the scanned data.

You can narrow the investigation:

SELECT
    query_execution_id,
    data_scanned_size / POWER(1024.0, 3) AS scanned_gb,
    total_execution_duration / 1000.0 AS duration_seconds,
    query_string,
    submission_date_time
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
  AND principal_arn = 'arn:aws:iam::111111111111:role/ReportingRole'
ORDER BY data_scanned_size DESC
LIMIT 20;

Now you can inspect the actual SQL.

Look for patterns such as:

SELECT *

without a real need for every column.

Large scans caused by missing partition filters.

Repeated queries against the same large dataset.

Joins that bring in much more data than necessary.

Queries that are executed frequently but return only a small result.

This is where query history becomes more useful than a simple billing report.

The billing report can tell you that data scanning is high.

The query history can help explain why.

Find Queries That Scan a Lot but Run Very Often

Another useful metric is the combination of frequency and scan size.

Imagine two queries:

Query A
Scans 500 GB
Runs once a month

Query B
Scans 100 GB
Runs 50 times every day

Query B is probably the better optimization target.

You can identify repeated query patterns by grouping similar SQL text.

A simple starting point is:

SELECT
    query_string,
    COUNT(*) AS execution_count,
    SUM(data_scanned_size) / POWER(1024.0, 4) AS total_scanned_tb,
    AVG(total_execution_duration) / 1000.0 AS avg_duration_seconds
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
  AND state_enum = 'SUCCEEDED'
GROUP BY query_string
ORDER BY total_scanned_tb DESC
LIMIT 20;

This works well when applications submit the same SQL repeatedly.

However, many applications generate SQL dynamically.

For example:

WHERE order_date = '2026-11-01'

and:

WHERE order_date = '2026-11-02'

are logically the same workload but have different query strings.

For those workloads, you may need to normalize the SQL before grouping it. That can be done in a downstream reporting layer if your environment requires more advanced workload analysis.

Find Slow Queries

Data scanned is only one part of performance.

A query may scan a reasonable amount of data but still take too long.

Use execution duration to find those queries.

SELECT
    query_execution_id,
    principal_arn,
    work_group_name,
    total_execution_duration / 1000.0 AS duration_seconds,
    data_scanned_size / POWER(1024.0, 3) AS scanned_gb,
    submission_date_time
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
  AND state_enum = 'SUCCEEDED'
ORDER BY total_execution_duration DESC
LIMIT 50;

Then compare duration with scanned data.

For example:

Query	Data scanned	Duration
Query A	900 GB	42 seconds
Query B	120 GB	5 minutes
Query C	700 GB	51 seconds
Query D	90 GB	7 minutes

Query D deserves attention even though it scans much less data.

This is why cost analysis and performance analysis should not be treated as exactly the same problem.

Separate Queue Time From Execution Time

A slow query does not always mean the SQL itself is slow.

Athena exposes several execution-duration fields, including queue time, planning time, and engine execution time.

This allows you to separate different causes.

For example:

SELECT
    query_execution_id,
    principal_arn,
    work_group_name,
    total_execution_duration / 1000.0 AS total_seconds,
    query_queue_time_duration / 1000.0 AS queue_seconds,
    query_planning_duration / 1000.0 AS planning_seconds,
    engine_execution_duration / 1000.0 AS engine_seconds
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '24' HOUR
  AND state_enum = 'SUCCEEDED'
ORDER BY total_execution_duration DESC
LIMIT 30;

Suppose you see:

Total time       180 seconds
Queue time        95 seconds
Planning time      5 seconds
Engine time       80 seconds

Rewriting the SQL may not solve the biggest problem.

The query spent more than half of its time waiting.

That points you toward workgroup configuration, concurrency, and workload scheduling rather than immediately changing the SQL.

On the other hand, if the queue time is small and engine execution consumes most of the duration, query and data-layout optimization becomes more interesting.

Find Tables That Are Actually Being Used

Query history can also help answer a different question:

Which tables are being used?

The query executions system table contains information about tables referenced by completed queries.

You can expand those references using UNNEST.

SELECT
    t.catalog_name,
    t.database_name,
    t.table_name,
    COUNT(*) AS query_count
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
CROSS JOIN UNNEST(tables_accessed) AS t
    (catalog_name, database_name, table_name)
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
GROUP BY
    t.catalog_name,
    t.database_name,
    t.table_name
ORDER BY query_count DESC;

This can produce a simple usage ranking:

orders              8,420 queries
transactions         5,180 queries
customer_events      2,760 queries
old_reporting        14 queries
legacy_exports        2 queries

Now you have evidence about actual Athena query activity.

That is much better than asking a team whether a table is still used and relying entirely on someone's memory.

Finding Potentially Unused Tables

This is where things become more interesting.

Athena query history can tell you which tables were referenced by completed queries.

If you combine that information with a known inventory of your tables, you can identify candidates that have not been queried recently.

The important word here is candidates.

You should not immediately delete a table simply because Athena history shows no recent queries.

A table may be used by:

Another AWS account
Another Region
A different query engine
An application that accesses S3 directly
A scheduled process outside Athena
A compliance process
A downstream data pipeline

So use query history as evidence, not as the only deletion decision.

A safer process looks like this:

Athena history
      |
      v
No recent references
      |
      v
Check other consumers
      |
      v
Check ownership
      |
      v
Check retention requirements
      |
      v
Mark as candidate
      |
      v
Confirm with owner
      |
      v
Archive or remove

This avoids turning an analytics cleanup exercise into an outage.

Find Tables With Very Low Usage

Completely unused tables are not the only problem.

Some tables are queried only a handful of times.

That can still matter when the underlying storage is large and expensive to maintain.

For example:

Table                  Queries in 30 days

orders                  8,420
customer_events         3,180
monthly_reports           420
legacy_customer_dump       12
old_archive                 2

The last two tables deserve review.

But again, low Athena usage does not mean no business value.

A table might be queried only once a quarter and still be required.

The goal is to find candidates for review, not automatically delete them.

Compare Workgroups

Workgroups are useful boundaries for analyzing workloads.

For example:

SELECT
    work_group_name,
    COUNT(*) AS query_count,
    SUM(data_scanned_size) / POWER(1024.0, 4) AS scanned_tb,
    AVG(total_execution_duration) / 1000.0 AS avg_duration_seconds
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '30' DAY
GROUP BY work_group_name
ORDER BY scanned_tb DESC;

This can help identify workloads that need attention.

You might discover that one workgroup generates most of the scanned data while another has very low activity.

That can lead to better questions:

Is the busy workgroup serving production workloads?
Is it running scheduled jobs?
Is it being used for ad hoc analysis?
Are users sharing the same workgroup?
Should workloads be separated?
Are query limits and controls configured appropriately?

Workgroup-level analysis is especially useful in larger teams because it gives you a way to compare workload groups rather than individual queries only.

Find Failed Queries That Keep Repeating

Failures are another form of wasted work.

Suppose an application runs a query every five minutes and the query fails because of a permanent SQL or permission problem.

The application may continue retrying it.

You can find recurring failures with:

SELECT
    principal_arn,
    error_category_enum,
    error_message,
    COUNT(*) AS failure_count
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
  AND state_enum = 'FAILED'
GROUP BY
    principal_arn,
    error_category_enum,
    error_message
ORDER BY failure_count DESC
LIMIT 30;

This is useful because a failed query is not necessarily harmless.

Repeated failures can create unnecessary workload, increase operational noise, and indicate an application problem.

If one role generated hundreds of identical failures, investigate the caller rather than treating each failure as an isolated Athena event.

Look at the SQL Before Changing the Infrastructure

One mistake teams make is jumping directly to infrastructure changes.

For example:

Athena query is slow
        |
        v
Increase everything

That is rarely a good first response.

Start with the query history.

Look at:

Data scanned
Execution time
Queue time
Planning time
Workgroup
Principal
Referenced tables
Query frequency

Then inspect the SQL.

You may find a simple issue such as:

Missing partition filters
Reading unnecessary columns
Joining tables before filtering
Repeated scans of the same data
Querying raw data when an aggregated table would work
Running the same expensive report too frequently

A small query change can sometimes be more useful than changing the surrounding architecture.

A Practical Performance Review Query

For a weekly review, you can combine several useful metrics.

SELECT
    query_execution_id,
    principal_arn,
    work_group_name,
    state_enum,
    ROUND(
        CAST(data_scanned_size AS DOUBLE) / POWER(1024, 3),
        2
    ) AS scanned_gb,
    ROUND(
        CAST(total_execution_duration AS DOUBLE) / 1000,
        2
    ) AS duration_seconds,
    ROUND(
        CAST(query_queue_time_duration AS DOUBLE) / 1000,
        2
    ) AS queue_seconds,
    SUBSTR(query_string, 1, 200) AS query_preview,
    submission_date_time
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
ORDER BY data_scanned_size DESC
LIMIT 100;

This is a good starting report because it puts several important signals next to each other.

You can then investigate the top results rather than reviewing every query manually.

Build a Simple Optimization Process

A practical Athena optimization process can be kept simple.

Step 1: Find the largest scans

Start with queries that scanned the most data.

Step 2: Check how often they run

A large query executed once may be less important than a smaller query executed thousands of times.

Step 3: Check execution time

Look at both total duration and engine execution time.

Step 4: Check queue time

If queue time is large, the problem may be workload concurrency rather than SQL.

Step 5: Inspect the tables

Look for large tables, missing partition filters, and repeatedly accessed datasets.

Step 6: Inspect the SQL

Look for unnecessary columns, broad scans, repeated joins, and inefficient filtering.

Step 7: Find unused or low-use tables

Treat these as cleanup candidates, not automatic deletion targets.

Step 8: Measure again

After changing a query, compare the new execution history with the old workload.

This last step is important.

Optimization should be measured rather than assumed.

Important Limitations

Athena system tables are useful, but they are not a complete observability platform.

The query executions system table contains completed query execution information.

It should not be treated as a real-time view of queries currently running.

The data is also scoped to an AWS account and Region, so organizations with many Athena accounts may need a separate central reporting approach.

The tables referenced by a query are useful for workload analysis, but they should not be confused with a complete data-lineage system.

Most importantly, query history should not be treated as the only source of truth for deciding whether a table can be deleted.

Use it alongside ownership information, application dependencies, data pipelines, retention requirements, and business requirements.

Common Mistakes

Looking Only at Query Count

A team can run thousands of small queries and still cost less than a team running a few huge scans.

Always consider scanned data as well as query count.

Optimizing the Slowest Query First

The slowest query is not necessarily the most important query.

A query that runs for ten minutes once a month may matter less than a two-minute query that runs thousands of times.

Ignoring Queue Time

High total duration does not automatically mean slow SQL.

Check how much time the query spent waiting.

Deleting Tables Based Only on Athena History

No recent Athena query does not prove that a table is unused.

Check other consumers before deleting anything.

Giving Query History to Everyone

Query text can contain sensitive information.

Control access to system table data appropriately.

Scanning the Entire History for Every Report

Use time filters and appropriate partition filters.

Do not repeatedly scan years of query history to build a daily dashboard.

Advantages and Tradeoffs

Using Athena system tables for workload analysis has several practical advantages.

Advantage	Explanation
No custom polling job	Query history is available as system table data
SQL-based analysis	Developers can investigate usage with Athena SQL
Detailed execution data	Timing, scanned data, identity, and workload information are available
Easy reporting	Queries can be turned into views and dashboards
Useful for optimization	Expensive and slow workloads can be ranked
Useful for cleanup	Low-use table candidates can be identified

There are also limitations.

Tradeoff	Explanation
Completed queries only	Not a replacement for real-time monitoring
Account and Region scope	Multi-account reporting needs additional design
Sensitive query text	Access needs to be controlled
Not complete lineage	Referenced tables are not a full lineage solution
Requires interpretation	Low usage does not automatically mean unused

Summary

Athena system tables give developers a much better way to investigate query workload without immediately building a custom audit pipeline.

The query executions system table can show which identities are running queries, how much data they scan, how long they take, which workgroups generate the activity, which tables are referenced, and where failures are occurring.

The most useful approach is to combine these signals.

Do not look only for the largest query.

Look for the query that is large and repeated.

Do not look only for the slowest query.

Check whether the time is actually spent running the engine or waiting in the queue.

Do not delete a table simply because Athena has not queried it recently.

Use the history to identify candidates, then confirm the table's other consumers and business purpose.

A good Athena performance review should therefore look something like this:

Query history
      |
      v
Find high scan workloads
      |
      v
Check frequency
      |
      v
Check execution and queue time
      |
      v
Inspect SQL and tables
      |
      v
Optimize expensive workloads
      |
      v
Review low-use datasets
      |
      v
Measure again

The important change is that much of this investigation can now be done directly with SQL against Athena's own system data. That removes a lot of the custom infrastructure traditionally used just to understand what is happening inside an Athena environment.