OnCallReadyStart learning

AWS Lesson 38.10 · AWS V: Data, Resilience & the Edge · 17 min read

DynamoDB: design for the questions you will ask

In plain words

Imagine a library where books are not shelved by title but by the questions readers ask. The partition key chooses the shelf, and the sort key keeps the books on that shelf in order, for example by date. Asking for one shelf, in order, is quick and cheap: that is a Query. Walking past every shelf in the building and picking a few books is a Scan, and you pay for every book you looked at.

If readers also ask a question the shelves were not arranged for, you keep a second catalogue arranged that way. It is updated a moment after the shelves, so it can briefly be behind.

DynamoDB: design for the questions you will ask

A relational database lets you store data first and decide later how to query it. DynamoDB works the other way round: you decide the access patterns first ("all orders of a customer, newest first", "all orders waiting to be shipped") and shape the keys so that each pattern is one cheap, fast request. In exchange you get single-digit-millisecond latency at any scale, no servers, no connections to pool, and no maintenance window. You also get a bill and a throttle if the keys are wrong.

Need to know: a table has a partition key (hashed to pick the partition) and an optional sort key (orders items inside a partition). The two together identify an item. Query reads one partition (cheap); Scan reads the whole table (expensive). A global secondary index (GSI) is a second copy of the data under other keys, for another access pattern, always eventually consistent. Items are written in DynamoDB JSON ({"S": "..."}, {"N": "42"}).

One table, many item types

The lab has a table shop-orders built the way DynamoDB is usually modelled: generic key names PK and SK, and several item types in one table (single-table design):

$ aws dynamodb describe-table --table-name shop-orders --query 'Table.{keys: KeySchema[].[AttributeName,KeyType], billing: BillingModeSummary.BillingMode, items: ItemCount, gsis: GlobalSecondaryIndexes[].IndexName}'
{
    "keys": [
        [
            "PK",
            "HASH"
        ],
        [
            "SK",
            "RANGE"
        ]
    ],
    "billing": "PAY_PER_REQUEST",
    "items": 33,
    "gsis": [
        "by-status"
    ]
}
$ aws dynamodb get-item --table-name shop-orders --key '{"PK": {"S": "CUSTOMER#101"}, "SK": {"S": "PROFILE"}}'
{
    "Item": {
        "PK": {
            "S": "CUSTOMER#101"
        },
        "SK": {
            "S": "PROFILE"
        },
        "name": {
            "S": "Ana Pop"
        },
        "email": {
            "S": "[email protected]"
        },
        "country": {
            "S": "FR"
        }
    }
}

CUSTOMER#101 + PROFILE is the customer's profile. The same partition holds the customer's orders, with sort keys ORDER#<date>#<id>. Only key attributes are declared in the table's schema. Every other attribute is free-form per item, which is why get-item shows types next to values.

Query: one partition, in sort-key order

$ aws dynamodb query --table-name shop-orders --key-condition-expression 'PK = :c AND begins_with(SK, :o)' --expression-attribute-values '{":c": {"S": "CUSTOMER#103"}, ":o": {"S": "ORDER#"}}' --query 'Items[].[SK.S,status.S,total.N]' --output text
ORDER#2026-10-01#5563	PAID	176
ORDER#2026-10-10#5556	REFUNDED	123
$ aws dynamodb query --table-name shop-orders --key-condition-expression 'PK = :c AND SK BETWEEN :a AND :b' --expression-attribute-values '{":c": {"S": "CUSTOMER#103"}, ":a": {"S": "ORDER#2026-10-05"}, ":b": {"S": "ORDER#2026-10-12"}}' --no-scan-index-forward --query 'Items[].SK.S' --output text
ORDER#2026-10-10#5556

A key condition must name the partition key with =, and may narrow the sort key with =, <, <=, >, >=, BETWEEN or begins_with. That is why the sort key starts with the date: "orders of a customer in a date range, newest first" (--no-scan-index-forward) is one query. A key condition without the partition key is refused:

$ aws dynamodb query --table-name shop-orders --key-condition-expression 'SK = :s' --expression-attribute-values '{":s": {"S": "PROFILE"}}'
aws: [ERROR]: An error occurred (ValidationException) when calling the Query operation: Query condition missed key schema element: PK

Filters run after the read, and you pay for the read

