Index projection types (ALL, KEYS_ONLY, INCLUDE) in DynamoDB - Step-by-Step Execution

Concept Flow - Index projection types (ALL, KEYS_ONLY, INCLUDE)

Start: Query on GSI/LSI

↓

Check Projection Type

↓

ALL

↓

Return all attributes

↓

Result Set

When querying a secondary index, DynamoDB checks the projection type to decide which attributes to return: all attributes, only keys, or keys plus some included attributes.

Execution Sample

DynamoDB

Query GSI with ProjectionType = ALL
Query GSI with ProjectionType = KEYS_ONLY
Query GSI with ProjectionType = INCLUDE (attr1, attr2)

Shows how DynamoDB returns different sets of attributes based on the index's projection type during a query.

Execution Table

Step	Projection Type	Attributes Requested	Attributes Returned	Notes
1	ALL	PartitionKey, SortKey, AttrA, AttrB, AttrC	PartitionKey, SortKey, AttrA, AttrB, AttrC	Returns all attributes from the base table
2	KEYS_ONLY	PartitionKey, SortKey, AttrA, AttrB, AttrC	PartitionKey, SortKey	Returns only the keys of the index
3	INCLUDE	PartitionKey, SortKey, AttrA, AttrB, AttrC	PartitionKey, SortKey, AttrA, AttrB	Returns keys plus included attributes AttrA and AttrB
4	INCLUDE	PartitionKey, SortKey, AttrA, AttrB, AttrC	PartitionKey, SortKey, AttrA	If only AttrA included, returns keys plus AttrA
5	KEYS_ONLY	PartitionKey, SortKey	PartitionKey, SortKey	Query only keys, no other attributes
6	-	-	-	Query ends after returning projected attributes

💡 Query completes after returning attributes based on the projection type.

Variable Tracker

Variable	Start	After Step 1	After Step 2	After Step 3	Final
Attributes Returned	None	All attributes	Only keys	Keys + included attrs	Keys + included attrs or keys only

Key Moments - 3 Insights

Why does a KEYS_ONLY projection return fewer attributes than ALL?

Because KEYS_ONLY projection returns only the partition and sort keys of the index, not the other attributes, as shown in execution_table row 2.

What happens if you query an index with INCLUDE projection but request attributes not included?

Only the keys and the explicitly included attributes are returned; other attributes are not available from the index, as shown in execution_table row 3.

Does ALL projection return attributes not present in the base table?

No, ALL projection returns all attributes from the base table for the items in the index, as shown in execution_table row 1.

Visual Quiz - 3 Questions

Look at the execution table, what attributes are returned at step 2 with KEYS_ONLY projection?

A Only partition and sort keys

B All attributes including keys

C Keys plus included attributes

D No attributes

💡 Hint

Refer to execution_table row 2 under 'Attributes Returned'

At which step does the query return keys plus some included attributes?

A Step 1

B Step 3

C Step 2

D Step 5

💡 Hint

Check execution_table rows for 'INCLUDE' projection type

If you want to get all attributes from the base table via an index, which projection type should you use?

A KEYS_ONLY

B INCLUDE

C ALL

D None

💡 Hint

See execution_table row 1 for ALL projection

Concept Snapshot

Index Projection Types in DynamoDB:
- ALL: Returns all attributes from the base table.
- KEYS_ONLY: Returns only the index keys (partition and sort keys).
- INCLUDE: Returns keys plus specified non-key attributes.
Choose projection type based on query needs and storage cost.

Full Transcript

When you query a DynamoDB secondary index, the database decides which attributes to return based on the index's projection type. There are three types: ALL returns every attribute from the base table, KEYS_ONLY returns only the partition and sort keys, and INCLUDE returns the keys plus some extra attributes you specify. This choice affects what data you get back and how much storage the index uses. The execution table shows step-by-step what attributes are returned for each projection type during queries.

Practice

1. Which DynamoDB index projection type copies all attributes from the base table to the index?

easy

A. ALL

B. KEYS_ONLY

C. INCLUDE

D. PARTIAL

Solution

1. Step 1: Understand projection types

   ALL copies every attribute from the base table to the index.

2. Step 2: Compare with other types

   KEYS_ONLY copies only keys; INCLUDE copies keys plus selected attributes.

