Topic lessonTopic: AWS databases: design, comparison and connectivity
DynamoDB fundamentals: keys, Query and read consistency
DynamoDB is a managed NoSQL database designed around keys and the access patterns an application needs. Follow a customer-order example to distinguish primary keys, Query, filters, indexes and freshness. This is not a direct conversion of a SQL Server table.
Choose keys from required reads and explain the difference between Query candidates, returned items, indexes and consistency.
For readers familiar with tables, rows and primary keys. Work through four fictional orders without an AWS account or live data.
Start with tables, items and keys
An item is one record and an attribute is a value within it. This example uses a partition key and sort key together to identify an item. Flexible non-key attributes do not remove the need to design business data formats and consistent updates.
| Term | Example | Role |
|---|---|---|
| Partition key (PK) | CUSTOMER#101 | Select orders belonging to the same customer |
| Sort key (SK) | 20261001#41 | Identify and order items within the PK, including date ranges |
| Other attributes | status=PAID, points=1200 | Data to return; these alone do not define a Query on this base table |
Decide how the application reads before storing
| Required behavior | Using this key design |
|---|---|
| List customer 101 orders for October | Query PK=CUSTOMER#101 with a date range on SK |
| Fetch order 41 directly | GetItem with the complete PK and SK |
| List PAID orders across all customers by date | Customer-based PKs do not directly support this; evaluate an index or another design |
| Read your order immediately after confirmation | Check the destination and consistency option against the freshness requirement |
Do not assume SQL joins or arbitrary predicates transfer as equivalent operations. If a design duplicates data for reads, it must also maintain those copies during changes. Conversely, DynamoDB does support transactions. Evaluate relationships, update boundaries and reads together.
Example D1: select by customer and date from four orders
These are fictional orders. Points are teaching values, not AWS charges or performance measurements. Dates in SK use a fixed-width string format so they sort by date within the same PK.
| Order | PK | SK | status | points |
|---|---|---|---|---|
| 41 | CUSTOMER#101 | 20261001#41 | PAID | 1200 |
| 42 | CUSTOMER#101 | 20261002#42 | PENDING | 800 |
| 43 | CUSTOMER#101 | 20261003#43 | PAID | 600 |
| 44 | CUSTOMER#202 | 20261001#44 | PAID | 400 |
- Customer 101, Oct 1–3 → Candidates: 41, 42, 43Key-selected scope
- Candidates: 41, 42, 43 → PAID: 41 and 43Filter after reading
- Customer 101, Oct 1–3
- Supply PK and SK conditions.
- Candidates: 41, 42, 43
- 44 belongs to another customer.
- PAID: 41 and 43
- A status filter removes 42.
A Query on this base table specifies one PK value, optionally narrowed by an SK condition. Orders 41, 42 and 43 match D1; filtering for PAID returns 41 and 43. Their points total 1200 + 600 = 1800. Do not include order 44 from another customer.
Filters and pagination do not replace key conditions
| Page | Item evaluated by the key condition | Item returned after PAID filter | Continuation |
|---|---|---|---|
| 1 | Order 42, PENDING | None | A continuation key exists, so keep going |
| 2 | Order 43, PAID | Order 43 | Assume this response still contains a continuation key |
| 3 | No remaining item | None | No continuation key; stop here |
D2 deliberately evaluates one item per page and stipulates that page two still has a continuation key. This is neither the AWS page-size limit nor a guarantee of exactly three pages. When a real response contains LastEvaluatedKey, use it to request the continuation. A filtered empty page alone does not prove that no matching items remain. A continuation key also does not guarantee that another matching item exists.
Design alternative access and freshness separately
A global secondary index (GSI) supports access through keys different from the base table. For PAID orders across customers, evaluate an access design based on status and date. Also inspect concentration on a small number of status values, projected attributes, write overhead and storage cost. A GSI is not an automatic accelerator for every query.
| Destination | Check |
|---|---|
| Base table | Choose default eventual consistency or supported strongly consistent reads according to requirements |
| Local secondary index (LSI) | Supports strong consistency; do not conflate it with a GSI |
| GSI | Eventually consistent; an immediate read need not reflect a just-completed base-table update |
Extend the small example into operational decisions
- List reads, writes and freshness needs for each screen or process.
- Map PK/SK and indexes to those access patterns.
- Plan consistent updates for duplicated attributes or multiple items.
- Estimate skew, item sizes, capacity mode, storage/index/network costs.
- Define IAM and data-level access, recovery and business-result checks.
This example teaches key and result semantics, not measured performance or cost. Before replacing SQL Server, compare existing SQL, data relationships and redesign costs with the RDS comparison lesson.
Check the keys and Query results
Choose an option to see the result and explanation.
Check, then explain in your own words
- Distinguish PK/SK from non-key attributes.
- Explain D1 three candidates versus two returned items.
- Explain why an empty page can require continuation.
- Explain GSI access and eventual consistency.
Check your understanding: Need PAID orders across all customers. Is scanning the base table and filtering a complete design?
No. Inspect volume, frequency, ordering and freshness, then evaluate indexes or another access design. Include concentrated status keys, index propagation and storage/update cost. Being able to scan does not establish that the design meets requirements.
A readable access path must still be assessed against frequency, scale, freshness and update requirements.
Sources and conditions checked
The official references used in this lesson are collected here with their scope, conditions and corresponding sections. Independent expert review and live AWS comparison have not been performed.
Scope and conditionsItems, attributes and primary keys; the example uses a simple base-table PK/SK, not every index key schema.
Used in this lessonStart with tables, items and keys Extend the small example into operational decisions
- AWS: DynamoDB Query APIChecked:
Scope and conditionsKey selection, post-read filtering, evaluated versus returned items and empty pages with continuation.
Used in this lessonDecide how the application reads before storing Example D1: select by customer and date from four orders Filters and pagination do not replace key conditions
Scope and conditionsDistinct scopes of GetItem, Query and Scan, and supported write operations.
Used in this lessonDecide how the application reads before storing
Scope and conditionsAlternative key access and index propagation; account for projection, cost and concentrated access.
Used in this lessonDecide how the application reads before storing Design alternative access and freshness separately Extend the small example into operational decisions
Scope and conditionsDifferences in access and authorization; no promise of unchanged SQL/join migration.
Used in this lessonDecide how the application reads before storing Extend the small example into operational decisions
Scope and conditionsDynamoDB supports transactions; distinguish atomicity from the freshness of a separate read.
Used in this lessonDecide how the application reads before storing Design alternative access and freshness separately
- AWS: DynamoDB Query paginationChecked:
Scope and conditionsContinue until LastEvaluatedKey is empty; a key alone does not guarantee another matching item.
Used in this lessonFilters and pagination do not replace key conditions
- AWS: DynamoDB read consistencyChecked:
Scope and conditionsStrong reads on base tables/LSIs versus eventual GSI reads; do not generalize to all global-table modes.
Used in this lessonDesign alternative access and freshness separately