When inserting streamed data into a time partitioned table in BigQuery (e.g. by day), not all expected partitions are shown when querying them (although all data are actually available).
For instance, although there are data available for the dates 2021-09-13 to 2021-09-15, it is not possible to see the corresponding partitions when querying them using legacy SQL.
bq query --use_legacy_sql '
SELECT partition_id
FROM [database.product$__PARTITIONS_SUMMARY__]
ORDER BY partition_id DESC '
Waiting on bqjob_id_1 ... (0s) Current status: DONE
+-------------------+
| partition_id |
+-------------------+
| __UNPARTITIONED__ |
| 20210912 |
| 20210911 |
| 20210910 |
+-------------------+
A closer look using the information contained within the table metadata INFORMATION_SCHEMA.PARTITIONS reveals a partition called __STREAMING_UNPARTITIONED__ containing all rows which have not been correctly partitioned.
bq query --nouse_legacy_sql '
SELECT table_name, partition_id, total_rows
FROM database.INFORMATION_SCHEMA.PARTITIONS
WHERE table_name="product"
ORDER BY partition_id DESC '
Waiting on bqjob_id_2 ... (0s) Current status: DONE
+------------+-----------------------------+------------+
| table_name | partition_id | total_rows |
+------------+-----------------------------+------------+
| product | __UNPARTITIONED__ | 0 |
| product | __STREAMING_UNPARTITIONED__ | 9519 |
| product | 20210912 | 3014 |
| product | 20210911 | 3152 |
| product | 20210910 | 3369 |
+------------+-----------------------------+------------+
So the question is, why are all these data left unpartitioned? Note that this behavior has business impact, since querying repetitively unpartitioned data might incur high expenses.