Add a second access pattern to a key-value store

WHAT THIS TESTS This is a NoSQL data-modeling problem. The interviewer wants to see that you know access patterns must be designed in advance and that querying on a non-key attribute requires a secondary structure, not a scan.

A GOOD ANSWER COVERS A key-value store can only look up efficiently by its key, here UserID. Querying by EmailAddress with a Scan plus filter reads every item and discards non-matches, which is slow and bills you for all the data read. The right answer is a global secondary index whose partition key is EmailAddress. DynamoDB maintains the GSI automatically: every base-table write is propagated to the index, and lookups by email become a targeted Query that reads only matching items. You project only the attributes the new feature needs to keep the index small. Discuss costs honestly: the GSI roughly duplicates indexed and projected data so storage grows, each base write now also consumes write capacity on the index, and the GSI is eventually consistent, so a just-written user may not appear immediately.

COMMON WRONG ANSWERS Proposing a full Scan with a FilterExpression, which still reads the entire table. Assuming a GSI is free or strongly consistent. Projecting all attributes when only a few are needed, inflating cost. Ignoring write amplification on the base table.

LIKELY FOLLOW-UPS When would a local secondary index fit instead, and why must it share the partition key? How do you handle a GSI hot partition if many users share a domain? What if email must be unique? How does projection type affect read cost?

ONE CONCRETE EXAMPLE A users table is keyed on UserID. Login by email requires finding a user from their address. You create a GSI named email-index with partition key EmailAddress projecting only UserID and displayName. Login now issues a Query on email-index returning the single matching item in milliseconds. You accept the added storage, the extra write unit per user write, and the small eventual-consistency window after sign-up.