$ aws dynamodb query --table-name shop-orders --key-condition-expression 'PK = :c' --filter-expression 'total > :t' --expression-attribute-values '{":c": {"S": "CUSTOMER#104"}, ":t": {"N": "100"}}'
aws: [ERROR]: An error occurred (ValidationException) when calling the Query operation: Invalid FilterExpression: Attribute name is a reserved keyword; reserved keyword: total
$ aws dynamodb query --table-name shop-orders --key-condition-expression 'PK = :c' --filter-expression '#t > :t' --expression-attribute-names '{"#t": "total"}' --expression-attribute-values '{":c": {"S": "CUSTOMER#104"}, ":t": {"N": "100"}}' --return-consumed-capacity TOTAL --query '{count: Count, scanned: ScannedCount, rcu: ConsumedCapacity.CapacityUnits}'
{
    "count": 1,
    "scanned": 4,
    "rcu": 0.5
}
$ aws dynamodb scan --table-name shop-orders --filter-expression '#s = :s' --expression-attribute-names '{"#s": "status"}' --expression-attribute-values '{":s": {"S": "REFUNDED"}}' --return-consumed-capacity TOTAL --query '{count: Count, scanned: ScannedCount, rcu: ConsumedCapacity.CapacityUnits}'
{
    "count": 5,
    "scanned": 33,
    "rcu": 0.5
}

Count is what matched, ScannedCount is what was read and billed. A filter expression saves bandwidth, not capacity. The scan read every item to return a few. On a table of 50 GB that is a slow, expensive way to answer a question, and it competes with production traffic for capacity. The first attempt failed for a reason that catches everyone once: total is one of DynamoDB's reserved words (so are status, name, date, count, items... more than 500 of them), and a reserved word in an expression must go through --expression-attribute-names as a placeholder (#t, #s).

A GSI: the same data under another key

"All refunded orders" is a real access pattern (a finance report), so it gets an index rather than a scan:

$ aws dynamodb describe-table --table-name shop-orders --query 'Table.GlobalSecondaryIndexes[0].{name: IndexName, keys: KeySchema[].[AttributeName,KeyType], projection: Projection.ProjectionType, status: IndexStatus}'
{
    "name": "by-status",
    "keys": [
        [
            "status",
            "HASH"
        ],
        [
            "SK",
            "RANGE"
        ]
    ],
    "projection": "ALL",
    "status": "ACTIVE"
}
$ aws dynamodb query --table-name shop-orders --index-name by-status --key-condition-expression '#s = :s' --expression-attribute-names '{"#s": "status"}' --expression-attribute-values '{":s": {"S": "REFUNDED"}}' --return-consumed-capacity INDEXES --query '{count: Count, keys: Items[].[PK.S,SK.S], rcu: ConsumedCapacity}'
{
    "count": 5,
    "keys": [
        [
            "CUSTOMER#108",
            "ORDER#2026-10-02#5661"
        ],
        [
            "CUSTOMER#107",
            "ORDER#2026-10-04#5647"
        ],
        [
            "CUSTOMER#103",
            "ORDER#2026-10-10#5556"
        ],
        [
            "CUSTOMER#102",
            "ORDER#2026-10-12#5535"
        ],
        [
            "CUSTOMER#101",
            "ORDER#2026-10-14#5521"
        ]
    ],
    "rcu": {
        "TableName": "shop-orders",
        "CapacityUnits": 0.5,
        "Table": {
            "CapacityUnits": 0
        },
        "GlobalSecondaryIndexes": {
            "by-status": {
                "CapacityUnits": 0.5
            }
        }
    }
}
$ aws dynamodb query --table-name shop-orders --index-name by-status --consistent-read --key-condition-expression '#s = :s' --expression-attribute-names '{"#s": "status"}' --expression-attribute-values '{":s": {"S": "PAID"}}'
aws: [ERROR]: An error occurred (ValidationException) when calling the Query operation: Consistent reads are not supported on global secondary indexes

A GSI has its own partition and sort key, its own capacity, and a projection (which attributes are copied: KEYS_ONLY, INCLUDE, ALL). DynamoDB keeps it in sync asynchronously, so a GSI read is never strongly consistent. Items without the index's key attribute (here the PROFILE items, which have no status) are simply not in the index: a sparse index, a cheap way to index only the items you care about. A local secondary index (LSI) keeps the partition key and changes only the sort key, and it can only be created with the table. Up to 20 GSIs per table, and every write to the table also writes to each GSI that projects it, which costs capacity.

Writes: conditions, updates and transactions

$ aws dynamodb put-item --table-name shop-orders --item '{"PK": {"S": "CUSTOMER#101"}, "SK": {"S": "PROFILE"}, "name": {"S": "duplicate"}}' --condition-expression 'attribute_not_exists(PK)'
aws: [ERROR]: An error occurred (ConditionalCheckFailedException) when calling the PutItem operation: The conditional request failed
$ aws dynamodb update-item --table-name shop-orders --key '{"PK": {"S": "CUSTOMER#103"}, "SK": {"S": "PROFILE"}}' --update-expression 'SET #n = :n, loyalty = if_not_exists(loyalty, :z) + :p' --expression-attribute-names '{"#n": "name"}' --expression-attribute-values '{":n": {"S": "Claire Martin-Roux"}, ":z": {"N": "0"}, ":p": {"N": "10"}}' --return-values UPDATED_NEW
{
    "Attributes": {
        "name": {
            "S": "Claire Martin-Roux"
        },
        "loyalty": {
            "N": "10"
        }
    }
}
$ aws dynamodb update-item --table-name shop-orders --key '{"PK": {"S": "CUSTOMER#103"}, "SK": {"S": "PROFILE"}}' --update-expression 'SET loyalty = loyalty - :p' --condition-expression 'loyalty >= :p' --expression-attribute-values '{":p": {"N": "50"}}'
aws: [ERROR]: An error occurred (ConditionalCheckFailedException) when calling the UpdateItem operation: The conditional request failed

