BigQuery Troubleshooting

This page lists common errors when you connect Sifflet to BigQuery, as reported by Test Connection, when loading assets, or in the logs of a source refresh.

Connection

The Project Isn't Found

Symptom

Project my-data-project not found.

Cause: the Project ID, Billing Project ID, or one of the Worker Project IDs of the source is wrong (for example, a project name instead of a project ID), or the service account has no access to this project.

Fix

1. In the Google Cloud console, open IAM & Admin > Settings for the project and copy its Project ID.
2. Update the source with this ID (see Create the Source).
3. Check that the service account has a role on this project (see step 1).

The BigQuery API Is Disabled

Symptom

BigQuery API is not enabled on the target project. Please enable the BigQuery API in the Google Cloud Console.

Cause: the BigQuery API isn't enabled on the project where Sifflet runs its queries (the billing project, or the data project if you don't use one).

Fix

1. In the Google Cloud console, go to APIs & Services > Library, search for BigQuery API, and click Enable on this project.
2. Run Test Connection again.

Authentication

The Credential Isn't Valid JSON

Symptom

Credentials are not in valid JSON format. Please fix the format of your credentials.

Cause: the content of the credential isn't the complete JSON key file of the service account.

Fix

1. Open the JSON key file and copy its whole content, including the first { and the last }.
2. Paste it in the credential in Integrations > Credentials (see Create the Credential).

Sifflet Can't Authenticate with the Service Account

Symptom: when you click Load assets, or in the source logs:

Error while listing BigQuery Datasets

Cause: Google Cloud rejects the service account key: the key was deleted, or the service account was disabled or deleted.

Fix

1. In the Google Cloud console, go to IAM & Admin > Service Accounts and find the Sifflet service account.
2. If the service account is disabled, enable it.
3. If the key was deleted, create a new JSON key (Keys > Add key > Create new key) and update the credential in Integrations > Credentials.
4. If the service account was deleted, follow step 1 again and update the credential.

Permissions

No Dataset Appears, or a Dataset Is Missing

Symptom: Load assets lists no dataset, or doesn't list the dataset you want, or shows:

No assets found in this project. Please check that the permission bigquery.datasets.get is granted on the project.

Cause: Sifflet lists only the datasets that the service account can access. The service account lacks bigquery.datasets.get on the project, or on the missing dataset if you grant access dataset by dataset.

Fix

1. Grant the custom role to the service account on the project, or on the missing dataset (see step 1).
2. Click Reset and reload in the scope selection.

Tables Can't Be Listed or Read

Symptom: one or more errors such as:

Access Denied: Permission bigquery.tables.list denied on dataset my-data-project:my_dataset.

Some permissions are missing: Permission bigquery.tables.get, bigquery.tables.getData denied on dataset my_dataset

No table found in the dataset my_dataset.

Cause: the service account lacks bigquery.tables.list, bigquery.tables.get, or bigquery.tables.getData on the dataset, or the dataset contains no table.

Fix

1. Add the missing permissions to the custom role granted to the service account (see Create a Custom Role).
2. If the dataset is empty, deselect it from the source scope or create its tables first.
3. Run Test Connection again.

The Job History Can't Be Read

Symptom

The project my-data-project does not exist or user does not have permission to query table my-data-project:region-us.INFORMATION_SCHEMA.JOBS_BY_PROJECT. In that case, check that the permission bigquery.jobs.listAll is granted on the project.

Cause: the service account lacks bigquery.jobs.listAll on the data project, or on one of the worker projects. Sifflet needs the job history for lineage, data usage, and update history. This error also appears when the project named in the error has no recent jobs in the region of your datasets, even if the permission is granted.

Fix

1. Grant a role with bigquery.jobs.listAll to the service account on the project named in the error (see step 1 and worker projects).
2. If the permission is already granted, check that the project has run at least one query job in the region of your datasets.
3. If the project is a worker project you don't need, remove it from Worker Project IDs.
4. Run Test Connection again.

Warning About INFORMATION_SCHEMA.SCHEMATA

Symptom: Test Connection shows a warning that starts with:

The project my-data-project does not exist or user does not have permission to query table my-data-project.INFORMATION_SCHEMA.SCHEMATA.

Cause: the service account lacks bigquery.jobs.create on the billing project (or the data project if you don't use one), or bigquery.datasets.get on the project. This is expected if you grant access dataset by dataset.

Fix

1. If you grant access dataset by dataset and no check of Test Connection fails, ignore the warning.
2. Otherwise, grant bigquery.jobs.create on the billing project and bigquery.datasets.get on the project, then run Test Connection again.