S3 Layout and Partitioning for a Training Set
A team stores 4 years of clickstream Parquet in S3 under s3://lake/events/year=/month=/day=/hour=/, about 120 GB/day, ~180 TB total. Analysts query with Athena and complain that a 7-day lookback scan costs far more than expected. A data scientist pulls 18 months for feature engineering into a SageMaker Processing job on 4 ml.m5.4xlarge instances and the job spends 70% of its wall time listing and opening small files - average file size is 4 MB, and the hour partition produces ~24 files per day per source. Late-arriving events arrive up to 3 days after the event date and are appended to the prior day's partition. Some queries filter on event_type and device, which are not partition columns.
1. Explain the Athena cost symptom and propose a storage change that reduces scanned bytes for the 7-day lookback without breaking late-arrival handling.
Read the worked answer
Athena bills by bytes scanned, and partition pruning only helps when the predicate matches partition columns. A 7-day lookback prunes to 168 hour partitions, but each hour partition holds ~24 small files, so the engine opens ~4,000 objects and reads full column chunks; with no statistics-driven skipping, filters on event_type and device do not reduce bytes read, only rows returned. The 4 MB files also waste per-file overhead.
Two changes. First, compact: run a periodic job that rewrites each day partition into ~128–512 MB Parquet files, which cuts object count and improves columnar read efficiency. Second, add event_type and device as partition columns (or, if cardinality is high, sort/cluster within files and rely on Parquet predicate pushdown - but pushdown still reads row groups, so partitioning is the reliable lever for Athena cost).
Late arrivals complicate compaction: rewriting a day partition while appends land can drop data. Handle it by writing late events to a separate staging prefix, running compaction on partitions older than the 3-day watermark, and merging staging into the compacted partition atomically (write new files, then swap). Keep the hour partition only if hour-level pruning is actually used; otherwise collapse to day to reduce partition count and metadata overhead.
2. The Processing job is I/O bound. What changes to instance choice, input mode, and file layout would you make, and what would you verify first?
Read the worked answer
Verify before changing: confirm the job is I/O bound rather than CPU bound by checking instance CPU utilization and S3 request metrics. If CPU is low and time is dominated by LIST and GET, the fix is fewer, larger objects and better read parallelism.
The dominant fix is the same compaction described for Athena: 4 MB average files mean the job issues millions of GETs, and listing 18 months of hour partitions is itself expensive. Compact to 128–512 MB files and the object count drops by one to two orders of magnitude.
Instance choice: ml.m5.4xlarge is general purpose; for a shuffle-heavy or high-throughput read workload, a storage-optimized or compute-optimized family with more network bandwidth per vCPU can help, but the real lever is file count. Also consider SageMaker's FastFile or Pipe input mode so data streams from S3 instead of being fully downloaded before processing starts, reducing startup latency on 18 months of data.
Layout: if the job only needs a subset of columns, ensure the Parquet schema lets it project those columns, and push the date filter into the S3 prefix so the job lists only the needed partitions rather than the whole prefix. Finally, distribute work so each instance reads a disjoint set of partitions to avoid duplicate listing.