Sparse index pattern in DynamoDB - Step-by-Step Execution

Concept Flow - Sparse index pattern

Create main table with items

↓

Add GSI with attribute only on some items

↓

Insert items with and without GSI attribute

↓

Query GSI returns only items with attribute

↓

Result: Sparse index filters items

The sparse index pattern uses a secondary index that only includes items having a specific attribute, so queries on that index return only those items.

Execution Sample

DynamoDB

CREATE TABLE Products (
  ProductID STRING HASH KEY,
  Category STRING,
  Price NUMBER,
  OnSale BOOLEAN
);

CREATE GLOBAL SECONDARY INDEX OnSaleIndex ON Products (OnSale);

Creates a table and a sparse GSI that indexes only items where OnSale is true.

Execution Table

Step	Action	Item Inserted	GSI Attribute Present?	GSI Index Updated	Query OnSaleIndex Result
1	Insert item	{"ProductID": "P1", "Category": "Books", "Price": 10, "OnSale": true}	Yes	Yes	P1 included
2	Insert item	{"ProductID": "P2", "Category": "Books", "Price": 15}	No	No	P1 only
3	Insert item	{"ProductID": "P3", "Category": "Electronics", "Price": 100, "OnSale": true}	Yes	Yes	P1, P3 included
4	Query OnSaleIndex	N/A	N/A	N/A	Returns items P1 and P3 only
5	Insert item	{"ProductID": "P4", "Category": "Electronics", "Price": 200}	No	No	P1, P3 only
6	Query OnSaleIndex	N/A	N/A	N/A	Still returns P1 and P3 only

💡 Query on sparse index returns only items with OnSale attribute true; items without attribute are excluded.

Variable Tracker

Variable	Start	After 1	After 2	After 3	After 4	After 5	Final
Items in Table	[]	[P1]	[P1, P2]	[P1, P2, P3]	[P1, P2, P3]	[P1, P2, P3, P4]	[P1, P2, P3, P4]
Items in GSI	[]	[P1]	[P1]	[P1, P3]	[P1, P3]	[P1, P3]	[P1, P3]
Query Result	[]	[P1]	[P1]	[P1, P3]	[P1, P3]	[P1, P3]	[P1, P3]

Key Moments - 2 Insights

Why doesn't item P2 appear in the GSI or query results?

Because P2 does not have the OnSale attribute set, it is excluded from the sparse index as shown in execution_table rows 2 and 4.

What happens if an item does not have the attribute used for the sparse index?

The item is not included in the GSI, so queries on that index will not return it, as seen with items P2 and P4 in the execution_table.

Visual Quiz - 3 Questions

Look at the execution_table, which items are returned by the OnSaleIndex query after step 3?

A Only P3

B P1 and P3

C P1, P2, and P3

D P2 and P4

💡 Hint

Check the 'Query OnSaleIndex Result' column at step 4 in the execution_table.

At which step is item P4 inserted and does it appear in the GSI?

A Step 5, Yes

B Step 6, Yes

C Step 5, No

D Step 6, No

💡 Hint

Look at the 'Item Inserted' and 'GSI Index Updated' columns at step 5 in the execution_table.

If the OnSale attribute was added to P2 with value true after insertion, what would change in the variable_tracker?

A P2 would appear in Items in GSI and Query Result after update

B No change, P2 stays excluded

C P2 would be removed from Items in Table

D P2 would appear only in Query Result but not in GSI

💡 Hint

Sparse index includes items with the attribute; see how Items in GSI and Query Result track items.

Concept Snapshot

Sparse index pattern in DynamoDB:
- Create a GSI on an attribute present only on some items.
- Only items with that attribute appear in the GSI.
- Queries on the GSI return only those items.
- Useful to filter items efficiently without scanning the whole table.
- Items missing the attribute are excluded from the index.

Full Transcript

The sparse index pattern in DynamoDB uses a global secondary index (GSI) that only includes items having a specific attribute. When you create a GSI on an attribute that only some items have, only those items appear in the index. For example, if you create a GSI on an 'OnSale' attribute, only items where OnSale is true will be indexed. When you query this GSI, you get only those items, effectively filtering the data. Items without the attribute do not appear in the GSI or query results. This pattern helps efficiently query subsets of data without scanning the entire table.