feat(api): streaming/compressed bulk table export + import for tenant clone/backup (staging + forked-DB rebuild)

[EricAndrechek]

Summary

There's no efficient, API-only way to export a table's (or a whole tenant's) data for rebuilding it in another environment — the primitive you need to stand up a staging/dev copy, fork a tenant DB for testing, or take a portable backup. The raw-SQL endpoint (POST /v1/admin/query) can SELECT data, but its response-size + request-body caps and JSON encoding make it unsuitable for bulk exfil; direct clickhouse-client is efficient but a managed tenant's operator may have no ClickHouse access (the same constraint behind #361).

Context — round-tripping a tenant's data today

• Export: POST /v1/admin/query with SELECT … FORMAT JSONEachRow. But internal/api/query.go enforces a response-size cap (TestQueryHandler_ResponseSizeCap) and request-body cap (TestQueryHandler_RequestBodyCap), so any non-trivial table must be manually paginated (by seq/utc), and JSON is a bulky wire format for millions of telemetry rows.
• Import: re-POST /v1/ingest the rows (NDJSON, batched to CH). Works, but also uncompressed + row-oriented.
• Direct ClickHouse (SELECT … FORMAT Native, clickhouse-backup) is the efficient path — but a managed WaveHouse Cloud tenant may not expose CH; an API-only operator is stuck (same gap as feat(api): first-class multi-statement DDL/migration application (provision a tenant without direct ClickHouse access) #361, for the data side instead of DDL).

So there's no first-class, API-only, efficient dump → restore for a WaveHouse instance.

Why we want it

The SCC 2026 tracker pilot needs staging/dev environments seeded from prod — to test schema migrations, policy/role edits, and pipe changes against real-shaped data before promoting to the live tenant. The WaveHouse Cloud control plane will want the same primitive to fork/clone a tenant (branch a DB for testing, snapshot/restore, migrate between instances). "Copy this tenant's data somewhere else, fast, without CH access" is a recurring need, and pagination-over-JSON doesn't scale to it.

Suggested

A streaming, admin-gated export paired with a bulk import:

• GET /v1/admin/export?table={table} — chunked/streaming response (no response-size cap) in an efficient/compressed format: ClickHouse Native or Parquet, gzip/zstd. Optional where / time-range / after_seq for incremental export and for splitting large tables.
• Whole-instance variant (GET /v1/admin/export → a manifest + per-table streams) for a full tenant clone.
• POST /v1/admin/import?table={table} accepting the same format — or teach /v1/ingest to accept Native/Parquet bodies + Content-Encoding: gzip/zstd — for a fast restore that skips per-row JSON re-encode.
• Scope to the management surface (ties into Separate management-plane auth from data-plane auth (platform operator credential) #359 — export/import are platform-operator ops, distinct from the data plane).

Net: an API-only operator can dump → restore a tenant efficiently, enabling staging/forked copies and portable backups without direct ClickHouse access.

Related: #361 (multi-statement DDL/migration — the schema side of provisioning; this is the data side), #359 (management vs data-plane auth — export belongs on the management surface), and the /v1/admin/query size caps in internal/api/query.go.

Found building the SCC 2026 tracker pilot (local emulation of WaveHouse Cloud); we currently paginate /v1/admin/query + re-/v1/ingest as a workaround. Happy to contribute.

[coderabbitai]

🔗 Related PRs

#164 - refactor(ingest)!: insert-only pipeline; mutations via /v1/query [closed]
#174 - refactor(table names): handle unsafe table names [closed]
#177 - feat: caching for local table/scope invalidation [closed]
#182 - refactor: full api --> ingest --> clickhouse --> dlq refactor [closed]

---

🧪 Issue enrichment is currently in open beta.

You can configure auto-planning by selecting labels in the issue_enrichment configuration.

To disable automatic issue enrichment, add the following to your .coderabbit.yaml:

issue_enrichment:
  auto_enrich:
    enabled: false

💬 Have feedback or questions? Drop into our discord!