Amazon Athena introduces system tables for query execution observability

Many of you use Amazon Athena to run SQL queries across your data lake, powering everything from interactive analytics to scheduled reporting pipelines, often with limited observability into those workloads. As your Athena usage grows, with more users, workgroups, and queries, you need to know which queries are expensive, who ran them, whether performance is changing, and how to attribute costs.

Athena now offers system tables and today we introduce the first, query_executions, which automatically captures your usage data and makes it available for observability, usage analysis, and audit use cases. When you turn on system tables, usage data becomes available within minutes of query completion across your workgroups. Athena stores it as Apache Iceberg tables on Amazon S3 Tables, a capability of Amazon Simple Storage Service (Amazon S3), with configurable retention. You can query it directly with Athena while AWS manages the tables for you, including schema definition, partitioning, compaction, and snapshot maintenance. This removes the operational burden of managing the tables.

Athena provides usage data through APIs like GetQueryExecution, Amazon CloudWatch metrics, and AWS CloudTrail logs. With this launch, usage data is also available as an S3 Table that you can search, filter, aggregate, and join directly without building or maintaining custom pipelines. Platform teams can build cost allocation dashboards that show data scanned by workgroup, by user, or by table without custom extract, transform, and load (ETL) pipelines. Additionally, the AWS Agent Toolkit provides skills for querying system tables to surface usage patterns and optimization recommendations. Because Athena stores the data in the open Apache Iceberg format, you can also query it with DuckDB, Amazon Redshift, or another Iceberg-compatible engine.

In this post, we walk through what system tables are, how to get started, and, most importantly, what you can do with them.

The challenge: Understanding your Athena workloads at scale

Understanding your query workloads at scale has previously required more effort than running the queries themselves. Consider these common questions:

• Cost attribution: Which team ran the most expensive queries this month?
• Performance analysis: Are any queries getting slower, or scanning more data than usual?
• Audit and compliance: Who ran queries against a specific table last quarter?
• Operational awareness: What is the error rate for my production workload?
• Usage patterns: What does daily query volume look like, and when are peak hours?

Answering these questions used to mean stitching together the Athena API, Amazon CloudWatch metrics, and AWS CloudTrail logs yourself, or building custom pipelines to snapshot query history into a queryable format. In other words, you built analytics pipelines to analyze your analytics pipelines. System tables remove that undifferentiated heavy lifting.

What are system tables?

The first system table, query_executions, records one row for every query that completes (successfully or with an error) across your workgroups, capturing:

• Query identification: Query execution ID, query string, submission time.
• Context: Workgroup, catalog, database, tables.
• User identity: Who submitted the query.
• Performance: Total execution time, planning time, queue time and so on.
• Resource consumption: Bytes scanned, data processing unit (DPU) count.
• Outcome: Execution state (succeeded, failed, canceled), detailed error information.

Having all this information in one place changes how you can reason about your Athena workloads. Instead of investigating one query at a time through the console or API, you can ask questions across your entire query history. You can aggregate, filter, trend, and join, just as you would with any other analytical dataset. You can build dashboards, set up scheduled reports, or run ad-hoc queries when you need to understand what is happening.

Getting started

Turning on system tables takes only a few steps:

1. Open the Athena web console.
2. In the left navigation pane, under Administration, choose System tables.
3. Choose Configure, then turn on Enable system tables for all your current and future resources.
4. (Optional) Expand Data encryption to choose an AWS Key Management Service (AWS KMS) key. If you don’t choose one, Athena uses Amazon S3 managed keys (SSE-S3).
5. Choose Save. Athena provisions a service-managed AWS Simple Storage Service (Amazon S3) table bucket in your account and begins recording query executions across your workgroups.

After you turn it on, Athena automatically records new query executions. The system table becomes accessible like any other Athena table: from the query editor on the console, the API and SDKs, or the JDBC and ODBC drivers.

Derive insights from system tables with an AI agent

You can also explore your system tables by asking questions in natural language. Because they’re standard tables in the AWS Glue Data Catalog, you can point a Model Context Protocol (MCP)-compatible AI agent such as Kiro or Claude Code at them. The connection point is the AWS MCP Server, a managed MCP server that gives your agent access to AWS using your existing AWS Identity and Access Management (IAM) credentials.

