DynamoDB for Data Engineers: Single-Table Design, Streams & S3 Export

DynamoDB is the piece of the modern stack that most data engineers meet from the outside — as a source they have to drain into a warehouse, a CDC feed they have to consume, or an interview whiteboard they have to model on — long before they ever get to design one themselves. It is a fully managed key-value and document store that gives you single-digit-millisecond reads at any scale, but it earns that speed by refusing to be a relational database: there are no joins, no ad-hoc WHERE clauses that the engine will happily optimize, and no "just add an index later and the slow query gets fast." Every efficient access path has to be designed before the first item is written, because the shape of the partition key and sort key you choose is the shape of every query you will ever be allowed to run cheaply.

This guide is the walkthrough you wished existed the first time someone handed you a DynamoDB table and said "get this into Snowflake by Friday," or the first time an interviewer asked "model a social feed in one table and explain how you'd stream changes to analytics." It opens the box in layers: the data model (how items hash into partitions and what a read or write actually costs), single-table design driven by access patterns instead of entities, the secondary indexes that buy you new query paths, the change log that turns every item mutation into an ordered event, and finally the S3 export path that bridges an operational table into a columnar warehouse without ever scanning it. Each section pairs a teaching block with a Solution-Tail interview answer — code, a step-by-step trace, an output table, then a concept-by-concept breakdown of why it works.

When you want hands-on reps immediately after reading, drill the database practice library →, sharpen your modeling with the indexing practice library →, and rehearse the pipeline side on the data-processing practice library →.

---

On this page

• The DynamoDB data model — keys, items, and capacity
• Single-table design and access patterns
• GSIs and query patterns
• DynamoDB Streams — change data capture
• S3 export and warehouse integration
• Cheat sheet — DynamoDB for data engineers recipes
• Frequently asked questions
• Practice on PipeCode

---

1. The DynamoDB data model — keys, items, and capacity

The partition key, sort key, and capacity model are the three levers that decide every query you can ever run cheaply

The one-sentence invariant: a DynamoDB table is a distributed hash map whose primary key is either a single partition key or a composite (partition key, sort key) pair — the partition key hashes an item onto one of many physical partitions, the sort key orders items within that partition, and every read or write is billed in capacity units, so the model you get is "O(1) lookup by full key, cheap range scan within a partition, and expensive full-table scan for anything else." Nothing about that sentence is optional. If you internalize it, single-table design, GSIs, and streams all fall out as consequences; if you skip it, you will design a table that works in the demo and melts under a hot partition in production.

The primitives that matter.

• Item. The row equivalent — a collection of attributes, one of which is the partition key. An item is at most 400 KB including attribute names and values. There is no fixed schema beyond the primary key; two items in the same table can have entirely different attributes.
• Partition key (a.k.a. hash key). The attribute DynamoDB hashes to pick a physical partition. To read or Query efficiently you must supply the exact partition key value — there is no "partition key LIKE prefix." High cardinality and even access distribution are the whole game.
• Sort key (a.k.a. range key). The optional second half of a composite primary key. Items sharing a partition key are stored sorted by sort key, which is what makes range conditions (begins_with, between, >) cheap. The partition key plus sort key together must be unique.
• Capacity units. One RCU buys one strongly-consistent read of up to 4 KB/second (or two eventually-consistent reads); one WCU buys one write of up to 1 KB/second. Everything you do is priced in these units, whether you run on-demand (pay-per-request) or provisioned.

The physical partitioning story interviewers probe.

• DynamoDB spreads items across many storage partitions; each partition tops out around 10 GB of data, 3,000 RCU, and 1,000 WCU. When a table grows or its throughput climbs, DynamoDB splits partitions automatically.
• A hot partition is the classic failure: if 90% of traffic targets one partition key value (say status = 'ACTIVE' as a key), that single partition's throughput ceiling becomes your table's ceiling, and you get throttling while the table is 99% idle.
• Adaptive capacity and burst capacity soften short spikes, but they cannot rescue a fundamentally skewed key design. The fix is always at the key level — add entropy, shard the hot key, or choose a higher-cardinality attribute.

The 2026 reality — on-demand is the default.

• On-demand capacity is the modern default: you pay per read/write request and DynamoDB handles scaling instantly. It is the right call for spiky, unpredictable, or new workloads, and it removes the "did we provision enough?" operational load.
• Provisioned capacity (with auto-scaling) is cheaper at steady, predictable, high volume — you reserve RCU/WCU and optionally buy reserved capacity. Data engineers usually meet provisioned tables on mature, high-throughput services where the cost math has been done.
• Reads have two consistency modes. Eventually-consistent reads are the default and cost half an RCU; strongly-consistent reads cost a full RCU and are not available on GSIs. Knowing which you need per access pattern is a real cost lever.

What interviewers listen for.

• Do you say "I pick the partition key for even distribution and high cardinality" before talking about attributes? — senior signal.
• Do you name the 400 KB item limit and what to do about large blobs (offload to S3, store a pointer)? — required answer.
• Do you distinguish Query (by partition key) from Scan (whole table) and treat Scan as a last resort? — required answer.
• Do you reason about RCU/WCU cost rather than assuming reads are free? — senior signal.

Worked example — choosing a partition key that spreads load

Detailed explanation. The single most consequential decision in a DynamoDB table is the partition key. A good one distributes both storage and throughput evenly across partitions; a bad one funnels traffic onto one partition and throttles. Walk through picking a partition key for an IoT telemetry table that ingests readings from millions of devices.

• The access pattern. "Get the last N readings for a given device," and "write a reading as it arrives."
• The tempting-but-wrong key. sensor_type (e.g. TEMPERATURE, HUMIDITY) — only a handful of distinct values, so a handful of partitions absorb all writes. Instant hot partition.
• The right key. device_id — millions of distinct values, so writes and reads spread across the whole partition space. Use the reading timestamp as the sort key so "last N readings" is a cheap descending range scan.

Question. Design the primary key for the telemetry table so that writes distribute evenly and "last N readings for a device" is a single efficient query.

Input.

Candidate partition key	Distinct values	Distribution	Verdict
sensor_type	~5	catastrophically skewed	reject
region	~20	skewed	reject
device_id	millions	even	accept
device_id#yyyy-mm	millions × months	even + time-bounded	accept (write-sharded)

Code.

import boto3

ddb = boto3.client("dynamodb")

ddb.create_table(
    TableName="telemetry",
    KeySchema=[
        {"AttributeName": "device_id", "KeyType": "HASH"},   # partition key
        {"AttributeName": "reading_ts", "KeyType": "RANGE"}, # sort key
    ],
    AttributeDefinitions=[
        {"AttributeName": "device_id", "AttributeType": "S"},
        {"AttributeName": "reading_ts", "AttributeType": "S"},  # ISO-8601 string sorts lexically = chronologically
    ],
    BillingMode="PAY_PER_REQUEST",   # on-demand
)

# Last 10 readings for one device — a single Query, newest first
resp = ddb.query(
    TableName="telemetry",
    KeyConditionExpression="device_id = :d",
    ExpressionAttributeValues={":d": {"S": "device-8f21"}},
    ScanIndexForward=False,   # descending sort key = newest first
    Limit=10,
)

Step-by-step explanation.

1. device_id as the partition key gives millions of distinct hash targets, so writes spread across the entire partition space — no single device can hot-spot the table.
2. reading_ts as an ISO-8601 string sort key is stored in lexical order, which for zero-padded ISO timestamps is identical to chronological order. That makes "readings between two times" a native range condition.
3. ScanIndexForward=False reads the sort key in descending order, so Limit=10 returns the ten newest readings without scanning the whole item collection.
4. BillingMode=PAY_PER_REQUEST is on-demand — appropriate for telemetry whose volume is spiky and hard to predict.
5. If a single device ever became hot (e.g. a firmware bug hammering one sensor), the write-sharded key device_id#yyyy-mm splits its collection across months; you fan out reads across the small known set of month-shards.

Output.

Metric	sensor_type key	device_id key
Distinct partitions used	~5	millions
Write hot-spot risk	severe	negligible
"Last N readings" query	Scan + filter	single Query
Throughput ceiling	one partition's	whole table's

Rule of thumb. Pick the partition key for cardinality and even access first; pick the sort key so your most common "give me a range" access pattern is a native Query. If any single key value can attract a large fraction of traffic, shard it with a suffix before you ship.

Worked example — sizing reads and writes in capacity units

Detailed explanation. Data engineers routinely misjudge DynamoDB cost because they assume reads are free. They are not — every read and write is metered. Walk through computing the capacity cost of a realistic access pattern so you can defend a cost estimate in a design review.

• RCU rule. 1 RCU = one strongly-consistent read of ≤ 4 KB per second; eventually-consistent reads are half price (0.5 RCU per 4 KB).
• WCU rule. 1 WCU = one write of ≤ 1 KB per second. A 3.5 KB item costs 4 WCU to write (round up per KB).
• Rounding. Both RCU and WCU round the item size up to the next unit boundary (4 KB for reads, 1 KB for writes).

Question. A service reads a 6 KB item 500 times/second (eventual consistency is fine) and writes a 2.5 KB item 100 times/second. Compute the RCU and WCU demand.

Input.

Operation	Item size	Rate/sec	Consistency
Read	6 KB	500	eventual
Write	2.5 KB	100	standard

Code.

import math

def rcu(item_kb, reads_per_sec, strongly_consistent):
    units_per_read = math.ceil(item_kb / 4)          # 4 KB per RCU
    if not strongly_consistent:
        units_per_read = units_per_read / 2           # eventual = half price
    return units_per_read * reads_per_sec

def wcu(item_kb, writes_per_sec):
    units_per_write = math.ceil(item_kb / 1)          # 1 KB per WCU
    return units_per_write * writes_per_sec

read_demand  = rcu(6, 500, strongly_consistent=False)  # 6KB -> 2 units -> /2 -> 1 * 500
write_demand = wcu(2.5, 100)                           # 2.5KB -> 3 units * 100

print(read_demand, "RCU")   # 500.0 RCU
print(write_demand, "WCU")  # 300 WCU

Step-by-step explanation.

1. A 6 KB item rounds up to two 4 KB read blocks → 2 units for a strongly-consistent read. Eventual consistency halves that to 1 unit per read.
2. At 500 reads/second that is 500 RCU of demand — a real, billable number, not "free."
3. A 2.5 KB item rounds up to three 1 KB write blocks → 3 WCU per write.
4. At 100 writes/second that is 300 WCU.
5. On-demand you simply pay for those request-units; provisioned you must reserve at least this many (plus headroom) or auto-scale, and under-provisioning shows up as ProvisionedThroughputExceededException throttling.

Output.

Access pattern	Per-op units	Rate	Total demand
6 KB eventual read	1 RCU	500/s	500 RCU
2.5 KB write	3 WCU	100/s	300 WCU

Rule of thumb. Estimate capacity as ceil(size / block) × rate, halve reads when eventual consistency is acceptable, and remember that fat items multiply cost linearly — a 40 KB item costs 10× the read units of a 4 KB one, which is a strong argument for keeping items lean.

