SaaS Data Export Pipeline for Enterprise Clients

There's a feature most teams never prioritize until an enterprise deal stalls on it: getting data out. Mid-market and enterprise buyers refuse to keep their records trapped in your silo — their compliance team wants automated exports into their own data lake, and a browser download capped at 5,000 rows fails the security review on the spot. A proper data export pipeline isn't a nice-to-have; it's a revenue-expansion asset that turns "can you get our data into Snowflake?" from a dealbreaker into a yes.

We've watched this play out. We nearly lost an enterprise contract over a feature nobody had prioritized — their security review asked a question our product couldn't answer cleanly, and the deal stalled until we built the unglamorous infrastructure to answer it. Data export is exactly that kind of feature. Here's how to build one that streams without crashing, encrypts before it leaves, and logs every byte that goes out — the seven rules that matter.

Data Export Pipeline Rule 1: Stream, Never Load

The single most dangerous export bug is fetching the whole table into memory:

1// FATAL: loads millions of rows into RAM and OOM-crashes the process.
2async function badExport(tenantId: string) {
3  const all = await this.db.invoice.findMany({ where: { tenantId } });
4  return JSON.stringify(all);
5}

A multi-gigabyte table pulled into a JS array is an out-of-memory exception waiting for a big customer. The fix is to stream — pull rows in small batches and pipe them straight to the destination, so memory stays flat whether the export is 10MB or 10GB.

Rule 2: Stream From Postgres With pg-query-stream

Prisma doesn't stream query results, so for the export path drop to the pg driver with pg-query-stream, and pipe the cursor through a Transform into an S3 multipart upload:

1// src/exports/streaming-export.service.ts
2import { Injectable } from '@nestjs/common';
3import { Pool } from 'pg';
4import QueryStream from 'pg-query-stream';
5import { Transform } from 'node:stream';
6import { pipeline } from 'node:stream/promises';
7import { Upload } from '@aws-sdk/lib-storage';
8import { S3Client } from '@aws-sdk/client-s3';
9
10@Injectable()
11export class StreamingExportService {
12  private readonly s3 = new S3Client({});
13  constructor(private readonly pool: Pool) {}
14
15  async exportToS3(tenantId: string, bucket: string, key: string): Promise<void> {
16    const client = await this.pool.connect();
17    try {
18      // Cursor stream: only a small batch is ever in memory.
19      const rows = client.query(
20        new QueryStream('SELECT id, amount, status, created_at FROM invoices WHERE tenant_id = $1', [tenantId]),
21      );
22
23      const toNdjson = new Transform({
24        objectMode: true,
25        transform(row, _enc, cb) {
26          cb(null, Buffer.from(JSON.stringify(row) + '\n'));
27        },
28      });
29
30      const upload = new Upload({
31        client: this.s3,
32        params: { Bucket: bucket, Key: key, Body: toNdjson },
33      });
34
35      // Backpressure: the slow upload throttles the fast DB read automatically.
36      await Promise.all([pipeline(rows, toNdjson), upload.done()]);
37    } finally {
38      client.release();
39    }
40  }
41}

The cursor holds a small batch, the upload applies backpressure, and the process sips memory instead of swallowing the table.

Rule 3: Support the Formats Data Teams Actually Use

Match the client's ingestion stack:

• CSV — universal for business reporting; get comma-escaping and quoting right or columns shift.
• JSON Lines (NDJSON) — one object per line, parseable without loading a giant array. The natural fit for streaming.
• Apache Parquet — columnar, compressed, schema-carrying. It slashes file size and downstream query cost in Athena, Snowflake, and Databricks, which is why analytics teams ask for it by name.

Offer CSV for everyone, NDJSON for engineers, Parquet for the heavy data accounts.

Rule 4: Deliver Over S3 and SFTP — Securely

Two enterprise delivery channels cover almost everyone:

• S3 — drop objects into the client's bucket, with them provisioning an IAM role scoped to s3:PutObject and an external ID. You never hold long-lived keys to their storage.
• SFTP — for legacy and on-prem networks, connect with SSH key-pair auth, never plaintext passwords.

Schedule these on a staggered, queue-backed cron so every tenant's job doesn't fire on the same tick and exhaust your connection pool — store per-tenant schedules in a table and dispatch through your job queue:

1CREATE TABLE tenant_export_schedules (
2    id BIGSERIAL PRIMARY KEY,
3    tenant_id UUID NOT NULL,
4    cron_expression VARCHAR(50) NOT NULL DEFAULT '0 1 * * *',
5    target_format VARCHAR(20) NOT NULL CHECK (target_format IN ('CSV','JSONL','PARQUET')),
6    destination_type VARCHAR(20) NOT NULL CHECK (destination_type IN ('S3_BUCKET','SFTP_SERVER')),
7    is_active BOOLEAN DEFAULT TRUE,
8    next_run_at TIMESTAMPTZ,
9    last_run_at TIMESTAMPTZ
10);
11
12CREATE INDEX idx_export_schedules ON tenant_export_schedules (is_active, next_run_at);

Rule 5: Encrypt Before It Leaves, Not Just at Rest

Encrypting the storage volume isn't enough — enterprise clients expect the file encrypted before it crosses the network. Take their public PGP key and encrypt the stream with OpenPGP.js on the fly, so only their private key can read it. A misconfigured bucket or an intercepted transfer then leaks nothing. With breaches averaging $4.88M (IBM, 2024), client-side PGP is the control their security team will explicitly look for — and the cheapest way to never be the cause of one.

Rule 6: Make Delivery Resilient

Networks drop. Build for it with the standard reliability patterns:

1. Pre-flight check — write a tiny test file to the destination and delete it to confirm credentials before streaming gigabytes.
2. Exponential backoff with jitter — on a failed transfer, retry with growing, randomized delays.
3. Dead-letter queue — after a few consecutive failures, route the job to a DLQ and alert ops (Slack, PagerDuty). A silently stuck export is worse than a loud failure.

Rule 7: Audit Every Byte That Leaves

Every export needs an append-only trail — security officers need to know exactly what data left, when, and whether it arrived intact. Hash the stream as you write it and record the result:

1CREATE TABLE export_audit_logs (
2    id BIGSERIAL PRIMARY KEY,
3    tenant_id UUID NOT NULL,
4    schedule_id BIGINT REFERENCES tenant_export_schedules(id),
5    file_name VARCHAR(255) NOT NULL,
6    file_size_bytes BIGINT,
7    record_count INT,
8    sha256_checksum CHAR(64),       -- verify integrity post-delivery
9    status VARCHAR(20) NOT NULL,    -- PROCESSING, ENCRYPTED, DELIVERED, FAILED
10    error_message TEXT,
11    completed_at TIMESTAMPTZ
12);
13
14CREATE INDEX idx_export_audit ON export_audit_logs (tenant_id, status);

Pipe the same bytes through crypto.createHash('sha256') as you upload, ship the digest as a .sha256 sidecar, and the client's data team can confirm the file arrived whole. This is the same discipline as a proper enterprise audit log — enterprise buyers don't ask whether you have it, they ask to query it.

Stream it, format it, encrypt it, deliver it, and log it. Build the data export pipeline this way from the start and it stops being the feature that stalls a contract and becomes the one that closes it — the unglamorous infrastructure that quietly answers every question a security review can throw, while your servers never even notice the 10GB going out the door.