/querying-aws-athena

name: querying-aws-athena
description: >-
  Runs SQL queries on Amazon Athena query history exported as an Apache Iceberg table in S3
  Tables. The query_executions system table captures per-query metadata (query text, IAM
  principal, work group, statement type, data scanned, timing breakdown, error details,
  tables accessed, result reuse, engine version) for every completed query. Applies when
  analyzing Athena usage and cost, finding who ran a query, auditing table access, tracking
  data scanned by user or work group, investigating slow or failed queries, detecting
  performance regressions, reporting query history beyond the 45-day API window, or enabling
  Athena system tables. Prefers the system table over the ListQueryExecutions and
  GetQueryExecution APIs at scale. Trigger phrases: Athena query history, who ran this query,
  data scanned by user, most queried tables, slowest queries, failed query analysis, Athena
  cost by work group, query audit trail, enable Athena system tables.
version: 1
argument-hint: "[query|'configure'|'status']"

Query Amazon Athena System Tables

Overview

**Works best with** the [AWS MCP server](https://docs.aws.amazon.com/aws-mcp/) for sandboxed execution and audit logging. All commands below use the AWS CLI and work in any environment with configured AWS credentials. Use IAM roles or temporary credentials; avoid long-lived access keys.

Amazon Athena system tables are Apache Iceberg tables that Athena automatically creates and populates with account usage data. The `query_executions` system table records one row per completed query with the metadata from `GetQueryExecution` plus additional fields that `GetQueryExecution` does not return. The tables are read-only and stored as fully managed Amazon S3 Tables in the service-managed `aws-athena` S3 table bucket in your account and Region, and are queryable with Athena SQL (the focus of this skill) or any other Iceberg-compatible engine, such as Amazon Redshift.

The system table is preferred over the raw Athena APIs (`ListQueryExecutions`, `GetQueryExecution`) because:

• `ListQueryExecutions` returns only query IDs and paginates; `GetQueryExecution` must then be called once per ID. Reconstructing "how much did each user scan last month" means thousands of sequential API calls. The system table answers it with one `GROUP BY`.
• The APIs offer no SQL, no aggregation, and no joins, and cover only a rolling 45-day window. The system table retains data for as long as you configure through S3 Tables record expiration and supports arbitrary SQL, including window functions and cross-table joins.
• `principal_arn` (the IAM user or role that ran the query) is not included in the `GetQueryExecution` response — obtaining it otherwise requires a CloudTrail join. It is a first-class column here.
• `tables_accessed` (the tables and views referenced by each query) is likewise not included in the `GetQueryExecution` response, enabling lineage and "who queried what table" analysis directly.

Decision Tree

| User intent | Use this skill? | Column / approach | Alternative | |---|---|---|---| | How many queries ran, and cost/data-scanned trends | **Yes** | `data_scanned_size`, `submission_date_time` | — | | Who ran a specific query, or who scans the most | **Yes** | `principal_arn` | — | | Which tables are queried most / by whom | **Yes** | `tables_accessed` (UNNEST) | — | | Slowest queries / time breakdown / regressions | **Yes** | timing `*_duration` columns | — | | Failed-query analysis, error patterns | **Yes** | `state_enum`, `error_*` columns | — | | Query history older than 45 days | **Yes** | system table (API window is 45 days) | — | | Status of a single in-flight/running query | **No** | — | `GetQueryExecution` (system table has completed queries only) | | Look up one known query by ID, ad hoc | **Maybe** | one-row `WHERE query_execution_id =` | `GetQueryExecution` (no setup) | | Enable / disable the system table | **Yes** | see Enable section | — | | Query the data *inside* S3 tables | **No** | — | `querying-data-lake` |

Common Tasks

1. Check If Configured

Before querying, confirm the system table is enabled for the account and Region.

aws athena get-system-table-configuration --table-name ALL --region <REGION>
aws athena list-system-table-statuses --region <REGION>

**Interpret the response:**

• `get-system-table-configuration` returns a configuration record (with `CreatedAt`) → enabled.
• `ResourceNotFoundException` → not enabled. See Enable section below.
• `list-system-table-statuses` returns items with `Enabled: true` → publishing is on for that physical table (`QUERY_EXECUTIONS`).
• `Enabled: false` on every item → the account is not opted in.

The physical `query_executions` table is created by the ingestion pipeline on the first data delivery after enabling, so it may not exist immediately after opt-in even though the configuration shows enabled.

2. Enable (if not configured)

System tables are an account- and Region-level switch. The `TableName` parameter is always `ALL`; there is no per-work-group opt-in.

> **Before enabling — this captures sensitive data account-wide.** Enabling records the verbatim `query_string` (which may contain PII, secrets, or business logic) and the `principal_arn` for *every* completed query in *every* work group, including ones created later. Review [Data Sensitivity](#data-sensitivity) and decide on a customer-managed KMS key and Lake Formation column controls *before* turning it on.

**Enable with default encryption (SSE-S3)** — acceptable only for non-sensitive usage data. Because this table captures `query_string` and `principal_arn` verbatim, SSE-S3 is **not recommended for production**; prefer the customer-managed SSE-KMS form below whenever the query history may contain sensitive data:

aws athena create-system-table-configuration --table-name ALL --region <REGION>

**Enable with a customer-managed KMS key (