Fix GCP BigQuery Transfer Errors

When working with GCP BigQuery, you may encounter a configuration error that prevents your data pipeline or messaging system from working. This guide explains the most common mistake with transfer and shows the exact fix.

A Common Mistake

Creating a BigQuery Data Transfer Service job without granting the transfer service account access to the source, causing the transfer to fail silently.

The incorrect command:

bq mk --transfer_config --target_dataset=my_dataset --display_name='S3 Transfer' --data_source=amazon_s3 --params='{"bucket":"my-s3-bucket","file_pattern":"*.csv"}'

Error output:

Transfer config created.
First scheduled run:
State: FAILED
Error: AccessDenied: Access to S3 bucket 'my-s3-bucket' is denied.
The BigQuery transfer service account does not have permissions to read the S3 bucket.

The Correct Approach

The right way to configure transfer in GCP BigQuery:

BigQuery transfer SA: service-PROJECT_NUMBER@gcp-sa-bigquerydatatransfer.iam.gserviceaccount.com
Grant S3 read permissions to this SA.
Then create the transfer config.

Successful result:

Transfer config created.
Scheduled run:
State: SUCCEEDED
Transferred 5000 rows from S3 to BigQuery.
Data is loaded to the target dataset on schedule.

How to Prevent This

Find the transfer SA in Cloud Console or by checking the transfer run logs. Grant this SA access to the source (S3 bucket, GCS bucket, other database). Monitor transfer runs in Cloud Console. Set up email notifications for failures. Test with a manual run first.

FAQ

Built by the developers of Doda Browser, DodaZIP, and Durga Antivirus Pro. Secure your cloud with DodaTech.