Update to AWS Data Analytics

{
  "description": "Execute and manage Athena SQL queries across default and federated catalogs (Glue, S3 Tables, Redshift). Triggers on phrases like: query data, run SQL, athena query, analyze table, SQL query, workgroup status, profile table, query Redshift catalog, query S3 Tables. Do NOT use for finding specific data assets (use finding-data-lake-assets), full catalog audits (use exploring-data-catalog), importing data (use ingesting-into-data-lake).",
  "included_files": [
    {
      "relative_path": "references/query-patterns.md",
      "size_in_bytes": 5045
    },
    {
      "relative_path": "references/workgroup-selection.md",
      "size_in_bytes": 3944
    }
  ],
  "name": "querying-data-lake",
  "skill_md_contents": "---\nname: querying-data-lake\ndescription: >-\n  Execute and manage Athena SQL queries across default and federated catalogs (Glue,\n  S3 Tables, Redshift). Triggers on phrases like: query data, run SQL, athena query,\n  analyze table, SQL query, workgroup status, profile table, query Redshift catalog,\n  query S3 Tables. Do NOT use for finding specific data assets (use finding-data-lake-assets),\n  full catalog audits (use exploring-data-catalog), importing data (use ingesting-into-data-lake).\nmetadata:\n  version: \"1\"\n  argument-hint: \"'[SQL-query|query-name|workgroup-name|catalog-name|''profile TABLE_NAME'']'\"\n---\n\n# Query Data Lake\n\nExecute SQL queries on Amazon Athena across default and federated catalogs (Glue, S3 Tables, Redshift) with workgroup selection, statement classification, and error recovery.\n\n## Overview\n\nExecutes and manages Athena SQL queries across default and federated catalogs. Selects a workgroup, resolves target assets (delegating fuzzy references to `finding-data-lake-assets`), classifies statements for safety, and reports cost and data scanned. Use the AWS MCP server for sandboxed execution and audit logging; the same AWS CLI commands work directly when the MCP server is not available.\n\n**Constraints for parameter acquisition:**\n\n- You MUST accept a single optional argument: SQL text, a named-query name, a workgroup name, a catalog name, or `profile TABLE_NAME`\n- You MUST accept the argument as direct text or a pointer to a file containing SQL\n- You MUST ask the user for the target AWS region if not already set\n- You MUST confirm the output S3 location before executing any non-trivial query\n- You MUST respect the user's decision to abort at any step\n\n## Common Tasks\n\n### 1. Verify Dependencies\n\nCheck for required tools and AWS access before running queries.\n\n**Constraints:**\n\n- You MUST verify AWS MCP server tools are available (`aws___call_aws`) and run queries through them when present; fall back to AWS CLI only if the MCP server is unavailable\n- You MUST NOT fall back to shell or Bash for query execution — results must be captured via the MCP tool or `aws athena` CLI so output location and cost are tracked\n- You MUST confirm credentials with `aws sts get-caller-identity` and inform the user about any missing tools\n\n### 2. Resolve Workgroup\n\nCheck caller identity, list workgroups, auto-select the best one (see [workgroup-selection.md](references/workgroup-selection.md)).\n\n**Constraints:**\n\n- You MUST select a workgroup before submitting any query (prevents output-location errors)\n- You MUST present the selected workgroup and its output location to the user\n- You MUST NOT auto-escalate to a different workgroup on failure without user confirmation\n\n### 3. Resolve the Target Asset\n\nIf the user refers to a table by name, by business concept (\"our quarterly report\", \"the sales data\"), by S3 path, or by catalog without specifying the table, delegate to `finding-data-lake-assets` to return the concrete `database.table` (and catalog if non-default).\n\n**Constraints:**\n\n- You MUST NOT attempt to resolve fuzzy asset references with `athena list-data-catalogs` or by iterating `get-tables` — those miss federated catalogs and waste tokens\n- You SHOULD skip this step only when the user provides a fully-qualified reference (exact `database.table`) or raw SQL they want executed as-is\n- You MUST state the resolved asset explicitly before building the query: \"Found [table] in [catalog]. Using this for the query.\"\n- You SHOULD default to the default Glue catalog unless the user mentions \"federated\", \"Redshift\", \"S3 Tables\", or `finding-data-lake-assets` returns a different catalog\n\n### 4. Discover Schema\n\nFor analytical queries, You SHOULD profile the target table before building the final query. You MUST show sample rows (`SELECT ... LIMIT 5`) as part of profiling.\n\n### 5. Build Query\n\nTable addressing depends on catalog type:\n\n- Default Glue catalog: `database.table` (omit the catalog prefix for single-catalog queries). In cross-catalog queries, qualify default-catalog tables with `\"awsdatacatalog\".database.table`.\n- Registered data source: `datasource.database.table`\n- Unregistered Glue catalog: `\"catalog/subcatalog\".database.table`\n\n### 6. Classify and Execute\n\nClassify the SQL statement before executing:\n\n| Statement | Behavior |\n|---|---|\n| `SELECT`, `SHOW`, `DESCRIBE`, `EXPLAIN` | Safe — execute |\n| `INSERT`, `UPDATE`, `DELETE`, `DROP`, `ALTER`, `CREATE`, `TRUNCATE`, `MERGE` | Destructive — warn the user and require explicit confirmation |\n| Unsure | Treat as destructive; confirm |\n\nExample tool call (via AWS MCP server):\n\n```\naws___call_aws(command=\"aws athena start-query-execution --work-group <WORKGROUP_NAME> --query-string '<sql>' --query-execution-context Database=<db>\")\n```\n\nFor federated or S3 Tables catalogs, also set `Catalog=<CATALOG_PATH>` in the execution context (e.g. `Catalog=s3tablescatalog/<BUCKET_NAME>`).\n\n**Constraints:**\n\n- You MUST warn the user before executing when the target is Redshift-federated (\"No partition pruning — every query scans the full table\")\n- You MUST warn the user before executing a cross-catalog join (\"Cross-catalog joins incur network overhead and may be slow\")\n- You MUST confirm the output S3 location before executing\n- You MUST explain which tool is being called before executing\n- You MUST respect the user's decision to abort\n\n### 7. Present and Recover\n\nPresent results with cost, data scanned, duration, and actionable insights. On failure, list available workgroups and let the user choose which to retry with.\n\n### Argument Routing\n\nResolve in this order; stop at the first match:\n\n1. Contains SQL keywords (`SELECT`, `SHOW`, `DESCRIBE`, `INSERT`, etc.) — SQL text, execute directly\n2. `profile TABLE_NAME` — run comprehensive table profiling (see [query-patterns.md](references/query-patterns.md))\n3. Matches a known named query — look up and execute\n4. Matches a known workgroup — show workgroup status and recent queries\n5. Matches a known catalog — delegate to `exploring-data-catalog` to enumerate databases and tables\n6. No args — show recent query activity and available tables\n\n### Principles\n\n- Always select workgroup before executing (prevents output-location errors)\n- Profile unfamiliar tables before running analytical queries\n- Present cost alongside results so users build cost awareness\n- Suggest `LIMIT` for exploratory queries on large tables\n- Never ask domain questions with obvious answers, but always confirm security-relevant actions (workgroup switches, output location changes, non-SELECT statements)\n\n## Troubleshooting\n\n| Error | Cause | Fix |\n|---|---|---|\n| Redshift identifier error with mixed case | Redshift-federated names are lowercase only | Lowercase the identifier |\n| `CatalogId` validation failure | ARN passed instead of catalog name | Pass the catalog name, not the ARN |\n| Cross-catalog `information_schema` returns nothing | Missing catalog qualifier | Use catalog-qualified path: `\"catalog\".information_schema.tables` |\n| Query fails with output-location error | Workgroup has no output location configured | Select a different workgroup with an output location, or configure one |\n| Destructive statement executed without confirmation | Statement classification skipped | Always classify `INSERT`/`UPDATE`/`DELETE`/`DROP`/`ALTER`/`CREATE`/`TRUNCATE`/`MERGE` and confirm with the user |\n\n## Additional Resources\n\n- [Workgroup selection logic](references/workgroup-selection.md)\n- [Common query patterns](references/query-patterns.md)\n- [Athena best practices](https://docs.aws.amazon.com/athena/latest/ug/performance-tuning.html)\n- [Athena federated query](https://docs.aws.amazon.com/athena/latest/ug/connect-to-a-data-source.html)\n"
}