3. Final Answer:

   ALL -> Option A

4. Quick Check:

   ALL = copies all attributes [OK]

Hint: ALL means every attribute is copied to the index [OK]

Common Mistakes:

• Confusing INCLUDE with ALL
• Thinking KEYS_ONLY copies all attributes
• Assuming PARTIAL is a valid projection type

2. Which of the following is the correct way to specify a KEYS_ONLY projection in a DynamoDB index definition?

easy

A. "ProjectionType": "ALL"

B. "ProjectionType": "KEYS_ONLY"

C. "ProjectionType": "INCLUDE"

D. "ProjectionType": "KEYS"

Solution

1. Step 1: Recall correct syntax for KEYS_ONLY

   The projection type must be exactly "KEYS_ONLY" in quotes.

2. Step 2: Eliminate incorrect options

   ALL and INCLUDE are valid but not KEYS_ONLY; "KEYS" is invalid syntax.

3. Final Answer:

   "ProjectionType": "KEYS_ONLY" -> Option B

4. Quick Check:

   KEYS_ONLY syntax = "KEYS_ONLY" [OK]

Hint: Use exact string "KEYS_ONLY" for keys-only projection [OK]

Common Mistakes:

• Using "KEYS" instead of "KEYS_ONLY"
• Confusing INCLUDE with KEYS_ONLY syntax
• Omitting quotes around projection type

3. Given a DynamoDB table with attributes id, name, age, and city, and a Global Secondary Index (GSI) with projection type INCLUDE and non-key attributes age and city, which attributes will be available in the index?

medium

A. id, age, and city

B. id, name, age, and city

C. Only age and city

D. Only id and name

Solution

1. Step 1: Identify keys and included attributes

   Index always includes keys; INCLUDE adds specified non-key attributes.

2. Step 2: Determine which attributes are keys

   Assuming id is the partition key, name is not a key.

3. Step 3: Combine keys and included attributes

   Index has id (key), plus age and city (included).

4. Final Answer:

   id, age, and city -> Option A

5. Quick Check:

   INCLUDE = keys + chosen attributes [OK]

Hint: INCLUDE = keys plus only specified extra attributes [OK]

Common Mistakes:

• Including non-key attributes not specified in INCLUDE
• Assuming all attributes are included
• Confusing keys with non-key attributes

4. You defined a DynamoDB index with projection type INCLUDE but forgot to specify any non-key attributes. What will happen when you try to create this index?

medium

A. The index will include keys plus all attributes by default.

B. The index will include only the keys, ignoring INCLUDE.

C. The index will include all attributes from the base table.

D. The index creation will fail with a validation error.

Solution

1. Step 1: Understand INCLUDE projection requirements

   INCLUDE requires specifying at least one non-key attribute.

2. Step 2: Check behavior when no attributes specified

   Omitting non-key attributes causes a validation error during index creation.

3. Final Answer:

   The index creation will fail with a validation error. -> Option D

4. Quick Check:

   INCLUDE needs attributes; missing causes error [OK]

Hint: INCLUDE must list attributes; empty list causes error [OK]

Common Mistakes:

• Assuming INCLUDE defaults to ALL attributes
• Thinking index silently defaults to KEYS_ONLY
• Ignoring validation errors on index creation

5. You want to optimize a DynamoDB Global Secondary Index (GSI) to reduce storage costs but still need to query by email and retrieve phone and address attributes quickly. Which projection type should you choose?

hard

A. ALL

B. KEYS_ONLY

C. INCLUDE with phone and address

D. INCLUDE with email only

Solution

1. Step 1: Understand storage cost vs attribute needs

   ALL copies all attributes but costs more storage; KEYS_ONLY copies only keys.

2. Step 2: Identify needed attributes for queries

   You need email as key and phone, address for quick retrieval.

3. Step 3: Choose INCLUDE projection with needed attributes

   INCLUDE lets you copy keys plus only phone and address, saving storage.

4. Final Answer:

   INCLUDE with phone and address -> Option C

5. Quick Check:

   INCLUDE = keys + needed attributes saves cost [OK]

Hint: Use INCLUDE to add only needed attributes, saving space [OK]

Common Mistakes:

• Choosing ALL and increasing storage unnecessarily
• Using KEYS_ONLY and missing needed attributes
• Including only email but not phone or address