# 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.
