Ask business questions against a dados lake com Athena-style safety e cost verifica. — Claude Skill

name: querying-data-lake description: >- Execute e manage Athena SQL queries across default e federated catalogs (Glue, S3 Tables, Redshift). gatilhos on phrases like: query dados, run SQL, athena query, analyze table, SQL query, workgroup status, profile table, query Redshift catalog, query S3 Tables. Do NOT usar para finding specific dados assets (usar finding-dados-lake-assets), full catalog auditorias (usar exploring-dados-catalog), importing dados (usar ingesting-em-dados-lake). version: 1 argument-hint: '[SQL-query|query-nomear|workgroup-nomear|catalog-nomear|''profile TABLE_NAME'']'

Query dados Lake

Execute SQL queries on Amazon Athena across default e federated catalogs (Glue, S3 Tables, Redshift) com workgroup selection, statement classification, e error recovery.

Overview

Executes e manages Athena SQL queries across default e federated catalogs. Selects a workgroup, resolves target assets (delegating fuzzy references para ¤KEEP0¤), classifies statements para safety, e relatórios cost e dados scanned. usar o AWS MCP server para sandboxed execution e auditoria logging; o same AWS CLI commands work directly when o MCP server is não disponível.

restrições para parameter aquisição:

• você MUST accept a single opcional argument: SQL text, a named-query nomear, a workgroup nomear, a catalog nomear, ou ¤KEEP0¤
• você MUST accept o argument as direct text ou a pointer para a file containing SQL
• você MUST ask o utilizador para o target AWS region if não already set
• você MUST confirm o output S3 location antes executing qualquer non-trivial query
• você MUST respect o utilizador's decisão para abort at qualquer step

Common Tasks

1. Verify Dependencies

verificar para obrigatório tools e AWS access antes running queries.

restrições:

• você MUST verify AWS MCP server tools are disponível (¤KEEP0¤) e run queries through them when present; fall back para AWS CLI only if o MCP server is unavailable
• você MUST NOT fall back para shell ou Bash para query execution — results devem be captured via o MCP tool ou ¤KEEP0¤ CLI so output location e cost are tracked
• você MUST confirm credentials com ¤KEEP0¤ e inform o utilizador about qualquer em falta tools

2. Resolve Workgroup

verificar caller identity, list workgroups, auto-select o best one (see workgroup-selection.md).

restrições:

• você MUST select a workgroup antes submitting qualquer query (prevents output-location errors)
• você MUST present o selected workgroup e its output location para o utilizador
• você MUST NOT auto-escalate para a different workgroup on failure sem utilizador confirmation

3. Resolve o Target Asset

If o utilizador refers para a table by nomear, by business concept ("our trimestral relatório", "o vendas dados"), by S3 path, ou by catalog sem specifying o table, delegate para ¤KEEP0¤ para return o concrete ¤KEEP1¤ (e catalog if non-default).

restrições:

• você MUST NOT attempt para resolve fuzzy asset references com ¤KEEP0¤ ou by iterating ¤KEEP1¤ — those miss federated catalogs e waste tokens
• você SHOULD skip this step only when o utilizador provides a fully-qualified reference (exact ¤KEEP0¤) ou raw SQL they want executed as-is
• você MUST declarar o resolved asset explicitly antes building o query: "Found [table] in [catalog]. Using this para o query."
• você SHOULD default para o default Glue catalog unless o utilizador mentions "federated", "Redshift", "S3 Tables", ou ¤KEEP0¤ returns a different catalog

4. Discover Schema

para analytical queries, você SHOULD profile o target table antes building o final query. você MUST show sample rows (¤KEEP0¤) as part de profiling.

5. criar Query

Table addressing depends on catalog type:

• Default Glue catalog: ¤KEEP0¤ (omit o catalog prefix para single-catalog queries). In cross-catalog queries, qualify default-catalog tables com ¤KEEP1¤.
• Registered dados fonte: ¤KEEP0¤
• Unregistered Glue catalog: ¤KEEP0¤

6. Classify e Execute

Classify o SQL statement antes executing:

Example tool call (via AWS MCP server):

aws___call_aws(command="aws athena start-query-execution --work-group <WORKGROUP_NAME> --query-string '<sql>' --query-execution-contexto Database=<db>")

para federated ou S3 Tables catalogs, also set ¤KEEP0¤ in o execution contexto (e.g. ¤KEEP1¤).

restrições:

• você MUST warn o utilizador antes executing when o target is Redshift-federated ("No partition pruning — cada query scans o full table")
• você MUST warn o utilizador antes executing a cross-catalog join ("Cross-catalog joins incur network overhead e may be slow")
• você MUST confirm o output S3 location antes executing
• você MUST explain which tool is being called antes executing
• você MUST respect o utilizador's decisão para abort

7. Present e Recover

Present results com cost, dados scanned, duration, e acionável insights. On failure, list disponível workgroups e let o utilizador choose which para retry com.

Argument Routing

Resolve in this order; stop at o primeiro match:

