Enterprise File Processing System

Large batch files in, validated records out

Design a service that takes large partner files (payroll, claims, settlement), checks and splits them, and applies every valid record downstream exactly once, with a reject file and an acknowledgement per file.

Overview

Many businesses still exchange data as files. An employer sends a payroll file to a bank, a clinic sends a claims file to an insurer, a card processor sends a settlement file to a merchant. The files are large (a few megabytes to tens of gigabytes), arrive on a schedule, and carry thousands to millions of records that must each be checked and applied to a downstream system.

The hard part is not reading a CSV. It is doing it correctly when things go wrong: the partner sends the same file twice, the upload stops halfway, one record in ten million is malformed, the worker processing chunk 200 of 320 crashes, or the file lands one minute after the daily cutoff. The design has to apply every valid record exactly once in effect, account for every invalid one, and tell the partner what happened. Three tools carry most of the weight: an idempotent file ID with a content hash, a chunk manifest whose progress is checkpointed, and idempotent upserts downstream. The Idempotency and Message Queues topics cover those ideas in general; this page applies them.

Questions to ask first

Who sends files, and how? — 500 partners, by SFTP or by HTTPS upload to a pre-signed URL
How many and how big? — 2,000 files a day, 200 MB on average, the largest 20 GiB
Formats? — CSV, fixed-width and XML, one schema per partner feed
When do they arrive? — 60% in the two hours before an 18:00 cutoff
What does done mean? — Every valid record applied downstream once; every invalid one in a reject file
How fast? — A receipt within 15 minutes, results within 60 minutes of arrival (targets we chose)
Security? — Files are PGP-encrypted by the partner; encrypted at rest; scanned before parsing
How long to keep files? — Seven years, unchangeable (an assumed regulatory rule)

What you will be able to do

Take a file from a partner's upload to applied records, and say what is recorded at each step.
Recognize a re-sent file with a content hash and an idempotent file ID, and tell a duplicate from a correction.
Split a large file into chunks that are processed in parallel and restart from checkpoints after a crash.
Make downstream writes safe to repeat, and keep the order that matters.
Handle bad records, poison chunks, cutoffs, acknowledgements and retention.

Key concepts

Object-created events
Quarantine bucket
Content hash
Idempotent file ID
Chunk manifest
Leases and checkpoints
Idempotent upsert
Reject file
Dead-letter queue
Cutoff time

Functional requirements

Accept files by SFTP and by HTTPS upload to a pre-signed URL
Detect each new file without polling the partner
Scan, decrypt and validate a file before any record is read
Recognize duplicate and re-sent files
Process large files in parallel chunks and resume after failures
Apply each valid record downstream once; write each invalid record to a reject file
Send a receipt and a result acknowledgement file to the partner
Show each file's status on a dashboard; keep files and audit records for seven years

Non-functional requirements

Exactly-once effect: a record is never applied twice and never silently dropped
No unscanned or unvalidated byte reaches a parser or the downstream system
Receipt within 15 minutes and results within 60 minutes for files up to 5 GiB (targets we chose)
Survives the loss of any worker or instance mid-file without restarting the whole file
Encrypted in transit and at rest; stored files cannot be changed or deleted during retention

Interview questions

How do you guarantee each record is applied exactly once?
How do you detect that a file has arrived?
A partner sends the same file again. What happens?
How would you process a 20 GB file quickly?
What happens if the system crashes halfway through a file?
How do you handle bad data?
How do you keep files secure?
How do you handle cutoff times and SLAs?
How do you keep the order of records and files?