Worked example — the 400 KB item limit and large-attribute offload

Detailed explanation. DynamoDB caps an item at 400 KB. When a natural entity carries a large blob — a document body, an image, a big JSON payload — you must not stuff it into the item. The pattern is to store the blob in S3 and keep only a pointer plus metadata in DynamoDB. Walk through the offload.

• The symptom. ValidationException: Item size has exceeded the maximum allowed size on write.
• The fix. Put the large attribute in S3; store the S3 key, size, and content-type in DynamoDB.
• The bonus. Items stay small → reads are cheaper (fewer RCU) and item collections stay under the 10 GB partition ceiling longer.

Question. Redesign a documents table whose body attribute can exceed 400 KB so writes never fail and reads stay cheap.

Input.

Attribute	Before	After
doc_id (PK)	string	string
title	string	string
body	up to 5 MB text	removed
s3_key	—	s3://docs/<doc_id>.txt
byte_size	—	number

Code.

import boto3, json

s3  = boto3.client("s3")
ddb = boto3.client("dynamodb")

def put_document(doc_id, title, body: str):
    body_bytes = body.encode("utf-8")
    key = f"{doc_id}.txt"

    # 1. Large payload -> S3
    s3.put_object(Bucket="docs", Key=key, Body=body_bytes,
                  ContentType="text/plain")

    # 2. Small metadata item -> DynamoDB (well under 400 KB)
    ddb.put_item(
        TableName="documents",
        Item={
            "doc_id":    {"S": doc_id},
            "title":     {"S": title},
            "s3_key":    {"S": f"s3://docs/{key}"},
            "byte_size": {"N": str(len(body_bytes))},
        },
    )

Step-by-step explanation.

1. The large body moves to S3 under a deterministic key derived from doc_id, so the item and its blob stay linked without a separate mapping.
2. The DynamoDB item now holds only small metadata — a few hundred bytes — so it never approaches the 400 KB ceiling and costs a single RCU to read.
3. Read paths that only need title never pay to transfer the megabyte body; read paths that need the body do one extra S3 GetObject.
4. Keeping items small also keeps item collections well under the 10 GB per-partition limit, delaying forced splits.
5. The same pattern applies to anything bulky — embeddings, base64 images, denormalized arrays — offload to S3 (or a separate table) and store a reference.

Output.

Concern	Before (inline body)	After (S3 offload)
Write success	fails > 400 KB	always succeeds
Read cost (metadata)	many RCU	1 RCU
Partition pressure	high	low
Blob storage cost	DynamoDB rate	S3 rate (cheaper)

Rule of thumb. Keep DynamoDB items lean and boring. Anything that can grow past a few KB — especially unbounded text or binary — belongs in S3 with a pointer in the item. Small items are cheaper to read, faster to stream, and friendlier to every downstream pipeline.

Data engineering interview question on the DynamoDB data model

A senior interviewer might ask: "You are given a new DynamoDB table that will store user sessions — hundreds of millions of rows, written constantly, read by user_id, and you must also fetch a single session by its session_id. Design the primary key, choose a capacity mode, and explain how you avoid a hot partition and how you keep read costs bounded."

Solution Using a composite primary key with on-demand capacity

import boto3

ddb = boto3.client("dynamodb")

# Primary key: partition by user_id (high cardinality), sort by session start time.
# Uniqueness of (user_id, started_at) is guaranteed by including session_id in the SK.
ddb.create_table(
    TableName="sessions",
    KeySchema=[
        {"AttributeName": "user_id", "KeyType": "HASH"},
        {"AttributeName": "sk",      "KeyType": "RANGE"},  # "2026-09-05T10:04:11Z#sess-abc123"
    ],
    AttributeDefinitions=[
        {"AttributeName": "user_id",    "AttributeType": "S"},
        {"AttributeName": "sk",         "AttributeType": "S"},
        {"AttributeName": "session_id", "AttributeType": "S"},
    ],
    BillingMode="PAY_PER_REQUEST",
    GlobalSecondaryIndexes=[{
        "IndexName": "gsi_session_id",
        "KeySchema": [{"AttributeName": "session_id", "KeyType": "HASH"}],
        "Projection": {"ProjectionType": "ALL"},
    }],
)

# Access pattern A — recent sessions for a user (cheap range scan, newest first)
ddb.query(
    TableName="sessions",
    KeyConditionExpression="user_id = :u",
    ExpressionAttributeValues={":u": {"S": "user-42"}},
    ScanIndexForward=False,
    Limit=20,
)

# Access pattern B — one session by its global id (via the GSI)
ddb.query(
    TableName="sessions",
    IndexName="gsi_session_id",
    KeyConditionExpression="session_id = :s",
    ExpressionAttributeValues={":s": {"S": "sess-abc123"}},
)

Step-by-step trace.

Input	Value	Reasoning
Partition key	user_id	hundreds of millions of users = even spread, no hot partition
Sort key	started_at#session_id	orders sessions by time; suffix guarantees uniqueness
Capacity mode	on-demand	constant, spiky writes; no provisioning guesswork
Access A	Query user_id = :u	one round trip for a user's recent sessions
Access B	GSI on session_id	direct lookup by the global id without a Scan
Read consistency	eventual on the GSI	GSIs are eventual-only; halves RCU cost

After deployment, writes fan out across the full user_id space so no partition throttles; "recent sessions for a user" is one descending Query bounded by Limit; and "one session by id" is a single-item GSI lookup instead of a full-table Scan. Read cost stays O(items returned), not O(table).

Output:

Access pattern	Mechanism	Cost
Recent sessions for a user	Query on base table PK	O(returned) RCU
Session by global id	Query on gsi_session_id	1 GSI read
Write a session	PutItem	1+ WCU per KB
Hot-partition risk	user_id spread	negligible

Why this works — concept by concept:

• Partition key for spread — user_id has enormous cardinality, so DynamoDB's hash distributes writes and reads evenly across partitions; no single value can monopolize a partition's throughput.
• Sort key for ordering — encoding started_at first in the sort key makes "newest sessions" a native descending range scan, and appending session_id guarantees the composite key is unique even when two sessions start in the same millisecond.
• GSI for the second access pattern — a lookup by session_id is impossible on the base table (wrong partition key), so a GSI keyed on session_id provides that path without ever scanning the table.
• On-demand capacity — constant spiky session traffic is exactly the workload on-demand is built for; you avoid both throttling and over-provisioning, and cost tracks actual usage.
• Cost — every access is O(items touched), never O(table): a bounded Query for the common path, a single-item GSI read for the by-id path, and one write per session. The eliminated cost is the full-table Scan a naive "find session by id" design would have forced.

Database
Topic — database
Database modeling and key-design problems

Practice →

Indexing Topic — indexing Indexing and access-path problems

Practice →

---

2. Single-table design and access patterns

Model your access patterns first, then overload one table's partition key and sort key to serve them all

