Connect SPSync to Google BigQuery

This guide covers Google BigQuery. SPSync uses one destination kind, bigquery. BigQuery has no connection string: the credential is a Google Cloud service-account key file, and the destination is a dataset in that account’s project.

Before you start

A Google Cloud project with the BigQuery API enabled and billing switched on.
Nothing to install on the machine that runs SPSync. The Google client library is part of SPSync.
Rights to create a service account and grant it roles on the project.
A decision on the dataset region. A dataset keeps the region it was created in, so choose it before the first run.

Create the database and a least-privilege user

In BigQuery the dataset is the whole namespace: dataset_name is the dataset, and every list table, child table and sp_users sits directly in it. SPSync creates the dataset on first use if it does not exist, in the service account’s own project. Datasets in another project are not supported.

The service account needs two roles, and the commands below are from the Google Cloud documentation and have not been run here:

BigQuery Data Editor (roles/bigquery.dataEditor) to create and change tables and to insert, update and merge rows.
BigQuery Job User (roles/bigquery.jobUser) to run load and query jobs. This role is granted on the project.

gcloud iam service-accounts create spsync-loader --project=PROJECT_ID

gcloud projects add-iam-policy-binding PROJECT_ID \
  --member="serviceAccount:spsync-loader@PROJECT_ID.iam.gserviceaccount.com" \
  --role="roles/bigquery.jobUser"

gcloud projects add-iam-policy-binding PROJECT_ID \
  --member="serviceAccount:spsync-loader@PROJECT_ID.iam.gserviceaccount.com" \
  --role="roles/bigquery.dataEditor"

gcloud iam service-accounts keys create spsync-key.json \
  --iam-account="spsync-loader@PROJECT_ID.iam.gserviceaccount.com"

To keep the account away from every other dataset, create the dataset yourself in the region you want and grant Data Editor on that dataset only. Keep Job User on the project. SPSync then finds the dataset and does not need to create one.

Keep spsync-key.json out of source control. It is a long-lived secret.

Allow SPSync to reach it

BigQuery is a Google-hosted API, so there is no database firewall to open. SPSync makes outbound HTTPS calls to Google’s APIs, so the machine that runs it must be allowed to reach bigquery.googleapis.com and oauth2.googleapis.com. If your organisation uses VPC Service Controls around the project, SPSync’s address or network must be inside the perimeter or an access level must allow it. The perimeter check has not been tried here.

Encrypt the connection

Every call to Google’s APIs is over HTTPS, with no setting to change.

Authentication SPSync supports today

A service-account key: the whole downloaded JSON file, passed as text. That is the only credential type SPSync accepts for BigQuery.

Not supported today: Application Default Credentials, workload identity federation, service-account impersonation and user OAuth sign-in. Say so in a security review rather than assuming a keyless setup works.

Connect from SPSync

dataset_name is the BigQuery dataset. bigquery_location is the dataset’s region, for example US, EU or europe-west2. It matters only when SPSync has to create the dataset; an existing dataset keeps its own region. If you leave it out, SPSync uses US.

Desktop app: open the Destination screen, choose Google BigQuery, paste the whole contents of the key file into Connection string, set Dataset location if the dataset is not in US, and choose Test connection.

CLI: the environment variable holds the JSON text of the key file, not a path.

export SPSYNC_BQ_SERVICE_ACCOUNT="$(cat spsync-key.json)"
spsync export-config --output replication.yaml \
  --site-url https://contoso.sharepoint.com/sites/x --list Projects \
  --pipeline-name spsync_dev --prefix dev \
  --destination-kind bigquery --connection-string-env SPSYNC_BQ_SERVICE_ACCOUNT \
  --dataset-name sharepoint_sync --bigquery-location EU \
  --client-id <entra-app-client-id>

AI assistant: with the admin MCP server connected, ask for the destination requirements, then let the assistant create the destination with bigquery_location. You enter the key on the secure entry page, never in the chat.

What Test connection and doctor check

spsync doctor (destination-connectivity) creates a BigQuery client, runs SELECT 1 and, when the dataset is named, reads that dataset. A pass proves the key is valid, the project accepts jobs from it and, if the dataset exists, that it is reachable in the region given. The desktop Test connection button runs the SELECT 1 query.

It does not prove the account can create tables or write rows: Data Editor is only used on the first load. SPSync’s own BigQuery verification against a live Google Cloud project is not complete, so run one small list first.

Common errors

What you see	What it means and the fix
`403 ... does not have bigquery.jobs.create permission`	The service account has no BigQuery Job User role on the project. Grant it.
`403 ... Access Denied: Dataset` or `bigquery.tables.create`	The account has no Data Editor role on the project or the dataset. Grant it.
`404 Not found: Dataset ... was not found in location`	The dataset exists in a different region from `bigquery_location`. Set the region the dataset really has.
`Invalid JWT` or `invalid_grant`	The key was deleted or disabled, or the pasted text is not the complete JSON file. Create a new key.
`Access Not Configured` or `BigQuery API has not been used`	Enable the BigQuery API on the project.