Design Bulk CSV and Individual Record Updates with Reliable, Partitioned Processing

Design a system that lets customers change their records in two ways: - **Individual updates:** change one record at a time through an API or a form in the UI. - **Bulk updates:** upload a CSV file in which each row describes a change to one record, so that a customer can change many records at once. Both paths write to the same records. The design must be reliable: rows of an uploaded file must never be silently lost or applied twice, even when servers crash or requests are retried, and the customer must be able to find out what happened to their upload. It must also partition both the work and the data, so that large files and many customers uploading at the same time are handled. ```hint A file is a job, not a request Think about what the customer gets back immediately when they upload a large file, and how they later learn the outcome. ``` ```hint Row-level bookkeeping If a worker crashes halfway through a file, decide what lets the system resume without applying any row twice or skipping one. ``` ```hint Two writers, one record An individual update and a bulk row can target the same record at nearly the same moment. Decide which one should win, and how you enforce that. ``` ### Clarifying Questions - What are the records (product listings, account settings, campaign settings, something else), who owns them, and roughly how many does one customer have? - How large can a CSV file be, how many uploads happen per day, and how quickly must a bulk change take effect? - Should a file apply all or nothing, or row by row with a report of the rows that failed? - If an individual update and a bulk row change the same record, which one should win? - Does each row carry a full replacement of the record or only the fields to change? Can rows also create or delete records? - Do downstream systems, such as a search index or a serving cache, need to see every change? ### What a Strong Answer Covers - A clear API for both paths, including asynchronous job semantics for uploads and a way to get status and results - File intake: where the file is stored, how it is validated, and how it is split into units of work - Partitioning of the work and of the data, with fairness between customers - Reliability: idempotent row application, checkpoints, leases, retries and dead-letter handling, giving an exactly-once effect - An explicit conflict policy between bulk and individual writes, and how it is enforced - Propagation to downstream systems, observability, and a per-row outcome report ### Follow-up Questions - One customer uploads a file many times larger than usual while hundreds of others upload small files. How do you keep things fair? - A customer uploads the same file twice by mistake. What happens? - How would you support "undo this upload"? - Halfway through a job, the database partition holding some of the records becomes unavailable. What does the job do, and what does the customer see?