Katkat
Back

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

PinterestSoftware Engineer · Onsite
HardSoftware EngineerOnsiteSystem Design

Last updated

A system design question about letting customers update records individually and in bulk by uploading CSV files. It focuses on reliable asynchronous job processing, idempotent row application, partitioning large files and the underlying data, conflicts between the two write paths, and reporting row outcomes.

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.

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?

Hints

Hint 1

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 2

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 3

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.

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?