Skip to content

ADR-0420: BigQuery database sink

A real type: bigquery backend for the database sink family: snapshot upserts with delete reconciliation into a partitioned, clustered BigQuery table via atomic MERGE DML — Workload Identity first, service-account key as the explicit fallback.

Theme: 04 · Export & sinks · Status: Current

Context

The database sink family (ADR-0414) ships Postgres (ADR-0402), MongoDB (ADR-0417), and BigQuery. The bigquery type is registered in the backend registry and admitted by the CRD and webhook together, so an admissible sink is exportable.

Why BigQuery as the next database backend:

  • Fleet teams on GCP want SQL analytics and dashboards over inventory (Looker/Data Studio, ad-hoc Standard SQL) without operating a Postgres instance — a serverless analytics projection of the same canonical snapshot.
  • It exercises a genuinely different corner of the family contract: no enforced primary keys, no transactions across statements, partition/clustering instead of indexes, job-based execution. Surviving that without changing the family CRD is further proof the abstraction holds.

The backend must honor the locked database-family contracts:

  • Identity (inventory_namespace, inventory_name, target_name, source_uid) and delete reconciliation — stale rows for an (inventory, cluster) partition are removed each export; an empty snapshot clears the partition (ADR-0401, ADR-0402). The Postgres backend implements this in internal/sink/postgres/backend.go; column naming below mirrors its DDL exactly.
  • Credentials never in spec/status/logs — secret material only via Secret references.
  • Cross-cutting provisioning (ADR-0416): ensure (default) creates the destination table if missing; existing never issues DDL and preflights existence.
  • Capabilities are the relational-store profile (cap.RelationalStore(): state store with SupportsDelete), so empty snapshots still reach Export as [] and prune stale rows.
  • Connection-test parity — automatic probe plus KollectConnectionTest (ADR-0403), wired through internal/sink/probe.go like postgres.TestConnection / mongodb.TestConnection.

Decision

1. type: bigquery on the database family — no new kind

KollectDatabaseSink gains a real bigquery backend behind the existing spec.type enum. The placeholder BigQuerySpec (api/v1alpha1/sink_common_types.go) becomes a full config block:

apiVersion: kollect.dev/v1alpha1
kind: KollectDatabaseSink
metadata:
  name: fleet-analytics
  namespace: team-a
spec:
  type: bigquery
  cluster: prod-west            # optional — labels rows; clustering key
  exportMinInterval: 5m         # optional — also the cost lever (see Consequences)
  provisioning:
    mode: ensure                # ensure (default) creates the table; never the dataset
  bigquery:
    project: acme-fleet-analytics
    dataset: inventory          # must already exist — kollect never creates datasets
    table: inventory_items
    location: EU                # optional — job placement; defaults to dataset location
    secretRef:                  # optional — omit to use ADC / Workload Identity
      name: bigquery-sa-key
      namespace: kollect-system

// BigQuerySpec configures BigQuery relational export (ADR-0420).
type BigQuerySpec struct {
    // project is the GCP project id that owns the dataset.
    // +required
    Project string `json:"project"`

    // dataset is the BigQuery dataset id; it must already exist.
    // +required
    Dataset string `json:"dataset"`

    // table is the destination table name.
    // +required
    Table string `json:"table"`

    // location pins job placement (for example EU); defaults to the dataset location.
    // +optional
    Location string `json:"location,omitempty"`

    // secretRef references a Secret holding a service-account JSON key
    // (key credentials.json). When absent, Application Default Credentials are used.
    // +optional
    SecretRef *SecretReference `json:"secretRef,omitempty"`
}

project, dataset, and table are required; location and secretRef are optional. No conversion machinery is provided under the v1alpha1 posture in ADR-0414.

2. Authentication — ADC first, key file as the explicit alternative

Exactly two modes, resolved in this order:

Mode Trigger Mechanism
ADC / Workload Identity Federation (primary) bigquery.secretRef absent Client built with no explicit credentials; the manager pod's bound service account resolves via Application Default Credentials (GKE Workload Identity or federated WIF elsewhere)
Service-account JSON key (alternative) bigquery.secretRef set Secret key credentials.json passed as client credentials JSON

No other auth modes (no API keys, no OAuth user flows, no access-token fields). The builder (internal/sink/build_context.go) gains a bigquery branch resolving spec.bigquery.secretRef into BuildContext.DatabaseSecretData — the same seam spec.postgres.databaseRef uses — except the ref is optional, and an absent ref is valid (ADC), not an error.

Required IAM on the bound principal (documented on the CRD page, not enforced by kollect): roles/bigquery.dataEditor on the dataset plus roles/bigquery.jobUser on the project.

3. Write path — one atomic MERGE per export, no streaming inserts

Three candidates were evaluated against the export semantics (idempotent whole-snapshot upserts, debounced cadence via exportMinInterval, payload bounded by the ~1.5 MiB maxExportBytes envelope cap from ADR-0103):