For reliable results, use the open source Agent Skill for Athena system tables that AWS publishes in the AWS Agent Toolkit. A skill is a versioned package of instructions and vetted examples that teaches an agent to use a capability well. This one supplies the system table schemas and a library of example queries. It also encodes the conventions that keep queries correct and cost-efficient, such as filtering on the partition column and treating the tables as read-only. The agent then uses the right column names and writes partition-pruned SQL that scans less data, instead of the unpredictable results of free-form prompting. The skill is open source and designed to cover new system tables as they become available.

The agent turns each question into SQL, runs it through Athena, and returns the answer. You can keep the conversation going to refine results or chart a trend.

• Cost attribution: “Which workgroup scanned the most data last month, and how does that compare to the month before?”
• Audit and compliance: “List every query that accessed the customer_orders table last quarter and who ran them.”
• Operational awareness: “What’s the error rate for the production workgroup this week, excluding user syntax errors?”

Scope the IAM credentials your agent uses to the least privilege. Read-only access to Athena, AWS Glue, and the system tables is enough. No write permissions are required to ask questions. Because the AWS MCP Server uses IAM-based access controls and logs every API call to CloudTrail, you keep a full audit trail of what the agent did on your behalf.

What can you do with system tables?

In the following sections, we walk through the most common use cases with a representative query for each. These are a starting point. For a broader library of ready-to-run queries, see the example queries in the Agent Skill for Athena system tables or have an agent write them for you using the skill.

Usage patterns: Understand how Athena is being used

System tables give you a complete picture of how Athena is used across your organization. This is often the first thing teams explore, and it gives you a baseline understanding of your workloads.

Daily query volume:

Track query volume over time (swap ‘day’ for ‘hour’ to find peak hours):

SELECT
    DATE_TRUNC('day', submission_date_time) AS day,
    COUNT(*) AS query_count
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '30' DAY
GROUP BY 1
ORDER BY 1

Adoption trends by workgroup over time:

SELECT
    DATE_TRUNC('week', submission_date_time) AS week,
    work_group_name,
    COUNT(*) AS query_count,
    COUNT(DISTINCT principal_arn) AS distinct_users
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '90' DAY
GROUP BY 1, 2
ORDER BY 1, 2

Distribution of query sizes:

SELECT
    CASE
        WHEN data_scanned_size = 0 THEN '0 bytes'
        WHEN data_scanned_size < 1048576 THEN '<1 MB'
        WHEN data_scanned_size < 10485760 THEN '1-10 MB'
        WHEN data_scanned_size < 104857600 THEN '10-100 MB'
        WHEN data_scanned_size < 1073741824 THEN '100 MB-1 GB'
        WHEN data_scanned_size < 10737418240 THEN '1-10 GB'
        WHEN data_scanned_size < 107374182400 THEN '10-100 GB'
        ELSE '>100 GB'
    END AS scan_bucket,
    COUNT(*) AS query_count,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 2) AS percent_of_query_count
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '30' DAY
AND state_enum = 'SUCCEEDED'
GROUP BY 1
ORDER BY MIN(data_scanned_size)

Cost attribution: Allocate spend across teams and users

With the Athena data scanned pricing model, charges are based on the amount of data scanned, so attributing bytes scanned to users and workgroups maps directly to cost. With system tables, you can build chargeback and showback reports without standing up a custom metering pipeline. You can find the Regional per-TB price to estimate cost, or programmatically download up-to-date prices using the AWS Price List API.

Total data scanned by users over a billing period:

SELECT
    work_group_name,
    region_name,
    ROUND(SUM(data_scanned_size) / POWER(1024.0, 4), 3) AS total_tb_scanned,
    COUNT(*) AS query_count,
    ROUND(SUM(data_scanned_size) * 100.0 / SUM(SUM(data_scanned_size)) OVER (), 3) AS percent_of_total_tb_scanned
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '30' DAY
AND state_enum = 'SUCCEEDED'
GROUP BY 1, 2
ORDER BY 1, 2

Change work_group_name to principal_arn to break the same report down by user.

Performance analysis: Find and fix performance issues

Understanding performance trends helps you catch regressions early and optimize the queries that matter most.

Find the slowest queries in the last 7 days:

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

Detect workload performance issues over time:

Compare aggregate execution time in a workgroup this week against last week to spot trends before they become user-visible problems:

