I’m encountering a puzzling issue in BigQuery where my JOIN operations function correctly on their own, but the complete query containing them along with CASE statements results in an error. The error indicates that the table is not found in the US location, even though the JOIN syntax works fine when executed independently. I can perform any type of JOIN (INNER, LEFT, RIGHT, OUTER) smoothly in isolation.
This is the section of my query with the JOIN that’s causing trouble:
FROM `analytics-demo-456789.inventory_data.Facilities` AS facility
LEFT JOIN
`analytics-demo-456789.inventory_data.Shipments` AS shipments
ON
facility.facility_id = shipments.facility_id
Below is the entire query that’s triggering the location error:
SELECT
Facilities.facility_id,
CONCAT(Facilities.region, ':' , Facilities.facility_name) AS location_info,
COUNT(Shipments.shipment_id) AS total_shipments,
(SELECT
COUNT(*)
FROM inventory_data.shipments AS ship_count) AS overall_total,
CASE
WHEN COUNT(shipments.shipment_id)/(SELECT COUNT (*) FROM inventory_data.shipments AS Shipments) <= 0.3
then "Processed 0%-30% of Shipments"
WHEN COUNT(shipments.shipment_id)/(SELECT COUNT (*) FROM inventory_data.shipments AS Shipments) > 0.3
and COUNT(shipments.shipment_id)/(SELECT COUNT (*) FROM inventory_data.shipments AS Shipments) <= 0.7
then "Processed 31%-70% of Shipments"
ELSE "Processed more than 70% of Shipments"
END AS processing_summary
FROM `analytics-demo-456789.inventory_data.Facilities` AS facility
LEFT JOIN
`analytics-demo-456789.inventory_data.Shipments` AS shipments
ON
facility.facility_id = shipments.facility_id
GROUP BY
Facilities.facility_id,
location_info
HAVING
COUNT(Shipments.shipment_id) > 0
Does anyone have insight on why this location error occurs solely in the more complex query?
Hit this exact location error three times this year. BigQuery gets confused about which region to run your query when table references are inconsistent.
Yeah, there’s the alias case sensitivity thing others mentioned, but your subqueries are what’s really breaking this. BigQuery sees inventory_data.shipments in the subquery and analytics-demo-456789.inventory_data.Shipments in the main query, then can’t figure out if they’re the same table or different ones in different locations. Query planner just gives up and defaults to US region for the unqualified name. If your dataset isn’t in US, boom - location error.
Easy fix: pick one table reference style and stick with it. Either use fully qualified names everywhere (with project ID), or use short form throughout. Don’t mix them.
Also, your HAVING clause has Shipments.shipment_id but should probably be shipments.shipment_id to match your alias. These little inconsistencies make the location problem worse.
Hit this exact nightmare twice building our warehouse dashboard. Yeah, it’s your inconsistent table references, but there’s also a nasty query parsing order issue that makes it extra frustrating.
BigQuery processes subqueries before it figures out your main query’s table aliases. So when it sees FROM inventory_data.shipments AS ship_count in the subquery, it hasn’t established the region context from your main FROM clause yet. The query planner tries resolving that unqualified table name on its own and just defaults to US region.
I fixed it by ditching the subqueries completely. Use a CTE at the top to grab your total count first, then reference it in your main query:
WITH total_count AS (
SELECT COUNT(*) as overall_total
FROM `analytics-demo-456789.inventory_data.Shipments`
)
Then cross join with your main query. Kills the location issue and runs way faster since you’re not recalculating the same count over and over.
your table references dont match - you’re using Facilities and Shipments (capitalized) in your SELECT and HAVING clauses, but your FROM clause has them aliased as lowercase facility and shipments. since BigQuery’s case sensitive, its looking for tables with those exact names that dont exist.
Had this exact issue 6 months ago when migrating reporting queries. You’re right about the alias problem, but there’s another gotcha.
You’re mixing reference styles in your subqueries. Check this line:
(SELECT COUNT (*) FROM inventory_data.shipments AS Shipments)
You’re using inventory_data.shipments without the full project path, but your main query uses analytics-demo-456789.inventory_data.Shipments. BigQuery can’t figure out which dataset location to use when you mix these patterns.
Pick one - either use the full project path everywhere or stick with the short form (just make sure all tables are in the same region).
Also, that subquery runs once per row in your CASE statement. Quick fix: calculate the total once with a window function:
COUNT(shipments.shipment_id) / SUM(COUNT(shipments.shipment_id)) OVER() as ratio
Then use the ratio in your CASE logic. Much faster and no location issues.
Yeah, aroberts nailed it - the alias mismatch is your main problem. But honestly, you’ve got a worse issue with those repeated subqueries tanking your performance.
Every (SELECT COUNT (*) FROM inventory_data.shipments AS Shipments) in your CASE statement? BigQuery runs that fresh each time. Add your complex JOINs and you’re looking at a debugging nightmare.
Hit this same wall last year with our inventory analytics. Instead of fighting BigQuery’s quirks, I just built an automated solution that handles the data transformation and business logic outside SQL.
It pulls from BigQuery, does all the aggregations and case logic, then spits out clean results wherever you want. No alias headaches, no repeated subqueries, and changing business rules doesn’t mean rewriting SQL.
When your data structure changes or you need more complex logic, just update the workflow instead of debugging SQL for hours.
Check out Latenode for this kind of data automation: https://latenode.com