Amazon Athena for Coding Agents: Query Tool Audit Logs and Eval Traces on S3 Without a Warehouse

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 run MSCK REPAIR TABLE again.
  • 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 can GROUP BY.
json
{
  "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"
}
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: 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:
QuestionBest 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 outputOpenSearch
What does AWS charge per service?Cost Explorer and the Cost and Usage Report
Figure 1. Agent tool audits and eval traces flow through Firehose into day-partitioned S3 prefixes, then Athena queries them through a capped workgroup. Diagram by CheatCoders.
Figure 1. Agent tool audits and eval traces flow through Firehose into day-partitioned S3 prefixes, then Athena queries them through a capped workgroup. Diagram by CheatCoders.

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 as tool_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
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")
The trailing newline matters: Firehose concatenates records, and the newline keeps each JSON object on its own line. 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 with MSCK 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 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}/'
);
Create 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 injected projection type would work for tenant IDs, but every query must then filter on a tenant, so cross-tenant reports fail. Keep tenant_id as a normal column unless a single tenant dominates your data.
  • Only use 'has_encrypted_data' or encryption_option for 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 one SELECT * 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
aws athena create-work-group \
  --name coding-agent-analytics \
  --configuration 'ResultConfiguration={OutputLocation=s3://agent-athena-results/coding-agent/},EnforceWorkGroupConfiguration=true,PublishCloudWatchMetricsEnabled=true,BytesScannedCutoffPerQuery=10737418240'
That cutoff is 10 GiB per query; pick a value that fits your data volume. Then let analysts run queries only in this workgroup:
json
{
  "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/*"]
    }
  ]
}
If the lake uses SSE-KMS, analysts also need kms:Decrypt on that key.

Step 4: Queries that earn their keep

Always filter on dt 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
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';
Partition columns must come last in the 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?

  1. Your cost dashboard shows Bedrock spend up sharply on Monday. Cost Explorer confirms it’s Bedrock, but not who or why.
  2. Query 1 for Monday shows one tenant’s patcher role using far more tokens than usual, all with prompt_version = review-v4, which shipped on Friday.
  3. Query 3 shows review-v4 has a similar pass rate to review-v3 but higher p95 latency, a hint that it produces longer outputs or loops.
  4. A follow-up query groups that tenant’s Monday sessions by session_id and finds a handful with hundreds of apply_patch calls each: a retry loop when tests fail.
  5. 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

SymptomLikely causeFix
Query returns zero rows for todayProjection range or storage.location.template doesn’t match the S3 prefix, or Firehose uses UTC daysCompare the template with an actual object key; align the Firehose time zone
HIVE_CURSOR_ERROR or JSON parse errorsPretty-printed or multi-line JSON recordsWrite one record per line with a trailing newline
Query fails on bytes scannedWorkgroup cutoff hit, usually from a missing dt filterAdd the partition filter and select fewer columns
Queries slow on recent dataMany small objectsRaise Firehose buffering; compact to Parquet
HIVE_TOO_MANY_OPEN_PARTITIONS in CTASMore than 100 partitions in one writeSplit into CTAS plus INSERT INTO batches
Access denied reading the lakeMissing kms:Decrypt or S3 permissions for the analyst roleGrant them on the specific key and prefix
Old partitions added with MSCK are ignoredProjection is enabled, so Glue partition metadata is ignoredRely 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

Related on CheatCoders: AWS Batch for overnight agent fleets · SSM Parameter Store for agent config Last updated on October 6, 2026

Watch: 100 Days of AI on YouTube

Short videos from CheatCoders, one AI engineering topic per day.

Open the playlist on YouTube · CheatCoders on YouTube

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.

Comments

No comments yet. Why don’t you start the discussion?

Leave a comment

No account needed. Name and email are optional.