WITH weekly_stats AS (
    SELECT
        work_group_name,
        IF(submission_date_time >= DATE_TRUNC('week', CURRENT_DATE), 'this_week', 'last_week') AS period,
        COUNT(*) AS query_count,
        AVG(total_execution_duration) AS avg_exec_ms,
        APPROX_PERCENTILE(total_execution_duration, 0.95) AS p95_exec_ms,
        SUM(data_scanned_size) AS total_bytes_scanned
    FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
    WHERE submission_date_time >= DATE_TRUNC('week', CURRENT_DATE) - INTERVAL '7' DAY
    AND state_enum = 'SUCCEEDED'
    GROUP BY 1, 2
)
SELECT
    tw.work_group_name,
    lw.query_count AS last_week_queries,
    tw.query_count AS this_week_queries,
    lw.avg_exec_ms / 1000.0 AS last_week_avg_sec,
    tw.avg_exec_ms / 1000.0 AS this_week_avg_sec,
    ROUND((tw.avg_exec_ms - lw.avg_exec_ms) * 100.0 / lw.avg_exec_ms, 1) AS avg_pct_change,
    lw.p95_exec_ms / 1000.0 AS last_week_p95_sec,
    tw.p95_exec_ms / 1000.0 AS this_week_p95_sec
FROM weekly_stats tw
JOIN weekly_stats lw ON tw.work_group_name = lw.work_group_name
WHERE tw.period = 'this_week'
AND lw.period = 'last_week'
ORDER BY avg_pct_change DESC

Analyze queue time to understand capacity pressure:

SELECT
    work_group_name,
    DATE(submission_date_time) AS query_date,
    COUNT(*) AS query_count,
    AVG(query_queue_time_duration) / 1000.0 AS avg_queue_sec,
    APPROX_PERCENTILE(query_queue_time_duration, 0.50) / 1000.0 AS median_queue_sec,
    APPROX_PERCENTILE(query_queue_time_duration, 0.95) / 1000.0 AS p95_queue_sec,
    MAX(query_queue_time_duration) / 1000.0 AS max_queue_sec
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '14' DAY
GROUP BY work_group_name, DATE(submission_date_time)
HAVING AVG(query_queue_time_duration) > 1000
ORDER BY avg_queue_sec DESC

Find the top queries by data scanned:

Identify the queries that scan the most data. These are your biggest cost drivers and the best candidates for optimization (partitioning, predicate pushdown, format changes):

SELECT
    query_execution_id,
    work_group_name,
    principal_arn,
    submission_date_time,
    data_scanned_size / POWER(1024, 4) AS data_scanned_tb,
    total_execution_duration / 1000.0 AS execution_seconds,
    SUBSTR(query_string, 1, 300) AS query_preview
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '7' DAY
AND state_enum = 'SUCCEEDED'
ORDER BY data_scanned_size DESC
LIMIT 50

These queries help platform teams run Athena like a service, with service level objectives (SLOs), trend visibility, and early warning when something changes.

Audit and compliance: Know who ran what, and when

If your organization has regulatory or security requirements, system tables provide a structured, queryable audit trail.

All queries by a specific user in a time range:

SELECT
    query_execution_id,
    work_group_name,
    submission_date_time,
    state_enum,
    total_execution_duration / 1000.0 AS execution_seconds,
    data_scanned_size / POWER(1024, 2) AS data_scanned_mb,
    SUBSTR(query_string, 1, 200) AS query_preview
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE principal_arn = 'arn:aws:iam::111122223333:user/example-user' -- Replace with target user
AND submission_date_time >= CURRENT_DATE - INTERVAL '7' DAY
ORDER BY submission_date_time DESC

Queries that accessed a specific table:

SELECT
    query_execution_id,
    work_group_name,
    principal_arn,
    submission_date_time,
    state_enum,
    ROUND(data_scanned_size / POWER(1024.0, 3), 3) AS data_scanned_gb,
    SUBSTR(query_string, 1, 200) AS query_preview
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
CROSS JOIN UNNEST(tables_accessed) AS t(tbl)
WHERE tbl.database_name = 'my_database' -- Replace with target database
AND tbl.table_name = 'my_table' -- Replace with target table
AND submission_date_time >= CURRENT_DATE - INTERVAL '30' DAY
ORDER BY submission_date_time DESC

Because Athena stores system tables as Iceberg data in your own account, and only the Athena service can write records, they provide a reliable record of query activity for audit purposes. You control retention, access policies, and encryption.

Operational monitoring: Track error rates and workload health

