A global e-commerce company wants to analyze customer behavior across different regions and languages. Their customer data, stored in Amazon S3, is currently organized by `upload_date`. To facilitate efficient queries and machine learning model training that frequently filter data by `country` and `language`, the data engineering team decides to re-organize the data. Which data partitioning scheme in S3 would be most effective for this scenario?
- As3://bucket/data/language=en/country=US/
- Bs3://bucket/data/upload_date=YYYY-MM-DD/
- Cs3://bucket/data/country=US/language=en/upload_date=YYYY-MM-DD/
- Ds3://bucket/data/country=US/language=en/
Show answer & explanationAnswer & explanation
Correct answer: C. s3://bucket/data/country=US/language=en/upload_date=YYYY-MM-DD/
The most effective partitioning scheme should prioritize the most frequently used filters, or a combination that allows maximum pruning. Since queries frequently filter by `country` and `language`, and `upload_date` is also a likely filter, a hierarchical structure that includes all three is best. Starting with `country`, then `language`, and finally `upload_date` (or `upload_date` first then `country/language`) allows query engines to quickly narrow down the data scanned.
Why the other options are wrong
- A. Partitioning by `language` then `country` is also a valid hierarchical approach. However, given that `country` is often a primary geographical filter, `country` first, then `language`, and then `upload_date` (or vice-versa for the first two) would be slightly more conventional and equally effective for these specific filters.
- B. This scheme only partitions by `upload_date`. Queries filtering by `country` or `language` would still need to scan all data within the relevant dates, which is inefficient.
- D. This scheme partitions by `country` and `language` but omits `upload_date`. If queries also filter by date, they would scan all dates within the specified country/language, which is inefficient.
Multi-Column Partitioning in S3
Organizing data in Amazon S3 using a hierarchical folder structure based on multiple frequently-queried columns to optimize query performance and cost.
- Uses `key=value/` folder structure.
- Order of partition keys matters for query pruning.
- Most selective or frequently filtered columns often come first.
- Avoids scanning unnecessary data, reducing query time and cost.
- Integrates with Glue Data Catalog for schema discovery.
Memory trick: Partition your data by country, then language, then date for perfect pruning.