Create and Query DynamoDB Tables with Composite Keys – SAP-C02 Study Guide

Purpose of This Lesson

This hands-on exercise demonstrates how to create a DynamoDB table, define a composite primary key, load sample items with the AWS CLI, and retrieve data using scans and queries.

The important design lesson is that DynamoDB key selection determines how items are uniquely identified and how efficiently applications can access them.

Key Concepts

Creating a DynamoDB table

A DynamoDB table requires:

• A table name.
• A primary key definition.
• A capacity mode and related table settings.

A table can use either:

1. A simple primary key, consisting only of a partition key.
2. A composite primary key, consisting of a partition key and sort key.

The key attribute types supported by DynamoDB are:

• String
• Number
• Binary

The key type must be consistent with the table’s key schema. For example, a string partition key cannot be queried as a number.

Simple primary key

With only a partition key, each item must have a unique partition-key value across the table.

For example, a table keyed only by clientId cannot store multiple items with the same client ID.

Composite primary key

A composite primary key contains:

• Partition key: Determines the logical partition to which an item belongs.
• Sort key: Orders and differentiates items that share the same partition-key value.

The combination of partition key and sort key must be unique. This allows multiple related items to share a partition key while retaining unique identities.

For example:

Partition key	Sort key	Valid?
client-123	2026-01-01T10:00:00Z	Yes
client-123	2026-01-02T10:00:00Z	Yes
client-456	2026-01-01T10:00:00Z	Yes
client-123	2026-01-01T10:00:00Z	Duplicate key; not allowed

A common access pattern is to use a customer, tenant, account, or device identifier as the partition key and a timestamp or entity identifier as the sort key.

Flexible item structure

DynamoDB is schemaless at the item-attribute level. Items in the same table do not need to contain the same non-key attributes. One item may contain attributes that another item does not have.

However, this flexibility does not apply to the primary key schema:

• Every item must contain the required key attributes.
• The key attributes must use the data types defined by the table.
• Attribute values should still be modeled consistently for predictable application behavior.

DynamoDB supports different data types, including strings, numbers, booleans, lists, maps, sets, and binary values.

Capacity modes

DynamoDB provides two principal capacity modes:

• Provisioned capacity: You specify read capacity units and write capacity units. This can be appropriate when traffic is predictable and capacity can be planned.
• On-demand capacity: DynamoDB automatically accommodates traffic without requiring capacity-unit planning. This is useful for variable or difficult-to-predict workloads, although the per-request pricing model differs from provisioned capacity.

The choice should be based on workload predictability, scaling behavior, cost requirements, and operational simplicity.

The console may also expose table classes such as Standard and Standard-IA. Standard-IA can reduce storage costs for data accessed infrequently, but its request pricing and workload suitability must be evaluated before selecting it.

Loading items with the AWS CLI

A batch write can load multiple items into a DynamoDB table. A typical command uses a JSON request file:

aws dynamodb batch-write-item \
  --request-items file://eto-items.json

The request file maps a table name to a list of PutRequest or DeleteRequest operations. For example:

{
  "ETO": [
    {
      "PutRequest": {
        "Item": {
          "clientId": {"S": "client-123"},
          "created": {"S": "2026-01-01T10:00:00Z"}
        }
      }
    }
  ]
}

A successful batch operation can still return UnprocessedItems. Applications must retry those items, normally with exponential backoff, until the response contains no unprocessed requests or an appropriate retry policy is reached.

Scan versus query

A scan reads every item in a table or index and then applies any filter expression. It is useful for small administrative exercises, but it is generally inefficient for production access patterns because it examines the complete data set.

A query uses the key schema to retrieve items efficiently:

• The partition key is required.
• A sort-key condition can optionally narrow the result set.
• Queries can retrieve all items for one partition key or a range of sort-key values.

Example query using the AWS CLI:

aws dynamodb query \
  --table-name ETO \
  --key-condition-expression "clientId = :client" \
  --expression-attribute-values '{":client":{"S":"harold@example.org"}}'

A query is normally the correct operation when the required data can be identified through the table’s primary key or an appropriate secondary index.

Global and secondary indexes

DynamoDB supports additional access patterns through indexes:

• Local secondary index (LSI): Uses the same partition key as the base table but a different sort key. It must be created when the table is created.
• Global secondary index (GSI): Can use a different partition key and sort key. It can be created or modified after table creation, subject to service behavior and operational considerations.

