Not found: Dataset was not found in location

The location at the end of this message is where the job ran, not where the dataset is. A dataset in EU is invisible to a query running in US, and the error reads exactly like a typo.

Not found: Dataset my-project:analytics was not found in location EU

What BigQuery means

Read the last three words. location EU is not a fact about the dataset. It is where the job ran.

A BigQuery query job runs in exactly one location, and it can only see datasets in that location. If your dataset lives in US and the job ran in EU, BigQuery looked in EU, found nothing called analytics, and said so. The message is the same one you get for a misspelled dataset name.

The check is quick. If the name in the message is spelled exactly the way you wrote it, suspect the region before you suspect yourself.

The smallest query that reproduces it

Any correct reference, run in the wrong location:

SELECT 1 FROM `my-project.analytics.events`

The same query with the job in US works. Nothing about the SQL changed.

A query with no table in it runs anywhere, which is a handy way to confirm the job itself is fine:

SELECT 1

The fix

Find out where the dataset actually is:

bq show --format=prettyjson my-project:analytics

The location field is the answer. Then either run the job there, or move the data.

In Querylab.io a table’s Details tab reports its Data location, so you can read the region off the table instead (Table information).

A dataset’s location is fixed when it is created and cannot be changed afterwards. Moving it means recreating it somewhere else: a cross-region copy, an export to Cloud Storage and a load back, or cross-region replication. All three leave you with a second copy and a second storage bill.

Where the location comes from

You rarely set it directly, so it is worth knowing who decides:

• A query with tables in it. BigQuery infers the location from the datasets referenced, and all of them have to be in the same one.
• The bq CLI. --location=EU, or the location in ~/.bigqueryrc. If you set it once for a European project it stays set.
• The API. jobReference.location on the insert, or the location on jobs.query.
• A scheduled query or a transfer. Fixed when it was created, and not changed by editing the SQL.
• INFORMATION_SCHEMA. The region qualifier in the name, `region-us`.INFORMATION_SCHEMA.JOBS, has to match the location the job runs in.

Two datasets in two regions

By default a query sees only one location, so a join between a US dataset and an EU one fails with the message above, naming whichever dataset is not where the job is.

Global queries lift that restriction, but they are off by default and turning them on needs BigQuery Admin: two ALTER PROJECT ... SET OPTIONS statements, one on the project running the query and one on the project holding the data, plus bigquery.jobs.createGlobalQuery on the caller. BigQuery then copies the data it needs into the execution region, so there is a transfer cost and a data residency question to answer first. Replicating one dataset into the other’s region is usually the simpler answer. See Locations.

Variants

The job’s error_result.reason is notFound.