Balancing Acts: DynamoDB as a Rewards Ledger
Goal
At Imprint, we are building rewarding ways to pay and drive customer engagement, acquisition, and brand loyalty. Central to our rewards platform is the ability to track earning of rewards and balances to ensure customers have access to their rewards and we can report back to our partner brands.
This post firstly goes over the business needs for retrieving and recording customer balances and rewards and the design of an optimized DynamoDB table schema that satisfied the business requirements. And then using optimistic locking to maintain the integrity of writing and reading the latest balance.
Dynamodb Table Design
Understanding the requirements and business needs for data access is crucial when designing our DynamoDB table schema to optimize performance for frequent and critical operations, effectively model different entities within a single table, and avoid costly refactoring.
The key access patterns we focused on were
- Retrieving the Latest Balance. The purpose is to allow customers to view rewards and balances, determine eligibility for rewards redemption, and calculate the balance following a reward event.
- Log Reward Entry and Update Balance. Reward events are recorded chronologically and in append-only fashion; the balance is updated to reflect each event. Reward and balance entities are combined to efficiently write the reward and its resulting balance as a single item in the table.
Schema Details
Example is in ascending created at order
Primary Table: the primary key is composed of the customer account ID (partition key), and reward events’ created_at timestamp (sort key). The partition represents the customer’s reward and balance and is sorted in the order the reward events occurred. To get a customer’s reward and balance history, query for the partition indexed by the customer’s account ID.
Active Attribute: an item has an “active” attribute if the item contains the latest balance and most recent reward item; otherwise the “active” attribute is omitted from the item. Each account partition only has one “active” row. Read and write capacities are directly related to the item size; unnecessary attributes are omitted from items rather than filled with default empty values. When a reward event occurs, the reward entity and active balance are written in one item, and the previous balance is inactivated by removing the active attribute.
Optimistic Locking
Optimistic locking strategy is employed to safeguard the integrity of the latest balance. The tradeoff is that transactions on stale balance have to be rolled back and retried; we are comfortable with the tradeoff since reward events for one account occur steadily. Optimistic locking is implemented using the TransactWriteItems API to atomically and simultaneously create a new active balance and inactivate the previous balance on the condition that it is still active (active attribute is true).
Should concurrent events arise and the latest balance is fetched by multiple processes, only one process will succeed in inactivating the previous balance and write a reward entry and new active balance. Other update attempts will fail the condition since the balance they aim to modify is already inactive (active attribute has been removed) and will retry.
Latest Balance Query
Maintaining a reliable balance history is essential because each new entry depends directly on the latest balance. Our initial query performs a strongly consistent read on the primary table partitioned on the customer account, sorted rewards by descending created at timestamp (sort key), and limited the result to the topmost item holding the latest balance.
However, the query occasionally returned no results and no errors. We suspected the inconsistency was due to new reward balance entries being written in tandem. Based on the understanding of database transactions, these writes were likely in the ‘prepare’ phase where conditions were being validated. The strongly consistent query detected these ‘prepare’ stage writes as part of its one-item limit but was later filtered out, leaving us with no results, since limit operation is applied before filter operation in queries. To confirm our suspicion, the query was limited to two items. When processing numerous concurrent reward events, occasionally only 1 item was returned which corroborated our theory that a prepared write was indeed picked up by the query but filtered out.
Since we can not prevent our query from never returning no balance, we set a strict requirement that every account needs a 0 amount initial balance before any rewards can be accrued. So when accruing rewards, an internal error is returned and request is retried if the query for latest balance returns no results.
Conclusion
The key takeaway is that since you define the indexes in DynamoDB tables, to efficiently use these indexes you first have to understand your critical access patterns. The design of the DynamoDB schema and queries was underpinned by firstly understanding the business requirements and the crucial data access patterns. As a result, we focused on reliably retrieving the latest customer balance in order to efficiently record reward and resulting balance and to ensure the integrity of the customer balance.