Indexes consume storage and capacity and should be designed around known query patterns rather than added indiscriminately.

A global table is different from a global secondary index. A global table replicates DynamoDB data across AWS Regions for multi-Region availability and local access. It is a replication architecture, not merely an alternate query index.

Exam-Relevant Takeaways

• Choose a partition key that supports the application’s primary access patterns and distributes traffic effectively.
• Use a sort key when multiple related items must share a partition-key value.
• The composite key, not either component individually, identifies an item uniquely.
• A DynamoDB query requires an equality condition on the partition key; sort-key conditions are optional.
• A scan reads the table broadly and can consume substantial read capacity. Avoid scans for routine application lookups.
• DynamoDB is schemaless for non-key attributes, but key attributes and their types are defined by the table schema.
• BatchWriteItem supports bulk puts and deletes, but callers must handle UnprocessedItems.
• Provisioned capacity is suitable for predictable demand; on-demand capacity is simpler for variable demand and removes manual capacity planning.
• A GSI supports a different key schema from the base table; an LSI retains the base table’s partition key.
• A global table provides cross-Region replication and is not the same feature as a GSI.

Architecture Decision Guide

Requirement	Suitable DynamoDB design	Important consideration
One item per customer or device	Simple partition key	The partition-key value must be unique
Multiple events or records per customer	Composite key with customer ID as partition key and timestamp or ID as sort key	Choose a sort-key format that supports range queries and ordering
Retrieve one customer’s records	Query on the partition key	More efficient than scanning the table
Retrieve items by a different business attribute	GSI with an appropriate key schema	Indexes add storage and capacity cost
Variable or unpredictable traffic	On-demand capacity	Review request pricing and workload limits
Stable, forecastable traffic	Provisioned capacity	Capacity must be monitored and adjusted as demand changes
Occasional administrative inspection of all data	Scan	Paginate results and avoid using it as a normal application access pattern
Multi-Region DynamoDB access and replication	Global tables	Design for replication behavior, conflict handling, and Regional failure scenarios

Common Exam Traps

• Assuming a sort key is always required: A table can use a simple partition key when each item has a unique partition-key value.
• Treating the partition key as globally unique in a composite-key table: Multiple items can share the same partition key when their sort keys differ.
• Using Scan when a Query is possible: A scan can read every item and is usually the wrong choice for a targeted lookup.
• Assuming a filter makes a scan efficient: A filter expression is applied after items are read; it does not make the scan equivalent to a key-based query.
• Forgetting that a query needs the partition key: A query cannot arbitrarily search any attribute unless an index with a suitable key schema is used.
• Confusing GSIs and LSIs: An LSI shares the base table’s partition key; a GSI can define a different partition key.
• Ignoring unprocessed batch-write items: A successful API response does not necessarily mean every requested item was written if UnprocessedItems are returned.
• Assuming schemaless means keyless: Non-key attributes are flexible, but every item must conform to the table’s key requirements.
• Treating a global table as an index: Global tables replicate data between Regions; they do not simply provide another lookup key.
• Assuming default capacity settings are universal: Console defaults and capacity choices can vary. Select capacity mode based on the stated workload and requirements.

Real-World Engineer Notes

• Design DynamoDB from access patterns outward. List the queries the application must perform before choosing the primary key and indexes.
• Timestamp sort keys work well for time-ordered records. Use an unambiguous format such as ISO 8601 when lexical ordering should match chronological ordering.
• A high-volume workload concentrated on one partition-key value can create a hot partition. Consider whether the access pattern needs write sharding or a different key model.
• Keep batch-write retry logic bounded and use exponential backoff. Repeated immediate retries can increase throttling.
• Use pagination for scans, queries, and batch operations. DynamoDB responses may be incomplete even when the operation succeeds.
• Avoid exposing arbitrary scan functionality through a high-traffic API. It can create unpredictable latency and read consumption.
• Secondary indexes should correspond to real access patterns. Every additional index increases storage, write, and operational considerations.
• Retaining a table for later global-table exercises reflects an important deployment distinction: creating a table is separate from configuring multi-Region replication and validating the resulting consistency and failure behavior.

Quick Reference Summary

