Optimizing S3 Data Lake for Low-Frequency Individual Record Lookups Prioritizing Simplicity

We have a large-scale S3 data lake with the following characteristics:

• Source: AWS Flink application writing Parquet files directly to S3
• Volume: ~4000 Parquet files per hour, ranging from 200GB to 1TB of Parquet data per hour
• Daily Volume: 12TB+ across ~96k files
• Record Count: ~80-130M records/hour or ~2.3-2.5B records/day
• S3 Partitioning by date and hour
• No lakehouse format (no Iceberg, Hudi, etc.), just uncompressed Parquet files in S3 being read by downstream AWS Glue (Apache Spark) jobs and AWS Athena
• Each record has 4 different keys associated with it that it can be looked up by
• Data likely to double in the next ~8 months
• 28 day rolling retention policy

Usage Patterns:

• Primary use case: Glue/Spark jobs - Read entire hour partitions for aggregations and analytics - performing fine
• Secondary use case: UI Lookups - Single key look ups for displaying a single requests payload in a UI. ~100-300 requests/day (low usage - but important users/usecase)
  • Users must specify the date the record took place in (hour is optional)
  • If no hour filter is provided, there's an 80% change the query times out past our 40 second API limit
  • If hour is provided, 90% success rate

Constraints:

• Write-heavy workload due to the amount of unique keys we see
• Low number of UI users, but fixing timeouts is critical as they are important users
• Data volume expected to double within a year
• Would like to avoid full re-architecture, but have already considered migrating to Apache Iceberg for other reasons (ex: we currently have no way to modify records)
• Would like as minimal downstream impact on the Glue jobs due to the amount of jobs dependent on it
• Would like to keep cost and complexity relatively low due to the user base being small and our team already supporting a lot of other use cases

Solutions Considered:

• Apache Iceberg: Use hidden partitioning/indexing for secondary lookups while maintaining hourly physical partitions for Spark jobs
• Custom manifest/index files: Create per-hour index files (one per key type) mapping keys to specific S3 file paths. See below:

The idea was to create manifest files as such for each primary key in each hourly partition: /manifest/datetime=2026-01-01--00/pk_type_1/partition<0_n>.parquet

We would read a batch of S3 events and create a map of key -> s3_path that we would store in a manifest file in S3/EFS. To make the manifest files smaller, we hash the key value and mod by 1000 (arbitrary number for now without more testing). This would allow us to look through 1/1000 of the keys to make look up faster.

The challenge here is maintaining these in a streaming architecture is complex (S3 event → SNS → SQS → Lambda would require expensive read-modify-write cycles or some type of batching of manifest files). This part is incredibly complex and would require a significant amount of complexity. Annoyingly most efficient read based file systems, like Parquet, don't support appends and require a full read + modify + write to make any modifications.

I thought about running the batch job 5 minutes past the hour each hour and falling back to simple Athena queries when the cache doesn't yet exist, but that seems like a pretty large pitfall and delay in a realtime system.

I of course have also considered DynamoDB (prohibitively expensive write cost for the amount of reads we do) and am also considering spinning up a PostgreSQL database to test with. I just wanted to see if I could architect a solution for this that wouldn't result in a need for a traditional database as that is only more management for our team (who is currently trying to move away from DB management).

Questions:

1. Would Apache Iceberg be a good fit for this use case with hidden partitioning and their manifest file system?
2. Is there a simpler solution I'm missing? I'm aiming for ~5-8 second queries with minimal operational overhead, not sub-second performance.
3. For the custom manifest approach, what's the best way to maintain indexes in a streaming context without expensive read-modify-write operations?
4. Are there lightweight indexing solutions better suited for streaming writes and infrequent point lookups?