Wildcard tables and _TABLE_SUFFIX in BigQuery

A wildcard table queries many similarly named BigQuery tables as one, and _TABLE_SUFFIX chooses which of them are read. Which suffix filters save bytes, which don't, and why partitioned tables are better than date-sharded ones.

A wildcard table is a table name that ends in *. BigQuery treats it as the union of every table in the dataset whose name starts with the text before the star. Each row gets a pseudocolumn, _TABLE_SUFFIX, with the part of the table name that the star matched. A filter on _TABLE_SUFFIX decides which tables are read, and only those are billed.

Wildcards exist for date-sharded data: one table per day or year, such as events_20260301 or gsod1990. If you are designing a new table, use a partitioned table instead. If the shards already exist, you query them like this.

The syntax

SELECT temp
FROM `bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX = '90'

bigquery-public-data.noaa_gsod has one table per year, gsod1929 to the present. The table name is gsod19*, so the query reads the tables from gsod1929 to gsod1999. The suffix of gsod1990 is 90.

The rules for the name:

• The star can only be the last character. gs*od1990 is not a wildcard; BigQuery looks for a table literally named that.
• The name needs backticks, because * is not allowed in an unquoted name. Without them the query fails with a syntax error at the star.
• An empty prefix, `noaa_gsod.*`, matches every table in the dataset. The suffix is then the whole table name. Google says a longer prefix performs better.

Reading one table, a few, or all

With no filter on the suffix, the query reads every matching table. These are the 71 yearly tables from 1929 to 1999, one column:

SELECT temp
FROM `bigquery-public-data.noaa_gsod.gsod19*`

That is 634 MB. Filtering on the suffix reads only the tables that satisfy it. One year:

SELECT temp
FROM `bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX = '90'

costs 20 MB, the same as naming the table directly:

SELECT temp
FROM `bigquery-public-data.noaa_gsod.gsod1990`

A range of years uses BETWEEN. Suffixes are strings, so the comparison is alphabetical, and with the same number of digits that works like numbers:

SELECT temp
FROM `bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX BETWEEN '90' AND '95'

That is six tables and 117 MB. For a prefix such as events_* with names like events_20260301, the suffix is the date string, and _TABLE_SUFFIX BETWEEN '20260301' AND '20260307' selects a week.

Anything built from the suffix alone also prunes

BigQuery works out which tables match without reading them, so you can compute with the suffix:

SELECT temp
FROM `bigquery-public-data.noaa_gsod.gsod19*`
WHERE CAST(_TABLE_SUFFIX AS INT64) >= 90

That reads the ten tables from 1990 to 1999, 194 MB. A function over the suffix is fine as long as nothing else in it comes from the table’s data.

Filters that don’t pick tables

A filter on a data column doesn’t choose tables. The year is also stored inside each table, as the year column, and this query asks for 1990 through it:

SELECT temp
FROM `bigquery-public-data.noaa_gsod.gsod19*`
WHERE year = '1990'

BigQuery has to open every table to find which rows have year = '1990'. It reads the year column as well as temp in all 71 tables, 1.1 GB, more than fifty times the 20 MB of the one-table query.

The suffix also can’t come from another query. Here the latest table is chosen with a subquery, and the estimate is the same as reading everything:

SELECT temp
FROM `bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX = (
  SELECT MAX(_TABLE_SUFFIX)
  FROM `bigquery-public-data.noaa_gsod.gsod19*`
)

This is the case Google’s docs describe as a filter that must be a constant expression. If you need the latest shard, find its name first and put the value in the query text.

A common mistake: the wrong suffix

The suffix is only the part after the prefix you wrote. With gsod19*, the suffix of gsod1990 is 90, not 1990:

SELECT temp
FROM `bigquery-public-data.noaa_gsod.gsod19*`
WHERE _TABLE_SUFFIX = '1990'

No table has the suffix 1990, so the query reads nothing, returns no rows and costs nothing. There is no error, so a query that returns nothing is how this shows up. When you don’t get the rows you expect, check what SELECT DISTINCT _TABLE_SUFFIX returns.

Other behavior to know

• Selecting the suffix. _TABLE_SUFFIX is a pseudocolumn, so SELECT * doesn’t return it. Write SELECT _TABLE_SUFFIX AS shard, ... to get it.
• No cache. Queries on wildcard tables are never served from the query cache, even when the text is identical and nothing changed. We ran the same wildcard query twice and both jobs read the data; the same query on a single table was a cache hit the second time. Each run is billed. See Query cache.
• One schema. BigQuery uses the schema of the most recently created matching table for the whole wildcard, even when you filter that table out. A column missing from an older table comes back as NULL. If a matched table has a column with the same name and a different type, the query can fail. Google notes that all matched tables are considered even when an equality filter on the suffix would exclude them.
• No views, external tables or mixed partitioning. Wildcards work on native BigQuery tables only. If the wildcard matches a view, the query fails even when _TABLE_SUFFIX would exclude it. Matching tables must have the same partitioning and clustering.
• DML targets. A wildcard table can be read in DML, for example in the FROM clause of an UPDATE, but it can’t be the table the statement changes.
• CMEK. Tables encrypted with customer-managed keys can’t be used in a wildcard query.

Wildcard tables or partitioned tables

Google recommends partitioning over sharding. A sharded set has one schema and one set of metadata per table, and BigQuery checks permissions on each of them. A partitioned table has one. A date filter prunes partitions on a partitioned table in the same way a suffix filter picks shards, and a partitioned table also gives you clustering, partition expiration and a required partition filter.

Wildcards are the way to read tables you didn’t design, such as public datasets and older exports. To move to partitioning, create a partitioned table with CREATE TABLE ... PARTITION BY ... AS SELECT from the wildcard and build the date column from _TABLE_SUFFIX. Google also documents a conversion of date-sharded tables into one ingestion-time partitioned table.

In Querylab.io the estimate in the status bar tells you how many bytes a suffix filter selects before you run the query. The Wildcard Table Scan Without Filter rule warns on a table_* with no _TABLE_SUFFIX filter and offers a quick fix that adds one (Code quality). For date-sharded tables, the Details tab has a Sharded Table Information group (GA4 events).