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_KEYyou registered. Bucker signs a ten-minute RS256 JWT per request (issis<ACCOUNT>.<USER>.SHA256:<fingerprint>). The host is<account>.snowflakecomputing.comand nothing else. - BigQuery: service-account impersonation. You grant
roles/iam.serviceAccountTokenCreatoron 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
PUTa 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):
exporter.tsbuildsSnowflakeSinkContext/BigQuerySinkContextfrom the opened config (readSnowflakeConfig,readBigQueryConfig) the way it does forOBJECT_STORE.service.tsvalidates the config at creation (normalizeSnowflakeConfig,normalizeBigQueryConfig), runstestSnowflakeConnection/testBigQueryConnectionbefore a row exists, and checks the host against the egress list.- A deployment supplies the per-organization Google identity with
setGoogleImpersonationSource. destinations_updatecallsplanSnowflakeSchemaUpdate/planBigQuerySchemaUpdatefor the preview andapply*SchemaUpdatewith the approved digest.