Why secondary indexes enable flexible queries in DynamoDB - Challenge Your Understanding

Challenge - 5 Problems

🧠 Conceptual

intermediate

How do secondary indexes improve query flexibility?

In DynamoDB, what is the main reason secondary indexes allow more flexible queries compared to querying only the primary key?

A They encrypt the data to improve security during queries.

B They automatically replicate the entire table data to another region for faster access.

C They compress the data to reduce storage costs.

D They allow queries on attributes other than the primary key without scanning the entire table.

💡 Hint

Think about how you can search by different columns without reading everything.

Secondary indexes let you query the table using different keys than the primary key, so you don't have to scan the whole table to find items.

❓ query_result

intermediate

Query result using a Global Secondary Index (GSI)

Given a DynamoDB table with a Global Secondary Index on the attribute 'Category', what will this query return?

Query on GSI where Category = 'Books'

A All items in the table regardless of Category.

B All items where the Category attribute equals 'Books'.

C All items where the primary key equals 'Books'.

D An error because GSIs cannot be queried.

💡 Hint

Remember what a GSI lets you do with attributes.

A GSI allows querying items based on the indexed attribute, here 'Category'. So the query returns all items with Category 'Books'.

📝 Syntax

advanced

Identify the correct syntax for querying a Local Secondary Index (LSI)

Which of the following DynamoDB query commands correctly queries a Local Secondary Index named 'DateIndex' on the attribute 'Date'?

A query({ TableName: 'Orders', IndexName: 'DateIndex', KeyConditionExpression: 'OrderId = :id and Date = :date', ExpressionAttributeValues: { ':id': '123', ':date': '2024-01-01' } })

B query({ TableName: 'Orders', KeyConditionExpression: 'DateIndex = :date', ExpressionAttributeValues: { ':date': '2024-01-01' } })

C query({ TableName: 'Orders', IndexName: 'DateIndex', KeyConditionExpression: 'Date = :date', ExpressionAttributeValues: { ':date': '2024-01-01' } })

D query({ TableName: 'Orders', IndexName: 'DateIndex', KeyConditionExpression: 'OrderId = :id', ExpressionAttributeValues: { ':id': '123' } })

💡 Hint

Remember that LSIs share the same partition key as the main table but have a different sort key.

LSIs require the partition key and the indexed sort key in the KeyConditionExpression. Option A correctly includes both.

❓ optimization

advanced

Choosing between Global and Local Secondary Indexes for flexible queries

You want to query a DynamoDB table by a non-primary key attribute that changes frequently and is not part of the primary key. Which index type should you use for best flexibility and why?

A Neither, because secondary indexes cannot be used for frequently changing attributes.

B Local Secondary Index, because it shares the partition key and is better for frequently changing attributes.

C Global Secondary Index, because it allows a different partition key and sort key than the main table and supports frequent attribute changes.

D Use a scan operation instead of any index for flexibility.

💡 Hint

Think about which index type allows different partition keys and supports attribute updates.

Global Secondary Indexes allow different partition and sort keys and can index attributes that change frequently, making them more flexible for such queries.

🔧 Debug

expert

Why does this query on a secondary index fail?

You run this DynamoDB query on a Global Secondary Index named 'StatusIndex' but get an error:

query({ TableName: 'Tasks', IndexName: 'StatusIndex', KeyConditionExpression: 'Status = :s', ExpressionAttributeValues: { ':s': 'Pending' } })

What is the most likely cause of the error?

A The KeyConditionExpression must include the partition key of the GSI, not just any attribute.

B The ExpressionAttributeValues are missing a required attribute for the primary key.

C The IndexName is misspelled and does not exist.

D DynamoDB does not support querying GSIs.

💡 Hint

Remember what must be included in the KeyConditionExpression when querying an index.

When querying a GSI, the KeyConditionExpression must include the GSI's partition key attribute. Using just 'Status' may be incorrect if 'Status' is not the partition key of the GSI.

Practice

1. What is the main benefit of using secondary indexes in DynamoDB?

easy

A. They allow querying data using different keys without changing the main table

B. They automatically backup your data

C. They increase the storage capacity of the table

D. They encrypt data for security

Solution

1. Step 1: Understand the role of secondary indexes

   Secondary indexes let you query the table using keys other than the primary key.

2. Step 2: Compare other options

   Options A, B, and C describe features unrelated to secondary indexes.

3. Final Answer:

   They allow querying data using different keys without changing the main table -> Option A

4. Quick Check:

   Secondary indexes = flexible queries [OK]

Hint: Secondary indexes = new query keys without table changes [OK]

Common Mistakes:

• Confusing secondary indexes with backups
• Thinking indexes increase storage size
• Assuming indexes encrypt data

2. Which of the following is the correct syntax to create a Global Secondary Index (GSI) in a DynamoDB table definition?

easy

