BigQuery and Power BI: when the region is the bug

The symptom

A query works in the BigQuery console and fails after the dashboard refresh. The error mentions a location or a dataset that cannot be found. The SQL is not the problem.

The cause

BigQuery datasets live in a processing location. Power BI and the connector have their own region and refresh path. If the dataset and the connector are not aligned, the refresh can fail even when the query is valid.

The fix

1. Check the dataset location.
2. Create the Power BI connection in a compatible region.
3. Keep the query parameterized by the observation window.
4. Add a last updated timestamp to the dashboard.
5. Document the region constraint in the repo.

SELECT
  customer_id,
  DATE_DIFF(CURRENT_DATE(), MAX(order_date), DAY) AS recency_days,
  COUNT(*) AS frequency,
  SUM(amount) AS monetary
FROM `project.dataset.orders`
WHERE order_date >= @window_start
GROUP BY customer_id

The lesson

Data location is an architectural decision, not an error message to retry. Write it down before the dashboard is built.