How to design partition keys, sort keys and a serving layer when your data has two sources with different SLAs

Batch pipelines are reliable, auditable, and built to handle complexity at scale, but they come with a cost: by the time data is consolidated, validated, and ready to serve, hours or days might have passed. For internal analytics that’s often fine, but for a customer staring at a mobile app wondering whether their action was registered, it is not.

This article is about a DynamoDB modeling problem that lives at that boundary. The case study below is fictional and designed to keep the focus on the technical problem rather than on any specific domain. Business rules and delays are simplified accordingly.

The core challenge is one that appears frequently in data-intensive systems: a batch pipeline owns the authoritative view of the data, but its consolidation cycle introduces a lag that makes the user-facing experience feel stale. The goal is to bridge that gap with a near-real-time layer without replacing the batch, without duplicating business logic, and without turning the serving layer into a consistency problem.

The Use Case

I've covered this use case in my previous articles about DynamoDB. Here’s a quick recap:

We’re a financial institution developing a new feature in our mobile app to promote our customers’ financial health, offering them monthly saving goals. As a reward for reaching these goals, the saved amount is automatically invested under special conditions and higher interest rates.

The goals are calculated in batch by a data platform and then loaded into the database to be displayed in the app. The only information the app writes to the database is which goal the customer chose.

For this article, we’re extending the use case. The batch pipeline remains the source of truth, but business needs a new capability: when a customer makes a deposit toward their goal, it should appear in the app within minutes, not the next day.

Each customer has a monthly saving goal per investment type: savings account, government bonds and a certificate of deposit. Each goal has a target amount and accumulates progress as the customer makes deposits over the course of the month. The batch pipeline computes the consolidated state of each goal daily and writes it to DynamoDB.

The catch is that a contribution is not immediately final; it goes through reconciliation across multiple systems before it can be considered confirmed. The new capability is about making contributions visible before the batch catches up and display them with a status of “pending confirmation.” If reconciliation fails, it disappears. If it succeeds, the batch pipeline will eventually pick it up and reflect it in the authoritative view. This gives us two data sources with different freshness and different levels of trust, both feeding the same API response. That is the problem this article addresses.

Solution diagram

The diagram below shows how the two pipelines are organized and where they converge.

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 batch table

The batch table holds the consolidated view of each customer: personal attributes, preferences, the goals they have chosen, the progress computed by the last batch run, and a few other fields that the API exposes to the mobile app. It is, effectively, the full customer profile + information about the goals the customer has already accomplished.

Every read starts with a customer. The API asks “what does this person look like right now?” and expects one consolidated response. Given that access pattern, there is no reason to spread the data across multiple items. The partition key is personId.

{
  "personId": "person-uuid",
  "name": "Ana Silva",
  "preferences": { "notifications": true, "language": "pt-BR" },
  "goals": [
    {
      "goalId": "SAVINGS_ACCOUNT",
      "targetAmount": 500.00,
      "savedAmount": 320.00,
      "status": "IN_PROGRESS",
      "specialRate": 0.08
    },
    {
      "goalId": "GOVERNMENT_BONDS",
      "targetAmount": 1500.00,
      "savedAmount": 0.00,
      "status": "NOT_STARTED",
      "specialRate": 0.12
    },
    {
      "goalId": "CERTIFICATE_OF_DEPOSIT",
      "targetAmount": 2000.00,
      "savedAmount": 2000.00,
      "status": "COMPLETED",
      "specialRate": 0.14
    }
  ],
  "lastUpdated": "2026-05-15T03:00:00Z"
}

There is no TTL on this table because nothing needs to expire passively between runs. The batch job owns the lifecycle of every item, rewriting records on each run.

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.