DEA-C01 Question 262
Single answerYou are working as a data engineer for a retail company that collects customer purchase data in real-time. The data is stored in an Amazon DynamoDB table. Your team needs to query this data frequently by customer ID and order date to generate purchase history reports. How should you design the DynamoDB table to optimize performance for these queries?
- A
Use a composite primary key with CustomerID as the partition key and OrderDate as the sort key.
- B
Use CustomerID as the partition key and create a Global Secondary Index (GSI) on OrderDate.
- C
Use OrderDate as the partition key and CustomerID as the sort key.
- D
Create one table for each customer, using OrderDate as the primary key.
Show answer and explanation
Correct answer: A
Explanation
In DynamoDB, it is important to design the table schema based on the anticipated access patterns. For querying by CustomerID and OrderDate, a composite primary key with CustomerID as the partition key and OrderDate as the sort key ensures efficient lookups and scalability. This approach avoids the need for additional indexes or complex table structures, making it the most suitable choice for the given scenario.
- A. Correct.
This is the correct answer because using a composite primary key with CustomerID as the partition key and OrderDate as the sort key allows efficient querying by both fields. This design aligns with the access pattern for querying data by customer ID and order date.
- B. Incorrect.
This is incorrect because while a GSI can help for different access patterns, it is unnecessary here since the primary access pattern is already supported by the composite primary key.
- C. Incorrect.
This is incorrect because using OrderDate as the partition key would distribute data poorly and make querying by CustomerID inefficient.
- D. Incorrect.
This is incorrect because creating separate tables for each customer is not scalable and would complicate the architecture unnecessarily.