Skip to content
bucker

Warehouse sinks: Snowflake and BigQuery

Bucker can write the streams a sink carries (ISSUE_ROLLUPS, ERROR_ESSENCES, and the rest) into a table in your own Snowflake account or BigQuery project. This page states what each sink promises, what it needs from you, and what it will never do on its own.

Status: the drivers are built and tested against recorded request and response fixtures only. No live Snowflake account or BigQuery project has been exercised yet, and a deployment's exporter has to hand each sink its connection before the kind can be created (see "What a deployment must wire").

The guarantee

Delivery is at least once. The table holds each (batch key, record id) exactly once.

The exporter derives a batch key from the batch's starting cursor, so a batch handed over again after a crash or a timeout carries the same key. Each driver maps that key onto the warehouse's own dedupe primitive, and then reads the count back.

Snowflake BigQuery
Dedupe MERGE ... ON t._BATCH_KEY = s._BATCH_KEY AND t._RECORD_ID = s._RECORD_ID the same MERGE, from a staging table
Retry of the same work the same statement over the same keys updates the rows it inserted job ids are a function of (table, batch key, content): a retried job answers 409 duplicate and the driver polls the original; an original found FAILED is run again as <id>-r1, -r2, ... so a quota blip or a cured schema drift really retries
Read-back SELECT COUNT(*) ... WHERE _BATCH_KEY = <key> AND _DAY = <day> the load job's outputRows, then a parameterised COUNT(*) on the target for the key and the day
A disagreement WarehouseReadBackMismatchError carrying the batch key and both counts, never a row the same, and a short load never reaches the MERGE

The day is part of the read-back because a batch that straddles midnight is delivered as one write per UTC day under the same batch key.

What this does not cover: a replay that carries FEWER rows than an earlier attempt under the same key and day (a record erased between the two) leaves the earlier rows in place, so the count disagrees and the batch is retried until the ledger pauses the sink with the mismatch as evidence. A row deleted from your table after the batch landed is not re-sent (the cursor has moved past it), and two batches with different keys that carry the same record id are two rows, because the record id alone is not the key.

The table

One table per sink, a fixed shape plus the columns you choose to promote.

Column Type (Snowflake / BigQuery) Holds
_batch_key, _record_id VARCHAR / STRING the dedupe key
_stream, _day VARCHAR, DATE the stream and its UTC day (BigQuery partitions on _day)
_watermark, _loaded_at TIMESTAMP_NTZ / TIMESTAMP the source cursor and the load time
_scrub_tier VARCHAR / STRING always stored
data VARIANT / JSON the whole record, already scrubbed

A new upstream field lands inside data; it never changes the table. A field you want as a typed column (a join key such as issue_id, trace_id, release) is promoted by naming it in the sink's columns (type, and optionally the record field it reads). Joining errors to your own revenue tables is then a join on those columns.

Credentials

Sealed with the rest of the sink's config; no route returns them.

  • Snowflake: a key pair. A PKCS#8 RSA private key (optionally encrypted, with its passphrase) of a service user whose RSA_PUBLIC_KEY you registered. Bucker signs a ten-minute RS256 JWT per request (iss is <ACCOUNT>.<USER>.SHA256:<fingerprint>). The host is <account>.snowflakecomputing.com and nothing else.
  • BigQuery: service-account impersonation. You grant roles/iam.serviceAccountTokenCreator on your service account to the Bucker identity dedicated to your organization, and grant that service account BigQuery access on one dataset. Bucker holds no key of yours. The identity it presents is per organization on purpose: a service-account email is not a secret, and Google has no second factor on the grant, so a shared Bucker identity would let another customer name your account.

Adding a credential or an egress host is a human act (connection.credential.set, egress.host.add). Add the sink's host to the organization's egress list first: <account>.snowflakecomputing.com, or bigquery.googleapis.com (and iamcredentials.googleapis.com for the token).

Errors

What the warehouse says What Bucker does
401 or 403, accessDenied auth_failed; retried, and the sink is paused after an hour of failing
a suspended or missing warehouse, a revoked grant, a missing dataset, a statement it will refuse again permanent: the sink is paused with the reason; no attempts are spent
a column the table lacks schema_drift, permanent: propose pipeline.destination_schema.update
429, 5xx, a timed-out or cancelled statement, a quota or backend error retried, honouring Retry-After
a read-back that disagrees retried (a re-MERGE converges); the evidence is the batch key and two counts

The error text is cut to one line and every quoted fragment is removed, because a warehouse echoes pieces of the statement that failed and the statement carries your data. It is rendered as data.

Changing the schema

The drivers never ALTER a table. Adding a column is pipeline.destination_schema.update: a proposal that shows the column, its type, the one table it touches and the exact statement, and that runs only after a person approves that statement (the apply refuses any digest but the plan's, and any statement other than the one it builds itself for that column on the sink's table). The action is registered as SENSITIVE, ask, pipeline:write, but it is still status: planned and not in force: this package ships the plan and the digest-bound apply, and the governed destinations_update route and tool that put a person's approval in front of the apply arrive with WH-09. Until then nothing calls the apply. There is no automatic inverse: a dropped column is your act.

Limits

  • Snowflake: a batch travels inside the statement as a JSON literal (the SQL API cannot PUT a file), in statements under 400 KB; a single record over about 200 KB is refused as permanent.
  • BigQuery: one load per batch, at most 8 MiB of NDJSON; a larger batch is refused as permanent and the exporter's batch size is what to lower. A staging table (_bucker_stage_<hash>) exists only while a batch is loading and is dropped after the merge; one left by a crash is overwritten by an identical replay, and is not swept otherwise (a janitor is a follow-up).

What a deployment must wire

The drivers report configured: false until the exporter hands a sink its context, so creation keeps refusing the kinds with an explanation instead of returning a 201 for a sink that cannot write. The wiring (it belongs to the destinations and exporter wiring packages, not to the drivers):

  1. exporter.ts builds SnowflakeSinkContext / BigQuerySinkContext from the opened config (readSnowflakeConfig, readBigQueryConfig) the way it does for OBJECT_STORE.
  2. service.ts validates the config at creation (normalizeSnowflakeConfig, normalizeBigQueryConfig), runs testSnowflakeConnection / testBigQueryConnection before a row exists, and checks the host against the egress list.
  3. A deployment supplies the per-organization Google identity with setGoogleImpersonationSource.
  4. destinations_update calls planSnowflakeSchemaUpdate / planBigQuerySchemaUpdate for the preview and apply*SchemaUpdate with the approved digest.