put-item replaces an item with the same key unless a condition says otherwise: attribute_not_exists(PK) is "create only". A failed condition is ConditionalCheckFailedException, which is DynamoDB's way of saying "someone else got there first": the basis of optimistic locking (a version attribute in the condition) and of "never let the balance go negative". update-item changes attributes in place, with arithmetic, without reading first. transact-write-items applies up to 100 writes across tables all-or-nothing (cancelled with the reason per item), at twice the capacity cost.

On the exam (SAA-C03 3.3, CLF-C02 3.4): DynamoDB = serverless key-value / document database, single-digit-millisecond latency at any scale, "the schema is the access patterns". "Query by an attribute that is not the key" = a GSI. "Strongly consistent reads" = only on the table and LSIs, never on a GSI. "Avoid scans" = model the access patterns into keys. For microsecond reads in front of it: DAX (DynamoDB Accelerator), an in-memory cache with the same API.

In an interview: "How do you design a DynamoDB table?" - "List the access patterns first, then pick a partition key with many distinct values that spreads the load, and a sort key that serves the range and order queries (often a date or a type prefix). Patterns the keys cannot serve get a GSI, sometimes a sparse one. Use conditions for uniqueness and optimistic locking. Avoid scans and hot keys."

You can now: read a single-table design, query a partition with key conditions and order, tell Query from Scan and what a filter costs, use a GSI for a second access pattern (and why it is eventually consistent), and write with conditions and update expressions, including reserved words.

Why it helps

DynamoDB gives single-digit-millisecond reads at any scale with no servers, no connections to pool and no maintenance window, but only when the keys match the access patterns. Wrong keys turn into scans, large bills and throttling, and a table's keys cannot be changed later without copying the data.

The lesson also covers the parts that catch everyone once: filter expressions that do not reduce cost, reserved words such as status and total, a GSI that is never strongly consistent, and put-item replacing an item that already exists.

Commands in this lesson

aws

FAQ

What is the difference between a Query and a Scan?

A Query reads one partition: the key condition must name the partition key with = and may narrow the sort key with =, <, >, BETWEEN or begins_with. A Scan reads the whole table. On a large table a Scan is slow, expensive and competes with production traffic for capacity, so real access patterns get keys or an index instead.

Why doesn't a filter expression make my query cheaper?

Filters run after the read. Count shows how many items matched; ScannedCount shows how many were read and billed. A filter saves bandwidth, not capacity. Check with --return-consumed-capacity TOTAL, and if a filter throws most of the items away, the pattern deserves its own key or a GSI.

Why does my expression fail on an attribute called status or total?

Both are DynamoDB reserved words, like name, date, count and more than 500 others. A reserved word in an expression must be replaced by a placeholder: write #s in the expression and pass --expression-attribute-names '{"#s": "status"}'. Values always go through --expression-attribute-values placeholders such as :s.

Can I read a GSI with strong consistency?

No. DynamoDB keeps a global secondary index in sync asynchronously, so its reads are always eventually consistent; --consistent-read on a GSI query is refused. Strongly consistent reads work only on the table and on local secondary indexes, which keep the partition key and can only be created with the table. Items without the index's key attribute are left out, a sparse index.

How do I stop put-item from overwriting an existing item?

Add a condition: --condition-expression 'attribute_not_exists(PK)' makes the write "create only". If the item exists, DynamoDB answers ConditionalCheckFailedException. The same mechanism gives optimistic locking with a version attribute and rules like "never let the balance go negative". transact-write-items applies up to 100 writes all-or-nothing, at twice the capacity cost.

In an interview Mid

How do you design a DynamoDB table?

List the access patterns first, before any keys. Then pick a partition key with many distinct values that spreads the load, and a sort key that serves the range and order questions, often a type prefix and a date such as ORDER#<date>#<id>, so "orders of a customer, newest first" is one Query with begins_with. Patterns the keys cannot serve get a GSI, sometimes a sparse one. Use condition expressions for uniqueness and optimistic locking, and avoid scans and hot keys.

Also asked: What is the difference between a Query and a Scan in DynamoDB? · What is single-table design, and why do teams use it? · How would you implement optimistic locking in DynamoDB?

Practise this lesson in the terminal Free, in your browser - a real Ubuntu terminal to try it in, with missions that check your work.