How to design partition keys, sort keys and a serving layer when your data has two sources with different SLAs
The batch pipeline reads from consolidated gold tables stored as files on an object storage, S3 in this case, and a Spark-based job applies business rules to compute the updated state for each customer. Those rules live in a relational database, shared between both pipelines. Once processed, the results are bulk loaded into DynamoDB.
The stream pipeline consumes customer activity events from a managed Kafka cluster and a containerized consumer service enriches each event before writing to a second DynamoDB table in near real-time. The two pipelines never write to the same table. They converge only at read time, where a single service merges both into one API response.
This is a practical application of the lambda architecture pattern, but what matters here is what that pattern demands from the data modeling and serving layer, and that is what the next section addresses.
Why two tables
The instinct when modeling in DynamoDB is to reach for the single-table design. In this case, two reasons pull the tables apart.
The first is TTL. DynamoDB’s time-to-live is a table-level configuration, not item-level behavior you can selectively enable. The NRT table needs items to expire automatically after a few days, while the batch table does not; its lifecycle is controlled by the batch job itself, which rewrites the data on each run. Sharing a table would mean either applying TTL to records that should never expire or managing expiration manually through application logic.
The second is the write pattern. The batch pipeline performs a bulk load on a daily schedule, replacing all existing records. The NRT consumer writes continuously, one event at a time, with idempotency requirements that bulk loads do not need. These are different operational profiles, and mixing them in a single table introduces coupling between two pipelines that can be independent. It also sounds like a nightmare for scenarios where reprocessing might be needed.
Splitting the tables keeps each pipeline simple, isolated and easier to operate. The complexity of merging them is contained in the serving layer.
Modeling the tables
A DynamoDB table does not have a fixed schema in the traditional sense, since each item can have different attributes, but every item must have a primary key. That key can be a single attribute, the partition key, or a composite of two, the partition key plus a sort key. DynamoDB uses the partition key to distribute data across storage nodes, and when a sort key is present, it orders items within the same partition.
*The choice of key matters more than anything else in DynamoDB modeling, because it determines how data is distributed, how it is accessed and what queries are possible.
*
The NRT table
The NRT table has a narrower scope. It holds only what the stream pipeline produces: the most recent balance update per investment type per customer, written as deposit events arrive. There is no need to model the rest of the customer here, because the serving layer will combine this data with the batch table at read time.
The partition key is personId and the sort key is goalId. Every Kafka event carries the customer’s updated balance for one investment type, and the consumer writes it to the NRT table using PutItem. Because there can be at most one pending balance per investment type at any moment, newer events naturally overwrite older ones for the same (personId, goalId) pair. The serving layer can retrieve all pending balances for a customer in a single query.
{
"personId": "person-uuid",
"goalId": "SAVINGS_ACCOUNT",
"currentBalance": 370.00,
"status": "PENDING_CONFIRMATION",
"eventId": "event-uuid-123",
"updatedAt": "2026-05-16T14:23:00Z",
"ttl": 1747699380
}
The ttl attribute is a Unix timestamp telling DynamoDB when to delete this item automatically. That is how time-to-live works in DynamoDB: you designate an attribute to hold the expiration timestamp, enable TTL on the table pointing to that attribute and Dynamo handles deletion in the background. Items in the NRT table expire after five days, long enough for the batch pipeline to process the contribution and take ownership, short enough to prevent stale pending records from accumulating unnecessarily.
The updatedAt attribute carries the event timestamp from Kafka and plays a role in the merge logic: the serving layer compares it against the batch's lastUpdated attribute to decide whether the NRT snapshot is fresher than the batch view. If the batch has already caught up, the NRT value is ignored; otherwise it is used and the goal is displayed with a "pending confirmation" indicator.
Serving the data
The two tables exist to be merged. Every read from the API does the same thing: fetch the customer’s batch view, fetch any pending updates from the NRT table and combine them into a single response before returning it to the app.
The two reads are independent and can run in parallel. The first is a GetItem on the batch table, keyed by personId. It returns the customer profile and the consolidated state of every goal as of the last batch run. The second is a Query on the NRT table, also keyed by personId. It returns every pending balance update for that customer across all investment types, typically zero to three items.
batch_response = dynamodb.get_item(
TableName="CustomerBatch",
Key={"personId": person_id}
)
nrt_response = dynamodb.query(
TableName="CustomerNRT",
KeyConditionExpression="personId = :pid",
ExpressionAttributeValues={":pid": person_id}
)
batch_item = batch_response.get("Item", {})
pending_by_goal = {
item["goalId"]: item
for item in nrt_response.get("Items", [])
}
response_goals = []
for goal in batch_item.get("goals", []):
pending = pending_by_goal.get(goal["goalId"])
if pending and pending["updatedAt"] > batch_item["lastUpdated"]:
response_goals.append({
**goal,
"savedAmount": pending["currentBalance"],
"status": "PENDING_CONFIRMATION"
})
else:
response_goals.append(goal)
A subtle but important property of this design is that the API does not need to know which events have already been incorporated by the batch, nor does it need to track any history of pending updates. The timestamp comparison is enough and the TTL eventually removes the NRT item.
About the costs
The GetItem on the batch table consumes 1 RCU for a strongly consistent read or 0.5 RCU for an eventually consistent one on items under 4KB.
The Query on the NRT table consumes RCUs proportional to the total size of the items returned, rounded up to the nearest 4KB block. With at most 3 items per customer (one per investment type) and small payloads, this almost always lands within a single 4KB block, meaning 1 RCU strongly consistent or 0.5 RCU eventually consistent.
For this use case, eventually consistent reads are acceptable on both tables. The batch item is small enough to fit in a single read unit and the NRT query returns at most 3 small items per customer, so the total read cost per API request lands at roughly 1 RCU. This is a direct consequence of the modeling choices: bounded goal count, single-item batch view, one NRT item per investment type.
Closing thoughts
What makes this work in DynamoDB is the modeling, and most of it comes down to how you use partition keys and sort keys. They are not just identifiers, they are the access path. The partition key decides how data is distributed and which queries are cheap. The sort key decides how items are organized within a partition and which queries are even possible.
Sort keys can do much more than what is depicted here. They support range queries with operators like begins_with, between and inequality comparisons, which means you can model hierarchies, timelines or status transitions directly in the key. Composite sort keys like STATUS#ACTIVE#DATE#2026-05-16 let a single Query retrieve, sort and filter items in ways that would otherwise require a secondary index or a scan.
If you are working on something similar or have approached the same problem differently, I would love to hear about it.
SOCIAL SHARE CARD GENERATOR