Checking Athena Query History Without Building Your Own Audit Tables

If you have been running Amazon Athena for a while, you probably know the usual way of checking query history.

You open the Athena console, look at Recent queries, or use APIs such as ListQueryExecutions and GetQueryExecution.

That works well when you only need to answer a question about a few recent queries.

The problem starts when you want to analyze query activity over time.

For example:

Which users are running the most Athena queries?
Which workgroup is generating the most activity?
How much data did a team scan last week?
Which queries are taking unusually long?
Which tables are being queried most often?
How many queries are failing?
Which users are responsible for most of the scanned data?

Historically, teams often solved these problems by collecting Athena API results and writing them into their own S3 audit tables.

That means another scheduled job, another Lambda function, another storage location, another table schema, and another process to maintain.

Athena now provides a simpler option.

Amazon Athena system tables automatically capture completed query execution information in a managed S3 Tables-backed table called query_executions. The table covers Athena workgroups in your account and Region and can be queried directly from Athena.

This changes the architecture considerably.

Instead of:

Athena
   |
   v
API polling job
   |
   v
Lambda / scheduled process
   |
   v
S3
   |
   v
Custom audit table
   |
   v
Athena analysis

you can use:

Athena
   |
   v
System Tables
   |
   v
query_executions
   |
   v
SQL analysis

The rest of this article walks through how that works and where it fits in a real AWS environment.

What Athena System Tables Are

Athena system tables are managed tables that expose Athena usage information as queryable data.

When system tables are enabled, Athena writes usage information to S3 Tables in your account. The data uses Apache Iceberg format, and Athena manages the table structure, partitioning, compaction, and snapshot maintenance.

For query history, the currently supported system table is:

query_executions

It contains one row for each completed query execution.

A query is written after it reaches a final state such as:

SUCCEEDED
FAILED
CANCELLED

Queries that are still QUEUED or RUNNING are not yet written to the table. Once a row is delivered, the data is immutable.

This distinction is important.

If someone runs a query and you immediately search the system table while that query is still running, you should not expect to find it there yet.

Enabling Athena System Tables

System tables are an opt-in feature.

They are enabled at the AWS account and Region level. Once enabled, the configuration covers the current and future Athena workgroups in that account and Region.

From the Athena console, the general process is:

Open the Athena console.
Go to Administration.
Open System tables.
Choose Configure.
Enable system tables.
Review the supported tables.
Configure encryption if required.
Save the configuration.

AWS currently provides the query_executions system table.

After enabling the feature, Athena starts publishing completed query information to the system table. AWS states that the data becomes available within minutes of query completion.

There is no Lambda function to deploy and no scheduled API polling job to maintain.

Where the Query History Is Stored

This part is slightly different from the Athena query results you may already be familiar with.

Athena stores system table data in an S3 Tables bucket named:

aws-athena

The query execution table is organized under the metadata namespace:

aws-athena
    |
    +-- metadata
          |
          +-- query_executions

The fully qualified table name is:

"s3tablescatalog/aws-athena"."metadata"."query_executions"

You can query it directly:

SELECT *
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
LIMIT 10;

AWS manages the underlying S3 Tables infrastructure, including table schema and maintenance. You do not insert rows into this table yourself.

That is one of the biggest differences from building your own audit table.

What Information Does query_executions Contain?

The table provides considerably more information than just:

Query ID
Query text
Status

It includes execution identity, workgroup, timing, query text, scanned data, execution durations, and tables referenced by the query.

Some useful columns include:

Column	What it tells you
query_execution_id	Unique ID for the Athena query
work_group_name	Workgroup where the query ran
account_id	AWS account where it ran
region_name	AWS Region
query_string	SQL submitted to Athena
principal_arn	IAM principal that submitted the query
submission_date_time	When execution started from the service perspective
completion_date_time	When it reached a final state
total_execution_duration	Total execution time
engine_execution_duration	Time spent in the query engine
query_planning_duration	Time spent planning
query_queue_time_duration	Time waiting in the queue
tables_accessed	Tables referenced by the SQL
state_enum	Final query state
error_category_enum	Error category for failed queries
error_message	Error information
data_scanned_size	Amount of data scanned

This gives you enough information to build useful operational reports without first creating an ETL pipeline for Athena query history.

Find Your Recent Queries With SQL

The simplest useful query is to retrieve recent executions.

SELECT
    query_execution_id,
    work_group_name,
    principal_arn,
    state_enum,
    submission_date_time,
    completion_date_time,
    data_scanned_size
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '24' HOUR
ORDER BY submission_date_time DESC;

This gives you a basic operational view.

Instead of opening the Athena console and manually checking query history, you can now use SQL to answer the same question.

You can also create a view around this query if your team repeatedly needs the same operational report.

Find Queries Submitted by a Specific User or Role

The principal_arn column is useful when several people or applications share the same Athena environment.

For example:

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

This can help answer:

What has this application role been doing in Athena?

It is also useful for troubleshooting.

If an application suddenly starts generating failed queries, you can identify the execution history for that principal instead of searching through application logs first.

Find the Most Expensive Queries