Path Verdict Why
Streaming inserts (legacy insertAll / Storage Write API) Rejected Append-only — every export would duplicate rows instead of upserting; rows in the streaming buffer cannot be touched by DML for up to ~90 minutes, which breaks delete reconciliation outright; per-byte ingest cost on every export cycle
Load job into staging + MERGE Fallback, deferred Free ingest and unbounded size, but two jobs per export plus staging-table lifecycle/GC to manage
Parameterized MERGE DML with UNNEST(@rows) Chosen One atomic, idempotent statement per export; upsert and stale-delete in a single job; no staging artifacts; works against the emulator

The chosen statement binds the snapshot as an ARRAY<STRUCT<...>> query parameter and mirrors the Postgres unnest($4::text[], $5::text[]) stale-delete pattern:

MERGE `project.dataset.table` AS t
USING UNNEST(@rows) AS s
ON  t.inventory_namespace = @inv_ns AND t.inventory_name = @inv_name
AND t.cluster = @cluster
AND t.target_name = s.target_name AND t.source_uid = s.source_uid
WHEN MATCHED THEN UPDATE SET
  payload = s.payload, exported_at = @exported_at,
  resource_namespace = s.resource_namespace
WHEN NOT MATCHED BY TARGET THEN INSERT
  (inventory_namespace, inventory_name, target_name, source_uid,
   cluster, resource_namespace, payload, exported_at)
  VALUES (@inv_ns, @inv_name, s.target_name, s.source_uid,
          @cluster, s.resource_namespace, s.payload, @exported_at)
WHEN NOT MATCHED BY SOURCE
  AND t.inventory_namespace = @inv_ns AND t.inventory_name = @inv_name
  AND t.cluster = @cluster
THEN DELETE

Size budget: the snapshot payload is capped at ~1.5 MiB (ADR-0103), far below the 10 MB query-request limit, so the array parameter always fits. Should a future change lift the envelope cap, the load-job + staging MERGE variant is the designated escape hatch — a backend implementation detail, no CRD change.

4. Schema mapping — mirror the Postgres column set

Columns replicate the Postgres DDL in internal/sink/postgres/backend.go one-to-one so the two backends stay interchangeable behind the family CRD:

Column Postgres BigQuery Notes
inventory_namespace TEXT NOT NULL STRING REQUIRED identity
inventory_name TEXT NOT NULL STRING REQUIRED identity
target_name TEXT NOT NULL STRING REQUIRED identity
source_uid TEXT NOT NULL STRING REQUIRED identity
cluster TEXT NOT NULL DEFAULT '' STRING REQUIRED from spec.cluster; empty string when unset
resource_namespace TEXT NOT NULL DEFAULT '' STRING REQUIRED item namespace, inventory namespace fallback
payload JSONB NOT NULL JSON REQUIRED full Item row (ADR-0405)
exported_at TIMESTAMPTZ NOT NULL TIMESTAMP REQUIRED UTC export time

Physical layout (ensure DDL):

  • Time partitioning on TIMESTAMP_TRUNC(exported_at, DAY).
  • Clustering on cluster, inventory_namespace, inventory_name, resource_namespace (BigQuery maximum of four clustering columns).
  • No enforced uniqueness — BigQuery has no enforced primary keys. Row identity is maintained purely by the MERGE semantics above; the ensure DDL may declare PRIMARY KEY (...) NOT ENFORCED as an optimizer hint, but correctness never depends on it.

Honest caveat: because upserts rewrite exported_at, live rows migrate to the current day's partition on every export and stale rows being deleted live in older partitions — so the delete side of the MERGE cannot be partition-pruned. Clustering on cluster + inventory columns is what bounds scanned bytes, and partitioning chiefly benefits downstream analytical queries and optional retention (partition expiration is out of scope for v1 — see Open questions).

5. Provisioning and delete semantics

  • provisioning.mode: ensure (default) creates the table with the partitioning/clustering spec if missing — once at backend construction, like the Postgres ensureTable (PERF-02: pooled backends do not repeat DDL per export). It never creates the dataset: a dataset is a billing/location-scoped container, the same way the Postgres backend never creates the database.
  • provisioning.mode: existing never issues DDL and preflights that the table exists, failing loudly with a terminal error when it does not.
  • Delete reconciliation is carried entirely by the MERGE (WHEN NOT MATCHED BY SOURCE … THEN DELETE, scoped to the (inventory, cluster) partition). An empty snapshot still reaches the backend as [] (cap.RelationalStore() semantics in internal/sink/cap) and degenerates to a plain scoped DELETE, exactly matching the Postgres empty-snapshot branch. Inventory deletion therefore clears its rows on the final empty export — no extra finalizer machinery beyond what Postgres has today.

6. Connection-test probe

RunConnectionTest (internal/sink/probe.go) gains a bigquery case, surfaced through the standard sink condition and KollectConnectionTest flow (ADR-0403). The probe is side-effect-free (no DDL, regardless of provisioning mode) and checks, in order:

  1. Credential resolution — ADC chain or credentials.json from the Secret resolves to a token source (catches missing Workload Identity bindings and malformed keys).
  2. Dataset existence — dataset metadata GET (catches wrong project/dataset and missing dataEditor grants).
  3. Job execution — a dry-run SELECT 1 query job (validates jobUser and job placement without scanning bytes or incurring cost).

