Gather requirements and scope the problem. Ask questions to clarify use cases and constraints. Discuss assumptions.
Without an interviewer to address clarifying questions, we'll define some use cases and constraints.
- What is classified as a financial reconciliation?
- Is there a distinction between GAAP and IFRS that needs to be accounted for and if so, are we expected to handle both methodologies?
- What is the expected timeline for reconciliations? Is it done in real-time, daily, monthly, annually?
- What is the purpose of this system? Are we aiming to mitigate fraud or reduce the potential costs associated with cash shortages or fees and penalties with insufficient funds? Are we aiming to be compliant with financial regulators?
- Could we estimate how feasible it would be to design and develop the system in house in terms of capacity, skills, opportunity cost versus outsourcing to a vendor or consulting firm?
-
As an account management representative, I want to check if a funding batch has been reconciled so that I have an accurate understanding of our customer's finances
-
As a customer, I want to check if a funding batch has been reconciled so that I have an accurate understanding of my finances
-
As a customer, I want to access my finances on a mobile device or computer so that I have convenient access to my finances 24/7
-
As a bank representative, I want to check if a transaction has been reconciled so that I have an accurate understanding of my finances
-
As a credit card representative, I want to check if a transaction has been reconciled so that I have an accurate understanding of my finances including fraudulent chargebacks
-
As an auditor, I want to track incoming and outgoing cash flows so that I can detect potential fraud and sign off on the accounting statements
- Auditing
- Mobile platforms
- Optimizing cash flows for customers
- Traffic is not evenly distributed
- Payments will factor in seasonality and temporal cycles such as weekend spending, Black Friday, and the holidays
- Core customers will have a larger percentage of total payments a la the Pareto rule
- Funding details are updated consistently and do not need to be cleaned
- 1 million customers
- 1 billion transactions per day
- 30 billion transactions per month
- 1:100 read to write ratio
Clarify with your interviewer if you should run back-of-the-envelope usage calculations.
- Size per transaction:
created_at- 5 bytesrecon_key- 8 bytesseller_id- 8 bytesbuyer_id- 8 bytesquantity- 4 bytestotal_price- 5 bytes- Total: ~40 bytes
- 1.2 TB of new transaction content per month
- 40 bytes per transaction * 30 billion transactions per month
- Assume most are new transactions instead of updates to existing ones
- X transactions per second on average
- Y read requests per second on average
Handy conversion guide:
- 2.5 million seconds per month
- 1 request per second = 2.5 million requests per month
- 40 requests per second = 100 million requests per month
- 400 requests per second = 1 billion requests per month
Outline a high level design with all important components.
Dive into details for each core component.
We could store the raw Sales API server log files on a managed Object Store such as Amazon S3, rather than managing our own distributed file system.
Clarify with your interviewer how much code you are expected to write.
We'll assume this is a sample log entry, tab delimited:
timestamp product_id category_id qty total_price seller_id buyer_id
t1 product1 category1 2 20.00 1 1
t2 product1 category2 2 20.00 2 2
t2 product1 category2 1 10.00 2 3
t3 product2 category1 3 7.00 3 4
t4 product3 category2 7 2.00 4 5
t5 product4 category1 1 5.00 5 6
...
The Sales Rank Service could use MapReduce, using the Sales API server log files as input and writing the results to an aggregate table sales_rank in a SQL Database. We should discuss the use cases and tradeoffs between choosing SQL or NoSQL.
We'll use a multi-step MapReduce:
- Step 1 - Transform the data to
(category, product_id), sum(quantity) - Step 2 - Perform a distributed sort
class SalesRanker(MRJob):
def within_past_week(self, timestamp):
"""Return True if timestamp is within past week, False otherwise."""
...
def mapper(self, _ line):
"""Parse each log line, extract and transform relevant lines.
Emit key value pairs of the form:
(category1, product1), 2
(category2, product1), 2
(category2, product1), 1
(category1, product2), 3
(category2, product3), 7
(category1, product4), 1
"""
timestamp, product_id, category_id, quantity, total_price, seller_id, \
buyer_id = line.split('\t')
if self.within_past_week(timestamp):
yield (category_id, product_id), quantity
def reducer(self, key, value):
"""Sum values for each key.
(category1, product1), 2
(category2, product1), 3
(category1, product2), 3
(category2, product3), 7
(category1, product4), 1
"""
yield key, sum(values)
def mapper_sort(self, key, value):
"""Construct key to ensure proper sorting.
Transform key and value to the form:
(category1, 2), product1
(category2, 3), product1
(category1, 3), product2
(category2, 7), product3
(category1, 1), product4
The shuffle/sort step of MapReduce will then do a
distributed sort on the keys, resulting in:
(category1, 1), product4
(category1, 2), product1
(category1, 3), product2
(category2, 3), product1
(category2, 7), product3
"""
category_id, product_id = key
quantity = value
yield (category_id, quantity), product_id
def reducer_identity(self, key, value):
yield key, value
def steps(self):
"""Run the map and reduce steps."""
return [
self.mr(mapper=self.mapper,
reducer=self.reducer),
self.mr(mapper=self.mapper_sort,
reducer=self.reducer_identity),
]The result would be the following sorted list, which we could insert into the sales_rank table:
(category1, 1), product4
(category1, 2), product1
(category1, 3), product2
(category2, 3), product1
(category2, 7), product3
The sales_rank table could have the following structure:
id int NOT NULL AUTO_INCREMENT
category_id int NOT NULL
total_sold int NOT NULL
product_id int NOT NULL
PRIMARY KEY(id)
FOREIGN KEY(category_id) REFERENCES Categories(id)
FOREIGN KEY(product_id) REFERENCES Products(id)
We'll create an index on id , category_id, and product_id to speed up lookups (log-time instead of scanning the entire table) and to keep the data in memory. Reading 1 MB sequentially from memory takes about 250 microseconds, while reading from SSD takes 4x and from disk takes 80x longer.1
- The Client sends a request to the Web Server, running as a reverse proxy
- The Web Server forwards the request to the Read API server
- The Read API server reads from the SQL Database
sales_ranktable
We'll use a public REST API:
$ curl https://amazon.com/api/v1/popular?category_id=1234
Response:
{
"id": "100",
"category_id": "1234",
"total_sold": "100000",
"product_id": "50",
},
{
"id": "53",
"category_id": "1234",
"total_sold": "90000",
"product_id": "200",
},
{
"id": "75",
"category_id": "1234",
"total_sold": "80000",
"product_id": "3",
},
For internal communications, we could use Remote Procedure Calls.
Identify and address bottlenecks, given the constraints.
Important: Do not simply jump right into the final design from the initial design!
State you would 1) Benchmark/Load Test, 2) Profile for bottlenecks 3) address bottlenecks while evaluating alternatives and trade-offs, and 4) repeat. See Design a system that scales to millions of users on AWS as a sample on how to iteratively scale the initial design.
It's important to discuss what bottlenecks you might encounter with the initial design and how you might address each of them. For example, what issues are addressed by adding a Load Balancer with multiple Web Servers? CDN? Master-Slave Replicas? What are the alternatives and Trade-Offs for each?
We'll introduce some components to complete the design and to address scalability issues. Internal load balancers are not shown to reduce clutter.
To avoid repeating discussions, refer to the following system design topics for main talking points, tradeoffs, and alternatives:
- DNS
- CDN
- Load balancer
- Horizontal scaling
- Web server (reverse proxy)
- API server (application layer)
- Cache
- Relational database management system (RDBMS)
- SQL write master-slave failover
- Master-slave replication
- Consistency patterns
- Availability patterns
The Analytics Database could use a data warehousing solution such as Amazon Redshift or Google BigQuery.
We might only want to store a limited time period of data in the database, while storing the rest in a data warehouse or in an Object Store. An Object Store such as Amazon S3 can comfortably handle the constraint of 40 GB of new content per month.
To address the 40,000 average read requests per second (higher at peak), traffic for popular content (and their sales rank) should be handled by the Memory Cache instead of the database. The Memory Cache is also useful for handling the unevenly distributed traffic and traffic spikes. With the large volume of reads, the SQL Read Replicas might not be able to handle the cache misses. We'll probably need to employ additional SQL scaling patterns.
400 average writes per second (higher at peak) might be tough for a single SQL Write Master-Slave, also pointing to a need for additional scaling techniques.
SQL scaling patterns include:
We should also consider moving some data to a NoSQL Database.
Additional topics to dive into, depending on the problem scope and time remaining.
- Where to cache
- What to cache
- When to update the cache
- Discuss tradeoffs:
- External communication with clients - HTTP APIs following REST
- Internal communications - RPC
- Service discovery
Refer to the security section.
See Latency numbers every programmer should know.
- Continue benchmarking and monitoring your system to address bottlenecks as they come up
- Scaling is an iterative process