System Design: Building a Data Export Platform

Data export is one of those features that sounds simple until it takes down production.

A product manager asks for "Export to CSV." The first implementation runs a SQL query in the web request, builds a file in memory, and streams it to the browser. It works in staging. Then a customer exports five years of audit logs, the query runs for minutes, the connection pool fills up, the application runs out of memory, and support gets a ticket saying "the export button is broken."

Real export systems need job orchestration, permissions, snapshots, object storage, progress tracking, audit logs, throttling, retention, and privacy controls.

This guide designs a production data export platform for SaaS products.

Requirements and System Goals

When building an enterprise-grade data export platform, we must structure the system to handle unpredictable request volumes and massive datasets without degrading the user-facing online transaction processing (OLTP) application.

Functional Requirements

• Async Export Request: Users must be able to request data exports in multiple formats (CSV, JSONL, and Parquet) asynchronously.
• Permission & Filter Validation: The system must enforce columns validation, tenant boundary isolation, and compliance-based data scoping (e.g., GDPR/DSAR workflows).
• Status & Progress Tracking: The system must expose the job status (e.g., QUEUED, RUNNING, SUCCEEDED, FAILED) along with granular metrics like processed row counts, written byte sizes, and percentage completion.
• Secure Retreival via Signed URLs: Generated export assets must be stored securely in private cloud storage and made accessible only via short-lived signed URLs.
• Automated Retention Cleanup: Old export files must be automatically purged after a configured retention window (e.g., 7 days) to limit storage footprint and satisfy compliance guidelines.
• API Request Idempotency: Clients must be able to submit requests with idempotency keys to prevent duplicate job processing during network retries.

Non-Functional Requirements

• Database Protection: The export engine must never exhaust database connections or CPU. Heavy analytical queries must be routed to read-replicas, use cursor pagination, and execute under strict timeouts.
• Bounded Memory Bounds: Workers must stream and encode database rows in small chunks to ensure constant, bounded memory usage (less than 128 MB RAM per worker process) regardless of export size.
• Scalability: The system must scale to handle exports with 10,000,000+ rows or files up to 20 GB in size.
• High Availability & Fault Tolerance: If an export worker fails mid-job, the system must detect the failure via heartbeats and resume execution from the last successful partition.

API Interfaces and Service Contracts

To support asynchronous processing and decouple clients from the long-running execution pipeline, we define a clear REST contract. Optionally, internal microservices can request exports using gRPC.

Create Asynchronous Export Job

Clients trigger a data export by submitting a POST request containing filters, required columns, and format preferences.

• Endpoint: POST /v1/exports
• Headers:
  • Content-Type: application/json
  • Idempotency-Key: <unique_uuid>
• Request Payload:

{
  "exportType": "audit_logs",
  "format": "parquet",
  "filters": {
    "from": "2026-01-01T00:00:00Z",
    "to": "2026-06-01T00:00:00Z",
    "userId": "usr_9921"
  },
  "columns": ["id", "createdAt", "action", "ipAddress", "userAgent"]
}

• Response Payload (HTTP 202 Accepted):

{
  "exportId": "exp_87df-921a-4c22-b13c",
  "status": "QUEUED",
  "createdAt": "2026-06-06T14:20:00Z",
  "links": {
    "status": "https://api.codesprintpro.com/v1/exports/exp_87df-921a-4c22-b13c",
    "cancel": "https://api.codesprintpro.com/v1/exports/exp_87df-921a-4c22-b13c/cancel"
  }
}

Retrieve Export Job Status

Clients poll the status endpoint to monitor job progress.

• Endpoint: GET /v1/exports/exp_87df-921a-4c22-b13c
• Response Payload (HTTP 200 OK):

{
  "exportId": "exp_87df-921a-4c22-b13c",
  "status": "RUNNING",
  "progressPercent": 65.4,
  "metrics": {
    "totalRowsProcessed": 654000,
    "totalBytesWritten": 163500000,
    "elapsedTimeMs": 28500
  },
  "expiresAt": null
}

Request Signed Download Link

Once an export reaches the SUCCEEDED status, the client requests a short-lived download URL.

• Endpoint: POST /v1/exports/exp_87df-921a-4c22-b13c/download-url
• Response Payload (HTTP 200 OK):

