docs / destinations / bigquery

BigQuery

deployable todaymerges

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

Ingest lands typed, deduplicated tables in BigQuery from any API it connects, including Abilene TX 311, Albany NY 311, Ann Arbor MI 311 and Anne Arundel County MD 311. You own the BigQuery account and the credentials; Ingest keeps no copy of the data.

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

Setup

Before you start

You need a billing-enabled Google Cloud project and a project administrator. Setup needs Service Usage Admin, Workload Identity Pool Admin, and Project IAM Admin permissions. Ingest receives only BigQuery Data Editor and Job User. No Google key or password is needed. Copy workspace-specific commands from the signed-in Ingest panel. Public docs contain placeholders for Ingest's AWS account and role. In the signed-in panel, only your Google project ID needs replacing.

1. Choose your project

  • Open Google Cloud Welcome. Use the project picker at the top to select the project for your BigQuery data.
  • Copy the Project ID shown under Welcome (e.g. acme-analytics-123456). This is your GCP project ID; use the ID, not the project name or number.
  • Click Activate Cloud Shell. Run this command with your GCP project ID filled in, and check the printed project. Authorize Cloud Shell if prompted. Keep the same terminal open for all three blocks. In the signed-in panel, the ID you enter above the command fills it in; on the public docs page, replace your-project-id yourself and keep the quotes.
INGEST_GCP_PROJECT="your-project-id"
gcloud projects describe "${INGEST_GCP_PROJECT}" \
  --format="table(projectId,name,projectNumber)"
  • Leave Dataset location at the bottom empty for US, or enter your location there. Examples: EU or us-central1. For an existing dataset, use its exact location. US and us-central1 are different locations. A saved project, provider, or location cannot be edited; create a new destination to correct it.

2. Connect your workspace

  • Run in the same Cloud Shell. Workload Identity Federation lets Google verify your workspace's AWS role and issue short-lived tokens. The condition trusts only this workspace.
gcloud services enable \
  bigquery.googleapis.com iam.googleapis.com \
  cloudresourcemanager.googleapis.com sts.googleapis.com \
  iamcredentials.googleapis.com \
  --project="${INGEST_GCP_PROJECT:?Run step 1 first}" &&
gcloud iam workload-identity-pools create ingest \
  --project="${INGEST_GCP_PROJECT}" --location="global" &&
gcloud iam workload-identity-pools providers create-aws ingest-aws \
  --project="${INGEST_GCP_PROJECT}" --location="global" \
  --workload-identity-pool="ingest" \
  --account-id="<ingest-aws-account>" \
  --attribute-mapping="google.subject=assertion.arn,attribute.aws_role=assertion.arn.extract('assumed-role/{role_name}/')" \
  --attribute-condition="assertion.arn.startsWith('<ingest-role-sts-prefix>')"
  • Continue only after success. Already exists? Ask your administrator to verify the existing provider. The ingest pool and ingest-aws provider must have the same AWS account, mapping, and workspace condition shown here. Do not delete or replace an existing provider to clear this error.

3. Grant access

  • Run this. Both project roles are required: Data Editor for datasets and tables, Job User for load and query jobs. The command looks up the numeric project number for you.
INGEST_GCP_NUMBER="$(gcloud projects describe "${INGEST_GCP_PROJECT:?Run step 1 first}" \
  --format='value(projectNumber)')" &&
INGEST_GCP_MEMBER="principalSet://iam.googleapis.com/projects/${INGEST_GCP_NUMBER}/locations/global/workloadIdentityPools/ingest/attribute.aws_role/<ingest-role-name>" &&
gcloud projects add-iam-policy-binding "${INGEST_GCP_PROJECT}" \
  --member="${INGEST_GCP_MEMBER}" --role="roles/bigquery.dataEditor" \
  --condition=None &&
gcloud projects add-iam-policy-binding "${INGEST_GCP_PROJECT}" \
  --member="${INGEST_GCP_MEMBER}" --role="roles/bigquery.jobUser" \
  --condition=None &&