The mental model in one line: single-table design is the deliberate practice of storing many entity types — users, orders, line items — in a single DynamoDB table, using generic key attributes whose values carry entity-type prefixes (USER#123, ORDER#456), so that related items share a partition key and can be fetched together in one Query; it starts not from an entity-relationship diagram but from an exhaustive list of the exact queries the application will run. Data engineers trained on normalized relational schemas find this alien at first — you are effectively pre-joining data at write time — but it is what lets DynamoDB answer complex access patterns with single-digit-millisecond latency and no joins.

The axes that matter.

• Access-pattern-first, not entity-first. In DynamoDB you cannot bolt on an efficient query later. You enumerate every read the app needs — "get user profile," "list a user's orders," "get an order with its line items" — before the schema exists, because the keys must be shaped to serve them.
• Generic key names. The partition key is literally named PK and the sort key SK (or pk/sk). Their values are typed strings like USER#123 and ORDER#456, which lets one table hold heterogeneous items.
• Item collections. All items sharing a PK form an item collection, stored physically together and sorted by SK. A single Query on that PK returns the whole collection — the user profile and their orders and their addresses — in one round trip.
• Denormalization is the point. You duplicate data across items to avoid joins. Consistency across duplicates is maintained at write time (often via transactions or streams), trading write complexity for read speed.

The access-pattern-first workflow.

• Step 1 — list access patterns. Write them as a table: pattern name, key condition, index. "List orders for a user → PK = USER#<id> AND begins_with(SK, 'ORDER#')."
• Step 2 — design keys to satisfy each. Every access pattern must map to either a Query on the base table or a Query on a GSI. If a pattern needs a Scan, the design is wrong.
• Step 3 — assign entity prefixes. Decide the PK/SK value templates per entity so related items collide into the same partition on purpose.
• Step 4 — validate. Re-walk every access pattern against the key design; confirm none requires a Scan or a FilterExpression doing heavy lifting.

PK/SK overloading — the core technique.

• The same PK value groups related entities: USER#123 holds the profile (SK = PROFILE), the user's orders (SK = ORDER#456), and their addresses (SK = ADDRESS#home).
• begins_with(SK, 'ORDER#') filters an item collection to just the orders — a cheap sort-key condition, not a filter over unrelated data.
• The adjacency-list pattern models many-to-many relationships (an order belongs to a user; a line item belongs to an order) by making the child's PK the parent's identifier.

What interviewers listen for.

• Do you start by listing access patterns, not by drawing entities? — required answer.
• Do you use overloaded generic keys (PK/SK with typed prefixes) rather than one table per entity? — senior signal.
• Do you explain item collections and "one Query returns the whole aggregate"? — senior signal.
• Do you acknowledge the write-side cost of denormalization and how you keep duplicates consistent? — senior signal.

Worked example — listing access patterns before the schema

Detailed explanation. The discipline that separates a working DynamoDB model from a broken one is writing the access-pattern list first. Walk through building it for a small e-commerce domain with users, orders, and line items.

• The domain. A user places orders; each order has line items. The app never runs ad-hoc analytics against this table — that is the warehouse's job (section 5).
• The patterns. Get a user; list a user's orders; get an order plus its line items; get a single line item.
• The rule. Each pattern must resolve to a Query, never a Scan.

Question. Enumerate the access patterns and map each to a concrete key condition on a single table.

Input.

#	Access pattern	Frequency
1	Get user profile	high
2	List a user's orders	high
3	Get an order + its line items	high
4	Get one line item	medium

Code.

Access-pattern map (single table "app", keys PK / SK)
=====================================================

1. Get user profile
   PK = USER#<user_id>   SK = PROFILE

2. List a user's orders
   PK = USER#<user_id>   SK begins_with "ORDER#"

3. Get an order + its line items
   PK = ORDER#<order_id> SK begins_with ""   (returns ORDER meta + ITEM#* rows)

4. Get one line item
   PK = ORDER#<order_id> SK = ITEM#<item_id>

Item shapes
-----------
USER#123  | PROFILE      | name, email, tier
USER#123  | ORDER#456    | order_id, total_cents, status   (duplicated for pattern 2)
ORDER#456 | ORDER#456    | order_id, user_id, total_cents, status
ORDER#456 | ITEM#1       | sku, qty, price_cents
ORDER#456 | ITEM#2       | sku, qty, price_cents

Step-by-step explanation.

1. Pattern 1 stores the profile under PK = USER#123, SK = PROFILE — a direct GetItem.
2. Pattern 2 stores a lightweight copy of each order under the user's partition (SK = ORDER#456), so begins_with(SK, 'ORDER#') lists them in one query. This copy is a deliberate denormalization for read speed.
3. Pattern 3 stores the authoritative order and its line items under PK = ORDER#456; a single Query on that partition returns the order metadata row plus every ITEM#* row — the whole aggregate in one round trip.
4. Pattern 4 is a direct GetItem on (ORDER#456, ITEM#1).
5. Nothing here requires a Scan or a FilterExpression over unrelated items — every pattern is a partition-scoped Query or a point GetItem, which is the mark of a correct design.

Output.

Pattern	Key condition	Operation
Get user	PK=USER#id, SK=PROFILE	GetItem
List orders	PK=USER#id, begins_with(SK,'ORDER#')	Query
Order + items	PK=ORDER#id	Query
One line item	PK=ORDER#id, SK=ITEM#id	GetItem

Rule of thumb. If you cannot write every access pattern as a Query or GetItem against your key design before you create the table, you are not done modeling. A pattern that forces a Scan is a design bug, not a runtime tuning problem.

Worked example — overloading PK/SK with entity prefixes

Detailed explanation. Overloading is the mechanism that lets one table hold many entity types. The PK and SK are generic; their values encode both the entity type and its id. Walk through writing the items and querying an item collection.

• The convention. <ENTITY>#<id> for both key parts, plus a type attribute for clarity and stream filtering.
• The payoff. Related items live in one partition and come back in one query, sorted by SK.

Question. Write the items for one user with two orders, then fetch the user plus their order list in a single query.

Input.

PK	SK	type	payload
USER#123	PROFILE	User	name, email
USER#123	ORDER#456	OrderRef	total_cents, status
USER#123	ORDER#789	OrderRef	total_cents, status

Code.

import boto3
ddb = boto3.resource("dynamodb")
tbl = ddb.Table("app")

# Write the user profile and two order references into one item collection
with tbl.batch_writer() as bw:
    bw.put_item(Item={"PK": "USER#123", "SK": "PROFILE",
                      "type": "User", "name": "Ada", "email": "[e-mail]"})
    bw.put_item(Item={"PK": "USER#123", "SK": "ORDER#456",
                      "type": "OrderRef", "total_cents": 4200, "status": "shipped"})
    bw.put_item(Item={"PK": "USER#123", "SK": "ORDER#789",
                      "type": "OrderRef", "total_cents": 1599, "status": "pending"})

# One Query returns the profile AND both orders (whole item collection)
from boto3.dynamodb.conditions import Key
resp = tbl.query(KeyConditionExpression=Key("PK").eq("USER#123"))
for item in resp["Items"]:
    print(item["SK"], item.get("type"))
# PROFILE   User
# ORDER#456 OrderRef
# ORDER#789 OrderRef

Step-by-step explanation.

1. All three items share PK = USER#123, so they land in the same item collection, physically co-located and sorted by SK.
2. PROFILE sorts before ORDER#456 and ORDER#789 because P < O? No — O < P lexically, so orders sort first; if you want the profile first, prefix it (#PROFILE) or query with ScanIndexForward and handle ordering client-side. The lesson: sort-key value design controls result order.
3. A single Query on PK = USER#123 returns the entire collection — one network round trip for the user and all their orders.
4. To fetch only orders, add SK begins_with 'ORDER#'; the sort-key condition is evaluated on the index, so you pay only for matching items.
5. The type attribute is not required for querying but is invaluable downstream — stream consumers and the warehouse use it to route and unpack heterogeneous items.

Output.

Query	Returns	Round trips
PK=USER#123	profile + 2 orders	1
PK=USER#123, begins_with(SK,'ORDER#')	2 orders only	1
GetItem(USER#123, PROFILE)	profile only	1

Rule of thumb. Design sort-key values as deliberately as partition keys — prefixes give you cheap begins_with filtering and control result ordering. A type attribute on every item pays for itself the moment you build a stream consumer or export.

Worked example — the adjacency-list pattern for relationships

Detailed explanation. Many-to-many and hierarchical relationships (a user has many orders; an order has many items; an item can appear in many orders) are modeled with the adjacency-list pattern: edges are items whose PK is one node and whose SK is the other. Walk through modeling "which users bought a given SKU" alongside "which items are in an order."

• The relationship. Order —contains→ Item, and we want both directions eventually (order→items on the base table, sku→orders via a GSI in section 3).
• The pattern. Store an edge item PK = ORDER#456, SK = ITEM#sku-9 for the forward direction.

Question. Model order-to-item edges so that "items in an order" is a base-table query, and set up the key so a later GSI can answer "orders containing a SKU."

Input.

PK	SK	GSI1PK	GSI1SK
ORDER#456	ITEM#sku-9	SKU#sku-9	ORDER#456
ORDER#456	ITEM#sku-3	SKU#sku-3	ORDER#456
ORDER#789	ITEM#sku-9	SKU#sku-9	ORDER#789

Code.

# Edge items carry BOTH the base key (order -> items) and GSI keys (sku -> orders)
edges = [
    {"PK": "ORDER#456", "SK": "ITEM#sku-9", "GSI1PK": "SKU#sku-9", "GSI1SK": "ORDER#456", "qty": 2},
    {"PK": "ORDER#456", "SK": "ITEM#sku-3", "GSI1PK": "SKU#sku-3", "GSI1SK": "ORDER#456", "qty": 1},
    {"PK": "ORDER#789", "SK": "ITEM#sku-9", "GSI1PK": "SKU#sku-9", "GSI1SK": "ORDER#789", "qty": 5},
]

with tbl.batch_writer() as bw:
    for e in edges:
        bw.put_item(Item=e)

# Base table: items in an order
tbl.query(KeyConditionExpression=Key("PK").eq("ORDER#456") & Key("SK").begins_with("ITEM#"))
# -> sku-9 (qty 2), sku-3 (qty 1)

# (Section 3 will query GSI1: GSI1PK = SKU#sku-9 -> ORDER#456, ORDER#789)

Step-by-step explanation.

1. Each edge item encodes the forward relationship in the base key (PK = ORDER#456, SK = ITEM#sku-9), so "items in order 456" is a begins_with(SK, 'ITEM#') query.
2. The same edge item also carries GSI1PK = SKU#sku-9 and GSI1SK = ORDER#456. When projected into a GSI, this inverts the relationship, letting you query "orders containing sku-9" — covered in section 3.
3. Storing both directions on one physical item means a single write maintains both access paths; there is no separate join table to keep in sync.
4. qty and other edge attributes live on the edge item, exactly where a relational schema would put them on a junction row.
5. This is how DynamoDB models graphs and many-to-many links without joins: edges are first-class items, and GSIs provide the reverse traversal.

Output.

Traversal	Where	Query
Order → items	base table	PK=ORDER#456, begins_with(SK,'ITEM#')
SKU → orders	GSI1	GSI1PK=SKU#sku-9
Edge attributes	on the edge item	qty, added at write time

Rule of thumb. Model relationships as edge items that carry both the base key and the GSI key. One write maintains both directions of traversal, and you never build or maintain a separate join table — the adjacency list is the join.

Data engineering interview question on single-table design

A senior interviewer might ask: "Model a single DynamoDB table for a SaaS app with tenants, users within a tenant, and projects owned by users. The app must: get a tenant, list users in a tenant, get a user, and list a user's projects — all as single queries. Show the key design, the item shapes, and the query for 'list users in a tenant.'"

Solution Using PK/SK overloading with item-collection queries

import boto3
from boto3.dynamodb.conditions import Key

tbl = boto3.resource("dynamodb").Table("saas")

# Item shapes (overloaded generic keys PK / SK)
items = [
    {"PK": "TENANT#acme", "SK": "TENANT#acme",   "type": "Tenant", "plan": "enterprise"},
    {"PK": "TENANT#acme", "SK": "USER#u1",       "type": "User",   "email": "[e-mail]"},
    {"PK": "TENANT#acme", "SK": "USER#u2",       "type": "User",   "email": "[e-mail]"},
    {"PK": "USER#u1",     "SK": "PROJECT#p10",   "type": "Project","name": "Atlas"},
    {"PK": "USER#u1",     "SK": "PROJECT#p11",   "type": "Project","name": "Nova"},
]
with tbl.batch_writer() as bw:
    for it in items:
        bw.put_item(Item=it)

# Access pattern: list users in a tenant (one item collection, one query)
resp = tbl.query(
    KeyConditionExpression=Key("PK").eq("TENANT#acme") & Key("SK").begins_with("USER#")
)
users = [i["email"] for i in resp["Items"]]
print(users)   # ['[e-mail]', '[e-mail]']

Step-by-step trace.

PK	SK	Entity	Serves access pattern
TENANT#acme	TENANT#acme	Tenant	get a tenant
TENANT#acme	USER#u1	User	list users in tenant
TENANT#acme	USER#u2	User	list users in tenant
USER#u1	PROJECT#p10	Project	list a user's projects
USER#u1	PROJECT#p11	Project	list a user's projects

Walking the query: PK = TENANT#acme selects the tenant's item collection; begins_with(SK, 'USER#') narrows it to just the user items, skipping the tenant metadata row; the result is both users in one round trip. "List a user's projects" is the same shape against PK = USER#u1. Every required access pattern is a single partition-scoped query — no scans, no joins.

Output:

Access pattern	Key condition	Result
Get tenant	PK=TENANT#acme, SK=TENANT#acme	1 item
List users in tenant	PK=TENANT#acme, begins_with(SK,'USER#')	2 users
Get user	PK=TENANT#acme, SK=USER#u1	1 item
List user's projects	PK=USER#u1, begins_with(SK,'PROJECT#')	2 projects

Why this works — concept by concept:

• Overloaded keys — PK and SK are generic attribute names whose values (TENANT#acme, USER#u1) carry entity type and id, letting tenants, users, and projects coexist in one table without a schema per entity.
• Item collections — co-locating a tenant with its users under PK = TENANT#acme means "list users in a tenant" is a single sort-key-filtered query over a physically contiguous collection.
• begins_with on the sort key — the USER# and PROJECT# prefixes turn a heterogeneous collection into cheaply filterable sub-lists, evaluated on the index rather than as a post-read filter.
• Access-pattern-first design — every one of the four required reads was mapped to a key condition before the table existed, which is why none of them degrades to a Scan.
• Cost — each access pattern is O(items returned) on a single partition: one Query per read, no cross-partition fan-out, no join. The write-side cost is maintaining the entity-prefixed keys, paid once per item at write time.

Database
Topic — database
Single-table and data-modeling problems

Practice →

Data processing Topic — data-processing Denormalization and aggregate-modeling problems

Practice →

---

3. GSIs and query patterns

A GSI is a second partition key on the same data — it buys you a new access pattern at the cost of a second, eventually-consistent write

The mental model in one line: a Global Secondary Index (GSI) is an automatically-maintained copy of your table's items, re-keyed on different attributes, so you can Query the same data along a partition key the base table doesn't support — it has its own partition/sort key, its own throughput, its own projected subset of attributes, and it is always eventually consistent, which means every access pattern the base table can't serve directly becomes "add a GSI," bounded by the extra write cost and the propagation lag. GSIs are the single most important tool for turning a rigid key-value store into something that answers many questions, and misunderstanding them (LSI vs GSI, projection cost, sparse indexes) is the most common DynamoDB interview stumble.

GSI vs LSI — the distinction interviewers probe.

• GSI (Global Secondary Index). Any attributes as its (partition, sort) key; can be added any time; has its own provisioned/on-demand throughput; is eventually consistent only. Up to 20 per table (soft limit). This is the workhorse.
• LSI (Local Secondary Index). Shares the base table's partition key but a different sort key; must be created at table creation and cannot be added later; supports strongly-consistent reads; shares the base table's throughput; subject to a 10 GB item-collection limit. Rarely worth the constraints in 2026.
• The default choice. Reach for a GSI unless you specifically need strong consistency on an alternate sort key within the same partition — then, and only then, consider an LSI, and only if you can create it up front.

Projections — what the GSI copies.

• KEYS_ONLY. The GSI stores only the index and base keys. Smallest and cheapest; every read that needs more attributes must fetch back from the base table.
• INCLUDE. Keys plus a named list of extra attributes. The right middle ground — project exactly what the access pattern reads.
• ALL. Every attribute is copied into the GSI. Most convenient, most storage, highest write cost; use when the GSI must satisfy reads without touching the base table.

GSI overloading and sparse GSIs.

• GSI overloading. Just like the base table, name the GSI keys generically (GSI1PK, GSI1SK) and put typed values in them, so a single GSI serves multiple access patterns across entity types.
• Sparse GSI. An item only appears in a GSI if it has the GSI's key attributes. Omit the GSI key on items you don't want indexed and the index stays small — perfect for "find items in state X" where X is rare (e.g. open_ticket = true).
• Query vs Scan on a GSI. A GSI is queried exactly like a table — KeyConditionExpression on its keys — and you should never Scan it for a targeted read.

What interviewers listen for.

• Do you correctly state "GSIs are eventually consistent, LSIs can be strong"? — required answer.
• Do you know a GSI has its own throughput and an under-provisioned GSI throttles writes to the base table (provisioned mode)? — senior signal.
• Do you use a sparse GSI to make "find the few items in state X" cheap instead of scanning? — senior signal.
• Do you choose a projection deliberately rather than defaulting to ALL? — senior signal.

Worked example — an inverted-index GSI (sort key becomes partition key)

Detailed explanation. The most common GSI is the "inverted index": take an attribute that is a sort key or a child id on the base table and make it the GSI's partition key, so you can look items up by the other end of the relationship. Walk through inverting the order/item edges from section 2 to answer "which orders contain SKU X."

• Base table. Edge items PK = ORDER#456, SK = ITEM#sku-9.
• The question the base table can't answer. "Which orders contain sku-9?" — that needs sku as a partition key.
• The GSI. Partition by GSI1PK = SKU#sku-9, sort by GSI1SK = ORDER#456.

Question. Create the GSI and query all orders that contain a given SKU.

Input.

Base PK	Base SK	GSI1PK	GSI1SK
ORDER#456	ITEM#sku-9	SKU#sku-9	ORDER#456
ORDER#789	ITEM#sku-9	SKU#sku-9	ORDER#789
ORDER#456	ITEM#sku-3	SKU#sku-3	ORDER#456

Code.

import boto3
from boto3.dynamodb.conditions import Key

ddb = boto3.client("dynamodb")

# Add an inverted GSI keyed on the SKU
ddb.update_table(
    TableName="app",
    AttributeDefinitions=[
        {"AttributeName": "GSI1PK", "AttributeType": "S"},
        {"AttributeName": "GSI1SK", "AttributeType": "S"},
    ],
    GlobalSecondaryIndexUpdates=[{
        "Create": {
            "IndexName": "GSI1",
            "KeySchema": [
                {"AttributeName": "GSI1PK", "KeyType": "HASH"},
                {"AttributeName": "GSI1SK", "KeyType": "RANGE"},
            ],
            "Projection": {"ProjectionType": "INCLUDE",
                           "NonKeyAttributes": ["qty"]},
        }
    }],
)

# Query: which orders contain sku-9?
tbl = boto3.resource("dynamodb").Table("app")
resp = tbl.query(IndexName="GSI1",
                 KeyConditionExpression=Key("GSI1PK").eq("SKU#sku-9"))
orders = [i["GSI1SK"] for i in resp["Items"]]
print(orders)   # ['ORDER#456', 'ORDER#789']

Step-by-step explanation.

1. The base table can query "items in an order" but not "orders containing a SKU," because sku is only a sort-key fragment, never a partition key.
2. The GSI re-keys the same edge items on GSI1PK = SKU#<sku>, so all edges for one SKU collide into a single GSI partition, queryable in one round trip.
3. Projection = INCLUDE [qty] copies only the keys plus qty into the GSI — enough to answer "which orders, and how many units" without fetching back to the base table.
4. The GSI is maintained automatically: every base-table write that touches GSI1PK/GSI1SK propagates to the GSI asynchronously (eventual consistency, typically well under a second).
5. Querying by GSI1PK = SKU#sku-9 returns both orders — the inverse traversal the base table could not provide.

Output.

GSI query	Returns	Consistency
GSI1PK=SKU#sku-9	ORDER#456, ORDER#789	eventual
GSI1PK=SKU#sku-3	ORDER#456	eventual
projection	keys + qty	INCLUDE

Rule of thumb. When you need to query by the "other end" of a relationship, invert it with a GSI: put the child/attribute on GSI1PK and the parent on GSI1SK. Project only the attributes the access pattern actually reads to keep the index small and write-cheap.

Worked example — a sparse GSI for a rare-state filter

Detailed explanation. A sparse GSI indexes only the items that carry the GSI's key attributes, which makes "find the few items in state X" a tiny, cheap query instead of a full-table scan. Walk through indexing only open support tickets out of millions of mostly-closed ones.

• The problem. 50 million tickets, 99.9% closed. "List open tickets" via Scan + filter reads all 50M items.
• The sparse trick. Only open tickets get a GSI1PK = OPEN attribute; closed tickets omit it, so they never enter the GSI.
• The payoff. The GSI holds only ~50k open tickets; querying it is proportional to open tickets, not total tickets.

Question. Design a sparse GSI so "list all open tickets" costs O(open), and show how closing a ticket removes it from the index.

Input.

ticket_id	status	GSI1PK (sparse)
T-1	open	OPEN
T-2	closed	(absent)
T-3	open	OPEN

Code.

import boto3
from boto3.dynamodb.conditions import Key
tbl = boto3.resource("dynamodb").Table("tickets")

# Opening a ticket: set the sparse GSI key
tbl.put_item(Item={"PK": "TICKET#T-1", "status": "open",
                   "GSI1PK": "OPEN", "GSI1SK": "2026-09-05T09:00Z"})

# Closing a ticket: REMOVE the sparse key so it drops out of the GSI
tbl.update_item(
    Key={"PK": "TICKET#T-1"},
    UpdateExpression="SET #s = :closed REMOVE GSI1PK, GSI1SK",
    ExpressionAttributeNames={"#s": "status"},
    ExpressionAttributeValues={":closed": "closed"},
)

# List all open tickets — reads only items in the sparse GSI
open_tickets = tbl.query(IndexName="GSI1",
                         KeyConditionExpression=Key("GSI1PK").eq("OPEN"))

Step-by-step explanation.

1. Only items that possess GSI1PK appear in the GSI. Open tickets set it to the constant OPEN; closed tickets have no such attribute.
2. "List open tickets" becomes Query GSI1 WHERE GSI1PK = 'OPEN', reading only the open tickets — not the 50 million closed ones.
3. Closing a ticket uses REMOVE GSI1PK, GSI1SK in the update expression, which deletes the item from the GSI while keeping it in the base table. The item silently drops out of the index.
4. Because all open tickets share the constant partition key OPEN, watch for a hot GSI partition if "open" volume is huge; shard the constant (OPEN#<n>) if needed.
5. The base table still holds every ticket for point lookups by ticket_id; the sparse GSI is purely the efficient "current open set."

Output.

Operation	Effect on GSI	Cost of "list open"
Open ticket	item enters GSI	—
Close ticket (REMOVE keys)	item leaves GSI	—
Query GSI1PK=OPEN	reads only open items	O(open), not O(total)

Rule of thumb. For "find the small set of items currently in state X," use a sparse GSI: write the GSI key only while the item is in that state and REMOVE it when the state ends. You turn an O(table) scan into an O(matching-items) query.

Worked example — GSI overloading to serve multiple patterns from one index

Detailed explanation. Just as the base table's keys are overloaded, a single GSI's generic keys (GSI1PK/GSI1SK) can serve several unrelated access patterns by putting different typed values in them per entity. Walk through one GSI answering both "orders by status" and "users by email domain."

• The idea. Different entity types write different value templates into the same GSI1PK.
• The payoff. Fewer indexes (each GSI costs storage + write throughput), more access patterns per index.

Question. Use one GSI to answer "orders in status pending" and "users at email domain acme.io."

Input.

Entity	GSI1PK	GSI1SK
Order	STATUS#pending	ORDER#456
Order	STATUS#shipped	ORDER#457
User	DOMAIN#acme.io	USER#u1

Code.

from boto3.dynamodb.conditions import Key

# Orders currently pending
tbl.query(IndexName="GSI1",
          KeyConditionExpression=Key("GSI1PK").eq("STATUS#pending"))
# -> ORDER#456

# Users at a given email domain — SAME index, different value template
tbl.query(IndexName="GSI1",
          KeyConditionExpression=Key("GSI1PK").eq("DOMAIN#acme.io"))
# -> USER#u1

Step-by-step explanation.

1. Both orders and users write into the same GSI1PK attribute, but with distinct value namespaces: STATUS#... for orders, DOMAIN#... for users.
2. A query for GSI1PK = STATUS#pending returns only order items in that status; a query for GSI1PK = DOMAIN#acme.io returns only users at that domain. The namespaces never collide.
3. One physical GSI thus serves two logically-unrelated access patterns, saving the storage and write-throughput cost of a second index.
4. When an order ships, updating GSI1PK from STATUS#pending to STATUS#shipped moves it between GSI partitions automatically — the "status index" stays correct with no extra bookkeeping.
5. The discipline: keep value namespaces globally unique across entity types so a single overloaded GSI never conflates two patterns.

Output.

Query	GSI1PK value	Returns
Orders pending	STATUS#pending	ORDER#456
Orders shipped	STATUS#shipped	ORDER#457
Users at domain	DOMAIN#acme.io	USER#u1

Rule of thumb. Overload one GSI across entity types with namespaced key values (STATUS#…, DOMAIN#…) before creating a second GSI. Every extra GSI is extra storage and write amplification; a well-namespaced overloaded index does the work of several.

Data engineering interview question on GSIs

A senior interviewer might ask: "You have a 200-million-row orders table keyed by order_id. Product needs two new reads: 'all orders for a customer, newest first' and 'all orders currently in the refund_pending state.' Design the indexes, choose projections, and explain the consistency and cost implications, including why one of them should be sparse."

Solution Using a customer GSI plus a sparse status GSI

import boto3
from boto3.dynamodb.conditions import Key

ddb = boto3.client("dynamodb")

ddb.update_table(
    TableName="orders",
    AttributeDefinitions=[
        {"AttributeName": "customer_id", "AttributeType": "S"},
        {"AttributeName": "created_at",  "AttributeType": "S"},
        {"AttributeName": "refund_pk",   "AttributeType": "S"},  # sparse: present only when refund_pending
    ],
    GlobalSecondaryIndexUpdates=[
        {"Create": {
            "IndexName": "gsi_customer",
            "KeySchema": [
                {"AttributeName": "customer_id", "KeyType": "HASH"},
                {"AttributeName": "created_at",  "KeyType": "RANGE"},
            ],
            "Projection": {"ProjectionType": "INCLUDE",
                           "NonKeyAttributes": ["total_cents", "status"]},
        }},
        {"Create": {
            "IndexName": "gsi_refund",
            "KeySchema": [{"AttributeName": "refund_pk", "KeyType": "HASH"},
                          {"AttributeName": "created_at", "KeyType": "RANGE"}],
            "Projection": {"ProjectionType": "KEYS_ONLY"},
        }},
    ],
)

tbl = boto3.resource("dynamodb").Table("orders")

# All orders for a customer, newest first
tbl.query(IndexName="gsi_customer",
          KeyConditionExpression=Key("customer_id").eq("CUST#42"),
          ScanIndexForward=False)

# All orders currently refund_pending (sparse index -> only those items exist here)
tbl.query(IndexName="gsi_refund",
          KeyConditionExpression=Key("refund_pk").eq("REFUND_PENDING"))

Step-by-step trace.

Requirement	Index	Key design	Projection
Orders for a customer, newest first	gsi_customer	PK customer_id, SK created_at	INCLUDE total, status
Orders in refund_pending	gsi_refund (sparse)	PK refund_pk (const), SK created_at	KEYS_ONLY
Set refund state	write refund_pk='REFUND_PENDING'	item enters sparse GSI	—
Clear refund state	REMOVE refund_pk	item leaves sparse GSI	—

Walking it: the customer GSI re-keys every order by customer_id, so "orders for CUST#42, newest first" is a descending query returning total_cents and status from the projection without touching the base table. The refund GSI is sparse — only orders with refund_pk set are present — so "list refund_pending" reads a few thousand items, not 200 million; because the projection is KEYS_ONLY, those reads are tiny and the code fetches full order details from the base table only for the handful that need action.

Output:

Access pattern	Reads	Consistency	Cost
Customer's orders	gsi_customer query	eventual	O(customer's orders)
Refund-pending list	gsi_refund query	eventual	O(pending), not O(table)
Full order detail	base-table GetItem	strong (if needed)	1 read per item

Why this works — concept by concept:

• GSI as an alternate partition key — re-keying orders on customer_id provides an access path the base table (keyed on order_id) fundamentally cannot, without duplicating the table by hand.
• Sparse GSI — writing refund_pk only while an order is refund-pending keeps that index tiny, converting "find orders in a rare state" from an O(200M) scan into an O(few-thousand) query.
• Projection choice — INCLUDE on the customer GSI carries just the attributes the list view shows; KEYS_ONLY on the sparse GSI keeps its per-item write cost minimal since it is only a routing index.
• Eventual consistency — both GSIs are eventual-only, which is fine for list views; anything needing the authoritative latest value reads the base table with strong consistency.
• Cost — each GSI adds write amplification (one extra write per indexed item) and storage for its projection, but turns two impossible-or-scan access patterns into O(result-size) queries. The sparse index is the key lever: its cost scales with the rare state, not the whole table.

Indexing
Topic — indexing
Secondary-index and query-path problems

Practice →

Database Topic — database Query-design and access-pattern problems

Practice →

---

4. DynamoDB Streams — change data capture

DynamoDB Streams is a 24-hour ordered change log — the native CDC tap for every item mutation

The mental model in one line: DynamoDB Streams is an ordered, 24-hour-retained log of every item-level change (INSERT, MODIFY, REMOVE) in a table, sharded so that all changes to a given partition key are strictly ordered, and consumable by Lambda triggers or the Kinesis Client Library — it is the mechanism that turns DynamoDB from a data island into a source of truth that fans out to search indexes, aggregate tables, and the analytics warehouse without ever polling or scanning the table. For a data engineer, Streams is the DynamoDB equivalent of a write-ahead log tail: it is how you build materialized views, replicate to other stores, and feed real-time CDC.

The primitives that matter.

• Stream record. One record per item-level change, carrying the eventName (INSERT / MODIFY / REMOVE), the item keys, and — depending on the view type — the new and/or old image of the item.
• 24-hour retention. Records live for 24 hours, then expire. Consumers must keep up or use the Kinesis Data Streams integration for longer retention (up to 365 days).
• Shards and ordering. The stream is partitioned into shards; all changes for a single partition key go to the same shard and are delivered in order. There is no global ordering across partition keys — only per-key.
• At-least-once delivery. A record can be delivered more than once (retries, resharding), so consumers must be idempotent.

Stream view types — choose what each record carries.

• KEYS_ONLY. Just the key attributes of the changed item. Smallest; the consumer must read the current item if it needs more.
• NEW_IMAGE. The entire item as it looks after the change. Ideal for replicating current state.
• OLD_IMAGE. The entire item as it looked before the change. Needed to compute deltas or capture what a REMOVE deleted.
• NEW_AND_OLD_IMAGES. Both — the richest and the usual choice for CDC and aggregation, since you can diff old vs new.

Ordering, delivery, and consumers.

• Lambda trigger. The simplest consumer: DynamoDB invokes your function with batches of records, tracks checkpoints, and retries on failure. Batch size, parallelization factor, and bisect-on-error are the tuning knobs.
• Kinesis adapter / KCL. For fan-out to multiple independent consumers or longer retention, route the stream through Kinesis Data Streams.
• Failure handling. Configure a bisect-on-function-error and an on-failure destination (SQS/SNS DLQ) so one poison record doesn't block a shard forever.

What interviewers listen for.

• Do you name the four view types and pick NEW_AND_OLD_IMAGES for CDC/aggregation? — required answer.
• Do you state "ordering is per partition key, not global"? — senior signal.
• Do you make consumers idempotent because delivery is at-least-once? — required answer.
• Do you know the 24-hour limit and reach for Kinesis when you need longer retention or multiple consumers? — senior signal.

Worked example — a Lambda stream processor that maintains a materialized aggregate

Detailed explanation. The canonical Streams use case is a materialized view: as orders are written, keep a running per-customer order count and total in a separate aggregate item. Walk through the Lambda that consumes NEW_AND_OLD_IMAGES and updates the aggregate idempotently.

• The trigger. Table orders with Streams on, view NEW_AND_OLD_IMAGES.
• The target. An aggregate item PK = CUST#42, SK = AGG holding order_count and total_cents.
• Idempotency. Because records can redeliver, track processed SequenceNumbers (or use conditional math) so a replay doesn't double-count.

Question. Write the Lambda handler that increments the customer aggregate on INSERT and decrements on REMOVE, safely under at-least-once delivery.

Input.

eventName	new image	old image	aggregate effect
INSERT	order, total 4200	—	count +1, total +4200
MODIFY	total 4500	total 4200	total +300
REMOVE	—	order, total 4500	count -1, total -4500

Code.

import boto3
tbl = boto3.resource("dynamodb").Table("orders")

def handler(event, _ctx):
    for rec in event["Records"]:
        ev   = rec["eventName"]                 # INSERT | MODIFY | REMOVE
        seq  = rec["dynamodb"]["SequenceNumber"]
        new  = rec["dynamodb"].get("NewImage", {})
        old  = rec["dynamodb"].get("OldImage", {})

        cust = (new or old)["customer_id"]["S"]
        new_total = int(new["total_cents"]["N"]) if new else 0
        old_total = int(old["total_cents"]["N"]) if old else 0

        d_count = (1 if ev == "INSERT" else -1 if ev == "REMOVE" else 0)
        d_total = new_total - old_total          # works for all three events

        try:
            tbl.update_item(
                Key={"PK": f"CUST#{cust}", "SK": "AGG"},
                UpdateExpression=("ADD order_count :c, total_cents :t "
                                  "SET last_seq = :s"),
                ConditionExpression="attribute_not_exists(last_seq) OR last_seq < :s",
                ExpressionAttributeValues={":c": d_count, ":t": d_total, ":s": seq},
            )
        except tbl.meta.client.exceptions.ConditionalCheckFailedException:
            pass   # already applied this or a newer record -> idempotent skip

Step-by-step explanation.

1. Each record carries eventName, the SequenceNumber, and (with NEW_AND_OLD_IMAGES) both images, so the handler can compute the exact delta for count and total.
2. d_total = new_total - old_total is uniform across all three events: on INSERT old is 0, on REMOVE new is 0, on MODIFY it is the real difference.
3. The ADD update atomically increments the aggregate counters — DynamoDB's atomic counters avoid read-modify-write races between concurrent invocations.
4. The ConditionExpression on last_seq makes the update idempotent: a redelivered (older-or-equal SequenceNumber) record fails the condition and is skipped, so at-least-once delivery never double-counts.
5. On ConditionalCheckFailedException the handler swallows the error — that is the expected path for a duplicate, not a failure.

Output.

Event stream	order_count	total_cents
INSERT 4200	1	4200
MODIFY 4200→4500	1	4500
REMOVE 4500	0	0
(INSERT 4200 redelivered)	0	0 (skipped)

Rule of thumb. Build stream consumers as idempotent delta-appliers: use atomic ADD for counters, compute deltas from NEW_AND_OLD_IMAGES, and guard with a monotonic SequenceNumber condition so redelivery is a no-op. Never assume exactly-once — DynamoDB Streams is at-least-once.

Worked example — choosing the right stream view type

Detailed explanation. The view type you pick determines both what your consumer can do and how much data crosses the stream. Walk through matching view type to three consumer goals.

• Replicate current state to OpenSearch. You need the full post-change item → NEW_IMAGE.
• Maintain deltas / audit before-and-after. You need both images → NEW_AND_OLD_IMAGES.
• Just invalidate a cache by key. You need only the key → KEYS_ONLY.

Question. For each consumer, choose the minimal view type that satisfies it and justify the cost trade-off.

Input.

Consumer goal	Needs new image?	Needs old image?	View type
Replicate to search index	yes	no	NEW_IMAGE
Delta aggregate / audit	yes	yes	NEW_AND_OLD_IMAGES
Cache invalidation	no	no	KEYS_ONLY

Code.

import boto3
ddb = boto3.client("dynamodb")

# Enable streams with the richest view for a CDC/aggregation table
ddb.update_table(
    TableName="orders",
    StreamSpecification={"StreamEnabled": True,
                         "StreamViewType": "NEW_AND_OLD_IMAGES"},
)

# For a table whose only consumer replicates state, NEW_IMAGE is enough:
# StreamViewType="NEW_IMAGE"
# For a table whose only consumer busts a cache, KEYS_ONLY is cheapest:
# StreamViewType="KEYS_ONLY"

Step-by-step explanation.

1. NEW_IMAGE ships the full item after the change — exactly what a search-index replicator needs to upsert the current document, and nothing more.
2. NEW_AND_OLD_IMAGES ships both, which is mandatory for anything computing a delta (aggregates) or auditing what changed, and it is the only view that tells you what a REMOVE deleted (via the old image).
3. KEYS_ONLY ships only the keys — smallest payload — and is perfect when the consumer just needs "this key changed, go invalidate it."
4. Richer views cost more stream throughput and Lambda payload size, so pick the minimal view that satisfies every consumer of that table.
5. If different consumers need different views, either standardize on the richest they collectively require, or route through Kinesis so each consumer reads independently.

Output.

View type	Payload	Enables
KEYS_ONLY	keys only	cache invalidation
NEW_IMAGE	post-change item	state replication
OLD_IMAGE	pre-change item	delete capture, deltas
NEW_AND_OLD_IMAGES	both	CDC, aggregation, audit

Rule of thumb. Default to NEW_AND_OLD_IMAGES for anything CDC-like — it is the only view that lets you diff and that captures deletes — and drop to NEW_IMAGE or KEYS_ONLY only when you have proven a single consumer needs less.

Worked example — Kinesis Data Streams for longer retention and fan-out

Detailed explanation. Native DynamoDB Streams retain 24 hours and are designed around a single Lambda consumer per shard. When you need multiple independent consumers, longer retention, or integration with the broader Kinesis/Flink ecosystem, route change data through Kinesis Data Streams instead. Walk through the trade-off.

• The limit. 24-hour retention; if a downstream backfill needs a week of history, native streams can't provide it.
• The fix. Enable Kinesis Data Streams for DynamoDB — same change records, but into a Kinesis stream with configurable retention (up to 365 days) and multiple consumers via enhanced fan-out.
• The caveat. Kinesis for DynamoDB is at-least-once and not guaranteed strictly ordered across records the way native streams are per shard, so idempotency matters even more.

Question. Decide between native Streams and Kinesis for a table that must feed both a real-time aggregator and a nightly warehouse backfill needing 7 days of replay.

Input.

Requirement	Native Streams	Kinesis for DynamoDB
Retention	24 h	up to 365 days
Multiple consumers	1 primary (Lambda)	many (enhanced fan-out)
Ordering	strict per partition key	best-effort
Replay window	24 h	configurable

Code.

import boto3
ddb = boto3.client("dynamodb")

# Route DynamoDB change records into a Kinesis Data Stream (long retention + fan-out)
ddb.enable_kinesis_streaming_destination(
    TableName="orders",
    StreamArn="arn:aws:kinesis:us-east-1:123456789012:stream/orders-cdc",
)

# Consumers now read from Kinesis:
#   - real-time aggregator (Lambda / KCL)
#   - nightly warehouse loader replaying up to 7 days

Step-by-step explanation.

1. Native Streams are ideal for a single, keep-up consumer with strict per-key ordering and a 24-hour safety window.
2. When a second, independent consumer (the warehouse backfill) needs its own cursor and a 7-day replay window, native Streams' 24-hour retention and single-primary-consumer model no longer fit.
3. enable_kinesis_streaming_destination mirrors the same change records into a Kinesis Data Stream, where retention is configurable (up to 365 days) and enhanced fan-out gives each consumer its own throughput.
4. The trade-off: Kinesis for DynamoDB relaxes strict ordering guarantees, so consumers must be idempotent and tolerate occasional reordering — reconcile by SequenceNumber/timestamp.
5. A common production shape is both: native Streams → Lambda for the low-latency aggregate, and Kinesis → Firehose → S3 for durable, replayable history feeding the warehouse.

Output.

Consumer	Source	Why
Real-time aggregate	native Streams → Lambda	strict order, low latency
7-day warehouse backfill	Kinesis → Firehose → S3	long retention, replay
Multiple analytics readers	Kinesis enhanced fan-out	independent cursors

Rule of thumb. Use native Streams for a single low-latency consumer needing strict per-key order and a 24-hour window; switch to (or add) Kinesis Data Streams when you need long retention, replay beyond a day, or several independent consumers. Either way, make every consumer idempotent.

Data engineering interview question on DynamoDB Streams

A senior interviewer might ask: "Your orders table must, in near-real time, keep a per-customer 'lifetime value' aggregate and replicate every order into OpenSearch for full-text search. Design the Streams setup — view type, consumers, ordering guarantees, idempotency, and failure handling — and explain how you'd replay the last 12 hours after a bad deploy."

Solution Using Streams → Lambda with idempotent aggregation and a DLQ

import boto3
tbl = boto3.resource("dynamodb").Table("orders")
search = boto3.client("opensearch")   # illustrative client

# Table configured with StreamViewType = NEW_AND_OLD_IMAGES
def handler(event, _ctx):
    for rec in event["Records"]:
        ev  = rec["eventName"]
        seq = rec["dynamodb"]["SequenceNumber"]
        new = rec["dynamodb"].get("NewImage", {})
        old = rec["dynamodb"].get("OldImage", {})
        cust = (new or old)["customer_id"]["S"]

        # 1. Maintain lifetime-value aggregate (idempotent atomic add)
        delta = (int(new.get("total_cents", {}).get("N", 0)) -
                 int(old.get("total_cents", {}).get("N", 0)))
        try:
            tbl.update_item(
                Key={"PK": f"CUST#{cust}", "SK": "LTV"},
                UpdateExpression="ADD ltv_cents :d SET last_seq = :s",
                ConditionExpression="attribute_not_exists(last_seq) OR last_seq < :s",
                ExpressionAttributeValues={":d": delta, ":s": seq},
            )
        except tbl.meta.client.exceptions.ConditionalCheckFailedException:
            pass  # duplicate/older record -> skip

        # 2. Replicate to search (upsert on INSERT/MODIFY, delete on REMOVE)
        doc_id = new.get("order_id", old.get("order_id"))["S"]
        if ev == "REMOVE":
            delete_from_search(doc_id)      # idempotent delete
        else:
            upsert_to_search(doc_id, new)   # idempotent upsert by id

Step-by-step trace.

Step	Mechanism	Guarantee
View type	NEW_AND_OLD_IMAGES	delta + delete capture
Ordering	per customer_id shard	LTV updates ordered per customer
Aggregate	atomic ADD + last_seq guard	idempotent under redelivery
Search replicate	upsert/delete by order_id	idempotent by document id
Poison record	bisect-on-error + SQS DLQ	one bad record doesn't wedge shard
Replay 12 h	re-point iterator within 24 h retention	reprocess without data loss

Walking it: every order mutation flows through one Lambda; the LTV aggregate uses an atomic ADD guarded by a monotonic last_seq, so a replay of the last 12 hours (well within the 24-hour retention) re-applies nothing it already saw; the search replication is keyed by order_id, so upserts and deletes are naturally idempotent; and a poison record is bisected out to a dead-letter queue instead of blocking its shard forever.

Output:

Concern	Solution	Result
Double counting	last_seq condition	exactly-once effect on LTV
Delete propagation	REMOVE → search delete	search stays consistent
Bad deploy	replay within 24 h	no data loss
Poison message	DLQ + bisect	shard keeps flowing
Latency	Lambda trigger	sub-second in steady state

Why this works — concept by concept:

• NEW_AND_OLD_IMAGES — carrying both images lets the aggregator compute an exact delta and lets the replicator know what a REMOVE deleted, which is impossible with NEW_IMAGE alone.
• Per-partition-key ordering — because all of a customer's order changes land on one shard in order, the LTV aggregate never applies a stale value out of sequence.
• Idempotent consumers — the atomic ADD guarded by last_seq, plus upsert/delete-by-id for search, make at-least-once delivery safe: replays and retries are no-ops.
• Failure isolation — bisect-on-error plus an SQS dead-letter destination stops a single un-processable record from stalling an entire shard, the classic Streams outage.
• Cost — one Lambda invocation per batch, atomic counter updates, and idempotent search writes; replay is free within the 24-hour window. The eliminated cost is polling or scanning the table to detect changes — Streams pushes them at O(changes), not O(table).

Data processing
Topic — data-processing
Streaming and change-data-capture problems

Practice →

Database Topic — database Materialized-view and aggregation problems

Practice →

---

5. S3 export and warehouse integration

Export to S3 is the zero-RCU, point-in-time bridge from DynamoDB to your analytics warehouse

The mental model in one line: DynamoDB's "export to S3" feature writes a consistent point-in-time snapshot of a table into S3 — as DynamoDB JSON or Amazon Ion, partitioned into files — without consuming any read capacity and without a Scan, by reading from the continuous backup (PITR) rather than the live table, so it is the correct way to move DynamoDB data into a columnar warehouse for analytics, while Streams handles the real-time incremental path. For a data engineer, the rule is blunt: never Scan a production DynamoDB table for analytics — export it to S3 and query it there.

The primitives that matter.

• Point-in-time export. Export reads from PITR (point-in-time recovery) continuous backups, so it never touches live table throughput — zero RCU consumed — and produces a transactionally-consistent snapshot as of a chosen timestamp.
• PITR is a prerequisite. You must enable PITR on the table before you can export; without it, export is unavailable.
• Output format. Files land in S3 as DynamoDB JSON (typed, e.g. {"total_cents":{"N":"4200"}}) or Amazon Ion, gzip-compressed, partitioned across many objects with a manifest.
• No Scan, no impact. Because export bypasses the live table, exporting a 10 TB table doesn't throttle production the way a Scan would.

Full export vs incremental export.

• Full export. A complete snapshot of the table as of a point in time. Use it to bootstrap the warehouse or take periodic full refreshes.
• Incremental export. Exports only the items that changed within a specified time window (backed by PITR), so after the initial full export you sync deltas cheaply instead of re-exporting everything.
• The pattern. One full export to seed, then scheduled incremental exports (e.g. hourly/daily windows) to keep the warehouse current — a batch CDC that complements or replaces the Streams path for analytics.

Query engines downstream.

• Glue + Athena. Point a Glue crawler at the export prefix to infer a schema, then query the DynamoDB JSON with Athena SQL — serverless, pay-per-scan.
• Redshift Spectrum / Spark. External tables over the S3 export let Redshift or Spark join DynamoDB data with the rest of the warehouse.
• Unpacking DynamoDB JSON. The typed format ({"N":"..."}, {"S":"..."}) usually needs a flattening step — Athena's json_extract, a Glue transform, or a Spark UDF — to become clean columnar rows.

What interviewers listen for.

• Do you say "never Scan for analytics — export to S3"? — required answer.
• Do you know export consumes zero RCU and requires PITR? — senior signal.
• Do you distinguish full vs incremental export and design a seed-then-delta pipeline? — senior signal.
• Do you contrast export (batch snapshots) with Streams (real-time CDC) and pick per requirement? — senior signal.

Worked example — full export to S3 then query with Athena

Detailed explanation. The bootstrap step for any DynamoDB→warehouse pipeline is a full point-in-time export, followed by a Glue crawler and Athena query. Walk through it end to end.

• Prerequisite. PITR enabled on the table.
• The export. export-table-to-point-in-time to an S3 prefix, DynamoDB JSON, gzip.
• The query. Glue crawler → Athena external table → SQL that flattens the typed JSON.

Question. Export the orders table to S3 and write the Athena query that returns total revenue by status.

Input.

Step	Tool	Output
Enable PITR	DynamoDB	continuous backup
Export	export-table-to-point-in-time	S3 DynamoDB JSON
Catalog	Glue crawler	Athena table
Query	Athena SQL	revenue by status

Code.

# 1. Ensure PITR is on (one-time)
aws dynamodb update-continuous-backups \
  --table-name orders \
  --point-in-time-recovery-specification PointInTimeRecoveryEnabled=true

# 2. Full point-in-time export to S3 (zero RCU on the live table)
aws dynamodb export-table-to-point-in-time \
  --table-arn arn:aws:dynamodb:us-east-1:123456789012:table/orders \
  --s3-bucket my-lake \
  --s3-prefix exports/orders/ \
  --export-format DYNAMODB_JSON

-- 3. After a Glue crawler catalogs exports/orders/, query in Athena.
--    DynamoDB JSON stores typed scalars, so unwrap Item.<attr>.<type>.
SELECT
    Item.status.S                              AS status,
    SUM(CAST(Item.total_cents.N AS BIGINT))    AS revenue_cents,
    COUNT(*)                                    AS order_count
FROM orders_export
GROUP BY Item.status.S
ORDER BY revenue_cents DESC;

Step-by-step explanation.

1. PITR must be enabled first; it is what the export reads from, which is why the export costs zero live read capacity.
2. export-table-to-point-in-time writes a consistent snapshot to the S3 prefix as gzipped DynamoDB JSON, partitioned across many objects with a manifest describing them.
3. A Glue crawler infers the schema of the export (a nested Item struct whose fields are typed maps like status.S and total_cents.N) and registers an Athena table.
4. The Athena query unwraps the typed JSON — Item.total_cents.N is a string inside the DynamoDB JSON, so CAST(... AS BIGINT) turns it into a number for aggregation.
5. The whole analytical query runs against S3, never against DynamoDB, so it cannot throttle production no matter how heavy the aggregation.

Output.

status	revenue_cents	order_count
shipped	128,400,000	30,200
pending	9,150,000	2,410
refunded	1,020,000	260

Rule of thumb. Bootstrap the warehouse with a full point-in-time export (PITR required, zero RCU), catalog it with Glue, and always CAST the typed DynamoDB-JSON scalars when querying in Athena. The analytics never touch the live table — that is the whole point.

Worked example — incremental export for ongoing sync

Detailed explanation. Re-exporting a large table in full every hour is wasteful. Incremental export ships only the items changed in a time window, so after the initial seed you sync cheap deltas. Walk through a daily incremental pipeline.

• The seed. One full export (previous example).
• The delta. Daily incremental export for the previous 24 hours.
• The merge. Downstream MERGE/upsert applies inserts, updates, and deletes into the warehouse table by key.

Question. Configure a daily incremental export and describe how the warehouse applies the delta idempotently.

Input.

Parameter	Value
Export type	INCREMENTAL_EXPORT
Window	previous 24 h
Format	DynamoDB JSON
Merge key	order_id

Code.

# Daily incremental export: only items changed in the window [from, to)
aws dynamodb export-table-to-point-in-time \
  --table-arn arn:aws:dynamodb:us-east-1:123456789012:table/orders \
  --s3-bucket my-lake --s3-prefix exports/orders/incr/ \
  --export-type INCREMENTAL_EXPORT \
  --incremental-export-specification \
      ExportFromTime=2026-09-04T00:00:00Z,ExportToTime=2026-09-05T00:00:00Z,ExportViewType=NEW_AND_OLD_IMAGES \
  --export-format DYNAMODB_JSON

-- Warehouse merge: apply the incremental delta by key (Redshift/Snowflake-style)
MERGE INTO analytics.orders AS tgt
USING staging.orders_incr AS src
ON tgt.order_id = src.order_id
WHEN MATCHED AND src.op = 'REMOVE' THEN DELETE
WHEN MATCHED                        THEN UPDATE SET
     status = src.status, total_cents = src.total_cents, updated_at = src.updated_at
WHEN NOT MATCHED                    THEN INSERT (order_id, status, total_cents, updated_at)
     VALUES (src.order_id, src.status, src.total_cents, src.updated_at);

Step-by-step explanation.

1. INCREMENTAL_EXPORT with an explicit [ExportFromTime, ExportToTime) window exports only items mutated in that period, using PITR — again zero live RCU.
2. ExportViewType=NEW_AND_OLD_IMAGES includes both images so the delta carries enough to distinguish updates from deletes downstream.
3. The warehouse MERGE keys on order_id: matched-and-removed rows are deleted, matched rows are updated, and new rows are inserted — one statement handles all three change types.
4. Because the merge is keyed and deterministic, re-running the same incremental window is idempotent — a retried load produces the same warehouse state.
5. This seed-then-daily-delta pattern is batch CDC: cheaper than full re-exports, and it keeps the warehouse within a day of the source without ever scanning the table.

Output.

Change in window	Delta row op	Warehouse effect
New order	INSERT	row inserted
Status change	MODIFY	row updated
Cancelled order	REMOVE	row deleted
Unchanged order	(absent)	untouched

Rule of thumb. Seed once with a full export, then run scheduled incremental exports and a keyed MERGE. Include old-and-new images so deletes propagate, and key the merge so retries are idempotent — that is batch CDC without a single Scan.

Worked example — Streams → Firehose vs export: pick the right path

Detailed explanation. Data engineers must choose between two DynamoDB→analytics paths: real-time via Streams/Kinesis Firehose, or batch via S3 export. They are complementary, and picking the wrong one wastes money or misses SLAs. Walk through the decision.

• Real-time path. Streams → Kinesis Firehose → S3 (or Redshift), delivering changes continuously with seconds-to-minutes latency.
• Batch path. Full + incremental S3 export on a schedule, delivering consistent snapshots with hours latency but zero RCU and trivial ops.
• The choice. Freshness requirement vs cost and operational simplicity.

Question. For a dashboard needing sub-minute freshness and a monthly finance report needing exact consistency, pick a path for each.

Input.

Consumer	Freshness need	Consistency need	Path
Ops dashboard	sub-minute	eventual OK	Streams → Firehose → S3
Finance report	monthly	strong point-in-time	full S3 export

Code.

Decision guide — DynamoDB to analytics
=======================================

Need sub-minute freshness?
   yes -> Streams (NEW_AND_OLD_IMAGES) -> Kinesis Firehose -> S3/Redshift
          + pay per change, real-time, must handle idempotency

Need periodic consistent snapshots / heavy historical scans?
   yes -> S3 export (full to seed, incremental to sync)
          + zero RCU, point-in-time consistent, batch latency

Need both?
   run both: Streams for the live dashboard, export for the
   consistent warehouse tables and backfills.

Step-by-step explanation.

1. A dashboard that must reflect changes within a minute needs the real-time path: Streams push each mutation to Firehose, which buffers and writes to S3 or Redshift continuously.
2. Firehose delivery is at-least-once and micro-batched, so the dashboard's loader must dedupe by key — same idempotency discipline as any stream consumer.
3. A monthly finance report needs exact, consistent numbers as of a timestamp, not the freshest possible — a full point-in-time export gives a transactionally-consistent snapshot with zero impact on the live table.
4. Export's batch latency (hours) is irrelevant for a monthly report but disqualifying for a live dashboard; conversely, running Streams to satisfy a monthly report would pay for real-time you don't need.
5. Mature stacks run both: Streams/Firehose for low-latency views, and scheduled exports for the authoritative, replayable warehouse tables.

Output.

Path	Latency	RCU cost	Best for
Streams → Firehose	seconds–minutes	per change	live dashboards
Full export	hours	zero	consistent snapshots, backfills
Incremental export	hours	zero	scheduled warehouse sync

Rule of thumb. Match the path to the freshness SLA: Streams/Firehose when minutes matter, S3 export when consistency and cost matter and hours are fine. They are complementary — run both when you need real-time views and an authoritative warehouse.

Data engineering interview question on S3 export

A senior interviewer might ask: "You must load a 5 TB DynamoDB orders table into Redshift for analytics without impacting the production service, then keep it within a day fresh. Walk me through the initial load, the ongoing sync, why you would not Scan, and how you handle the typed DynamoDB JSON and deletes."

Solution Using PITR full export, Glue catalog, and daily incremental exports

# 1. Enable PITR (prerequisite for any export)
aws dynamodb update-continuous-backups --table-name orders \
  --point-in-time-recovery-specification PointInTimeRecoveryEnabled=true

# 2. Initial FULL export — 5 TB, zero RCU, consistent point-in-time
aws dynamodb export-table-to-point-in-time \
  --table-arn arn:aws:dynamodb:us-east-1:123456789012:table/orders \
  --s3-bucket lake --s3-prefix orders/full/ --export-format DYNAMODB_JSON

# 3. Daily INCREMENTAL export — only yesterday's changes, with old+new images
aws dynamodb export-table-to-point-in-time \
  --table-arn arn:aws:dynamodb:us-east-1:123456789012:table/orders \
  --s3-bucket lake --s3-prefix orders/incr/ --export-type INCREMENTAL_EXPORT \
  --incremental-export-specification \
     ExportFromTime=2026-09-04T00:00:00Z,ExportToTime=2026-09-05T00:00:00Z,ExportViewType=NEW_AND_OLD_IMAGES \
  --export-format DYNAMODB_JSON

-- 4. Redshift Spectrum external tables over the S3 exports, then MERGE the delta in.
--    Flatten typed DynamoDB JSON on the way in.
INSERT INTO staging.orders_incr
SELECT
    json_extract_path_text(item, 'order_id', 'S')            AS order_id,
    json_extract_path_text(item, 'status', 'S')              AS status,
    CAST(json_extract_path_text(item, 'total_cents', 'N') AS BIGINT) AS total_cents,
    metadata_op                                              AS op   -- INSERT/MODIFY/REMOVE
FROM spectrum.orders_incr_raw;

MERGE INTO analytics.orders AS tgt
USING staging.orders_incr AS src ON tgt.order_id = src.order_id
WHEN MATCHED AND src.op = 'REMOVE' THEN DELETE
WHEN MATCHED THEN UPDATE SET status = src.status, total_cents = src.total_cents
WHEN NOT MATCHED THEN INSERT (order_id, status, total_cents)
     VALUES (src.order_id, src.status, src.total_cents);

Step-by-step trace.

Step	Mechanism	Property
PITR	continuous backup	enables export; zero RCU
Full export	point-in-time snapshot	5 TB, no production impact
Glue / Spectrum	external tables over S3	query without loading first
Flatten	json_extract + CAST	typed DynamoDB JSON → columns
Daily incremental	changed-items export	cheap ongoing sync
MERGE by order_id	keyed upsert/delete	idempotent, handles deletes

Walking it: the 5 TB initial load is a point-in-time export read from PITR, so production sees zero read load and no Scan; Spectrum queries the S3 files directly; a nightly incremental export ships only the day's changes with old-and-new images; and a keyed MERGE applies inserts, updates, and deletes idempotently. Freshness lands within a day, and the production table is never scanned.

Output:

Metric	Scan approach (rejected)	Export approach
Production impact	heavy RCU, throttling risk	zero RCU
Initial 5 TB load	days, at capacity cost	one export, no impact
Ongoing sync	re-scan	daily incremental delta
Delete handling	invisible without extra work	REMOVE rows in delta
Consistency	non-atomic	point-in-time consistent

Why this works — concept by concept:

• Point-in-time export — reading from PITR rather than the live table gives a consistent snapshot at zero read capacity, which is why a 5 TB export never throttles production, unlike a Scan.
• Full then incremental — one full export seeds the warehouse; scheduled incremental exports ship only changed items, making ongoing sync cheap and keeping freshness within the export cadence.
• NEW_AND_OLD_IMAGES on incrementals — including both images lets the downstream MERGE distinguish updates from deletes, so cancellations propagate instead of silently lingering.
• Keyed MERGE — upserting/deleting by order_id makes each load idempotent, so a retried export window converges to the same warehouse state.
• Cost — export cost scales with data exported (full once, deltas thereafter), all at zero live RCU, versus a Scan that costs O(table) RCU every run and risks throttling. Analytics run on S3/Redshift, never on the operational table.

Data processing
Topic — data-processing
Warehouse-load and batch-CDC problems

Practice →

Indexing
Topic — indexing
Partitioning and file-layout problems

Practice →

---

Cheat sheet — DynamoDB for data engineers recipes

• Partition-key spreading rule. Choose the partition key for high cardinality and even access distribution first, correctness of naming second. Reject low-cardinality attributes (status, region, type) as partition keys — they hot-spot. If a single key value can attract a large fraction of traffic, shard it with a suffix (device_id#yyyy-mm, OPEN#<n>). Sort key exists to make your most common range read a native Query with begins_with / between.
• Capacity math. RCU = ceil(item_kb / 4) × reads/sec, halved for eventual consistency; WCU = ceil(item_kb / 1) × writes/sec. On-demand (PAY_PER_REQUEST) is the 2026 default for spiky/new workloads; provisioned + auto-scaling wins only at steady, predictable, high volume. Keep items lean — a 40 KB item costs 10× the read units of a 4 KB one.
• 400 KB item limit. Never inline unbounded text/binary. Offload the blob to S3, keep s3_key + byte_size in the item. Small items are cheaper to read, faster to stream, and delay the 10 GB item-collection split.
• Single-table workflow. List every access pattern before the schema; map each to a Query/GetItem (a pattern that needs a Scan is a design bug). Use generic keys PK/SK with typed value prefixes (USER#123, ORDER#456), a type attribute on every item, and item collections so one Query returns a whole aggregate.
• Adjacency-list relationships. Model edges as items carrying both the base key (PK=ORDER#456, SK=ITEM#sku-9) and GSI keys (GSI1PK=SKU#sku-9, GSI1SK=ORDER#456). One write maintains both traversal directions; the adjacency list is the join.
• GSI vs LSI. GSI: any keys, add anytime, own throughput, eventual-only, up to 20/table — the default. LSI: same partition key + different sort key, must be created at table creation, strongly-consistent, shares throughput, 10 GB collection cap — rare. Prefer a GSI unless you specifically need strong consistency on an alternate sort key.
• GSI projections. KEYS_ONLY for routing indexes, INCLUDE [attrs] for list views (project exactly what you read), ALL only when the GSI must serve reads without touching the base table. Every projected attribute adds write amplification.
• Sparse GSI. Write the GSI key only while an item is in the target state and REMOVE it when the state ends, so "find the few items in state X" is O(matching) not O(table). Watch for a hot GSI partition when the key is a constant — shard it.
• Streams view types. KEYS_ONLY = cache-bust, NEW_IMAGE = state replication, OLD_IMAGE = delete/delta capture, NEW_AND_OLD_IMAGES = CDC/aggregation default. Ordering is strict per partition key (per shard), not global. 24-hour retention.
• Stream consumer contract. Delivery is at-least-once → consumers must be idempotent. Use atomic ADD counters guarded by a monotonic last_seq/SequenceNumber, upsert/delete by natural id for replication, bisect-on-error + a DLQ for poison records. Reach for Kinesis Data Streams when you need >24 h retention, replay, or multiple independent consumers.
• S3 export basics. Enable PITR (prerequisite), then export-table-to-point-in-time reads from continuous backups at zero RCU and never Scans. Output is gzipped DynamoDB JSON / Ion, partitioned with a manifest. Seed with a full export; keep fresh with INCREMENTAL_EXPORT windows using NEW_AND_OLD_IMAGES so deletes propagate.
• Export → warehouse. Catalog the S3 export with Glue; query via Athena / Redshift Spectrum / Spark; flatten typed DynamoDB JSON with json_extract + CAST. Apply incremental deltas with a keyed MERGE (REMOVE → DELETE, matched → UPDATE, unmatched → INSERT) so loads are idempotent. Never Scan a production table for analytics — export it.

Frequently asked questions

What is DynamoDB in one sentence for a data engineer?

DynamoDB is a fully managed, serverless key-value and document database that delivers single-digit-millisecond performance at any scale by hashing items onto physical partitions via a partition key and refusing the relational conveniences — no joins, no ad-hoc WHERE, no add-an-index-later — that would break that performance guarantee. For a data engineer it is usually a source: a table you model around fixed access patterns, tap for change data via DynamoDB Streams, and drain into a warehouse via S3 export. The whole discipline is designing keys and indexes up front so every important query is an O(result-size) Query, never an O(table) Scan.

Partition key vs sort key — what is the difference?

The partition key (hash key) determines which physical partition an item lives on; DynamoDB hashes its value to spread items across the storage fleet, and you must supply its exact value to read or Query efficiently. The sort key (range key) is optional and determines the order of items within a partition — items sharing a partition key are stored sorted by sort key, which is what makes range conditions like begins_with, between, and > cheap. Together (partition key, sort key) form a composite primary key that must be unique. Rule of thumb: pick the partition key for even distribution and high cardinality; design the sort key so your most common "give me a range of related items" access pattern is a single native Query.

What is single-table design and why is it recommended?

Single-table design stores multiple entity types — users, orders, line items — in one DynamoDB table using generic key attributes (PK, SK) whose values carry entity-type prefixes (USER#123, ORDER#456), so related items share a partition and can be fetched together in a single Query. It is recommended because DynamoDB has no joins: co-locating related items in one item collection lets you retrieve an entire aggregate (a user plus their orders plus their addresses) in one round trip instead of N queries across N tables. The catch is that you must enumerate every access pattern before creating the table and denormalize deliberately, maintaining consistency across duplicated data at write time. Done right, it delivers complex reads at single-digit-millisecond latency; done wrong (entity-first, like a relational schema) it forces expensive Scans.

GSI vs LSI — when do I use each?

Use a GSI (Global Secondary Index) for almost everything: it can be keyed on any attributes, added at any time, has its own throughput, and lets you query the same data along a partition key the base table doesn't support — at the cost of being eventually consistent only. Use an LSI (Local Secondary Index) only when you need strongly-consistent reads on an alternate sort key within the same partition key, and only if you can create it at table-creation time (LSIs cannot be added later), accepting that it shares the base table's throughput and imposes a 10 GB item-collection limit. In 2026, the honest default is "reach for a GSI"; LSIs are a niche tool for the specific strong-consistency-on-a-second-sort-key case.

How do DynamoDB Streams enable CDC?

DynamoDB Streams publishes an ordered, 24-hour-retained log of every item-level change (INSERT, MODIFY, REMOVE), sharded so that all changes for a given partition key are delivered in order, with each record optionally carrying the new and/or old image of the item. That is textbook change data capture: a consumer (a Lambda trigger or a Kinesis Client Library app) reads the change records and fans them out to search indexes, materialized aggregate tables, caches, or the analytics warehouse — all without polling or scanning the source table. Because delivery is at-least-once, consumers must be idempotent (atomic counters guarded by SequenceNumber, upsert/delete by natural id). When you need more than 24 hours of retention, replay, or multiple independent consumers, route the stream through Kinesis Data Streams for DynamoDB, which offers up to 365 days retention and enhanced fan-out.

How do I get DynamoDB data into a warehouse?

Two complementary paths. For batch analytics, use S3 export: enable PITR, then export-table-to-point-in-time writes a consistent point-in-time snapshot to S3 as DynamoDB JSON at zero read capacity and without a Scan; catalog it with Glue and query via Athena, Redshift Spectrum, or Spark, flattening the typed JSON with json_extract + CAST. Seed with one full export, then keep the warehouse fresh with scheduled incremental exports (using NEW_AND_OLD_IMAGES so deletes propagate) applied via a keyed MERGE. For real-time needs, use DynamoDB Streams → Kinesis Firehose → S3/Redshift, delivering changes with seconds-to-minutes latency. The cardinal rule: never Scan a production table for analytics — export it to S3 and query it there, and add the Streams path only when sub-minute freshness is actually required.

Practice on PipeCode

• Drill the database practice library → for the key-design, single-table, and query-modeling problems senior interviewers love to hand DynamoDB candidates.
• Sharpen your index intuition on the indexing practice library → for GSI vs LSI, sparse indexes, inverted indexes, and projection trade-offs.
• Rehearse the pipeline side on the data-processing practice library → for streams, CDC, incremental loads, and DynamoDB-to-warehouse export patterns.
• Stack these against PipeCode's broader 450+ data-engineering catalogue to anchor the "model access patterns first, never Scan for analytics" instinct against real graded inputs.

Lock in DynamoDB modeling muscle memory

Docs explain the API. PipeCode drills explain the decision — when to shard a hot partition key, when single-table design beats one-table-per-entity, when a sparse GSI turns an O(table) scan into an O(few) query, when Streams beat polling, and when to export to S3 instead of scanning. Pipecode.ai is Leetcode for Data Engineering — pattern-first practice tuned for the production trade-offs senior data engineers actually face.

Practice database problems →
Practice data-processing problems →