Bulk imports and exports

Designing features that move large amounts of data in and out of a system: CSV and file uploads processed as background jobs, validation and partial failures, idempotent resumable imports, throttling to protect production, streaming exports, presigned download links, scheduled and incremental exports, and APIs for bulk operations.

Reading is half of it. See this used in a real interview: walk through Design Airbnb →

Sooner or later every product needs bulk data movement: a merchant uploads 200,000 products from a spreadsheet, an enterprise customer exports all their records for an audit, a host syncs listings from another platform, a finance team downloads a year of transactions. Done naively in a request handler, these time out, overload the database and leave half-imported data behind. Interviewers occasionally ask about them directly, and they are a good example of asynchronous job design.

Imports as background jobs

1. The client uploads the file directly to object storage with a presigned URL. See media uploads and processing.
2. The API creates an import job record (pending) and enqueues it; the response is 202 Accepted with a job id. See background jobs.
3. A worker streams the file (never loading it all into memory), parses and validates rows, and writes them in batches.
4. Progress (rows processed, succeeded, failed) is stored on the job and shown to the user; completion triggers a notification.

Validation and partial failure

• Validate early: check headers, encoding and a sample of rows before starting, and fail fast with a clear message if the format is wrong.
• Per-row errors: collect invalid rows (with line numbers and reasons) into an error report the user can download and fix, rather than failing the whole import.
• Decide the semantics explicitly: all-or-nothing (stage everything, then commit in one switch) or best effort (import valid rows, report the rest). All-or-nothing suits financial data; best effort suits catalogues.
• For all-or-nothing at scale, write into a staging table or a new version, validate, then atomically publish. See data quality and data contracts.

Idempotent and resumable

Workers crash and jobs retry:

• Process in chunks with a checkpoint (byte offset or row number) saved after each committed batch, so a retry resumes rather than restarting.
• Make writes idempotent: upsert by a natural key (SKU, external id) or by (import id, row number), so reprocessing a chunk does not duplicate rows. See idempotency keys in practice.
• Uploading the same file twice should be detected (content hash) or harmless.

Protecting production

A large import competes with live traffic:

• Throttle write rates per tenant; use a separate, lower-priority worker pool. See multi-tenancy.
• Batch inserts and use bulk APIs; disable or defer expensive per-row side effects (emails, webhooks) or coalesce them.
• Update secondary systems (search index, caches) asynchronously through the normal change stream rather than inline. See change data capture.
• Watch replica lag and database load, pausing when they rise. See online schema migrations for the same backfill techniques.

Exports

• Run as a background job; stream query results in pages (keyset pagination) into a file in object storage, compressing as you go. See pagination.
• Read from a replica or snapshot to avoid loading the primary, and to get a consistent point-in-time view.
• Deliver with a short-lived presigned download link, sent by email or shown in the app; expire files after a few days.
• Formats: CSV for spreadsheets, JSON Lines for programmatic use, Parquet for analytics. See data lakes and lakehouses.
• Apply permissions at export time; exports are a common data leak path, so audit them. See audit logs.

Incremental and scheduled

Repeated full exports waste resources. Offer:

• Incremental exports of changes since the last export, using updated-at cursors or a change feed.
• Scheduled deliveries to the customer's storage bucket.
• Streaming integrations (webhooks or event streams) for near real-time sync. See webhooks.

Bulk APIs

For programmatic clients, provide bulk endpoints that accept many records per call (with per-item results) and asynchronous batch endpoints for very large operations, with limits on batch size and rate. See API design.

Large data transfers

For terabytes (onboarding an enterprise, migrating platforms), use multipart uploads, parallel workers, checksums per chunk, and possibly direct transfers between cloud storage buckets. Plan a verification step comparing counts and checksums. See distributed file systems.

In the interview

"Hosts upload a CSV via a presigned URL; an import job streams and validates it, upserts listings by external id in batches of 1,000 with checkpoints, throttled per host on a low-priority worker pool; invalid rows go to a downloadable error report; the search index updates through CDC. Exports stream from a replica into a compressed file with an expiring download link." See Design Airbnb.

Checklist

• Direct-to-storage upload; job record; 202 with progress tracking.
• Early format validation, per-row errors, explicit all-or-nothing or best-effort semantics.
• Chunked, checkpointed, idempotent processing.
• Throttling, separate worker pools, asynchronous side effects.
• Streaming exports from replicas to object storage with presigned links.
• Permissions and auditing on exports; incremental options.