I'm working on a reporting pipeline using Dataform in GCP to build Google Ads dashboards - Client Dashboard for each client and an Agency Internal Dashboard.
Here's my setup:
My Google Ads Transfer Service pushes raw data (e.g. campaign_basic_stats) into BigQuery in the US (multi-region) dataset — which is the default and non-configurable for Ads Transfer.
I created a Dataform repo in us-west1, because Dataform currently only supports execution in single-region locations.
I also created a BigQuery dataset (dataform_pipeline) in us-west1 to store Dataform model outputs.
Now when I try to reference the Google Ads tables from within my Dataform models (e.g. SELECT * FROM project_id.google_ads_dataset.campaign_basic_stats), I get this error:
Not found: Dataset project_id:dataform_pipeline was not found in location US..
From my understanding, this is a region mismatch issue, because:
- Dataform runs in us-west1
- My source Google Ads tables are in US
- BigQuery does not support cross-region operations (especially with views or temp tables)
What I've Tried:
- Verified that both my Dataform repo and output dataset are in us-west1
- Confirmed that the Google Ads tables are in US, and cannot be changed
- Considered copying data from US to us-west1 but I'm looking for a cleaner or official solution if possible
My Questions:
- What is the recommended way to use Google Ads Transfer data (in US multi-region) with a Dataform repo that must run in us-west1?
- Is there an official Google-recommended workaround?
- Is copying Google Ads tables from US into us-west1 the only practical option? Are there performance or cost concerns in doing this regularly?
Any advice from others who've run into this would be hugely appreciated!
