BigQuery

Load jobs straight from the worker. Native `MERGE`. No bucket to provision.

Every destination receives the same layout; see the tables Ingest creates.

Setup

Ingest needs to create datasets and tables in your project, run load jobs into them, and read back the small _ingest_* state tables it keeps beside your data. Nothing else: no billing access, no other Google API.

The one thing people get wrong

Two roles are needed, not one. roles/bigquery.dataEditor grants bigquery.datasets.create but not bigquery.jobs.create; roles/bigquery.jobUser grants the job permission and no data access. Granting only Data Editor produces a destination that connects, creates a dataset, and then fails every load, which reads like a broken pipeline rather than a missing grant.

Pick BigQuery in the deploy wizard and press Sign in with Google: consent opens in a small popup, you approve BigQuery access only, and Ingest stores the refresh token in your own secret namespace; it never sees your password. After you approve, Ingest lists the projects your account can see and runs the connection check itself; there is no project id to hunt down and no test button to remember.

Whoever signs in must already hold the two roles below: signing in grants Ingest the account's access, it does not add any.

On Google Workspace, check this first. If your administrator runs a session-control (reauthentication) policy, Google expires this grant on their schedule even after you approve it: the destination works, then stops, and the connection test says the authorisation is no longer valid. On such an organisation use Option B: a service-account key is not subject to that policy.

Then test it

Press Test connection. A worker connects with your credential, creates a scratch dataset named test_ingest_conn_… and a table in it, runs a real load job, reads the row count back, round-trips pipeline state, and drops the lot. Writing is the point: a credential that can create a dataset but not run a load job (Data Editor without Job User, exactly the trap above) fails here instead of on your first real run.

Staging

needs_staging: none. BigQuery load jobs accept a local file through the client library, and the load is atomic: either every record lands or none does. So there is no object-storage hop: no GCS bucket to create, and no cross-account grant for one. Of every warehouse here, BigQuery is the one that needs a single credential and nothing else.

Redshift is the contrast worth knowing: its COPY cannot read a local file, which is why it asks for an S3 staging prefix and an IAM role. That requirement is Redshift's, not a house style, and it does not apply here.

Writes

append: one load job per part, preceded by a sweep of this load's own _ingest_load_id, so a package that half-landed converges on retry instead of double-counting.
replace: staged, then a single CREATE OR REPLACE TABLE … AS SELECT. One atomic commit, because a reader querying mid-swap must never see a partial table.
merge: staged, NULL keys refused before anything touches the target, then a native MERGE whose USING clause carries the dedup (latest dedup_sort wins). A merge source with duplicate keys is an error in BigQuery rather than a coin flip, so the dedup cannot be a separate pass.

There are no client-side transactions across a load job and its DML, so the adapter declares supports_transactions: false and documents idempotent recovery instead of claiming a rollback that would only cover half the operation.

Conformance

Every adapter passes the same suite before it ships: create a table with every canonical type, append 100k rows, atomic replace, merge (latest dedup_sort wins; child tables delete-then-insert by root id), schema evolution, NULL-key rejection, package replay without duplicates, state and schema round-trip, row-count read-back, identifier edge cases, double-underscored table names (__quarantine, child tables), and rollback on a mid-load failure.