Athena query cost is strongly related to the amount of data scanned.

The system table includes the amount of data scanned, so you can rank queries by scan size.

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

Now you have a simple report showing the largest scans.

This is much more useful than looking only at query count.

A team running 5,000 small queries may actually cost less than another team running 100 queries that scan enormous amounts of data.

Find the Users Scanning the Most Data

You can aggregate the same information by IAM principal.

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

This gives you a different view of the environment.

Instead of asking:

Which query is expensive?

you are asking:

Which identity is generating the most scanned data?

That distinction becomes useful when working with multiple teams.

For example:

AnalyticsRole       18.4 TB
ReportingRole         6.2 TB
DataScienceRole       3.7 TB
ApplicationRole       1.1 TB

You can then investigate the workload that is responsible for the largest share of data scanning.

Find the Most Frequently Queried Tables

The tables_accessed column contains a list of table references.

Because it is an array, you can use UNNEST to turn those references into individual rows.

SELECT
    t.catalog_name,
    t.database_name,
    t.table_name,
    COUNT(*) AS access_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 access_count DESC
LIMIT 20;

This is useful for understanding actual data usage.

You may discover that:

orders
customer_events
transactions

are queried constantly while several other tables are barely touched.

That information can help with data lifecycle decisions, partitioning work, access reviews, and workload optimization.

One important detail is that tables_accessed represents table references found in the SQL text. It should not automatically be treated as a perfect record of every physical data operation performed underneath the query. AWS describes these as syntactic references.

Find Failed Queries

Query failures are another area where a system table becomes useful.

For example:

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

This can quickly show recurring failure patterns.

For example, if the same error appears hundreds of times, you may have a broken application query, missing permissions, an incorrect table reference, or a data pipeline problem.

Instead of investigating failures one by one, you can group them.

Separate Queue Time From Engine Time

This is one of the more useful operational details in the new table.

Athena provides several duration fields, including:

total_execution_duration
engine_execution_duration
query_planning_duration
query_queue_time_duration
service_pre_processing_duration
service_processing_duration

That means you can investigate where the time is actually being spent.

For example:

SELECT
    query_execution_id,
    work_group_name,
    total_execution_duration / 1000.0 AS total_seconds,
    engine_execution_duration / 1000.0 AS engine_seconds,
    query_planning_duration / 1000.0 AS planning_seconds,
    query_queue_time_duration / 1000.0 AS queue_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 20;

This helps avoid a common troubleshooting mistake.

Suppose a query takes 90 seconds.

That does not automatically mean the SQL engine needed 90 seconds to process the data.

Part of that time may have been spent waiting in a queue or planning the query.

If queue time is consistently high, rewriting SQL may not solve the underlying problem.

You may need to investigate workgroup concurrency or workload distribution instead.

Find Queries That Take Longer Than Expected

You can also create a simple slow-query report.

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,
    SUBSTR(query_string, 1, 250) AS query_preview
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY
  AND state_enum = 'SUCCEEDED'
  AND total_execution_duration > 60000
ORDER BY total_execution_duration DESC
LIMIT 50;

Now you can investigate queries taking longer than one minute.

You can change the threshold depending on the workload.

For an interactive analytics environment, 60 seconds may already be too slow.

For a large batch workload, a five-minute threshold might be more useful.

Query by Workgroup

Workgroups are often where teams separate Athena workloads.

The system table includes work_group_name, making it easy to compare activity.

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 gives you a simple workload-level view.

You can use it for internal chargeback or showback models, provided the accounting rules in your organization match the way you interpret scanned data.

The Data Is Not Available While a Query Is Running

This is an important behavior to understand before building monitoring around the system table.

The query_executions table contains completed executions.

A query that is currently:

QUEUED

or:

RUNNING

does not yet have a row in the system table.

The row appears after the query reaches its final state.

So this is not a replacement for real-time query monitoring.

For example:

User starts query
       |
       v
RUNNING
       |
       |  Not yet in system table
       v
SUCCEEDED
       |
       v
System table receives row

If your application needs real-time status while a query is executing, continue using the appropriate Athena APIs or other operational mechanisms.

You Still Need to Think About Query Text Security

There is a security issue that should not be overlooked.

The query_string column contains the SQL submitted to Athena, and AWS notes that query text can contain sensitive information such as personally identifiable information or hardcoded credentials.

That means you should not give every developer unrestricted access to the system table simply because it is "only query history."

For example, this query:

SELECT query_string
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions";

may expose more information than your users should see.

Treat query history as operational data with security requirements.

Restrict read access through IAM and, where appropriate, Lake Formation according to your organization's governance model. AWS specifically recommends restricting access because system table data can contain sensitive information.

Configure Retention Instead of Keeping Everything Forever

Another difference from the old API-history model is retention.

Athena does not automatically give you an indefinite history through the console. Console query history is retained for 45 days. If you need longer retention using the traditional API approach, you have to retrieve and save the information yourself.

System tables change that model.

The system table data is stored in S3 Tables, and retention is controlled through S3 Tables record expiration. Athena does not set a default expiration for this data.

