-- The "Anti-Pattern": Thousands of tiny files in a deep partition structure SELECT count(*) FROM raw_logs WHERE event_date = '2023-10-01'; -- The "Optimized" Approach: Compaction and partition pruning SELECT count(*) FROM optimized_logs WHERE event_date = '2023-10-01';

Why this matters: Amazon Athena charges based on the volume of data scanned. When your S3 layout creates a "small-files problem," Athena is forced to open, read metadata, and scan thousands of tiny objects, leading to increased latency and unnecessary costs that scale linearly with your technical debt.

Every data engineer working with AWS eventually reaches the same threshold: the moment their S3 bucket transitions from a simple landing zone to a queryable data lake. You start with a few JSON files, but within months, you have millions of objects spread across a deep, nested directory tree. You assume that because you are using a "modern" stack, performance will remain constant.

It does not.

How it actually works

When you execute a query in Amazon Athena, the engine must perform a "split" operation. It scans the metadata of the files located in the target prefix. If your data is scattered across thousands of 10KB files, the engine spends more time performing S3 ListObjectsV2 and HeadObject API calls than it does actually processing your data.

Consider an illustrative scenario: a logging pipeline writing events directly to S3 partitioned by date and hour.

s3://my-data-lake/logs/year=2023/month=10/day=01/hour=01/file1.json s3://my-data-lake/logs/year=2023/month=10/day=01/hour=01/file2.json ... s3://my-data-lake/logs/year=2023/month=10/day=01/hour=01/file1000.json

In this layout, if you query for a single hour, Athena must reconcile 1,000 file headers. If you scale this to a year, you are looking at millions of metadata requests. The overhead of opening these files creates a "bottleneck of small files."

To solve this, we move toward table formats like Apache Iceberg. Iceberg abstracts the physical file layout from the logical table. Instead of relying on Hive-style directory partitioning (which is brittle and metadata-heavy), Iceberg maintains a manifest file that acts as a map to your data files.

When you use the AWS Glue Data Catalog with Iceberg, you can define your partitioning strategy explicitly:

-- Using Iceberg with Glue/Athena CREATE TABLE logs_iceberg ( event_time timestamp, event_id string, payload string ) PARTITIONED BY (days(event_time)) LOCATION 's3://my-data-lake/iceberg/logs/' TBLPROPERTIES ('table_type'='ICEBERG');

By leveraging days(event_time), Iceberg performs hidden partitioning. You no longer need to manage physical directory paths like year=/month=/day=. The engine manages the file mapping, and more importantly, allows for automated compaction.

yellow and blue crates
Photo by Jason Wu on Unsplash

Design choices and tradeoffs

The primary tradeoff in S3 layout design is between ingestion latency and query performance.

If you optimize for real-time ingestion (writing every single event as it arrives), you create small files. This is great for producers but terrible for consumers like Athena or Amazon Redshift Spectrum. If you optimize for query performance, you must buffer data and write in larger chunks (typically 128MB to 512MB for Parquet/ORC files).

Compaction is the answer, but it is not free.

When running a rewrite_data_files procedure in Iceberg, you are effectively running a background job that reads small files and writes them back out into larger, optimized ones.

-- Compacting small files into 128MB chunks CALL system.rewrite_data_files( table => 'my_catalog.my_db.logs_iceberg', options => map('target-file-size-bytes', '134217728') );

The trade-offs:

  1. Compute Cost: Compaction is an EMR or Glue job. You pay for the compute to "clean up" your lake.
  2. Write Amplification: You are writing the same data twice (once on ingest, once during compaction).
  3. Consistency: Ensure you have a strategy for dealing with deleted data. Iceberg handles this via snapshots and vacuuming, but you must monitor these to avoid ballooning storage costs.

When to reach for it (and when not to)

Reach for Iceberg/Compaction when:

  • Your query patterns involve scanning large time ranges (e.g., "give me the last 30 days of sales").
  • You are hitting S3 throttling limits on metadata operations.
  • Your Athena queries are consistently slow despite having few rows returned, indicating an overhead-bound query.

Avoid over-engineering when:

  • You have a small dataset (under 100GB total). The overhead of managing manifests and compaction jobs will outweigh the performance gains.
  • You are using S3 Select for simple, ad-hoc file retrieval where partitioning is irrelevant.
  • You have a strict requirement for "milliseconds-from-event-to-query." In this case, accept the small-file penalty and use a tool like Amazon Managed Service for Apache Flink to aggregate before landing on S3.

a forest of trees
Photo by Ivan Stepanov on Unsplash

Clean up

If you have been testing these patterns with AWS Glue and Athena, remember to:

  1. Delete the data from your S3 bucket to stop incurring storage costs.
  2. Drop the tables from your AWS Glue Data Catalog.
  3. Delete the IAM roles or service-linked roles created for your testing environment.

Conclusion

The "small-files problem" is rarely a failure of the platform; it is a failure of the layout. By shifting from physical directory partitioning to managed table formats like Apache Iceberg, you decouple your storage layout from your query logic. Focus on file size consistency—aiming for the 128MB–512MB sweet spot—and automate your compaction. Your query costs will thank you.

Cover photo by Aashish Yadav on Unsplash.

This article was drafted with AI assistance and is intended for technical review and fact-checking before publication.