• Simple primary key: Partition key only; value must be unique.
• Composite primary key: Partition key plus sort key; the pair must be unique.
• Partition key: Determines item grouping and is required for a query.
• Sort key: Supports ordering and range conditions within a partition.
• Scan: Reads the table or index broadly; expensive at scale.
• Query: Uses a partition key and optionally a sort-key condition.
• GSI: Alternate key schema; can use a different partition key.
• LSI: Alternate sort key with the same partition key as the base table.
• Batch write: Bulk put/delete operation; retry returned UnprocessedItems.
• On-demand capacity: Simplifies capacity management for variable traffic.
• Provisioned capacity: Provides explicit read/write capacity planning for predictable traffic.
• Global table: Multi-Region DynamoDB replication, not an index.

Flashcards

1. Q: What are the two DynamoDB primary-key models?

A: A simple primary key containing only a partition key, and a composite primary key containing a partition key plus a sort key.

1. Q: What must be unique in a table with a composite primary key?

A: The combination of the partition-key value and sort-key value.

1. Q: Can multiple items have the same partition-key value?

A: Yes, when the table has a sort key and each item has a different composite key.

1. Q: What key condition is mandatory for a DynamoDB query?

A: An equality condition on the partition key.

1. Q: Why is a scan usually unsuitable for an application lookup?

A: It examines the table or index broadly and can consume significant read capacity, even if a filter returns few items.

1. Q: What is the main schema flexibility of DynamoDB?

A: Items can have different non-key attributes and attribute sets within the same table.

1. Q: What should a client do when BatchWriteItem returns UnprocessedItems?

A: Retry those items using an appropriate backoff strategy.

1. Q: How does a GSI differ from an LSI?

A: A GSI can use a different partition key, while an LSI must use the base table’s partition key and a different sort key.

1. Q: When is on-demand capacity often appropriate?

A: When traffic is variable, intermittent, or difficult to forecast and operational simplicity is important.

1. Q: What does a global table provide?

A: Multi-Region DynamoDB replication and access, supporting Regional resilience and lower-latency access for distributed users.

Practice Questions

Question 1

An application stores multiple purchases for each customer. The application must retrieve all purchases for one customer and optionally restrict results to a time range. Which key design is most appropriate?

A. Partition key purchaseTimestamp only
B. Partition key customerId and sort key purchaseTimestamp
C. Partition key customerId only, with the timestamp as a non-key attribute
D. A table scan with a filter on customerId

Correct answer: B

Explanation: A composite key groups purchases by customer and allows a query to apply a range condition to the timestamp sort key. A simple partition key would prevent multiple purchases for the same customer.

Question 2

A DynamoDB table contains millions of items. An API needs to return records for a specified customer ID. Which operation should be used when the customer ID is the table’s partition key?

A. Scan with a filter expression
B. Query with a partition-key condition
C. BatchWriteItem
D. A query without specifying the partition key

Correct answer: B

Explanation: Query is designed for key-based retrieval and requires the partition key. A scan reads the table broadly and is inefficient for this access pattern.

Question 3

An import process submits a batch write request. The API call succeeds, but the response contains entries in UnprocessedItems. What should the application do?

A. Assume all items were written because the HTTP request succeeded
B. Delete and recreate the table
C. Retry the unprocessed entries with backoff
D. Run a table scan and rewrite every item

Correct answer: C

Explanation: Batch writes can return unprocessed operations because of throttling or other temporary conditions. The application should retry only the unprocessed requests using controlled backoff.

Question 4

A workload has unpredictable traffic with long idle periods followed by bursts. The team wants to avoid manually calculating read and write capacity. Which capacity mode is generally the best starting point?

A. Provisioned capacity with a fixed low limit
B. On-demand capacity
C. An LSI
D. A global table

Correct answer: B

Explanation: On-demand mode removes the need to provision read and write capacity in advance and is suited to variable or difficult-to-predict workloads. Cost and service limits should still be evaluated.

Question 5

An application must query a DynamoDB table by email, but the base table uses userId as its partition key. Which solution supports this access pattern without scanning the table?

A. Add email as a non-key attribute and use a filter expression
B. Create a GSI with email as its partition key
C. Convert the table to use an LSI with email as its partition key
D. Use a scan and increase the table’s read capacity

Correct answer: B

Explanation: A GSI can provide an alternate key schema with email as the partition key. An LSI must retain the base table’s partition key, and filtering a scan does not provide efficient key-based access.