{
  "exportId": "exp_87df-921a-4c22-b13c",
  "downloadUrl": "https://s3.us-west-2.amazonaws.com/csp-exports/tenant-99/exp_87df.parquet?AWSAccessKeyId=AKIAIOSFODNN7EXAMPLE&Signature=vjbyPxybdZaNmGa%2ByT272YEAiv4%3D&Expires=1700000000",
  "expiresInSeconds": 300
}

Internal gRPC Service Contract

For backend orchestration and internal trigger services, we define a Protocol Buffer interface:

syntax = "proto3";

package codesprintpro.export.v1;

service ExportCoordinatorService {
  rpc RegisterExportJob (RegisterExportJobRequest) returns (RegisterExportJobResponse);
  rpc HeartbeatExportWorker (HeartbeatRequest) returns (HeartbeatResponse);
}

message RegisterExportJobRequest {
  string tenant_id = 1;
  string requested_by = 2;
  string format = 3;
  string payload_json = 4;
}

message RegisterExportJobResponse {
  string export_id = 1;
  string status = 2;
}

message HeartbeatRequest {
  string worker_id = 1;
  string export_id = 2;
  int64 progress_rows = 3;
}

message HeartbeatResponse {
  bool should_abort = 1;
}

High-Level Design and Visualizations

The data export platform decouples the online HTTP server path from the analytical extraction loop. The diagram below illustrates the ingestion, queue distribution, extraction from read-replicas, and object storage upload flows.

End-to-End Request Lifecycle

flowchart TD
    Client[Client Browser / App] -->|1. Submit Request POST /v1/exports| API[Export API Gateway]
    API -->|2. Verify RBAC / Quota| Authz[Authz Service]
    API -->|3. Check Idempotency Key| RedisCache[Redis Lock Cache]
    API -->|4. Create Job Records| MetaDB[(Metadata Store - PostgreSQL)]
    API -->|5. Publish Task Event| MQ[Export Task Queue - RabbitMQ/Kafka]
    API -->|6. Return Job ID / Status 202| Client
    
    MQ -->|7. Dequeue Task| Worker[Export Worker Pool]
    Worker -->|8. Fetch Metadata & Split Partitions| MetaDB
    Worker -->|9. Stream Scan Rows via Cursor| ReadReplica[(Read Replica Database)]
    Worker -->|10. Stream Multi-part Encoded Chunks| S3[Object Storage - AWS S3]
    Worker -->|11. Heartbeat & Update Progress| MetaDB
    Worker -->|12. Finish Job & Publish Event| EventBus[Event Bridge]
    
    EventBus -->|13. Push Notification| Notify[Notification Service]
    Notify -->|14. Send Email / Webhook| Client

Partitioned Worker Lifecycle and Buffer Loop

Large datasets are split into physical partitions (e.g., time ranges or ID spaces) to support parallel processing and resilient restarts.

flowchart TD
    Task[Task Dequeued] --> Planner[Task Partition Planner]
    Planner -->|Splits ranges by timestamp/ID| Partitions[Partition Queue: Part 1, Part 2, Part 3]
    
    Partitions --> Loop[Process Next Partition]
    Loop --> Reader[Read Replica Cursor Reader]
    Reader -->|Stream Rows| Buffer[Worker Ring Buffer - 8MB Memory Chunk]
    Buffer -->|If full| Gzip[Gzip/Parquet Encoder]
    Gzip -->|S3 Multi-part Upload API| S3Upload[Upload Part to Object Storage]
    S3Upload --> SaveProgress[Persist Partition State to Metadata DB]
    
    SaveProgress --> CheckComplete{Are all partitions done?}
    CheckComplete -->|No| Loop
    CheckComplete -->|Yes| Manifest[Generate and Write Final Manifest JSON]
    Manifest --> Complete[Commit S3 Multipart & Update Job to SUCCEEDED]

Low-Level Design and Schema Strategies

Our metadata store is a PostgreSQL database. It stores the lifecycle state of each export request, individual partition attempts, and active resource mappings.

PostgreSQL Table DDLs

