# 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.

Source: https://docs.querylab.io/bigquery/wildcard-tables/

Updated: 2026-10-14

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](https://docs.querylab.io/bigquery/partitioning/) instead. If the shards already exist, you query them like this.

## The syntax

```sql
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:

**every table that matches gsod19***

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

BigQuery dry run: this query processes 634.2 MB.

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

**_TABLE_SUFFIX = '90'**

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

BigQuery dry run: this query processes 20.0 MB.

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

**gsod1990 on its own**

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

BigQuery dry run: this query processes 20.0 MB.

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:

**1990 to 1995**

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

BigQuery dry run: this query processes 117.4 MB.

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.

### Suffix-only expressions also prune

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

**CAST(_TABLE_SUFFIX AS INT64) >= 90**

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

BigQuery dry run: this query processes 194.4 MB.

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:

**year = '1990' instead of _TABLE_SUFFIX**

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

BigQuery dry run: this query processes 1.1 GB.

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:

**_TABLE_SUFFIX = (SELECT MAX(...))**

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

BigQuery dry run: this query processes 634.2 MB.

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`:

**_TABLE_SUFFIX = '1990' with gsod19***

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

BigQuery dry run: this query processes 0 B.

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](https://docs.querylab.io/bigquery/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](https://docs.querylab.io/bigquery/clustering/), partition expiration and a [required partition filter](https://docs.querylab.io/bigquery/partitioning/#require-a-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](https://docs.querylab.io/writing-sql/code-quality/)). For date-sharded tables, the Details tab has a Sharded Table Information group ([GA4 events](https://docs.querylab.io/schema-browser/ga4-events/#the-shard-group)).

Query many tables at once with a wildcard and _TABLE_SUFFIX. Open the playground at https://app.querylab.io?playground=true&utm_source=docs&utm_medium=concepts to run it. It is the real app on public BigQuery datasets and needs no sign-in.

## Sources

Based on the BigQuery documentation by Google, CC BY 4.0, rewritten with examples checked against BigQuery:

- [Query multiple tables using a wildcard table](https://cloud.google.com/bigquery/docs/querying-wildcard-tables)

- [Introduction to partitioned tables](https://cloud.google.com/bigquery/docs/partitioned-tables)