DEA-C01 Question 261
Single answerYou have been tasked with designing a data model for a large-scale e-commerce application. The application requires storing customer purchase history, which will be queried frequently for analytics and personalization. The queries primarily involve filtering by customer ID and aggregating purchase totals over various time periods. Which data modeling approach is most appropriate to optimize both storage and query performance in this scenario?
- A
Store the data in a normalized relational database schema with separate tables for customers, purchases, and products.
- B
Use a denormalized schema with a single table containing all customer and purchase data.
- C
Store the data in a columnar database like Amazon Redshift with pre-aggregated purchase totals by customer and time period.
- D
Use a NoSQL key-value store like Amazon DynamoDB with customer ID as the partition key, and store purchase history as a nested document.
Show answer and explanation
Correct answer: D
Explanation
For this scenario, the key requirement is frequent querying and filtering by customer ID, combined with scalability and performance. A NoSQL approach, such as using Amazon DynamoDB, aligns well with these requirements. By using customer ID as the partition key and storing purchase history as a nested document, you can achieve efficient performance for both storage and query operations. This approach avoids the complexities of relational joins and leverages DynamoDB's scalability for large-scale e-commerce data.
- A. Incorrect.
While normalization reduces data redundancy, it results in complex queries with multiple joins, which could negatively impact performance for analytics and frequent filtering by customer ID.
- B. Incorrect.
A denormalized schema in a relational database might improve query performance but can lead to data inconsistency issues and inefficiency in storage when scaling to large datasets.
- C. Incorrect.
Columnar databases like Amazon Redshift are well-suited for analytical workloads but are less efficient for frequent, real-time filtering and updates by customer ID.
- D. Correct.
Using Amazon DynamoDB with customer ID as the partition key allows for efficient filtering and retrieval of customer-specific purchase history. Storing purchase history as a nested document reduces the need for complex joins, and DynamoDB's scalability supports high query performance.