Success message: BigQuery dataset metadata and dry-run query succeeded.

7. Error classification

Mapped onto the typed reconcile classes from ADR-0602 (internal/errors), keyed on googleapi.Error HTTP codes and job-status error reasons:

Signal Class Rationale
400 invalid / invalidQuery terminal bad config or schema drift; retry cannot help
401 / 403 accessDenied terminal credential/IAM misconfiguration until spec or binding changes
404 notFound (dataset; table in existing mode) terminal preflight contract violated
409 duplicate on ensure DDL success benign create race — treat as already-exists
429 rateLimitExceeded / quotaExceeded transient back off and retry; circuit breaker absorbs sustained throttling
5xx backendError / internalError, network timeouts transient standard retry-with-backoff

Unknown reasons default to transient, consistent with errors.ClassOf.

8. Validation and webhook rules

ValidateDatabaseSinkSpec (internal/validation/family_sink.go) already requires spec.bigquery and forbids the postgres/mongodb sibling blocks for type: bigquery. This ADR adds:

  • bigquery.project, bigquery.dataset, bigquery.table required, non-blank.
  • bigquery.secretRef optional; when set, the same Secret-reference shape rules as postgres.databaseRef. No mutually exclusive auth fields exist by construction — ADC is simply the absence of secretRef.
  • spec.layout stays forbidden and serialization.format stays JSON-only for the database family (capability matrix, ADR-0419).
  • The CRD enum, webhook allowlist, and backend registry all contain bigquery, keeping "admissible implies exportable" true.

9. Test evidence

The implementation satisfies the ADR-0706 ladder:

  • L0 unit: config resolution (required fields, secret-key lookup, ADC default), MERGE/DDL SQL builders (golden statement fixtures), error-classification table tests, webhook validation cases.
  • L3 integration (-tags=integration, testcontainers): the goccy/bigquery-emulator image — export rows and assert content, re-export mutated snapshot and assert upsert + stale delete, empty snapshot clears the partition, existing mode fails on a missing table, and the probe path. The emulator suite validates MERGE … UNNEST(@rows) directly.
  • Schema/manifests: golden OpenAPI spec fragment for the database sink CRD under test/schema/golden/ (extending the cases in test/schema/extract.go), a config/samples/advanced/kollect_v1alpha1_kollectdatabasesink_bigquery.yaml sample, and a refreshed docs/crds/kollectdatabasesink.md page.
  • Live GCP e2e (real project, WIF, real quotas) is maintainer-only and never runs in CI — there is no hermetic, free, secret-less way to exercise real IAM from a public repo.

Consequences

Positive

  • GCP-native analytics projection of inventory with full delete-reconcile and probe parity; the family CRD again absorbed a new backend without changing shape or the inventory reference model.
  • Serverless destination — no database to operate; partitioned/clustered layout keeps downstream dashboard queries cheap.
  • The single-statement MERGE write path keeps exports atomic and idempotent with no staging artifacts to garbage-collect.

Negative

  • New dependency surface: cloud.google.com/go/bigquery plus its google.golang.org/api / auth transitive tree — the first google-cloud-go SDK in the operator image (the GCS sink deliberately uses the S3-compatible XML API, internal/sink/gcs). Image size and vulncheck scope grow accordingly.
  • Emulator fidelity limits: the emulator approximates Standard SQL via ZetaSQL bindings and does not reproduce IAM, quotas, partition pruning, or job billing. L3 proves the SQL contract, not GCP behaviour — hence the maintainer-only live e2e.
  • Cost/quota honesty: every export is a query job; on-demand billing charges scanned bytes, and the delete side of the MERGE cannot be partition-pruned (clustering bounds it instead). On large tables with aggressive cadence this costs real money — exportMinInterval is the lever, and the CRD docs must say so plainly. DML statements also occupy slot capacity and per-table concurrent-mutation limits; sustained throttling surfaces as transient errors through the circuit breaker rather than data loss.
  • One more backend to harden and keep green under the "no merge without integration proof" bar.

Alternatives considered

  • Separate KollectBigQuerySink kind — rejected: ADR-0414 settled on family CRDs precisely so backends are type values, not kinds; a parallel kind would fork RBAC, refs, and status handling for zero expressiveness gain.
  • Bigtable — rejected: wide-column NoSQL with key-range scans, no SQL MERGE, and no analytics query model; it serves none of the SQL-dashboard use cases that motivate a database-family analytics backend.
  • Streaming inserts as the primary write path — rejected (§3): append-only duplicates conflict with snapshot-upsert semantics, and the streaming buffer blocks DML deletes for up to ~90 minutes, breaking delete-reconcile parity.

Open questions

  • OPEN: Should ensure ever create the dataset (currently: never, by design)? Creating it would need a location decision kollect should arguably not own.
  • OPEN: Optional partition-expiration / retention field on BigQuerySpec (cost control for high-churn fleets) — out of scope for v1, revisit with operator feedback.

See also