Why do Count and ScannedCount differ after a Scan with a FilterExpression?

Scan reads every item in the table (or the requested segment of it) first — that read count is ScannedCount — and only afterward discards the ones that don't match the FilterExpression, leaving Count as what's actually returned. A Scan's cost is driven by ScannedCount, not Count, which is why a highly selective filter on a large table is still an expensive full scan under the hood.

Why is total treated as a reserved keyword when it's just my own attribute name?

DynamoDB's expression language (used by FilterExpression, KeyConditionExpression, UpdateExpression, and ProjectionExpression) reserves several hundred words — many of them ordinary English words like total, size, or name — because the parser needs them for its own syntax. Storing an item with an attribute literally named total is always fine; the error only triggers when that word appears unescaped inside an expression string, which is why ExpressionAttributeNames exists as an escape hatch.

What is a Global Secondary Index, and why does a query on category need one?

A GSI is a second, DynamoDB-maintained copy of the table's items, organized by a different partition key (and optionally sort key) than the base table. The base table here is keyed by CustomerId/OrderId, so it has no way to jump straight to 'everything with category = X' without scanning every item; a GSI on category gives Query that same direct-lookup speed on a dimension the base table's own key doesn't support.

Does adding a GSI to an existing table require any downtime?

No. update-table with a GlobalSecondaryIndexUpdates create request backfills the index from the table's existing items in the background while the table keeps serving reads and writes normally. The index's IndexStatus reports CREATING until the backfill finishes, then ACTIVE. Querying a GSI while it's still CREATING raises a ValidationException (AWS documents this as 'Cannot read from backfilling global secondary index') rather than returning incomplete data or silently not finding the index — this lesson's own backfill finishes near-instantly on DynamoDB Local's tiny dataset, so it never actually hits that window.

DynamoDB: Scan, Reserved Words, and Global Secondary Indexes

A Scan reads every item in a table, unlike Query, which goes straight to one partition key. Filtering a scan doesn’t change that — it only changes what gets returned, not what gets read. This lesson shows that gap directly, then hits (and fixes) DynamoDB’s reserved-word rule, then adds a Global Secondary Index so a non-key attribute gets Query-speed lookups too.

Setting Up: Two Orders With a Category

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws12-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@"; }

aws dynamodb create-table --table-name Orders \
  --attribute-definitions AttributeName=CustomerId,AttributeType=S AttributeName=OrderId,AttributeType=S \
  --key-schema AttributeName=CustomerId,KeyType=HASH AttributeName=OrderId,KeyType=RANGE \
  --billing-mode PAY_PER_REQUEST --endpoint-url http://aws12-ddb:8000 >/dev/null

aws dynamodb put-item --table-name Orders \
  --item '{"CustomerId":{"S":"alice"},"OrderId":{"S":"o-001"},"category":{"S":"physical"},"total":{"N":"30"}}' \
  --endpoint-url http://aws12-ddb:8000
aws dynamodb put-item --table-name Orders \
  --item '{"CustomerId":{"S":"alice"},"OrderId":{"S":"o-002"},"category":{"S":"ebook"},"total":{"N":"55"}}' \
  --endpoint-url http://aws12-ddb:8000
echo "table and 2 items ready"

$ docker network create aws12-lab
2558c78ee36a42011a7fdae4f7e4bb5451a59cdf3c2e986e70e4a4edfb1f1468
$ docker run -d --name aws12-ddb --network aws12-lab amazon/dynamodb-local:3.3.2
c28484eb3caf7c16a7cc18a087b3e9ae128b911b279db3d4c901620b084631d4
$ until docker run --rm --network aws12-lab curlimages/curl:8.11.1 -s -o /dev/null http://aws12-ddb:8000; do sleep 0.3; done
$ bash setup.sh
table and 2 items ready

Scan With a Filter: Count vs. ScannedCount

Unlike Query, Scan has no partition key to start from — it reads the whole table and applies the filter afterward.

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws12-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@"; }

aws dynamodb scan --table-name Orders \
  --filter-expression "category = :c" \
  --expression-attribute-values '{":c":{"S":"physical"}}' \
  --endpoint-url http://aws12-ddb:8000

