Financial Data Modeling - The Event Sourcing Pattern

NoSQL databases like DynamoDB are often chosen to power financial applications, because of their flexible schema, high throughput, and low latency performance characteristics. This article, the first in a series, will introduce common use cases, and will explore strategies for tracking events, ensuring correctness and consistency, and implementing business rules.

To start, let’s imagine we are consultants, hired by a startup bank to build an application to support purchases and payments made by their credit card service. We have been asked to propose a data model, and build a proof of concept showing our design in action. The bank explains that there are three main entities that are involved with tracking credit card swipes.

The bank’s regulators require the datastore use the Event Sourcing  pattern. With Event Sourcing, all changes to an account state are stored as a chronological sequence of immutable events, rather than just updating the latest current balance within a single record. The current account balance can be calculated dynamically by replaying the sequence of events from the beginning of the period. To avoid replaying potentially thousands of historical events every time the current balance is needed, the system can add up and store snapshots of the balance at regular intervals. The very latest account balance is required to ensure the account’s current monthly spending limit is not breached.

The bank has shared the following requirements with us:

As database engineers, we have decisions to make about how to structure our tables and data. More specifically, we need to choose the number of tables, each table’s key schema, whether any secondary indexes are needed and what key schemas will work best. But this is not all—we also need to choose the format for attribute values, especially those that will be used as keys for the table or any indexes.

For example: What’s the recommended format for dates in DynamoDB?

You can also store dates in the numerical Unix Epoch format, such as 1795991696. If you are using the DynamoDB TTL feature , you will designate an attribute of type Number and store the future-dated expiration time, without including milliseconds.

Possible Approaches
Let’s start with some simple thought experiments to see how our solution meets the requirements. Since we have the three entities of Accounts, Journal, and Ledger, we can create separate tables with these names in our dev account. We can start by considering the Journal table, since it will be the most active table. Every use of the credit card will be processed by a middle tier application server and stored as an event in this table.

The choice of Partition Key seems obvious. We can store the AccountID (card ID) as the partition key.  But what about the Sort Key?  If we want an immutable log of events, stored in chronological order, it would seem that the sort key could be the timestamp of the event.

However, multiple serious issues exist with this strategy:

Events could arrive out of order, as shown here:

We have a requirement to prevent past-dated events from being added to the Journal, as a fraud defense. All events should be added to the end of the Journal, to make the calculation of current balance easier.

What we really need is a way to track events, sequenced in the exact order they are received by the DynamoDB service. We find a blog post, Implement Auto-increment with DynamoDB, which proposes three solutions to the challenge of generating a unique sequence number.

Strategy #1 does not seem to work, since we have a requirement for an ordered sequence of events.

Strategy #2 seems the most expensive, with two writes, and would result in a ledger with occasional glaring gaps in the sequence. Allowing gaps opens up a pandora’s box of risk, since it could be difficult to discern whether a gap was normal or represents the fraudulent deletion of an item.

Strategy #3 is less expensive than #2, requiring one read and just one write. It also produces a journal with ascending sequence numbers and no gaps. However, it runs the risk of collisions. The time delay between reading the latest value in a collection, and immediately inserting the next item should typically be on the order of 5-20ms. During this time, a different thread may be performing a similar write to the end of the ledger, and so the target item could already exist, in this race condition scenario.

Question: Will DynamoDB prevent you from over-writing an existing item?

Question: Can DynamoDB itself block updates and deletes for me?

DynamoDB’s set of IAM permissions include dynamodb:PutItem and dynamodb:UpdateItem. You can use IAM to prevent UpdateItem calls. However, PutItem will still replace an existing item if no condition expression is used! DynamoDB also has a set of PartiQL actions that do allow you to use IAM to prevent updates and deletes. Allowing only dynamodb:PartiQLInsert but not dynamodb:putItem and dynamodb:updateItem solves this problem. Details are found in this re:Post article: Using DynamoDB as an Append-Only Database 

Strategy #3 shows promise, but only if we can handle the race condition problem where the target item already exists. We learned from the client that requests will arrive at a maximum velocity of ten per second, and usually much less often, so the race condition should be rare.   We can therefore perform a conditional write with expression, which is also known as the Optimistic Concurrency pattern. If the request fails, we can wait a moment, re-query for the new final record, and retry the write.

An advantage of the Optimistic Concurrent pattern is that it does not require ACID transactions or a formal locking mechanism. While DynamoDB has robust support for transactions with the TransactWriteItems API, this adds cost, complexity, and inflexibility to the solution. For example, as we will see later, TransactWriteItems calls do not propagate atomically to other regions when using Global Tables, and the API is not even allowed when using Global Tables’ Multi-Region Strong Consistency mode.

Both approaches are documented in Best practices for handling concurrent updates in DynamoDB 

That's it for part 1. Next time we will examine how to implement these approaches, and how to select the best one based on performance, cost, and simplicity constraints. Please share your feedback below!