storage: no metadata-only path to update BigQuery clustering on an existing table (Storage API's PUT .../definition lacks a clustering field)

[vojtechnovotny-heu]

Summary

kbagent can already apply BigQuery clustering to an existing Storage table, but only via a
full copy: storage create-table --source-table-id ... --clustering-field ... +
storage swap-tables (shipped v0.67.0/v0.71.0, CLI + REST + the web UI's "Repartition" tab).
BigQuery's own API supports this as a metadata-only PATCH with no data rewrite (tables.patch,
clustering field on the Table resource —
https://cloud.google.com/bigquery/docs/reference/rest/v2/tables#clustering; see also
https://docs.cloud.google.com/bigquery/docs/manage-partition-cluster-recommendations#apply_clusters_directly_to_the_original_table).
Keboola's Storage API doesn't expose that yet: clustering/timePartitioning/
rangePartitioning only exist on the create-time POST .../tables-definition endpoint. The one
endpoint that patches an existing typed table, PUT /v2/storage/tables/{table_id}/definition,
has no such field — checked against the canonical apiary.apib spec; its attributes are limited
to displayName, description, and per-column length/nullable/default/description.

Current workaround, and its cost

The copy+swap path already works end-to-end and is documented in our own team's kbagent skill.
But it:

• requires a full read+write of the table's data, not a metadata change — expensive/slow on
  large tables;
• needs a second physical table to exist temporarily (2x storage during the swap window);
• has a known swap-timeout false negative (STORAGE_JOB_TIMEOUT on a 60s poll can fire even
  though the async Storage job actually succeeded server-side), so a swap has to be verified
  physically rather than trusted from the client's response.

None of that should be necessary for what's fundamentally a metadata-only change on BigQuery's
side.

Proposal

Extend PUT /v2/storage/tables/{table_id}/definition (Storage API) with an optional
clustering attribute (mirroring the shape already used on the create-time endpoint), proxying
to BigQuery's tables.patch. Once that endpoint exists, the kbagent-side addition is small and
follows the existing pattern exactly:

• client/storage_tables.py — a new method calling the new endpoint, mirroring create_table's
  existing clustering handling (client/storage_tables.py:556-562).
• services/storage_service.py — a sibling to _build_bigquery_layout
  (services/storage_service.py:200-273), with the same BigQuery-only pre-flight guard already
  used for the create-time flags (services/storage_service.py:943-967).
• commands/storage.py — a new command (e.g. storage set-clustering) or a flag addition to an
  existing describe/definition command.
• server/routers/storage.py — a matching REST route.

No new GCP credential handling is needed anywhere — this stays entirely inside kbagent's existing
"talk only to Keboola's Storage API" model. Confirmed: no google-cloud-bigquery/google-auth
dependency exists in the installed package today.

I recognize the actual gap sits in Keboola's Storage API, not kbagent's code — filing here since
keboola/cli is where I can raise it, and the kbagent-side work is worth tracking alongside
whatever Storage API change it depends on.

Not a duplicate

Checked gh issue list --repo keboola/cli --search "cluster|clustering|partition" --state all.
#621 (closed) is the closest hit — it was about table-detail not returning the table's
registered definition (the create-time layout), fixed in v0.88.0. Nothing found asking for an
in-place/no-rewrite clustering update path.

Environment

• kbagent v0.91.0
• Keboola BigQuery-backed project (stack connection.europe-west3.gcp.keboola.com)

[padak]

Did some digging into feasibility on the Storage API side (traced the update path through the connection repo's BigQuery driver — happy to share the specifics with the Storage/API team when this gets scoped).

One thing worth confirming before it goes further: BigQuery's tables.patch clustering update is metadata-only in the sense that it only changes the policy for how new data gets organized going forward — it does not retroactively recluster rows already in the table. Per Google's own docs (https://docs.cloud.google.com/bigquery/docs/manage-clustered-tables), existing rows keep their current physical layout until either:

• they're rewritten (e.g. UPDATE t SET col = col WHERE true, a full read+write billed like a query), or
• you do exactly the copy+swap this issue is trying to avoid.

So a metadata-only PATCH endpoint would still be a real win for "make this table clustered going forward, without a 2x-storage copy+swap window" — but it would not be a drop-in performance fix for an already-large table that needs to benefit from clustering immediately. That case still needs the existing copy+swap (or an explicit rewrite) to actually reorganize the historical data.

Can you confirm that matches your intent — i.e. the ask is scoped to "policy for new writes" rather than "immediate reorg of existing data"? Want to make sure that's explicit before it lands in any Storage API scoping discussion, so expectations on both sides (requester and Storage API team) are aligned.

[vojtechnovotny-heu]

Circling back on the "policy for new writes" vs "immediate reorg" distinction from the comment above — that split maps cleanly onto a real backlog we're working through on a lift-and-shift Snowflake→BigQuery migration, and it's worth naming the use case explicitly for anyone who lands on this issue.

Where a metadata-only clustering PATCH would fully replace copy+swap, not just improve it:

• Small tables (roughly <10GB in our case) where the physical-layout recommendation is clustering only — no partitioning needed. For these, a PATCH sets the go-forward policy at zero cost, and either (a) an incrementally-loaded table reclusters itself naturally as new rows land, or (b) if an immediate reorg is ever wanted, you run UPDATE ... WHERE true yourself, deliberately, at a time of your choosing — instead of that rewrite being forced on you as the only way to change the spec at all.
• Any table that's already partitioned and just needs its clustering key changed. Same logic: the reorg is optional and deferrable, the policy change isn't.

That's a meaningful slice of a typical post-migration cleanup backlog, since not every under-optimized table needs both partitioning and clustering — plenty just need clustering, and BigQuery has no ALTER path to add partitioning after the fact regardless, so this endpoint's ceiling is bounded to that slice by BigQuery's own limits, not Keboola's.

One more argument for prioritizing it, from direct experience this week: the existing copy+swap path (tables-definition + source) has a confirmed backend bug where the copy can silently drop the table's _timestamp system column and/or scramble column order — reproduced twice, independently, on a ~900GB table. This is already a WIP fix on Keboola's side, tracked at https://keboola.atlassian.net/servicedesk/customer/portal/9/SUPPORT-17454. A PATCH-based path sidesteps that class of bug entirely for whatever it covers, because no copy happens at all. For the clustering-only slice above, that's not just cheaper — it's a strictly safer mechanism, with no swap window and no column-parity gate needed.

[padak]

Thanks for the detail — that's exactly the split I was hoping to confirm, and the two cases you named (clustering-only tables under ~10GB, and already-partitioned tables needing a clustering-key change) map cleanly onto what a metadata-only PATCH endpoint could fully cover, bounded by BigQuery's own inability to add partitioning after the fact.

The _timestamp/column-order bug you found in the copy+swap path (SUPPORT-17454) is a strong additional argument on its own: for the slice of tables that only need clustering, a PATCH endpoint isn't just cheaper than copy+swap, it sidesteps that bug class entirely since no copy ever happens.

We've scoped the implementation path on the connection/BigQuery-driver side (the update handler already builds a tables.patch call for the existing fields, so adding clustering follows an existing pattern rather than a new one). To make that concrete rather than just descriptive, we're going to put together an illustrative draft PR in connection showing the shape of the change — proto/request/validation/driver touch points — as a scoping aid for whoever picks this up. It won't be meant to merge as-is (no access to the driver's test infrastructure from here), but should save the Storage API team the initial discovery work. Will link it here once it's up.

[padak]

The sketch PR is up: keboola/connection#8290 (draft).

It walks the full path — proto oneof, request validation, backend/column gates on the synchronous path, job params with back-compat defaults, the BigQuery driver patch, and a Snowflake rejection — and reuses the existing Clustering message and REST mapping from the create-time path rather than inventing anything.

Two findings from writing it that are worth knowing regardless of whether that PR ever moves:

1. driverMetadataInfo has to be kept in step. It's written once at table create and never re-derived, and snapshot restore rebuilds a table from the partitioning/clustering recorded there. A clustering update that doesn't refresh it means a later snapshot restore silently brings back the old layout — same failure mode PR #7822 fixed for swap-tables.
2. The BigQuery update handler never set TableInfo.meta at all. When adding it, reporting only the clustering (the thing the command changed) would make any consumer that persists the response drop the partitioning it can't see — so the response has to carry the whole layout.

Deliberately not verified: no live BigQuery run, no test-suite execution, and the generated protobuf class is hand-written pending an official regeneration. It's a scoping aid, not a merge candidate — all spelled out in the PR description.