printf '\nWorkload identity provider:\n//iam.googleapis.com/projects/%s/locations/global/workloadIdentityPools/ingest/providers/ingest-aws\n' "${INGEST_GCP_NUMBER}"
  • Paste the final output line into Workload identity provider at the bottom.
  • Leave Service account to impersonate (rarely needed) empty. Access is granted directly to your workspace identity; no Google service account is needed.

4. Test the connection

  • Click Test & configure at the bottom. Writes and reads temporary data, checks pipeline state, then attempts cleanup. Review any cleanup warning. If the test fails, fix the reported problem and choose Test again.

Staging

The worker submits Parquet files directly to BigQuery load jobs. No GCS staging bucket or bucket permission is required.

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.

Questions

Can Ingest load data into BigQuery?
Yes. BigQuery is live on Ingest's hosted service: the adapter passes all 15 conformance cases against the real service, and the setup steps are on this page.
Does Ingest merge into BigQuery, or only append?
BigQuery merges on the resource's primary key, so re-running a sync updates existing rows rather than duplicating them. Append and full replace are available per resource.
What does Ingest need to connect to BigQuery?
GCP project ID and Workload identity provider. Secrets are entered in a form, never in a chat, and held in a secret store under your own path: only the key names ever reach Ingest's database.
Which sources can Ingest load into BigQuery?
Every connector Ingest has certified (Abilene TX 311, Albany NY 311, Ann Arbor MI 311, Anne Arundel County MD 311, Anthropic, Asana, Auburn WA 311, Aurora IL 311, Austin TX 311, Baltimore MD 311, Baton Rouge LA 311, Beaumont TX 311, Bellevue WA 311, Bellingham WA 311, Berkeley CA 311, Bethlehem PA 311, Bloomington IN 311, Boca Raton FL 311, Boston MA 311, Bridgeport CT 311, Brockton MA 311, Brookline MA 311, Buffalo NY 311, Burlington VT 311, Cambridge MA 311, Cambridge MA Open Data, Charleston County SC 311, Charlotte NC 311, Chula Vista CA 311, Cincinnati OH 311, Chicago IL 311, New York City NY 311, Clayton County GA 311, Cleveland OH 311, Cobb County GA 311, Columbus OH 311, Corona CA 311, Dallas TX 311, Davenport IA 311, Denver CO 311, Detroit MI 311, Elgin IL 311, Encinitas CA 311, Everett WA 311, Federal Way WA 311, Fort Lauderdale FL 311, Gilbert AZ 311, Google Ad Manager, Google Analytics 4, Greenwich CT 311, Hartford CT 311, Honolulu HI 311, Houston TX 311, Howard County MD 311, Huntington WV 311, Huntsville AL 311, Indianapolis IN 311, Jersey City NJ 311, Jira, Kansas City MO 311, Kent WA 311, Knox County TN 311, Los Angeles CA 311, Lexington KY 311, Lincoln NE 311, LinkedIn Ads, Livonia MI 311, Louisville KY 311, Lubbock TX 311, Macon-Bibb County GA 311, Manchester NH 311, Maui County HI 311, Medford MA 311, Memphis TN 311, Miami FL 311, Miami-Dade County FL 311, Minneapolis MN 311, Montgomery County MD 311, Multnomah County OR 311, Naperville IL 311, Nashua NH 311, Nashville TN 311, Newark NJ 311, NOAA, New Orleans LA 311, Norfolk VA 311, Oakland CA 311, Omaha NE 311, Open Brewery DB, Paterson NJ 311, Peoria IL 311, Philadelphia PA 311, Pinellas County FL 311, Pittsburgh PA 311, PokéAPI, Portland ME 311, PostgreSQL, Prince George's County MD 311, Provo UT 311, Raleigh NC 311, Ramp, Renton WA 311, The Rick and Morty API, Sacramento CA 311, Salesforce, San Antonio TX 311, San Francisco CA 311, San Gabriel CA 311, San Jose CA 311, San Mateo County CA 311, San Rafael CA 311, Santa Fe NM 311, Scottsdale AZ 311, Seattle WA 311, Shopify, Somerville MA 311, Springfield MO 311, St. Petersburg FL 311, Tacoma WA 311, Toledo OH 311, Topeka KS 311, Vallejo CA 311, Washington DC 311, Wayne County MI 311, West Des Moines IA 311, Wichita KS 311, Worcester MA 311 and Youngstown OH 311), plus any API it authors on request. The data lands in a destination you own, under credentials you control. Ingest keeps no copy of it.

Sources certified against a real API today: Abilene TX 311, Albany NY 311, Ann Arbor MI 311, Anne Arundel County MD 311, Anthropic, Asana, Auburn WA 311, Aurora IL 311, Austin TX 311, Baltimore MD 311, Baton Rouge LA 311, Beaumont TX 311, Bellevue WA 311, Bellingham WA 311, Berkeley CA 311, Bethlehem PA 311, Bloomington IN 311, Boca Raton FL 311, Boston MA 311, Bridgeport CT 311, Brockton MA 311, Brookline MA 311, Buffalo NY 311, Burlington VT 311, Cambridge MA 311, Cambridge MA Open Data, Charleston County SC 311, Charlotte NC 311, Chula Vista CA 311, Cincinnati OH 311, Chicago IL 311, New York City NY 311, Clayton County GA 311, Cleveland OH 311, Cobb County GA 311, Columbus OH 311, Corona CA 311, Dallas TX 311, Davenport IA 311, Denver CO 311, Detroit MI 311, Elgin IL 311, Encinitas CA 311, Everett WA 311, Federal Way WA 311, Fort Lauderdale FL 311, Gilbert AZ 311, Google Ad Manager, Google Analytics 4, Greenwich CT 311, Hartford CT 311, Honolulu HI 311, Houston TX 311, Howard County MD 311, Huntington WV 311, Huntsville AL 311, Indianapolis IN 311, Jersey City NJ 311, Jira, Kansas City MO 311, Kent WA 311, Knox County TN 311, Los Angeles CA 311, Lexington KY 311, Lincoln NE 311, LinkedIn Ads, Livonia MI 311, Louisville KY 311, Lubbock TX 311, Macon-Bibb County GA 311, Manchester NH 311, Maui County HI 311, Medford MA 311, Memphis TN 311, Miami FL 311, Miami-Dade County FL 311, Minneapolis MN 311, Montgomery County MD 311, Multnomah County OR 311, Naperville IL 311, Nashua NH 311, Nashville TN 311, Newark NJ 311, NOAA, New Orleans LA 311, Norfolk VA 311, Oakland CA 311, Omaha NE 311, Open Brewery DB, Paterson NJ 311, Peoria IL 311, Philadelphia PA 311, Pinellas County FL 311, Pittsburgh PA 311, PokéAPI, Portland ME 311, PostgreSQL, Prince George's County MD 311, Provo UT 311, Raleigh NC 311, Ramp, Renton WA 311, The Rick and Morty API, Sacramento CA 311, Salesforce, San Antonio TX 311, San Francisco CA 311, San Gabriel CA 311, San Jose CA 311, San Mateo County CA 311, San Rafael CA 311, Santa Fe NM 311, Scottsdale AZ 311, Seattle WA 311, Shopify, Somerville MA 311, Springfield MO 311, St. Petersburg FL 311, Tacoma WA 311, Toledo OH 311, Topeka KS 311, Vallejo CA 311, Washington DC 311, Wayne County MI 311, West Des Moines IA 311, Wichita KS 311, Worcester MA 311, Youngstown OH 311.

Other destinations Ingest writes to: Amazon S3, Google Cloud Storage, Azure Blob Storage, Apache Iceberg, Ingest Managed Lakehouse, MotherDuck, Postgres, Amazon Athena, Databricks, Redshift, ClickHouse, MySQL / generic SQL, Snowflake.

Get your API data into BigQuery