-- Main metadata table for tracing the status and location of all export requests
CREATE TABLE export_jobs (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id VARCHAR(64) NOT NULL,
    requested_by VARCHAR(128) NOT NULL,
    export_type VARCHAR(64) NOT NULL,            -- e.g., 'audit_logs', 'transactions', 'billing'
    format VARCHAR(16) NOT NULL,                 -- 'csv', 'jsonl', 'parquet'
    status VARCHAR(32) NOT NULL,                 -- 'QUEUED', 'RUNNING', 'SUCCEEDED', 'FAILED', 'EXPIRED'
    filters JSONB NOT NULL,                      -- Query filters applied (validated against schema)
    requested_columns VARCHAR(64)[] NOT NULL,    -- Array of columns to include
    object_key VARCHAR(512),                     -- S3 destination path: 'tenant/exports/year/month/uuid.format'
    row_count BIGINT NOT NULL DEFAULT 0,
    byte_count BIGINT NOT NULL DEFAULT 0,
    error_code VARCHAR(128),
    error_message TEXT,
    created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    expires_at TIMESTAMPTZ NOT NULL,             -- Automatically populated based on retention config
    idempotency_key VARCHAR(256),
    CONSTRAINT uk_tenant_idempotency UNIQUE (tenant_id, idempotency_key)
);

-- Indexing for user dashboard list queries sorted by date
CREATE INDEX idx_export_jobs_tenant_date 
ON export_jobs (tenant_id, requested_by, created_at DESC);

-- Indexing for background workers retrieving queued tasks
CREATE INDEX idx_export_jobs_status_created 
ON export_jobs (status, created_at) 
WHERE status = 'QUEUED';

-- Tracking individual partition state and worker handoffs to handle retries and progress audits
CREATE TABLE export_job_partitions (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    job_id UUID NOT NULL REFERENCES export_jobs(id) ON DELETE CASCADE,
    partition_index INT NOT NULL,
    range_start VARCHAR(128) NOT NULL,
    range_end VARCHAR(128) NOT NULL,
    status VARCHAR(32) NOT NULL,                 -- 'PENDING', 'RUNNING', 'COMPLETED', 'FAILED'
    worker_id VARCHAR(128),
    row_count BIGINT NOT NULL DEFAULT 0,
    byte_count BIGINT NOT NULL DEFAULT 0,
    started_at TIMESTAMPTZ,
    completed_at TIMESTAMPTZ,
    CONSTRAINT uk_job_partition UNIQUE (job_id, partition_index)
);

CREATE INDEX idx_export_partitions_job 
ON export_job_partitions (job_id);

