Table "x" must be qualified with a dataset

BigQuery reads a table name with no dataset as a table in the default dataset, and there is none. Write dataset.table, set a default dataset, or define the name first. The usual cause is a misspelled or undefined CTE name, which BigQuery takes for a table.

Table "orders" must be qualified with a dataset (e.g. dataset.table).

What BigQuery means

A table in BigQuery lives in a dataset, so its full name is project.dataset.table. In a query you may leave out the project, and you may leave out the dataset only if the job has a default dataset. This query has none, so a bare orders is an error.

Before it reports the error, BigQuery looks for the name among the CTEs and, in a script, the temp tables that are visible at that point. If it finds nothing, it takes the name for a table. So the message does not always mean you forgot a dataset. Very often it means a name you thought was defined is not: a typo in a CTE name, a CTE that is missing, or a temp table from an earlier run.

A single statement gets no position. In a script the message starts with Invalid value: and ends with the position of the statement that failed.

The smallest query that reproduces it

SELECT * FROM orders

Table "orders" must be qualified with a dataset (e.g. dataset.table).

The same message comes back for INSERT INTO orders, UPDATE orders, DELETE FROM orders and CREATE TABLE orders.

The fixes

Add the dataset. dataset.table is enough when the table is in the project that runs the query. For another project, write all three parts. A project id with a hyphen needs no quotes in a FROM, or you can put the whole path in backticks:

SELECT * FROM bigquery-public-data.usa_names.usa_1910_2013 LIMIT 1

SELECT * FROM `bigquery-public-data.usa_names.usa_1910_2013` LIMIT 1

Both pass a dry run.

Set a default dataset. In a script, two system variables do it. @@dataset_id is a dataset of the project that runs the query. @@dataset_project_id changes the project:

SET @@dataset_project_id = 'bigquery-public-data';
SET @@dataset_id = 'usa_names';
SELECT name FROM usa_1910_2013 LIMIT 1;

The bq tool has a flag for the same thing, --dataset_id, and the query API has a defaultDataset setting. The bare name is then looked up there.

Check the CTE names. When the name in the message is one you meant to define, look at the WITH clause. A typo in the CTE name or in the reference:

WITH orders AS (SELECT 1 AS id), totals AS (SELECT * FROM ordres) SELECT * FROM totals

Table "ordres" must be qualified with a dataset (e.g. dataset.table).

The same happens when you copied part of a query and left the WITH behind. A join to a table you did not define is the same error:

WITH orders AS (SELECT 1 AS id)
SELECT *
FROM orders o
JOIN customer c ON o.id = c.id

Table "customer" must be qualified with a dataset (e.g. dataset.table).

Create the temp table in the same script. A temp table exists only for the script that created it. Running SELECT * FROM orders in a new run, after a script created orders as a temp table, gives this error. Put both in one script:

CREATE TEMP TABLE orders AS SELECT 1 AS id;
SELECT * FROM orders;

The variants of the message

Table "orders" must be qualified with a dataset (e.g. dataset.table).
Invalid value: Table "orders" must be qualified with a dataset (e.g. dataset.table). at [2:1]
Invalid value: Table "customers" must be qualified with a dataset (e.g. dataset.table). at [3:1]

The second is for this script, where the failing statement is on line 2:

SELECT 1;
SELECT * FROM orders;

The third is for a script whose first two statements create a temp table orders and read from it, and whose third reads customers:

CREATE TEMP TABLE orders AS SELECT 1 AS id;
SELECT * FROM orders;
SELECT * FROM customers;

The name stays the same if the table has an alias or is in backticks: FROM orders AS o and FROM `orders` both name orders.

Queries that look wrong but run

A defined CTE. WITH orders AS (SELECT 1 AS id) SELECT * FROM orders runs.

A temp table in the same script. The second statement above runs and returns the row.

A project id with a hyphen, unquoted. The FROM bigquery-public-data.usa_names... example above is valid.

Different errors that look like this one

Not found: Dataset <your project>:ds was not found in location US. You wrote two parts, ds.orders, so BigQuery read ds as a dataset of the project that runs the query and could not find it. A project id as the first part, my_project.orders, gets the same error, with my_project as the dataset. Write all three parts. See Dataset was not found in location.

Not found: Table project:dataset.table was not found in location US. The path is complete and the table is missing or in another location. See Table was not found in location.

Syntax error: Unexpected keyword ORDER. FROM order fails because ORDER is a reserved word. See Unexpected keyword.

Unrecognized name: x. A column name that was not found. See Unrecognized name.

In Querylab.io

When a query has a WITH clause, the editor marks a table name that is neither a CTE nor qualified: Undefined table or CTE 'ordres'. Did you forget to define this CTE? Without a WITH clause it does not mark an unqualified name, and BigQuery reports it when you pause or run.

After a failed run of a single query, the status bar can offer Qualify as <dataset>.orders, which writes the dataset in front of the name everywhere it is read as a table. It appears when exactly one dataset in the billing project holds a table of that name, once the schema browser has loaded the project’s datasets. With two candidates there is no button, because only you know which one you meant. An error from inside a script gets no button.