A company has an ecommerce application. To track product reviews, the company's development team uses an Amazon DynamoDB table. Every record includes…

The primary query requirement is to find reviews for a specific Product ID and sort them by Product Rating. The base table's partition key is Review ID, which cannot be used to efficiently query by Product ID. A Global Secondary Index (GSI) is required to support a query pattern on attributes different from the base table's primary key.

By creating a GSI with Product ID as the partition key, you can efficiently query for all reviews associated with a single product. Adding Product Rating as the sort key allows DynamoDB to store and retrieve the items for that product pre-sorted by rating. This combination directly and most efficiently addresses the query "find the 10 reviews with the highest rating for a given product."

B. This GSI would sort results by Review ID, not Product Rating, forcing the application to fetch all reviews for a product and perform inefficient client-side sorting.

C. A Local Secondary Index (LSI) is invalid here because it must share the same partition key as the base table (Review ID), but this option proposes Product ID.

D. This LSI uses the correct partition key (Review ID) for an LSI but does not help the query, which needs to filter by Product ID, not Review ID.

1. AWS DynamoDB Developer Guide

"Core components of Amazon DynamoDB": Under the "Primary Key" section

it explains that a composite primary key (partition key and sort key) allows for efficient querying and sorting. The query pattern in the question requires filtering on one attribute (Product ID) and sorting on another (Product Rating)

which maps directly to a composite key structure.

2. AWS DynamoDB Developer Guide

"Improving data access with secondary indexes": This section states

"If you want to query a table using an attribute that is not part of the table's primary key

you can create a global secondary index." This justifies the need for a GSI since the query is on Product ID

not the base table's key (Review ID).

3. AWS DynamoDB Developer Guide

"Global secondary indexes": This page clarifies that a GSI's key "can be a simple primary key (partition key) or a composite primary key (partition key and sort key)

" and crucially

"The primary key of a global secondary index can be different from the primary key of its base table." This supports using Product ID and Product Rating as the GSI's key.

4. AWS DynamoDB Developer Guide

"Local secondary indexes": This page states

"Every local secondary index must have the same partition key as its base table." This directly invalidates option C

which proposes a different partition key (Product ID) for the LSI than the base table's key (Review ID).