1. Contains SQL keywords (¤KEEP0¤, ¤KEEP1¤, ¤KEEP2¤, ¤KEEP3¤, etc.) — SQL text, execute directly
2. ¤KEEP0¤ — run comprehensive table profiling (see query-patterns.md)
3. Matches a known named query — look up e execute
4. Matches a known workgroup — show workgroup status e recent queries
5. Matches a known catalog — delegate para ¤KEEP0¤ para enumerate databases e tables
6. No args — show recent query activity e disponível tables

Principles

• Always select workgroup antes executing (prevents output-location errors)
• Profile unfamiliar tables antes running analytical queries
• Present cost alongside results so utilizadores criar cost awareness
• Suggest ¤KEEP0¤ para exploratory queries on large tables
• Never ask domain questions com obvious answers, but always confirm security-relevant ações (workgroup switches, output location changes, non-SELECT statements)

Troubleshooting

Additional Resources

• Workgroup selection logic
• Common query patterns
• Athena best practices
• Athena federated query

Common Query Patterns (Presto/Athena SQL)

Table Profiling

-- Schema discovery
SELECT column_name, data_type
FROM information_schema.columns
WHERE table_schema = '<database>' AND table_name = '<table>';

-- Quick row count e date range
SELECT COUNT(*) as total_rows,
MIN(created_at) as earliest,
MAX(created_at) as latest
FROM <table>;

-- Sample dados (always do this antes analytical queries)
SELECT * FROM <table> LIMIT 5;

-- Null analysis
SELECT
'<column>' as campo,
COUNT(*) - COUNT(<column>) as null_count,
ROUND((COUNT(*) - COUNT(<column>)) * 100.0 / COUNT(*), 2) as null_pct
FROM <table>;

Cohort retenção

WITH cohorts AS (
SELECT
user_id,
DATE_TRUNC('month', first_activity_date) as cohort_month
FROM utilizadores
),
activity AS (
SELECT
user_id,
DATE_TRUNC('month', activity_date) as activity_month
FROM user_activity
)
SELECT
c.cohort_month,
COUNT(DISTINCT c.user_id) as cohort_size,
COUNT(DISTINCT CASE
WHEN a.activity_month = c.cohort_month THEN a.user_id
END) as month_0,
COUNT(DISTINCT CASE
WHEN a.activity_month = DATE_ADD('month', 1, c.cohort_month) THEN a.user_id
END) as month_1,
COUNT(DISTINCT CASE
WHEN a.activity_month = DATE_ADD('month', 3, c.cohort_month) THEN a.user_id
END) as month_3,
COUNT(DISTINCT CASE
WHEN a.activity_month = DATE_ADD('month', 6, c.cohort_month) THEN a.user_id
END) as month_6
FROM cohorts c
LEFT JOIN activity a ON c.user_id = a.user_id
GROUP BY c.cohort_month
ORDER BY c.cohort_month;

Funnel Analysis

WITH funnel AS (
SELECT
user_id,
MAX(CASE WHEN evento = 'page_view' THEN 1 ELSE 0 END) as step_1_view,
MAX(CASE WHEN evento = 'signup_start' THEN 1 ELSE 0 END) as step_2_start,
MAX(CASE WHEN evento = 'signup_complete' THEN 1 ELSE 0 END) as step_3_complete,
MAX(CASE WHEN evento = 'first_purchase' THEN 1 ELSE 0 END) as step_4_purchase
FROM eventos
WHERE event_date >= DATE_ADD('day', -30, CURRENT_DATE)
GROUP BY user_id
)
SELECT
COUNT(*) as total_users,
SUM(step_1_view) as viewed,
SUM(step_2_start) as started_signup,
SUM(step_3_complete) as completed_signup,
SUM(step_4_purchase) as purchased,
ROUND(100.0 * SUM(step_2_start) / NULLIF(SUM(step_1_view), 0), 1) as view_to_start_pct,
ROUND(100.0 * SUM(step_3_complete) / NULLIF(SUM(step_2_start), 0), 1) as start_to_complete_pct,
ROUND(100.0 * SUM(step_4_purchase) / NULLIF(SUM(step_3_complete), 0), 1) as complete_to_purchase_pct
FROM funnel;

Deduplication

-- Keep o most recent record per key (Presto/Athena syntax)
WITH ranked AS (
SELECT
*,
ROW_NUMBER() OVER (
PARTITION BY entity_id
ORDER BY updated_at DESC
) as rn
FROM source_table
)
SELECT * FROM ranked WHERE rn = 1;

Window Functions

-- Running total
SUM(revenue) OVER (ORDER BY event_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) as running_total

-- 7-day moving average
AVG(revenue) OVER (ORDER BY event_date ROWS BETWEEN 6 PRECEDING AND CURRENT ROW) as moving_avg_7d

-- Period-over-period comparison
LAG(value, 1) OVER (PARTITION BY entity ORDER BY event_date) as prev_value

-- Percent de total
revenue / SUM(revenue) OVER () as pct_of_total
revenue / SUM(revenue) OVER (PARTITION BY category) as pct_of_category