$ bash scan_filter.sh
{
    "Items": [
        {
            "total": {
                "N": "30"
            },
            "CustomerId": {
                "S": "alice"
            },
            "OrderId": {
                "S": "o-001"
            },
            "category": {
                "S": "physical"
            }
        }
    ],
    "Count": 1,
    "ScannedCount": 2,
    "ConsumedCapacity": null
}

ScannedCount is 2 — the scan read both items in the table — while Count is 1, because only o-001 matched category = "physical" after the read. A bigger table with the same filter would scan (and pay for) every item, no matter how few match.

The Reserved-Word Error

total looks like an ordinary attribute name — and storing it was never a problem — but DynamoDB’s expression parser reserves it as a keyword, so using it directly inside a FilterExpression fails.

#!/usr/bin/env bash
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws12-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@"; }

aws dynamodb scan --table-name Orders \
  --filter-expression "total > :min" \
  --expression-attribute-values '{":min":{"N":"0"}}' \
  --endpoint-url http://aws12-ddb:8000

$ bash reserved_word.sh

aws: [ERROR]: An error occurred (ValidationException) when calling the Scan operation: Invalid FilterExpression: Attribute name is a reserved keyword; reserved keyword: total

A ValidationException naming total specifically as the reserved keyword — not a generic syntax error. The fix is ExpressionAttributeNames, which maps a placeholder (#t) to the real attribute name, letting the expression reference it without the literal word ever appearing in the parsed expression string.

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws12-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@"; }

aws dynamodb scan --table-name Orders \
  --filter-expression "#t > :min" \
  --expression-attribute-names '{"#t":"total"}' \
  --expression-attribute-values '{":min":{"N":"0"}}' \
  --endpoint-url http://aws12-ddb:8000 \
  --query '{Count:Count,ScannedCount:ScannedCount}'

$ bash fixed_filter.sh
{
    "Count": 2,
    "ScannedCount": 2
}

Both items have a positive total, so Count now matches ScannedCount at 2 — the alias changed nothing about what the filter means, only whether the parser accepts the expression at all.

Adding and Querying a Global Secondary Index

The base table’s key (CustomerId, OrderId) can’t answer “every order in category X” without a full scan. A GSI on category gives that question its own Query-speed index.

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws12-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@"; }

aws dynamodb update-table --table-name Orders \
  --attribute-definitions AttributeName=category,AttributeType=S \
  --global-secondary-index-updates '[{"Create":{"IndexName":"CategoryIndex","KeySchema":[{"AttributeName":"category","KeyType":"HASH"}],"Projection":{"ProjectionType":"ALL"}}}]' \
  --endpoint-url http://aws12-ddb:8000 \
  --query 'TableDescription.GlobalSecondaryIndexes[0].{IndexName:IndexName,IndexStatus:IndexStatus}'

until aws dynamodb describe-table --table-name Orders --endpoint-url http://aws12-ddb:8000 \
    --query 'Table.GlobalSecondaryIndexes[0].IndexStatus' --output text | grep -q ACTIVE; do
  sleep 0.3
done
echo "index ACTIVE"

$ bash add_gsi.sh
{
    "IndexName": "CategoryIndex",
    "IndexStatus": "CREATING"
}
index ACTIVE

The index starts CREATING and reaches ACTIVE once its backfill finishes, well within the loop above’s polling window. With the index ready, it can now be queried directly by category.

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws12-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@"; }

aws dynamodb query --table-name Orders --index-name CategoryIndex \
  --key-condition-expression "category = :c" \
  --expression-attribute-values '{":c":{"S":"physical"}}' \
  --endpoint-url http://aws12-ddb:8000 \
  --query '{Count:Count,ScannedCount:ScannedCount,Orders:Items[].OrderId.S}'

$ bash query_gsi.sh
{
    "Count": 1,
    "ScannedCount": 1,
    "Orders": [
        "o-001"
    ]
}

Compare ScannedCount here to the Scan above: querying the GSI by category scans only the items that actually share that category value, not the whole table — exactly the index-speed lookup the base table’s own key couldn’t provide.

Practice

1. Scan with the filter set to category = ebook instead, and check Count/ScannedCount.

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws12-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@"; }

aws dynamodb scan --table-name Orders \
  --filter-expression "category = :c" \
  --expression-attribute-values '{":c":{"S":"ebook"}}' \
  --endpoint-url http://aws12-ddb:8000 \
  --query '{Count:Count,ScannedCount:ScannedCount}'

$ bash practice1.sh
{
    "Count": 1,
    "ScannedCount": 2
}

ScannedCount is still 2 — a Scan always reads the whole table regardless of which value the filter is looking for — only Count changes, to match whichever single item is ebook.

2. Put a third order with category "physical", then query the GSI again and confirm it picked up the new item automatically.

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws12-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@"; }

aws dynamodb put-item --table-name Orders \
  --item '{"CustomerId":{"S":"alice"},"OrderId":{"S":"o-003"},"category":{"S":"physical"},"total":{"N":"12"}}' \
  --endpoint-url http://aws12-ddb:8000
aws dynamodb query --table-name Orders --index-name CategoryIndex \
  --key-condition-expression "category = :c" \
  --expression-attribute-values '{":c":{"S":"physical"}}' \
  --endpoint-url http://aws12-ddb:8000 \
  --query '{Count:Count,Orders:Items[].OrderId.S}'

$ bash practice2.sh
{
    "Count": 2,
    "Orders": [
        "o-001",
        "o-003"
    ]
}

Count is now 2 (o-001 and o-003) with no index-maintenance step of our own — a GSI is kept in sync with every write to the base table automatically. That sync is itself eventually consistent on real AWS (a GSI supports no other read type), so a query run immediately after a write can briefly still miss it there; DynamoDB Local’s own propagation here is fast enough that this lesson’s query, run moments after the write, doesn’t show that gap.

3. Query the GSI for a category value that doesn't exist, and compare to the reserved-word error from earlier.

#!/usr/bin/env bash
set -e
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws12-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@"; }

aws dynamodb query --table-name Orders --index-name CategoryIndex \
  --key-condition-expression "category = :c" \
  --expression-attribute-values '{":c":{"S":"subscription"}}' \
  --endpoint-url http://aws12-ddb:8000

$ bash practice3.sh
{
    "Items": [],
    "Count": 0,
    "ScannedCount": 0,
    "ConsumedCapacity": null
}

An empty, successful result — Count: 0, no error — unlike the reserved-word case above, which failed before DynamoDB ever looked at any data. A key condition that matches nothing is a normal, valid query; a reserved word in an unescaped expression never gets that far.

4. Try using total unescaped in a ProjectionExpression instead of a FilterExpression, confirming the same reserved-word rule applies there too.

#!/usr/bin/env bash
aws() { MSYS_NO_PATHCONV=1 docker run --rm --network aws12-lab -e AWS_ACCESS_KEY_ID=test -e AWS_SECRET_ACCESS_KEY=test -e AWS_DEFAULT_REGION=us-east-1 amazon/aws-cli:2.39.5 "$@"; }

aws dynamodb get-item --table-name Orders \
  --key '{"CustomerId":{"S":"alice"},"OrderId":{"S":"o-001"}}' \
  --projection-expression "total" \
  --endpoint-url http://aws12-ddb:8000

$ bash practice4.sh

aws: [ERROR]: An error occurred (ValidationException) when calling the GetItem operation: Invalid ProjectionExpression: Attribute name is a reserved keyword; reserved keyword: total

The same ValidationException, naming total again — the reserved-word rule belongs to the expression language itself, not to any one kind of expression, so FilterExpression, ProjectionExpression, KeyConditionExpression, and UpdateExpression all enforce it equally.

Cleanup

$ docker rm -f aws12-ddb
aws12-ddb
$ docker network rm aws12-lab
aws12-lab

Next: conditional writes, atomic counters, and reading a change off DynamoDB Streams.

Programming

Web Development

Generative AI

Machine Learning

Cybersecurity

Data Engineering

Cloud

DevOps

System Design

GPU & Parallel

Databases

Testing

Interviews