A. {"IndexName": "GSI1", "KeySchema": [{"AttributeName": "Category", "KeyType": "RANGE"}], "Projection": {"ProjectionType": "KEYS_ONLY"}}

B. {"IndexName": "GSI1", "KeySchema": [{"AttributeName": "Category", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}}

C. {"IndexName": "GSI1", "KeySchema": [{"AttributeName": "Category", "KeyType": "PRIMARY"}], "Projection": {"ProjectionType": "ALL"}}

D. {"IndexName": "GSI1", "KeySchema": [{"AttributeName": "Category", "KeyType": "SORT"}], "Projection": {"ProjectionType": "INCLUDE"}}

Solution

1. Step 1: Identify correct KeyType for GSI partition key

   For a GSI, the partition key uses KeyType 'HASH'. {"IndexName": "GSI1", "KeySchema": [{"AttributeName": "Category", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}} uses 'HASH'.

2. Step 2: Check Projection type

   {"IndexName": "GSI1", "KeySchema": [{"AttributeName": "Category", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}} uses 'ALL', which is valid. Other options use invalid KeyTypes like 'PRIMARY' or 'SORT'.

3. Final Answer:

   {"IndexName": "GSI1", "KeySchema": [{"AttributeName": "Category", "KeyType": "HASH"}], "Projection": {"ProjectionType": "ALL"}} -> Option B

4. Quick Check:

   GSI partition key = HASH [OK]

Hint: GSI partition key must be 'HASH' not 'RANGE' or others [OK]

Common Mistakes:

• Using 'PRIMARY' or 'SORT' as KeyType
• Confusing partition key with sort key
• Wrong ProjectionType values

3. Given a DynamoDB table with a Global Secondary Index (GSI) on attribute Category, what will the following query return?

Query({
  TableName: 'Products',
  IndexName: 'CategoryIndex',
  KeyConditionExpression: 'Category = :cat',
  ExpressionAttributeValues: { ':cat': 'Books' }
})

medium

A. All items where the primary key equals 'Books'

B. All items in the table regardless of Category

C. An error because the query does not specify the primary key

D. All items where the Category attribute equals 'Books' using the GSI

Solution

1. Step 1: Understand the use of IndexName in the query

   Specifying IndexName means the query uses the GSI on 'Category'.

2. Step 2: Analyze KeyConditionExpression

   The query filters items where 'Category' equals 'Books' using the GSI.

3. Final Answer:

   All items where the Category attribute equals 'Books' using the GSI -> Option D

4. Quick Check:

   Query with GSI filters by Category = 'Books' [OK]

Hint: Query with IndexName uses that index's keys [OK]

Common Mistakes:

• Assuming query filters primary key without IndexName
• Expecting error without primary key in main table
• Thinking query returns all items

4. You tried to query a DynamoDB table using a secondary index but got an error: ValidationException: Query condition missed key schema element. What is the most likely cause?

medium

A. You forgot to create the secondary index before querying

B. You used the primary key instead of the secondary index name

C. You did not specify the partition key attribute of the secondary index in the query

D. You used a Scan operation instead of Query

Solution

1. Step 1: Understand the error message

   The error means the query is missing a required key attribute from the index's key schema.

2. Step 2: Identify the missing key

   When querying a secondary index, you must specify its partition key attribute in the KeyConditionExpression.

3. Final Answer:

   You did not specify the partition key attribute of the secondary index in the query -> Option C

4. Quick Check:

   Missing index partition key causes ValidationException [OK]

Hint: Always include index partition key in query condition [OK]

Common Mistakes:

• Using primary key attributes instead of index keys
• Querying before index creation
• Confusing Scan with Query operation

5. You have a DynamoDB table with a primary key on UserId and a Global Secondary Index (GSI) on Email. You want to efficiently find users by their email address. Which approach best uses the GSI to achieve this?

hard

A. Query the table using the GSI with KeyConditionExpression 'Email = :emailValue'

B. Scan the entire table filtering items where Email equals the value

C. Query the table using the primary key UserId with the email value

D. Create a new table with Email as the primary key

Solution

1. Step 1: Identify the best way to find users by Email

   The GSI on Email allows querying by Email efficiently without scanning.

2. Step 2: Compare options

   Query the table using the GSI with KeyConditionExpression 'Email = :emailValue' uses the GSI query correctly. Scan the entire table filtering items where Email equals the value scans, which is slow. Query the table using the primary key UserId with the email value uses UserId, which won't help find by Email. Create a new table with Email as the primary key is unnecessary duplication.

3. Final Answer:

   Query the table using the GSI with KeyConditionExpression 'Email = :emailValue' -> Option A

4. Quick Check:

   Use GSI query for efficient Email lookup [OK]

Hint: Use GSI query, not scan or primary key, for alternate keys [OK]

Common Mistakes:

• Using Scan instead of Query on GSI
• Querying primary key with wrong attribute
• Creating redundant tables instead of indexes