AWS Certified Data Engineer – AssociateData Storage and ManagementMedium
A data engineer needs to implement data partitioning for a large dataset stored in Amazon S3. The data is generated daily and queried primarily by date range, specifically `year`, `month`, and `day`. The goal is to optimize query performance and reduce the amount of data scanned by analytics engines like Amazon Athena. Which partitioning strategy should be applied?
- APartition by `customer_id`
- BStore all data in a single, large partition
- CPartition by `region` and `product_category`
- DPartition by `year/month/day`
Show answer & explanationAnswer & explanation
Correct answer: D. Partition by `year/month/day`
Partitioning data by `year/month/day` aligns directly with the primary query pattern (date range). This strategy allows analytics engines to prune unnecessary S3 prefixes, scanning only the relevant data, which significantly improves query performance and reduces costs.
Why the other options are wrong
- A. Partitioning by `customer_id` would create too many small partitions and is not aligned with date-range queries.
- B. Storing all data in a single partition defeats the purpose of partitioning, leading to full table scans for every query and poor performance.
- C. Partitioning by `region` and `product_category` might be useful for other query types but not for the primary date-range queries.
S3 Data Partitioning
S3 data partitioning organizes data in S3 buckets by creating a folder structure based on one or more column values (e.g., `s3://bucket/key=value/`). This allows analytics engines to prune data, scanning only relevant subsets, which significantly improves query performance and reduces costs.
- Organizes data into logical folders
- Improves query performance by reducing scanned data
- Reduces query costs (pay-per-scan)
- Commonly used with date columns for time-series data
Memory trick: Partitions Pinpoint Performance.