Wildcard tables
Before partitioned tables existed, people split data by date into one table for each day. Analytics exports still arrive that way, and a wildcard table lets one query read several of them.
How it works
GA4 writes a table called events_YYYYMMDD for each day. Writing events_* reads every table whose name starts with events_. The pseudo column _TABLE_SUFFIX holds what the * matched, for example 20240103, and you filter it like a normal column.
- One table for each day is called sharding, and it is the legacy form of partitioning. You meet it in tables you did not design, such as GA4 exports and public datasets. For a new table, Google recommends a partitioned table.
- Put the wildcard name in backticks:
learn.ga4.events_*. _TABLE_SUFFIXis a string, soBETWEEN '20240101' AND '20240107'compares text. Write the days asYYYYMMDD.- Group by
_TABLE_SUFFIXto get one row for each day.SELECTcan return it and give it an alias. - A
_TABLE_SUFFIXfilter prunes when BigQuery can compute it before the run. A constant does, and so does a function of constants such asFORMAT_DATE('%Y%m%d', DATE '2024-01-03'). A comparison with a column, orIN (SELECT ...), reads every table.
Common mistake
A filter on an ordinary column such as event_date does not choose daily tables, because BigQuery still has to open every table to look at it. The wildcard events_* also matches events_intraday_YYYYMMDD tables, whose _TABLE_SUFFIX is intraday_YYYYMMDD, so a filter such as >= '20240101' includes them and a BETWEEN of two days does not.
Worked example
SELECT _TABLE_SUFFIX AS shard_date, COUNTIF(event_name = 'scroll') AS scrolls
FROM `learn.ga4.events_*`
WHERE _TABLE_SUFFIX IN ('20240102', '20240105')
GROUP BY shard_date
ORDER BY shard_dateThe filter names two days, so only those two daily tables take part.
Each day gives one row with its count of scroll events.