-- Auditing log entries specifically recording down-stream client downloads
CREATE TABLE export_download_audits (
    id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    export_id UUID NOT NULL REFERENCES export_jobs(id) ON DELETE RESTRICT,
    actor_id VARCHAR(128) NOT NULL,
    tenant_id VARCHAR(64) NOT NULL,
    client_ip VARCHAR(45) NOT NULL,
    user_agent TEXT,
    downloaded_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_export_download_audits_tenant 
ON export_download_audits (tenant_id, downloaded_at DESC);

Scaling and Operational Challenges

To scale data export to millions of records without causing database resource contention or Out-of-Memory (OOM) errors, we must evaluate database pagination behavior and resource footprint.

Back-of-the-Envelope Capacity Estimations

Let us estimate the metrics for a large enterprise export containing 10,000,000 rows of audit logs.

• Average Row Size: Let us assume each audit log entry serialized to CSV is approximately 250 bytes.
• Total Raw Data Size: $$\text{Total Size} = 10,000,000 \times 250\text{ bytes} = 2,500,000,000\text{ bytes} = 2.5\text{ GB}$$
• S3 Multi-part Upload Buffering: We use an in-memory buffer chunk size of 8 MB per part upload. $$\text{Total Parts} = \frac{2,500,000,000\text{ bytes}}{8,388,608\text{ bytes}} \approx 298\text{ parts}$$
• Network Egress Bandwidth: If the export worker executes the job in 100 seconds by streaming data from the database and pushing it to S3, the output upload bandwidth required is: $$\text{Egress Output} = \frac{2.5\text{ GB}}{100\text{ sec}} = 25\text{ MB/sec} \approx 200\text{ Mbps}$$ This is well within the gigabit interfaces of modern cloud VMs. However, we must limit concurrent exports per worker to avoid saturating host interfaces.

Database Query IOPS Saturation: Offset vs. Cursor Pagination

When running queries to pull millions of rows, offset pagination is a database killer.

The Offset Pagination Problem

Using standard offset queries:

SELECT * FROM audit_logs 
WHERE tenant_id = 't_99' 
ORDER BY id ASC 
LIMIT 5000 OFFSET 2000000;

For every page request, PostgreSQL must scan and sort all preceding 2,000,000 rows only to discard them and return the next 5,000. Under high volumes, this scan causes high Disk Read IOPS and saturates buffer pools.

The Cursor Pagination Solution

By preserving state across chunks, the worker performs index-seeking:

SELECT * FROM audit_logs 
WHERE tenant_id = 't_99' AND id > 'audit_log_2000000'
ORDER BY id ASC 
LIMIT 5000;

This query performs an index seek using the primary key index. The query time remains constant (under 2ms) whether reading the first page or the two-millionth page, reducing database CPU utilization from 99% to less than 2% for pagination loops.

Trade-offs and Architectural Alternatives

Designing a data export pipeline involves balancing consistency, format performance, and resource reservation.

Extraction Isolation Strategy Trade-offs

File Format Trade-offs

• CSV (Comma-Separated Values):
  • Pros: Ubiquitous, readable, simple parsing.
  • Cons: No native schemas, text conversion is expensive, vulnerable to formula injection.
• JSONL (JSON Lines):
  • Pros: Native support for structured/nested records, simple line-by-line stream parsing.
  • Cons: Large file sizes due to repetitive keys, slow serialization overhead.
• Parquet:
  • Pros: Columnar organization provides high compression ratios (up to 80% reduction vs. CSV), faster analytical querying.
  • Cons: High CPU cost to build schemas/chunks, requires external tooling to view.

Failure Modes and Fault Tolerance Strategies

Worker Crash and Progress Resumability

If a worker node crashes mid-export, the coordinator notices the missing heartbeat. Rather than restarting a multi-gigabyte export from scratch:

1. The partition planner reads the metadata store to identify which partitions are marked COMPLETED and which are still PENDING or RUNNING.
2. A new worker is allocated, retrieves the PENDING partitions, and resumes extraction.
3. The final step merges the written parts in S3 using the manifest.

Worker Local Disk Saturation

When exporting massive datasets, copying raw files to local disk scratch space can trigger disk space exhaustion. We enforce a diskless pipeline:

• The worker uses a pipeline structure where data from the database is processed through an in-memory stream directly to S3's multipart upload API.
• The maximum disk write allocation per task is strictly bounded to 0 bytes, eliminating local disk dependency.

Concurrency and Rate Limiting

To prevent a single customer from triggering dozens of heavy exports simultaneously:

• We enforce a token bucket rate-limiter on the POST /v1/exports route.
• A maximum of 3 active concurrent exports is allowed per tenant, with additional submissions queued in memory or rejected with HTTP 429 Too Many Requests.

Staff Engineer Perspective

Verbal Script

Interviewer: "How would you handle a scenario where an export worker crashes while generating a 10 GB CSV file?"

Candidate: "To prevent wasting database resources and restart delays, I would design the export process to use partition-based checkpointing. Instead of treating the 10 GB export as a single monolithic block, the coordinator divides the export query into time-based or ID-based partitions—for example, chunks of 100,000 rows.

As the worker completes each partition, it streams the chunk to a temporary object key in S3 and writes a COMPLETED status to the metadata database.

If the worker node crashes, the coordinator detects heartbeat loss within 10 seconds. It then assigns the task to another worker. The new worker queries the database to find only the unfinished partitions, processes them, and uploads their parts. Finally, the worker executes a merge or builds a manifest pointing to the partition files. This limits our rework boundary to less than a single partition size."

Interviewer: "How do you protect the main production database from being overwhelmed when generating these partitions?"

Candidate: "We use several isolation layers. First, all data reading queries are routed to read replicas, ensuring zero contention on primary transaction paths. Second, the client SDK queries replicas using cursor-based pagination—namely, checking the index where the previous fetch ended. This avoids offset page-scan bloat.

Lastly, we enforce query timeouts. If replica replication lag exceeds a threshold (e.g., 5 seconds), the worker pauses to let the replica recover. We also throttle the fetch loop to process at most 5,000 rows per batch with a brief delay, balancing export speed with database stability."

Read Next

• System Design: Building an Audit Log System
• System Design: Building a File Upload Platform
• System Design: Building a Distributed Job Scheduler