Back to basics: a terabyte of access logs for 34 cents
Back to basics
Almost everything I’ve written this past year has been about agents. Strands, MCP servers, tools that trace a network path while you go get coffee. I regret none of it, and I’m not going to be the guy complaining that everyone else writes about AI too. It’s the interesting thing happening right now, and writing about it is fair.
But every one of those agents runs on something. A VPC that routes, a cluster that schedules, a bucket that holds the data and an IAM role that lets you read it. The model doesn’t care how good it is if the subnet has no route out, and when something breaks at midnight you’re still the person reading a route table. Everyone needs a platform underneath, and no amount of prompt engineering builds one.
So I’m starting a new series. Back to basics: networking, compute, storage, the layer everything else sits on. These are the posts I’d hand to someone joining my team in their first week.
Starting with a thing that surprised me by how cheap it is.
The question
“What do our access patterns actually look like? Which paths are hot, who is hitting them, and how much are we serving from cache?”
Reasonable question. I had the data: eleven days of access logs out of our HTTP caching layer, exported from Elasticsearch as newline-delimited JSON. Every record is an Elasticsearch hit envelope, so five top level fields where the only interesting one is _source, which carries about thirty fields of its own.
Each day was 5 to 8 GB compressed, around 90 GB inflated. Eleven of those is roughly a terabyte of JSON.
Here’s what it cost me to make all of it queryable, from files on my laptop to SQL against the whole set:
About 34 cents.
Then a fraction of a cent per question after that. The bulk of my actual spend was S3 storage at four or five dollars a month for keeping two copies, which I could delete tomorrow.
That number is the point of this post. A terabyte of ad-hoc log analysis now costs less than a bus ticket, and you don’t need a data platform or anyone’s permission to do it. The SQL below is unremarkable and my setup was clunky in a couple of places I’ll happily own up to, which turns out to matter very little when the whole exercise costs pocket change.
Why this is so cheap
Athena is a query engine you point at object storage. You describe the shape of files already sitting in S3, then you write SQL against them. Nothing runs when you’re not querying, so there’s nothing to size and nothing to turn off.
Pricing is $5 per terabyte scanned, rounded up to the megabyte, with a 10 MB minimum per query. That’s the rate in eu-north-1 where I run, same as everywhere else I checked. DDL is free: CREATE TABLE, MSCK REPAIR, DROP, none of them cost anything. Failed queries are free too, though cancelled ones still bill you for whatever they read before you killed them.
The load-bearing detail is what “scanned” means. It’s bytes read from S3, and for compressed files that’s the compressed size, because you pay for bytes before decompression. My terabyte of JSON was 68 GB on disk as gzip, and Athena bills the 68 GB.
That is a 93% discount for typing gzip.
AWS spells this out on their pricing page with a 3 TB text file. Uncompressed, a query against one column costs $15, because text can’t be split and it reads everything. Gzip it at 3:1 and the same query is $5. Convert to Parquet as well, so only the referenced column gets read, and it’s $1.25.
Do both of those before you write a single query.
Getting the files into S3
The files arrived as zstd. I recompressed them to gzip on the way up, one day at a time:
1
2
3
4
5
6
zstd -dc access-2025.04.01.json.zst | gzip > access-2025.04.01.json.gz
aws s3 cp access-2025.04.01.json.gz \
s3://example-access-logs/access/raw/dt=2025-04-01/access-2025.04.01.json.gz
rm access-2025.04.01.json.gz
Three things in there matter more than they look.
The dt=2025-04-01/ in the key is the partition. Hive-style key=value prefixes are how Athena learns that these files belong to a date, which is what later lets a query for one day skip the other ten before reading a byte. Getting this into the layout at upload time costs nothing. Retrofitting it means moving every object.
Then the rm. Each intermediate .gz is 5 to 8 GB, so looping over eleven files without cleaning up adds close to 70 GB to your laptop. With the rm in there, disk stays flat at one file.
And put the bucket in the same region as Athena. Cross-region reads work fine and then charge you transfer for the privilege. AWS puts it more bluntly than I would: one query can transfer more data than the size of the dataset. aws s3api get-bucket-location --bucket example-access-logs tells you where the bucket is, and a null answer means us-east-1.
Now, the recompression. I did it because I assumed Athena wouldn’t read zstd. Athena has read zstd compressed text files since November 2021. The step was unnecessary and I should have spent thirty seconds in the documentation instead of an afternoon at the terminal.
A table over the raw JSON
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
CREATE EXTERNAL TABLE access_raw (
`_index` STRING,
`_type` STRING,
`_id` STRING,
`_score` DOUBLE,
`_source` STRUCT<
host: STRING,
url: STRING,
status: INT,
bytes: BIGINT,
handling: STRING,
client_ip: STRING,
user_agent: STRING,
duration_us: BIGINT,
ats: STRING,
aversion: STRING,
`type`: STRING
-- ...and the rest of the ~30 fields
>
)
PARTITIONED BY (dt STRING)
ROW FORMAT SERDE 'org.openx.data.jsonserde.JsonSerDe'
WITH SERDEPROPERTIES (
'ignore.malformed.json' = 'true',
'mapping.ats' = '@timestamp',
'mapping.aversion' = '@version'
)
LOCATION 's3://example-access-logs/access/raw/';
Free, because it’s DDL. Nothing has been read yet. This statement is pure description: there are files under that prefix and they look like this.
The gotchas here are the kind of thing that eats an hour if nobody’s told you.
@timestamp and @version can’t be referenced as columns, because a leading @ isn’t a legal identifier. That’s what mapping.ats and mapping.aversion are for, renaming the JSON key to something you can type. Without them the fields come back NULL and nothing tells you why. The mapping works on fields nested inside a struct and not just on top-level columns, which is the part I expected to fight with and didn’t.
timestamp is on Athena’s reserved word list for DDL, so a column named that needs backticks. type isn’t on the list, and I quoted it anyway out of habit from other engines. Athena publishes separate reserved words for DDL and for SELECT, and they’re different lists, so check rather than guess.
Names beginning with an underscore do need backticks in the DDL, which is why all five top-level columns have them. They’re fine bare on the Trino side, which is why the CTAS further down doesn’t bother.
You only need to declare the fields you intend to query. The SerDe maps what you tell it about and ignores the rest of the document. Thirty fields declared is thirty chances to typo something.
Then register the partition:
1
MSCK REPAIR TABLE access_raw;
Smoke test before you convert anything
If you take one step from this post, take this one.
1
2
3
4
5
SELECT _source.host, _source.url, _source.status,
_source.bytes, _source.handling, _source.ats
FROM access_raw
WHERE dt = '2025-04-01'
LIMIT 10;
Five thousandths of a cent. That’s the 10 MB minimum, which is about as close to free as billing gets.
Look at what comes back and check it field by field. Is status an integer or did it arrive as a string. Is bytes populated. Is handling showing hit and miss the way you expect. Is ats a real ISO timestamp, which is what tells you the SerDe mapping worked.
If ats is NULL, the nested mapping isn’t being honoured, and the fix is to declare _source as a plain STRING and pull fields out with json_extract_scalar instead. Better to find that out on ten rows for a rounding error than after twenty-five minutes of converting a terabyte.
I did the whole pipeline on one day first, end to end, before touching the other ten. One day is a complete rehearsal for about three cents.
Convert to Parquet
Parquet earns its place twice over. It’s columnar, so a query touching three fields reads three columns instead of thirty. And any worker can start reading from the middle of a Parquet file, because each section carries its own compression and the footer records where each one begins. Gzip can’t do that, which matters more than it sounds and I’ll come back to it.
CREATE TABLE AS SELECT does the conversion in one statement:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
CREATE TABLE access
WITH (
format = 'PARQUET',
parquet_compression = 'SNAPPY',
external_location = 's3://example-access-logs/access/parquet/',
partitioned_by = ARRAY['dt']
) AS
SELECT
_id AS id,
CAST(from_iso8601_timestamp(_source.ats) AS TIMESTAMP) AS ts,
_source.host AS host,
_source.url AS url,
_source.status AS status,
_source.bytes AS bytes,
_source.handling AS handling,
_source.client_ip AS client_ip,
_source.user_agent AS user_agent,
_source.duration_us AS duration_us,
_source.backend_response AS backend_response_s,
_source."type" AS record_type,
-- ...remaining fields
dt
FROM access_raw;
Three cents. It reads about 6 GB of gzip for that one day.
Quoting switches dialect mid-file, which is confusing the first time you hit it. The CREATE EXTERNAL TABLE above goes through Glue and speaks Hive, so backticks. The body of the CTAS is Trino, so double quotes. Backticks in the CTAS body get you backquoted identifiers are not supported; use double quotes to quote identifiers, and double quotes in the Hive DDL get read as a string literal instead of an identifier.
The partition column goes last in the SELECT list. Athena matches partition columns by position rather than by name, so put dt in the middle and you’ll get a table partitioned by whichever field landed at the end.
Do the casting and renaming here, once. ats becomes a real TIMESTAMP called ts, parsed at conversion instead of on every future query. backend_response becomes backend_response_s, because the _s reminds me it’s seconds while duration_us is microseconds, and I’d otherwise mix them up inside a week. This is the one moment where renaming is free.
CTAS also caps at 100 partitions, with the error HIVE_TOO_MANY_OPEN_PARTITIONS: Exceeded limit of 100 open writers for partitions/buckets. Eleven daily partitions is fine. Partition by hour and you’re over the line in five days.
For the remaining ten days I used INSERT INTO rather than rebuilding the table:
1
2
3
4
INSERT INTO access
SELECT ...same projection...
FROM access_raw
WHERE dt <> '2025-04-01';
Thirty-one cents for about 62 GB of gzip. INSERT INTO only scans what you haven’t converted yet, it appends rather than demanding an empty destination, and it carries the same 100-partition ceiling per statement. Batching by week is the sane pattern for anything bigger than this.
The clunky part
Two things bit me, and both trace back to the same root.
Gzip isn’t splittable. A gzip reader has to start at the beginning and work forward, so one .gz object can only ever be read by one node. My daily file was 6 GB compressed and 90 GB inflated, streamed through a single worker while the rest of the cluster had nothing to do. That’s why a conversion scanning only 6 GB can take anywhere from five to thirty minutes.
One of my conversion runs crossed the thirty minute mark and Athena cancelled it.
By about ten seconds.
CTAS is DML, and Athena cancels DML at 30 minutes by default. No partial results, nothing to resume from. It had already written some output, and CTAS refuses to write into a location that has data in it, so the retry died instantly with HIVE_PATH_ALREADY_EXISTS and I had to go and empty the prefix by hand. Athena doesn’t reliably clean up after a cancelled write, though it does leave a data manifest listing the files it meant to write, which beats guessing. I raised the DML timeout in Service Quotas, which goes up to 240 minutes, and it went through on the next attempt.
Here’s the root cause of both. I converted a non-splittable format into a different non-splittable format. Neither gzip nor the zstd I started from can be split, so the recompression cost me an afternoon and moved the ceiling precisely nowhere.
File layout was the problem. Eleven enormous single objects can’t parallelize no matter which algorithm compressed them, and had I split each day into a handful of smaller files, every worker would have had something to do.
That’s the whole clunky story, and I want to be clear about its scale: one afternoon and zero dollars, on a job that a few years ago would have been a cluster, a ticket, and a conversation about budget.
The questions I actually asked
This is the part that felt like cheating. Every query below runs in seconds and costs somewhere between a thousandth of a cent and five cents. They’re scoped to one day here, but dropping the dt filter to hit all eleven costs pennies rather than dollars. Cheap enough to be sloppy, and sloppy is the point, because the seventh question is usually the good one and you only get there if the first six were free.
Cache hit ratio:
1
2
3
4
5
6
7
SELECT handling,
COUNT(*) AS reqs,
COUNT(*) * 100.0 / SUM(COUNT(*)) OVER () AS pct
FROM access
WHERE dt = '2025-04-01'
GROUP BY handling
ORDER BY reqs DESC;
Who’s actually hitting us, which was the real question behind the original ask:
1
2
3
4
5
6
7
8
9
10
SELECT client_ip,
COUNT(*) AS reqs,
COUNT(DISTINCT url) AS distinct_urls,
COUNT(DISTINCT user_agent) AS distinct_uas,
SUM(bytes) AS bytes_out
FROM access
WHERE dt = '2025-04-01'
GROUP BY client_ip
ORDER BY reqs DESC
LIMIT 50;
The shape to look for is high request count, high distinct URLs, low distinct user agents. That combination is almost always a cluster’s outbound NAT address, which means the traffic is internal. Someone actually browsing looks nothing like it: a handful of URLs, one user agent, and that’s it.
One caveat worth more than the query itself. If you sit behind a CDN or any reverse proxy that terminates TLS, client_ip is the edge node and not the end user. Every conclusion you draw from it will be confidently wrong. Check a handful of values before building anything on that column, and fall back to user_agent when it turns out to be useless.
The bill
| Step | Cost |
|---|---|
| Upload to S3 | Free, request charges negligible |
| All DDL: CREATE, MSCK, DROP | Free |
| Smoke test on 10 rows | ~$0.00005, the 10 MB minimum |
| CTAS, one day, scans ~6 GB gz | ~$0.03 |
| INSERT INTO, ten days, scans ~62 GB gz | ~$0.31 |
| Total to make ~1 TB of JSON queryable | ~$0.34 |
| Each analysis query afterwards | thousandths of a cent up to ~$0.05 |
| S3 storage, eleven days as gzip | ~$1.60/month |
| S3 storage, eleven days as Parquet | ~$2.50 to $3.80/month |
Two honest notes on that table.
The Parquet copy came out larger on disk than the gzip original, which I didn’t expect. Snappy trades compression ratio for speed and I’d asked for Snappy, so that’s the obvious suspect. If storage mattered I’d use parquet_compression = 'GZIP', which CTAS supports and which is actually the default if you say nothing at all. Either way it’s still clearly worth it, because storage is priced in cents and the columnar layout is what makes every query afterwards nearly free. Shaving cents off storage to make your queries cost dollars is a trade I’ve watched people make with a straight face.
And keep the raw files for a while. You’ll get a field wrong. When you do, the raw copy is the only thing that lets you redo the conversion, and re-uploading a terabyte from a laptop is a genuinely bad afternoon.
What I’d do differently
I’d leave the zstd alone. Athena reads it.
Next time I’d split each day into a handful of files in the low hundreds of megabytes instead of one 6 GB object, so the conversion can use more than one worker.
Partition projection is worth setting up on day one instead of MSCK REPAIR. Repair works, and it relists every prefix every time, so it gets slower every day forever. Projection has Athena calculate partition locations from a pattern, which means no metadata calls and tomorrow’s partition already exists as far as the table is concerned.
And I’d convert in weekly batches from the start, so a failure costs a week rather than the whole set.
The part I’m slightly sheepish about: I’m not a SQL person. I read it fine, I just don’t write it from cold very often, and window functions still send me to a documentation tab. So I had Claude write most of these queries, and it’s a better trade than it sounds, because SQL is a lot easier to read than it is to write. I know roughly what the answer should look like, I can tell whether a query is asking the right question, and I can spot a join about to duplicate rows. Getting there from an empty editor is the slow part, and that’s the part I handed over.
Takeaway
Four dollars a month of storage and thirty-four cents of compute turned a terabyte of logs into something I can ask questions of in seconds, with no cluster and nobody’s approval needed.
Spend ten minutes on file layout before you spend an afternoon on compression, and smoke test on ten rows before you convert a terabyte.
Next in Back to basics: