Skill: Athena SQL Querying

Capability Domain: Analytics
Technology Stack: Amazon Athena
Linked Activities: Present the Catalog via Athena

Recipe

1. One workgroup: Scope Contract athena_workgroup (default ops-lake-analysts), EnforceWorkGroupConfiguration on, scan cap 5GB to start.
2. Glue crawler tables are the contract views (github_issues, …). Named Athena queries are proofs (LIMIT 10), not a second catalog. scripts/create_athena_views.py after crawlers READY; skip views with no Glue table.
3. Backfill envelope is {backfill, kind, repo, payload: {…}}. Query json_extract_scalar(raw_json, '$.payload.number'), $.payload.closed_at, $.payload.state — not top-level GitHub fields. Prefer the snapshot that has closed_at when deduping by issue number.
4. Query results → athena-results bucket, 7-day lifecycle.
5. Always filter on source / dt. SELECT * on an unpartitioned scan blows the cap.

Pitfalls

• AWS login refresh is not Athena AccessDenied. Local Jupyter/awswrangler can fail with AccessDeniedException on CreateOAuth2Token, then LoginRefreshRequired: "The refresh token has expired" / "Please reauthenticate using aws login". Wrangler never reached StartQueryExecution. This is credential session expiry, not lake IAM / workgroup deny. Procedure: (1) terminal aws login (or this org's equivalent aws sso login); (2) confirm aws sts get-caller-identity matches docs/lake-scope-contract.yaml aws_account (live opsdl: 411113550285); (3) Restart the Jupyter kernel, then Restart & Run All — boto3 clients in a running kernel keep the dead token; (4) the login credential provider also needs pip install 'botocore[crt]' (opsdl requirements-eda.txt).
• Treating crawler tables as incomplete until CREATE VIEW — the table name already matches the contract view.
• Querying $.number instead of $.payload.number on backfill rows.
• Shipping gitlab_commits as a default when the contract is GitHub-only.
• Correlated EXISTS/UNNEST subqueries in Athena (feature filters belong in Python after the SQL returns).