| name | athena-diagnostics |
| version | 1.0.0 |
| last_updated | 2025-04-12 |
| description | Use this skill to investigate and troubleshoot Amazon Athena problems by analyzing query failures, timeout issues, syntax errors, performance degradation, data format problems, partitioning, SerDe errors, workgroup configuration, federated queries, cost optimization, Glue Catalog integration, Lake Formation permissions, IAM access, encryption, and Athena for Apache Spark issues. Activate when: query failures, query timeouts, syntax errors, slow queries, data format mismatches, partition projection issues, SerDe deserialization errors, workgroup misconfiguration, query result location errors, federated query failures, data source connector issues, high data scan costs, query result reuse problems, Glue Data Catalog sync issues, Lake Formation permission denials, IAM permission errors, encryption failures, Spark session issues, notebook errors, or the user says something is wrong with Athena without naming specific symptoms.
|
| compatibility | Requires AWS CLI with athena, glue, s3, lakeformation, and cloudtrail permissions. Optional: Athena JDBC/ODBC driver for connectivity testing, AWS Glue console access for catalog verification.
|
Athena Diagnostics
When to use
Any Athena investigation where the console alone is insufficient — query execution failures, performance issues, data format problems, workgroup configuration, federated queries, cost optimization, catalog integration, security permissions, or Spark notebook issues.
Investigation workflow
Step 1 — Collect and triage
# List workgroups and their configuration
aws athena list-work-groups --query 'WorkGroups[*].{Name:Name,State:State,Engine:EngineVersion.SelectedEngineVersion}'
version: "1.0.0"
last_updated: "2025-04-12"
# Get workgroup details
aws athena get-work-group --work-group <workgroup-name>
# List recent query executions
aws athena list-query-executions --work-group <workgroup-name> --max-items 10
# Get query execution details (status, data scanned, runtime)
aws athena get-query-execution --query-execution-id <query-id>
# Batch get multiple query executions
aws athena batch-get-query-execution --query-execution-ids <id1> <id2> <id3>
# Check Glue Data Catalog databases
aws glue get-databases --query 'DatabaseList[*].{Name:Name,Location:LocationUri}'
version: "1.0.0"
last_updated: "2025-04-12"
# Check specific table definition
aws glue get-table --database-name <db> --name <table>
Triage returns:
- Workgroup state, engine version, configuration
- Query execution status, error messages, data scanned
- Runtime duration and query type
- Glue catalog database and table definitions
If the query status is FAILED, the StateChangeReason IS the root cause. Don't chase infrastructure symptoms.
Step 2 — Domain deep dive (only if needed)
# Get query execution statistics
aws athena get-query-runtime-statistics --query-execution-id <query-id>
# Check S3 output location accessibility
aws s3 ls <output-location>
# Check table partitions
aws glue get-partitions --database-name <db> --table-name <table> --max-items 10
# Check Lake Formation permissions
aws lakeformation list-permissions --principal DataLakePrincipalIdentifier=<arn>
# Check data source connectors (federated queries)
aws athena list-data-catalogs
# CloudTrail for Athena API events
aws cloudtrail lookup-events --lookup-attributes AttributeKey=EventSource,AttributeValue=athena.amazonaws.com --max-results 20
Read references/athena-guardrails.md before concluding on any Athena issue.
Step 3 — Detailed investigation (low-confidence cases only)
# Check named queries for reference
aws athena list-named-queries --work-group <workgroup-name>
# Check prepared statements
aws athena list-prepared-statements --work-group <workgroup-name>
# Verify S3 bucket policy for query results
aws s3api get-bucket-policy --bucket <results-bucket>
# Check KMS key access for encrypted results
aws kms describe-key --key-id <key-id>
aws kms list-grants --key-id <key-id>
# IAM policy simulation for Athena actions
aws iam simulate-principal-policy --policy-source-arn <role-arn> \
--action-names athena:StartQueryExecution athena:GetQueryResults s3:GetObject glue:GetTable
# Check Glue crawler status (if partitions are stale)
aws glue get-crawler --name <crawler-name>
Tool quick reference
| Tool / Command | When to use |
|---|
aws athena get-query-execution | Query status, error message, data scanned, runtime |
aws athena get-work-group | Workgroup config, output location, engine version |
aws athena list-data-catalogs | List federated data sources and connectors |
aws glue get-table | Table schema, SerDe, input format, location |
aws glue get-partitions | Partition keys, values, storage descriptors |
aws lakeformation list-permissions | Lake Formation grants for a principal |
aws s3 ls | Verify S3 data/output location accessibility |
aws cloudtrail lookup-events | Athena API call history and errors |
aws iam simulate-principal-policy | Verify IAM permissions for Athena operations |
aws kms describe-key | KMS key status and configuration |
Gotchas: Athena
These are the mistakes commonly made during Athena troubleshooting.
- Athena charges per TB of data scanned — not per query or per time. Use partitioning, columnar formats (Parquet/ORC), and compression to reduce costs. A full table scan on CSV data is the most expensive operation.
- Default query timeout is 30 minutes. This is configurable per workgroup (up to the service maximum). Long-running queries that exceed the timeout are cancelled automatically.
- Workgroups control cost, access, and configuration. Each workgroup has its own query result location, encryption settings, data scan limits, and engine version. Users inherit workgroup settings.
- Glue Data Catalog is the metastore for Athena. Tables, databases, and partitions are defined in Glue. If the Glue table definition is wrong (wrong SerDe, wrong location, wrong schema), Athena queries fail.
- CTAS (CREATE TABLE AS SELECT) and INSERT INTO are used for ETL in Athena. CTAS creates a new table from query results. INSERT INTO appends to an existing table. Both write to S3 in the specified format.
- Federated queries use Lambda-based connectors to query external data sources (RDS, DynamoDB, Redis, etc.). The Lambda function must have network access to the source and IAM permissions. Federated queries are slower than native S3 queries.
- The query result location must be writable by the executing principal. If the S3 bucket policy or IAM policy doesn't allow PutObject, the query fails even if the SELECT itself succeeds.
- Athena engine v3 is based on Trino (formerly PrestoSQL). Engine v2 is based on Presto. There are syntax and function differences between versions. Check the workgroup engine version when debugging syntax errors.
- Partition projection eliminates the need for MSCK REPAIR TABLE or Glue crawlers by computing partition values on the fly. But it requires correct configuration in table properties — wrong projection config causes empty results, not errors.
- SerDe (Serializer/Deserializer) must match the data format. Using OpenCSVSerDe on JSON data or JsonSerDe on CSV data produces NULL values or parse errors. Always verify the SerDe matches the actual file format.
- S3 path patterns matter. Athena reads ALL files in the table's S3 location. Stray files (like _SUCCESS markers, hidden files, or wrong-format files) cause query failures or incorrect results.
- Athena does not support UPDATE or DELETE on regular tables. For mutable data, use Iceberg table format which supports ACID transactions in Athena engine v3.
Anti-hallucination rules
- Always cite specific query execution IDs, error messages, Glue table definitions, or S3 paths as evidence.
- Never suggest SSH-ing into Athena infrastructure — it is fully serverless with no instances to access.
- Never recommend changing Athena's pricing model — it is always per-TB-scanned (or per-query for provisioned capacity).
- Never claim Athena supports UPDATE/DELETE on regular tables — only Iceberg tables support DML.
- Never suggest Athena has persistent compute resources — each query runs on ephemeral compute.
- Spend no more than 2 minutes on any single hypothesis. Pivot if inconclusive.
26 runbooks
Runbooks are organized by failure domain. Use the appropriate runbook based on the symptom category.
| Category | IDs | Covers |
|---|
| A — Query | A1–A4 | Query failures, timeout, syntax errors, performance |
| B — Data | B1–B3 | Data format issues, partitioning, SerDe errors |
| C — Workgroup | C1–C2 | Workgroup configuration, query result location |
| D — Federation | D1–D2 | Federated query, data source connectors |
| E — Cost | E1–E2 | Data scanned optimization, query result reuse |
| F — Integration | F1–F2 | Glue Catalog, Lake Formation |
| G — Security | G1–G2 | IAM permissions, encryption |
| H — Spark | H1–H2 | Athena Spark, notebook issues |
| Z — Catch-All | Z1 | General Athena troubleshooting |
