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.

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_date

The filter names two days, so only those two daily tables take part.

Each day gives one row with its count of scroll events.

All BigQuery lessons