How to Efficiently Filter DynamoDB Records by Non-Key Attribute?

Why the Answer Is Correct

Creating a Global Secondary Index (GSI) with OrderSource as the partition key enables direct, highly efficient Query operations. Since the table contains over 100,000 records, a partition-based lookup guarantees logarithmic time complexity and minimal read capacity consumption. This approach isolates the target items to a single partition, delivering consistent sub-millisecond latency that significantly enhances user experience. The AWS documentation explicitly recommends this pattern for accessing data through alternative access paths.

Why the Other Options Are Wrong

Option A triggers a full table Scan, which reads every item regardless of the filter condition, causing excessive latency and high costs. Option B proposes a Local Secondary Index (LSI), but LSIs must share the base table’s partition key (OrderID), making it impossible to use OrderSource as the primary lookup attribute. Option C places OrderSource as a sort key, but a Query operation still requires a valid partition key value to locate data, rendering it ineffective for direct attribute-based filtering. These alternatives fail to provide the deterministic routing necessary for scalable application performance.

Community Comment Notes

Candidate discussions highlight confusion regarding index key placement, with some users incorrectly advocating for sort-key-only configurations due to perceived uniqueness benefits. As noted in the top-voted comments, candidates who selected option C misunderstood how DynamoDB resolves secondary index lookups without a partition key qualifier. Reviewers consistently emphasize that exam scenarios prioritize predictable read patterns over theoretical indexing flexibility. Aligning your mental model with AWS-recommended access patterns will prevent similar pitfalls.