17 views
Your coding agents already produce useful data: which tool ran, for which tenant, how long it took, how many tokens it used, and whether it worked. Your eval suite produces more: which prompt version passed which test cases, and at what cost. Most teams either leave this data in log groups nobody queries or plan a warehouse “next quarter”. Meanwhile simple questions go unanswered. Which tenant used the most tokens yesterday? Did the new prompt version make the core suite slower?
Amazon Athena runs standard SQL directly against data in Amazon S3. It’s serverless, so there’s no cluster to run, and you pay for the queries you run. This guide shows how to land agent tool audits and eval traces in S3 so they are cheap to query, how to define the tables with partition projection, how to cap query spend, and which SQL earns its keep. It merges our earlier posts on agent audit logs and eval traces into one guide.
TL;DR
- Write one JSON object per line (the JSON SerDes require single-line records) and deliver it with Amazon Data Firehose, not one S3 object per event.
- Partition by day (
dt=YYYY-MM-DD) and use partition projection, so you never runMSCK REPAIR TABLEagain.- Run analysts in a workgroup with an enforced results location and a per-query scan limit.
- Compact older data to Parquet with CTAS. A single CTAS or INSERT INTO can write at most 100 partitions.
- Store counts and metadata, not raw prompts or secrets.
What goes in the agent data lake
Two record types cover most questions. A tool invocation is written every time the agent calls a tool. An eval trace is written once per eval case by your test harness. Keep both flat, so every field becomes a column you canGROUP BY.
json
In S3, each record must sit on one line. The OpenX and Hive JSON SerDes expect single-line JSON, and pretty-printed records cause errors. An eval trace has a similar shape: {
"ts": "2026-10-05T20:15:03.441Z",
"tenant_id": "acme",
"session_id": "sess_9f3a",
"agent_role": "patcher",
"prompt_version": "review-v3",
"model_id": "MODEL_ID",
"tool": "apply_patch",
"ok": true,
"latency_ms": 842,
"input_tokens": 4100,
"output_tokens": 900,
"repo": "acme/payments",
"error_code": null,
"actor_arn": "arn:aws:sts::111122223333:assumed-role/agents-batch-job/job-1"
}eval_id, suite, prompt_version, model_id, passed, latency_ms, token counts, guardrail_intervened and ts. Record whether a guardrail blocked the output as its own column, so blocked unsafe output isn’t counted as a model failure.
Use this lake alongside your other tools, not instead of them:
| Question | Best tool |
|---|---|
| Which tenant, tool or prompt version drove token use last month? | Athena over S3 (this guide) |
| What is failing right now, in the last hour? | CloudWatch Logs Insights and metrics |
| Which AWS APIs did the agent’s IAM role call? | CloudTrail logs (Athena can query those too) |
| Full-text search across tool output | OpenSearch |
| What does AWS charge per service? | Cost Explorer and the Cost and Usage Report |
Before you start
- An S3 bucket for the lake (block public access, default encryption with SSE-S3 or SSE-KMS) and a separate bucket or prefix for Athena query results with a lifecycle rule.
- A Glue database, for example
coding_agent. - Agreement on the schema. Treat it as a contract: add fields, never rename them.
Step 1: Deliver records through Firehose
Writing one S3 object per tool call creates millions of tiny files, and the Athena performance guide is clear that many small files hurt query performance. Send records to a Firehose stream instead. It buffers them and writes larger objects under a prefix such astool_invocations/dt=!{timestamp:yyyy-MM-dd}/. Firehose evaluates the timestamp in UTC by default, and you can set a custom time zone (for example Asia/Kolkata) if you want partitions to follow your local day.
python
The trailing newline matters: Firehose concatenates records, and the newline keeps each JSON object on its own line. import json
import boto3
firehose = boto3.client("firehose")
STREAM = "agent-tool-invocations"
DROP_FIELDS = {"prompt", "completion", "tool_input", "tool_output"} # never ship raw text
def clean(record: dict) -> dict:
return {k: v for k, v in record.items() if k not in DROP_FIELDS}
def emit_tool_audits(records: list[dict]) -> None:
"""Send audit records in batches of up to 500 (the PutRecordBatch limit)."""
for start in range(0, len(records), 500):
batch = [
{"Data": (json.dumps(clean(r), separators=(",", ":")) + "\n").encode()}
for r in records[start:start + 500]
]
for _ in range(3):
resp = firehose.put_record_batch(DeliveryStreamName=STREAM, Records=batch)
if resp["FailedPutCount"] == 0:
break
# Retry only the records that failed.
batch = [rec for rec, res in zip(batch, resp["RequestResponses"]) if "ErrorCode" in res]
else:
raise RuntimeError(f"{len(batch)} audit records failed after retries")PutRecordBatch can partly fail, so always check FailedPutCount and retry just the failed records. For eval traces, the harness can write a JSON Lines file per run and upload it to eval_traces/dt=YYYY-MM-DD/; a run produces one reasonably sized file, so Firehose is optional there.
Step 2: Create tables with partition projection
Classic partitions must be registered in the Glue Data Catalog withMSCK REPAIR TABLE or ALTER TABLE ADD PARTITION. Forget, and new days silently return nothing. With partition projection, Athena calculates partition locations from table properties, so a new day is queryable as soon as its files land, and Athena skips the catalog partition lookup.
sql
Create CREATE EXTERNAL TABLE IF NOT EXISTS coding_agent.tool_invocations (
ts string,
tenant_id string,
session_id string,
agent_role string,
prompt_version string,
model_id string,
tool string,
ok boolean,
latency_ms int,
input_tokens int,
output_tokens int,
repo string,
error_code string,
actor_arn string
)
PARTITIONED BY (dt string)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
LOCATION 's3://agent-lake-prod/tool_invocations/'
TBLPROPERTIES (
'projection.enabled' = 'true',
'projection.dt.type' = 'date',
'projection.dt.format' = 'yyyy-MM-dd',
'projection.dt.range' = '2026-01-01,NOW',
'projection.dt.interval' = '1',
'projection.dt.interval.unit' = 'DAYS',
'storage.location.template' = 's3://agent-lake-prod/tool_invocations/dt=${dt}/'
);coding_agent.eval_traces the same way over eval_traces/. A few things to know:
- When projection is enabled, Athena ignores partition metadata registered in Glue for that table.
- A projected day with no data doesn’t cause an error; the query just returns no rows for it.
- Why not partition by tenant too? The
injectedprojection type would work for tenant IDs, but every query must then filter on a tenant, so cross-tenant reports fail. Keeptenant_idas a normal column unless a single tenant dominates your data. - Only use
'has_encrypted_data'orencryption_optionfor client-side (CSE-KMS) encrypted data. SSE-S3 and SSE-KMS objects need no extra table properties; S3 decrypts them.
Step 3: Put a ceiling on query spend
On-demand Athena SQL is billed by the amount of data each query scans, so oneSELECT * across a year of JSON is the expensive mistake. Create a workgroup that enforces its own settings, so users can’t override the results location or the scan limit. The per-query cutoff can be set anywhere from 10 MB upward.
bash
That cutoff is 10 GiB per query; pick a value that fits your data volume. Then let analysts run queries only in this workgroup:
aws athena create-work-group \
--name coding-agent-analytics \
--configuration 'ResultConfiguration={OutputLocation=s3://agent-athena-results/coding-agent/},EnforceWorkGroupConfiguration=true,PublishCloudWatchMetricsEnabled=true,BytesScannedCutoffPerQuery=10737418240'json
If the lake uses SSE-KMS, analysts also need {
"Version": "2012-10-17",
"Statement": [
{
"Effect": "Allow",
"Action": [
"athena:StartQueryExecution",
"athena:GetQueryExecution",
"athena:GetQueryResults",
"athena:StopQueryExecution",
"athena:GetWorkGroup"
],
"Resource": "arn:aws:athena:ap-south-1:111122223333:workgroup/coding-agent-analytics"
},
{
"Effect": "Allow",
"Action": ["glue:GetDatabase", "glue:GetTable", "glue:GetPartitions"],
"Resource": [
"arn:aws:glue:ap-south-1:111122223333:catalog",
"arn:aws:glue:ap-south-1:111122223333:database/coding_agent",
"arn:aws:glue:ap-south-1:111122223333:table/coding_agent/*"
]
},
{
"Effect": "Allow",
"Action": ["s3:GetObject", "s3:ListBucket"],
"Resource": ["arn:aws:s3:::agent-lake-prod", "arn:aws:s3:::agent-lake-prod/*"]
},
{
"Effect": "Allow",
"Action": ["s3:GetObject", "s3:PutObject", "s3:ListBucket", "s3:GetBucketLocation"],
"Resource": ["arn:aws:s3:::agent-athena-results", "arn:aws:s3:::agent-athena-results/*"]
}
]
}kms:Decrypt on that key.
Step 4: Queries that earn their keep
Always filter ondt so Athena reads only the days you need, and select only the columns you use.
sql
-- 1. Token use by tenant and prompt version for one day
SELECT tenant_id,
prompt_version,
count(*) AS tool_calls,
sum(input_tokens + output_tokens) AS tokens,
count_if(NOT ok) AS failures
FROM coding_agent.tool_invocations
WHERE dt = '2026-10-05'
GROUP BY 1, 2
ORDER BY tokens DESC
LIMIT 50;
-- 2. Unusual patch volume per IAM principal (tune the threshold to your baseline)
SELECT actor_arn, count(*) AS patches, count(DISTINCT repo) AS repos
FROM coding_agent.tool_invocations
WHERE dt = '2026-10-05' AND tool = 'apply_patch'
GROUP BY 1
HAVING count(*) > 200
ORDER BY patches DESC;
-- 3. Eval pass rate and p95 latency by prompt version, last 7 days
SELECT prompt_version,
count(*) AS cases,
avg(CASE WHEN passed THEN 1.0 ELSE 0.0 END) AS pass_rate,
approx_percentile(latency_ms, 0.95) AS p95_ms,
count_if(guardrail_intervened) AS guardrail_blocks
FROM coding_agent.eval_traces
WHERE dt BETWEEN '2026-09-29' AND '2026-10-05'
AND suite = 'core'
GROUP BY 1
ORDER BY pass_rate ASC;Step 5: Compact cold data to Parquet
JSON is easy to write but expensive to scan, because Athena must read whole records even when you need two columns. Parquet is columnar, so queries read only the columns they touch. Convert closed days with CTAS:sql
Partition columns must come last in the CREATE TABLE coding_agent.tool_invocations_parquet
WITH (
format = 'PARQUET',
write_compression = 'SNAPPY',
external_location = 's3://agent-lake-prod/tool_invocations_parquet/',
partitioned_by = ARRAY['dt']
) AS
SELECT ts, tenant_id, session_id, agent_role, prompt_version, model_id, tool, ok,
latency_ms, input_tokens, output_tokens, repo, error_code, actor_arn,
dt
FROM coding_agent.tool_invocations
WHERE dt BETWEEN '2026-09-01' AND '2026-09-30';SELECT list, and one CTAS (or INSERT INTO) can write at most 100 partitions, so convert a month at a time and append later months with INSERT INTO. The target location must be empty. Firehose can also convert JSON to Parquet on arrival if you give it a Glue schema, which removes this step for new data.
Worked example: why did token use jump on Monday?
- Your cost dashboard shows Bedrock spend up sharply on Monday. Cost Explorer confirms it’s Bedrock, but not who or why.
- Query 1 for Monday shows one tenant’s
patcherrole using far more tokens than usual, all withprompt_version = review-v4, which shipped on Friday. - Query 3 shows
review-v4has a similar pass rate toreview-v3but higher p95 latency, a hint that it produces longer outputs or loops. - A follow-up query groups that tenant’s Monday sessions by
session_idand finds a handful with hundreds ofapply_patchcalls each: a retry loop when tests fail. - You roll back to
review-v3, add a per-session tool-call cap, and add query 2 as a scheduled check. Each query scanned one day of data in a capped workgroup.
Troubleshooting
| Symptom | Likely cause | Fix |
|---|---|---|
| Query returns zero rows for today | Projection range or storage.location.template doesn’t match the S3 prefix, or Firehose uses UTC days | Compare the template with an actual object key; align the Firehose time zone |
HIVE_CURSOR_ERROR or JSON parse errors | Pretty-printed or multi-line JSON records | Write one record per line with a trailing newline |
| Query fails on bytes scanned | Workgroup cutoff hit, usually from a missing dt filter | Add the partition filter and select fewer columns |
| Queries slow on recent data | Many small objects | Raise Firehose buffering; compact to Parquet |
HIVE_TOO_MANY_OPEN_PARTITIONS in CTAS | More than 100 partitions in one write | Split into CTAS plus INSERT INTO batches |
| Access denied reading the lake | Missing kms:Decrypt or S3 permissions for the analyst role | Grant them on the specific key and prefix |
| Old partitions added with MSCK are ignored | Projection is enabled, so Glue partition metadata is ignored | Rely on projection only and keep the template correct |
Production checklist
- ☐ One JSON record per line; schema documented and only ever extended
- ☐ Firehose delivery with buffering, a
dt=prefix and an error prefix - ☐ Raw prompts, completions and tool payloads excluded or redacted at the emitter
- ☐ Tables use partition projection with a matching location template
- ☐ Workgroup enforces results location and per-query scan cutoff
- ☐ Analyst IAM limited to that workgroup, the lake prefix and the KMS key
- ☐ Monthly CTAS or Firehose conversion to Parquet
- ☐ Lifecycle rules on the lake and the results bucket
FAQ
Should I use CloudTrail Lake for agent auditing?
CloudTrail records AWS API calls, not your own tool names or prompt versions, so you need an application-level lake either way. Also note that AWS CloudTrail Lake is no longer open to new customers from May 31, 2026. If you don’t already use it, query CloudTrail logs delivered to S3 with Athena instead.Athena or OpenSearch for tool logs?
OpenSearch for full-text search and fast interactive dashboards. Athena for SQL across months of data without running a cluster. Many teams keep recent data in OpenSearch and the full history in S3.When should I move to a warehouse?
When you need many concurrent BI users with fast dashboards, heavy joins with product databases, or fine-grained row-level security beyond what Lake Formation gives you. Until then, Athena keeps the stack small.Can I gate deployments on eval results?
Yes. Have the pipeline run query 3 for the candidate and current prompt versions and fail if the core suite’s pass rate drops by more than your agreed threshold.Official documentation
- What is Amazon Athena?
- Use partition projection with Amazon Athena and supported projection types
- OpenX JSON SerDe
- Configure per-query and per-workgroup data usage controls
- CTAS considerations and limitations and working around the 100-partition limit
- Tables based on encrypted datasets
- Firehose custom S3 prefixes and PutRecordBatch
- Working with AWS CloudTrail Lake (availability note)
Watch: 100 Days of AI on YouTube
Short videos from CheatCoders, one AI engineering topic per day.
Most viewed
- Python Decorators Explained: From Simple Wrappers to Production Patterns
- AI Agent Frameworks in 2025: LangGraph vs CrewAI vs AutoGen vs Raw API
- Claude Code vs Cursor vs GitHub Copilot: Choose by Workflow, and Set Up One Repo That Works With All Three
- Python String Methods: Every str Method With Real Production Examples
- API Debugging in 30 Seconds: The Fastest Ways to Fix API Issues
Newly added
- n8n vs AI Agent: I Gave Both the Same Job (Local, Ollama)
- Local AI Video Generator: Build Your Own App (Free, Python, No GPU)
- AI Wrote Raw Binary Instead of Code. Here’s What Happened (WebAssembly)
- I Built a Browser That Only Runs Encrypted Binary (Ep 1)
- RAG From Scratch: Chunking, Embeddings, Reranking and Evaluation (Real Numbers)
Deep-dive PDF
Get the expanded guide for this post — extra diagrams-style checklists, failure modes, and a production walkthrough. Free when you subscribe to CheatCoders.
Already subscribed? or open the subscribe page.
Discover more from CheatCoders
Subscribe to get the latest posts sent to your email.