Financial aggregator (Mint)

01 · CONTEXT

Lots of writes, few reads

The classic exercise asks for a Mint-style financial aggregator: the user connects a financial account, the service extracts that account's transactions, categorizes each one, and analyzes monthly spending by category. The service also recommends a budget, lets the user set their own, lets them override a transaction's category, and sends notifications when spending approaches or exceeds the budget. High availability is a requirement.

The source's scale assumptions describe a large, asymmetric-use base: ten million users, thirty million financial accounts, and five billion transactions per month, against five hundred million reads per month. The source summarizes this as a ten-writes-to-one-read ratio and calls the system write-heavy, because users transact every day, but few visit the site daily.

On average, that is 2,000 transactions per second and 200 reads per second, with about 250 GB of new transaction content per month. The source also assumes that traffic is not evenly distributed. For the requirements gathering of the case, these numbers and assumptions are the source's starting point.

02 · INGESTION

From the connected account to the categorized transaction

When connecting an account, the client talks to the web server, which forwards the request to the Accounts API; it writes the newly provided account to the accounts table in the SQL database. The source notes that adding or removing financial accounts is relatively rare.

Transaction extraction is triggered in three situations: when the user links the account for the first time, when they refresh the account manually, and, every day automatically, for those who were active in the last 30 days.

1. The client requests the extraction; the web server forwards the request to the Accounts API.

2. Instead of extracting right away, the Accounts API puts a job on a queue, because extracting can take a while.

3. The extraction service takes the job off the queue, fetches the account's transactions from the financial institution, and stores the result as raw log files in object storage.

4. For each transaction, the extraction service calls the categories service.

5. The budget service calculates the aggregated monthly spending by category and, through the notifications service, warns the user who is approaching or has exceeded the budget.

6. The extraction service writes the categorized transactions and the aggregated monthly spending by category to the SQL database.

The source prefers to do the extraction asynchronously, with a message queue, because extracting can take a while, and admits that this brings additional complexity. The completion notice to the user also goes through the notifications service, which in turn uses a queue to send notifications asynchronously.

03 · CATEGORIES AND BUDGET

Dictionary, crowd, and budget model

To categorize, the source proposes seeding a seller-to-category dictionary with the most popular sellers. With 50,000 sellers and under 255 bytes per entry, it would take about 12 MB of memory. For sellers outside the dictionary, the source suggests using the manual category overrides that users themselves make, a sort of crowdsourcing.

For the budget recommendation, the source starts with a generic model that distributes amounts by category according to income brackets. That way there is no need to store the 100 million budget items from the assumptions, only the ones the user overrides.

For the budget service, the source presents two ways to produce the aggregated monthly spending: SQL queries over the transactions table or, as an alternative, MapReduce jobs over the raw transaction files.

04 · READ

Cache in front of the database

For reads, the source proposes putting user sessions, aggregated statistics by category, and recent transactions in an in-memory cache such as Redis or Memcached.

1. Static content can come out of object storage and be cached on the CDN.

2. The read request reaches the web server, which forwards it to the Read API.

3. The Read API first queries the in-memory cache and, if the content is there, returns what is cached.

4. If the content is not in the cache, the Read API fetches it from the SQL database; the read replicas should handle these cache misses, as long as they are not overloaded replicating writes.

5. After fetching from the database, the Read API updates the cache with the content.

For the 200 average reads per second, the source says that popular content traffic should be served by the cache and not by the database, and that the cache also helps with uneven traffic and spikes.

05 · WRITES

When a single database is not enough

The 2,000 average writes per second, even more at peaks, can be hard for a single SQL write database with a secondary. The source lists additional SQL scaling patterns: federation, sharding, denormalization, and SQL tuning, and suggests considering moving part of the data to a NoSQL database.

For history, the source suggests keeping only one month of transactions in the database and leaving the rest in a data warehouse or in object storage, and proposes a separate analytical database, with a data warehouse such as Amazon Redshift or Google BigQuery. Object storage such as Amazon S3 comfortably holds the 250 GB of new content per month.

1. The writes of categorized transactions concentrate on the primary SQL database, which the source points to as a hard spot for the average writes.

2. The read replicas serve the cache misses, as long as they are not overloaded replicating writes.

3. The in-memory cache serves popular content and helps with uneven traffic and spikes.

4. A batch job analyzes the raw files and generates the aggregated monthly spending by category, which can greatly reduce the load on the database.

06 · WRAP-UP

What to take into the interview

The source warns against jumping straight from the initial design to the final one. The path it indicates is to measure with load testing, profile to find bottlenecks, address them by evaluating alternatives and trade-offs, and repeat.