BigQuery Not found Table usage_daily was not found in location US: health score pipeline broken

TL;DR

The table and the query are probably in different places, literally. BigQuery datasets live in one location, and a job that looks in US cannot see a table whose dataset sits in another location such as the EU, while a fully qualified name alone does not move the job. Check the datasets real location, run the job in that location or move the pipeline coherently, and the not found error clears without recreating anything.

BigQuery Not found Table usage_daily was not found in location US: health score pipeline broken

Use this when

• The pipeline fails with Not found Table and names a location such as US
• The table is visible and queryable in the BigQuery console under some project
• A migration, new dataset, or new orchestration region was introduced recently

Not for this skill when

• The tables expiration passed and it was deleted, which the dataset listing and table details will show
• The failure is a permission error dressed differently, which appears as an access denied message instead

Steps

1. Open the dataset containing usage_daily in the BigQuery console and read its location from the dataset details, then read the project and dataset the pipeline actually references from its configuration. Write both down exactly, because project, dataset, and location mismatches often hide behind similar names across environments such as production and staging copies. Verify: dataset location and the pipelines referenced project and dataset are recorded side by side
2. Check the location your job runs in. In the job history or your orchestrators configuration, find the location the query job was submitted with. Console queries inherit the datasets location, which is why the same SQL succeeds by hand, while scheduled jobs often default to US or to the orchestrators own region setting regardless of the dataset. Verify: the submitted job location is identified from job history or configuration
3. Align location end to end. Either submit the job in the datasets location, or, if the architecture intends US, recreate the table in a US dataset through the upstream pipeline, never by a one-off manual copy that the next pipeline run will overwrite or ignore. If the query joins datasets in two locations, that join can never work, and the real fix is co-locating the datasets through the pipeline design. Verify: the job and all joined datasets share one location, confirmed in configuration
4. Fully qualify the table name with project and dataset in the pipeline SQL and pin the location in the job configuration or connection settings, so a future environment default cannot silently repoint the run. Keep environment specific values in configuration, not pasted into SQL strings scattered across the pipeline. Verify: the corrected pipeline run completes and reads the expected row count for usage_daily
5. Recover the missed health scoring. Re-run the pipeline for every failed date, reconcile daily usage counts against the source systems totals, and re-score accounts whose health inputs were missing, annotating the dashboards so the stale period is not mistaken for a usage collapse. Verify: missed dates are reprocessed and reconciled with scoring refreshed
6. Document the location decision in the dataset description and the pipeline runbook, including which datasets must stay co-located for joins, and add a pipeline assertion that fails early if the configured location differs from the datasets actual location, turning the next migration mistake into a loud configuration error instead of missing tables. Verify: the assertion exists and fails in a test run with a deliberately wrong location

Why it happens

BigQuery is location strict. A query job runs in one location and can only touch datasets stored there, and it reports tables elsewhere as not found in the jobs location. Interactive console use masks this because the console picks the datasets location for you. Scheduled pipelines carry their own location default, so a dataset migration or a new region setting breaks them while every manual check passes.

Compatibility: BigQuery datasets and job locations, job history, and orchestration tools such as Airflow or dbt with BigQuery connections, in current versions.

Variants and edge cases

BigQuery not found table was not found in location US

['The verbatim phrasing. The location named in the error is where BigQuery looked, not where the table is, and that distinction is the whole diagnosis.']

BigQuery table exists but job says not found

['The console-versus-job phrasing. Trust the dataset details page for location and the job history for the submitted location, and compare those two facts first.']

BigQuery dataset location mismatch pipeline

['The migration variant, common when a EU dataset was created by a privacy or residency project while orchestration kept a US default. Co-location decisions need the data owner, not only the engineer.']

Edge cases to work through before you close the incident:

• Table names in errors can include a generated project or dataset from a template variable. Log the rendered SQL and configuration at runtime, not the template, when you diagnose.
• Wildcard tables spanning datasets still obey location rules. A wildcard does not rescue a cross-location design.
• Copying a table across locations is possible with transfer or export jobs, but a copied table goes stale instantly. Fix the pipeline location or the dataset location, not the symptom.

Provenance

Resolved from the public thread: https://vectle.com/posts/pst_FDWwXYUvp9HwBugnAGsSJg