System tables help you build operational visibility into your Athena workloads, beyond what CloudWatch aggregate metrics provide. Because the data is per-query, you can distinguish a harmless spike in user syntax errors from an actionable spike in system errors, which aggregate metrics can’t tell you apart.

Error rate by workgroup:

WITH error_stats AS (
    SELECT
        work_group_name,
        COUNT(*) AS total_queries,
        COUNT(*) FILTER (WHERE state_enum = 'FAILED' AND error_category_enum = 1) AS system_errors,
        COUNT(*) FILTER (WHERE state_enum = 'FAILED' AND error_category_enum = 2) AS user_errors
    FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
    WHERE submission_date_time >= CURRENT_DATE - INTERVAL '7' DAY
    GROUP BY work_group_name
)
SELECT
    work_group_name,
    total_queries,
    system_errors,
    ROUND(system_errors * 100.0 / SUM(system_errors) OVER (), 1) AS percent_of_all_system_errors,
    user_errors,
    ROUND(user_errors * 100.0 / SUM(user_errors) OVER (), 1) AS percent_of_all_user_errors
FROM error_stats

To investigate a spike, group the failed queries by error_category_enum and error_message to see exactly which errors are concentrated where.

Identify queries that exhausted resources:

These are good candidates for optimization: they might benefit from partitioning improvements, query rewrites, or additional capacity:

SELECT
    query_execution_id,
    work_group_name,
    principal_arn,
    submission_date_time,
    error_message,
    ROUND(data_scanned_size / POWER(1024.0, 3)) AS data_scanned_gb,
    ROUND(total_execution_duration / 1000.0, 1) AS execution_seconds,
    SUBSTR(query_string, 1, 200) AS query_preview
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '7' DAY
AND state_enum = 'FAILED'
AND error_message LIKE 'Query exhausted resources%'
ORDER BY data_scanned_size DESC

Capacity reservation optimization

If you use Capacity Reservations, system tables help you see how your provisioned capacity is being used. Athena populates the dpu_count column only for queries that run on a capacity reservation. That does double duty: it identifies which queries ran on reserved capacity, and how many DPUs each consumed. Trend the DPU-hours your reservations consume to check whether they are sized correctly. You can also use the execution-time and queue-time patterns shown earlier to judge whether an on-demand workload is a good candidate for a reservation.

Weekly DPU-hours consumed by capacity-reservation queries

SELECT
    DATE_TRUNC('week', submission_date_time) AS week,
    COUNT(*) AS reservation_queries,
    ROUND(SUM(dpu_count * total_execution_duration / 3600000.0), 1) AS dpu_hours
FROM "s3tablescatalog/aws-athena"."metadata"."query_executions"
WHERE submission_date_time >= CURRENT_DATE - INTERVAL '90' DAY
AND state_enum = 'SUCCEEDED'
AND dpu_count > 0 -- populated only for capacity-reservation queries
GROUP BY 1
ORDER BY 1

Architecture and data model

Athena system tables use a special type of S3 table bucket that works like a regular S3 table bucket, but only AWS services can write to it. This gives you the benefits of owning your data while maintaining the integrity of the records:

• You own the data – It’s in your account, encrypted with your keys, governed by your policies.
• Immutable records – Only Athena can write to the system table. No IAM principal in your account can modify or delete individual records.
• Standard format – Any Iceberg-compatible engine can read the data, including Athena, Amazon Redshift, Amazon EMR, Apache Spark, Trino, and others.
• Automatic maintenance – S3 Tables handles compaction, snapshot management, and data expiration based on your configured retention.

Athena doesn’t set a default retention. It retains records until you configure expiration using S3 Tables record expiration, with a minimum of one day and no upper limit. The system partitions data for efficient scanning, and query execution data arrives in batches, typically within a few minutes of a query completing. To see the partition columns, use the DESCRIBE statement.

Conclusion

System tables for Amazon Athena give you queryable visibility into your query workloads, with no custom infrastructure required. Whether you need to attribute costs to teams, investigate performance regressions, produce compliance evidence, or understand how Athena is being used, the answers are now a query away. Because Athena registers the system table in the AWS Glue Data Catalog and stores it in open Apache Iceberg format in your own account, you can point Amazon Quick Sight or a business intelligence (BI) tool directly at it. This removes the custom pipelines teams previously had to maintain.

To get started, turn on system tables from the Athena console. For more information, see System Tables in the Amazon Athena User Guide.

About the authors