That means you should configure retention intentionally.

For example:

Development
    30 days

Production
    180 days

Compliance workload
    Organization-defined retention

The exact periods should come from your organization's operational and compliance requirements.

Keeping everything forever is not automatically better. It increases storage and can also increase the amount of historical sensitive query text that remains accessible.

Filter Partition Columns

System tables are partitioned.

AWS recommends using partition filters when querying them so that Athena can reduce the amount of data scanned. AWS specifically recommends checking the table schema with DESCRIBE and using appropriate partition columns such as work_group_name.

For example, avoid blindly running:

SELECT *
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions";

against a large historical dataset.

Instead, narrow the query:

SELECT
    query_execution_id,
    principal_arn,
    state_enum,
    data_scanned_size
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE work_group_name = 'analytics'
  AND submission_date_time >= CURRENT_TIMESTAMP - INTERVAL '7' DAY;

The exact partition strategy should be checked against the current schema in your environment.

The general rule is simple:

Do not scan years of query history when you only need last week's activity.

Does This Replace Custom Audit Tables?

For many Athena query-history use cases, it can remove a large amount of custom plumbing.

You may no longer need:

Scheduled Lambda
       |
       v
ListQueryExecutions
       |
       v
GetQueryExecution
       |
       v
Custom JSON
       |
       v
S3
       |
       v
Glue table
       |
       v
Athena report

Instead:

Athena
   |
   v
query_executions
   |
   v
SQL
   |
   +-- Cost analysis
   +-- Usage reports
   +-- Failure analysis
   +-- Performance analysis
   +-- Audit queries

However, that does not mean custom audit systems are always unnecessary.

You may still need a separate data platform if you want to combine Athena activity with:

CloudTrail events
Application logs
AWS billing data
Business ownership metadata
Ticketing information
Security events
Activity from multiple AWS accounts
Activity across multiple Regions

Athena system tables currently operate within a single AWS account and Region, and AWS does not provide built-in cross-account or cross-Region consolidation for them.

So think of system tables as a better source for Athena usage data, not necessarily as a replacement for your entire observability platform.

A Practical Architecture for a Team

For a typical analytics team, a reasonable setup could look like this:

                    Athena
                      |
                      v
              query_executions
                      |
          +-----------+-----------+
          |           |           |
          v           v           v
      Cost View   Usage View   Failure View
          |           |           |
          +-----------+-----------+
                      |
                      v
               Internal Dashboard

The views can answer questions such as:

How much data did each team scan?

Which queries failed most often?

Which workgroups are busiest?

Which tables are used most?

Which queries are taking longer?

Which IAM roles are generating the most activity?

And because the underlying data is populated automatically, the application does not need to maintain its own query-history ingestion process.

Common Mistakes

Treating System Tables as Real-Time Monitoring

The table contains completed executions.

Do not use it as your only source for detecting currently running queries.

Giving Everyone Access to Query Text

Query text can contain sensitive information.

Protect the table accordingly.

Ignoring Retention

System table data does not automatically expire according to a default retention period.

Configure S3 Tables record expiration intentionally.

Querying Without Filters

Historical query data can grow quickly.

Use appropriate time and partition filters.

Assuming It Covers Multiple Accounts

System tables currently work within one AWS account and Region.

If your organization has several Athena accounts, you need a separate strategy for centralizing that data.

Assuming tables_accessed Is a Perfect Lineage System

The field identifies table references in submitted SQL. It should not be treated as a complete physical data-lineage solution.

Rebuilding the Old API Polling Pipeline Anyway

If your only goal was to store Athena query execution history, first evaluate the system table before creating another Lambda-plus-S3 pipeline.

When a Custom Pipeline Still Makes Sense

There are situations where you may still want your own audit data store.

For example, suppose your organization has:

Account A
    Athena

Account B
    Athena

Account C
    Athena

and your security team wants one central dashboard.

Because Athena system tables are scoped to a single account and Region, you may need a separate collection mechanism to centralize data.

Similarly, if your audit requirements require data from Athena, CloudTrail, application logs, and billing records in one data model, a central pipeline may still be appropriate.

The key difference is that you no longer need to build custom infrastructure just to solve the basic "what queries ran?" problem inside one Athena account and Region.

Summary

Athena's query_executions system table provides a much cleaner way to analyze query history than building a custom API-polling pipeline.

It automatically records completed Athena query executions and exposes information about the query, identity, workgroup, execution timing, scanned data, failures, and referenced tables.

That makes several common operational tasks possible with ordinary SQL:

Find expensive queries
Find slow queries
Find failed queries
Track users
Compare workgroups
Measure scanned data
Find frequently accessed tables

The biggest change is architectural. Instead of writing a Lambda function that repeatedly calls Athena APIs and stores the results in your own audit table, you can query Athena's managed system table directly.

There are still boundaries to understand. The data is delivered after query completion, system tables are scoped to an account and Region, query text may contain sensitive information, and retention needs to be configured.

For teams that only need reliable Athena query-history analysis, those differences can eliminate a fair amount of infrastructure that previously had to be maintained by hand.