BigQuery destination on GCP

On a GCP cluster, SkaleData's Airbyte can write to BigQuery without a service account key. Every Airbyte pod, including the pods that check and sync your connectors, runs as a Google service account that SkaleData creates when you install Airbyte. You grant that account access to BigQuery, and you leave the key field empty in the destination.

The BigQuery destination can be in a different project from the cluster, as long as you grant the roles in that project.

Before you startCopy Anchor Link

You need:

• An Airbyte application on a GCP cluster.
• Permission to grant IAM roles in the project that holds your BigQuery datasets, such as Project IAM Admin (roles/resourcemanager.projectIamAdmin).

Find the Airbyte service accountCopy Anchor Link

SkaleData names the account after the Airbyte application, in the project of the cloud the cluster runs in:

<application-name>@<cluster-project>.iam.gserviceaccount.com

• <application-name> is the name you gave the Airbyte application when you added it. It's also the application's Kubernetes namespace. If it's longer than 30 characters, the account uses the first 30, without a trailing hyphen.
• <cluster-project> is the GCP project of the cloud the cluster belongs to. The console shows it as Project ID on the cloud's page.

For example, an Airbyte application named airbyte on a cluster in project acme-platform runs as airbyte@acme-platform.iam.gserviceaccount.com.

Each account belongs to one application. GKE Workload Identity tells pods apart by namespace, not by cluster, so SkaleData refuses an Airflow or Airbyte application whose account another one in the same GCP project already uses, even on another cluster. If you run several clusters in one project, give each one's Airbyte application its own name, such as airbyte-dev and airbyte-prod.

To check that the account exists:

gcloud iam service-accounts describe \
  airbyte@acme-platform.iam.gserviceaccount.com --project=acme-platform

Grant BigQuery accessCopy Anchor Link

Grant the account two roles in the project that holds your BigQuery datasets:

Grant them at the project level. If you grant BigQuery Data Editor on a single dataset instead, the destination can't create the airbyte_internal dataset. In that case, create airbyte_internal yourself, or the dataset you set in Airbyte Internal Table Dataset Name, and grant the role on both datasets.

With gcloud:

for role in roles/bigquery.dataEditor roles/bigquery.jobUser; do
  gcloud projects add-iam-policy-binding acme-analytics \
    --member="serviceAccount:airbyte@acme-platform.iam.gserviceaccount.com" \
    --role="$role"
done

With Terraform:

locals {
  airbyte_sa = "airbyte@acme-platform.iam.gserviceaccount.com"
}

resource "google_project_iam_member" "airbyte_bigquery" {
  for_each = toset(["roles/bigquery.dataEditor", "roles/bigquery.jobUser"])

  project = "acme-analytics"
  role    = each.value
  member  = "serviceAccount:${local.airbyte_sa}"
}

Airbyte doesn't need BigQuery Data Owner, because it doesn't change dataset permissions.

Set up the destinationCopy Anchor Link

In Airbyte, create a BigQuery destination:

Then save the destination. Airbyte runs a connection check before it saves.

Choose the dataset locationCopy Anchor Link

BigQuery can't query tables in different locations together. Use the location of the datasets that you'll join this data with, or that your transformations (dbt, for example) read from. The multi-region US and the region us-central1 are different locations.

To see the locations of a project's datasets:

bq ls --format=json --project_id=acme-analytics \
  | jq -r '.[] | "\(.datasetReference.datasetId)\t\(.location)"'

If the default dataset already exists, the location must match it, or the connection check fails.

Loading methodCopy Anchor Link

Use Batched Standard Inserts. It loads data in batches: the BigQuery driver turns large inserts into file uploads, so you don't pay streaming insert costs.

GCS Staging writes files to a Cloud Storage bucket and loads them from there. It needs an HMAC key, which is a long-lived credential that you store in Airbyte and rotate yourself, and a bucket to manage. Consider it only for very large syncs or backfills, or if syncs fail on BigQuery load job quotas.

TroubleshootingCopy Anchor Link

Saving fails with errors.http.badGatewayCopy Anchor Link

The first connection check of a connector can take more than a minute: the node downloads the connector image before it runs the check. SkaleData stops waiting for Airbyte's response after 60 seconds, and Airbyte shows errors.http.badGateway.

Save the destination again. The node keeps the image, so the next check is faster.

The connection check fails with a permission errorCopy Anchor Link

• Check that you granted both roles to the right account, in the project in Project ID. Role grants can take a minute or two to apply.
• Check that Service Account Key JSON is empty. If you enter a key, the destination uses that key's account instead.

Use BigQuery as a sourceCopy Anchor Link

The Airbyte BigQuery source connector requires a service account key: it can't use the Airbyte service account. To read from BigQuery, create a key for a service account with BigQuery Data Viewer (roles/bigquery.dataViewer) and BigQuery Job User (roles/bigquery.jobUser), and enter it in the source.

Some organizations block key creation with the constraints/iam.disableServiceAccountKeyCreation organization policy. If yours does, you can't use BigQuery as an Airbyte source.