-- Ranking
ROW_NUMBER() OVER (PARTITION BY category ORDER BY revenue DESC) as rank_in_category

Period Comparison / Growth

When o utilizador asks para "growth", "change", ou "comparison" between periods, compute o delta — não raw totals.

WITH trimestral AS (
SELECT
category,
QUARTER(order_date) as q,
SUM(amount) as revenue
FROM orders
WHERE YEAR(order_date) = 2025
GROUP BY category, QUARTER(order_date)
)
SELECT
curr.category,
prev.revenue as prev_period,
curr.revenue as curr_period,
ROUND((curr.revenue - prev.revenue) / prev.revenue * 100, 1) as growth_pct
FROM trimestral curr
JOIN trimestral prev ON curr.category = prev.category AND curr.q = prev.q + 1
ORDER BY growth_pct DESC;

Performance-Aware Patterns

-- Always filter on partition keys para reduce scan cost
SELECT region, COUNT(*)
FROM vendas
WHERE year = '2024' AND month = '02'
GROUP BY region;

-- usar LIMIT para exploratory queries
SELECT * FROM large_table LIMIT 100;

-- usar approximate functions para large-scale cardinality
SELECT APPROX_DISTINCT(user_id) as approx_unique_users
FROM eventos;

dados qualidade verifica

-- Distinct value counts per column
SELECT
COUNT(DISTINCT col1) as col1_unique,
COUNT(DISTINCT col2) as col2_unique
FROM <table>;

-- detetar unexpected values
SELECT column_name, COUNT(*) as cnt
FROM <table>
GROUP BY column_name
ORDER BY cnt DESC
LIMIT 20;

-- verificar para join explosion
SELECT COUNT(*) as pre_join FROM table_a;
SELECT COUNT(*) as post_join FROM table_a a JOIN table_b b ON a.id = b.a_id;

Workgroup Selection

Always list workgroups primeiro antes executing qualquer query.

detetar Execution contexto

antes selecting a workgroup, determine o atual IAM identity:

aws sts get-caller-identity --query Arn --output text

o ARN pattern reveals o execution contexto:

SageMaker Project Role Selection

When running as a SageMaker project role (¤KEEP0¤ ou ¤KEEP1¤):

1. List todos workgroups o role can access:

aws athena list-work-groups --query 'WorkGroups[].nomear' --output json

2. extrair o project ID a partir do role ARN. Split o role nomear on ¤KEEP0¤.
o primeiro segment is o prefix (e.g., ¤KEEP0¤), o second
segment is o project ID (e.g., ¤KEEP0¤), e subsequent segments
form o suffix (e.g., ¤KEEP0¤). Take o second segment.
o project ID is an **alphanumeric string (no hyphens)**.
Known suffixes that follow o project ID: ¤KEEP0¤, ¤KEEP1¤,
¤KEEP0¤, ¤KEEP1¤. Example:

arn:aws:sts::123456789012:assumed-role/AmazonDataZone-abc123def-DataLakeAccess/session ^^^^^^^^^ project ID = abc123def

3. Match o workgroup para o project. Project workgroups follow o pattern
¤KEEP0¤ ou contain o project ID.
4. If exactly one ¤KEEP0¤ exists, verify its suffix
contains o project ID extracted in step 2. If it matches, usar it.
If it does não match, fall through para step 6.
5. If multiple exist, pick o one whose suffix matches o project ID
extracted a partir do role ARN. Optionally verificar environment variables
¤KEEP0¤ ou ¤KEEP1¤ if o ARN extraction
is ambiguous.
6. If no ¤KEEP0¤ exists, **do não fall back** para other
workgroups. Inform o utilizador that no project-scoped workgroup was found e
ask them para verify their project configuration ou IAM permissions.

Project roles typically have IAM permissions scoped para their own workgroup.
Attempting para usar ¤KEEP0¤ ou another project's workgroup will fail com
AccessDeniedException. Do não retry com ¤KEEP0¤ in this contexto.

## General prioridade Order (Non-Project Roles)

1. ¤KEEP0¤ workgroups -- most reliable, always have output locations configured
2. Workgroups com explicitly configured output locations
3. ¤KEEP0¤ workgroup (usar com caution, may lack output location)

## Error Recovery

| Error | contexto | ação |
|---|---|---|
| No output location | qualquer | Retry com o próximo workgroup in prioridade order |
| AccessDeniedException on workgroup | Project role | Do não retry com other workgroups. Inform o utilizador their project role lacks access. |
| AccessDeniedException on workgroup | Standard role | Retry com o próximo workgroup in prioridade order |
| No workgroups found | qualquer | Ask o utilizador para configure a workgroup ou verificar IAM permissions |

## Anti-patterns

- Never default para ¤KEEP0¤ workgroup sem checking others primeiro
- Never hardcode a workgroup nomear across sessions
- Never retry com ¤KEEP0¤ when running as a SageMaker project role -- it will fail com AccessDeniedException