What do _PARTITIONTIME and _PARTITIONDATE expose in an ingestion-time partitioned BigQuery table?

## Why pseudo-columns exist When you create a table with `PARTITION BY _PARTITIONDATE`, BigQuery assigns each row to a partition by the time it was ingested. Nothing in the row itself records that. So the partitioning value is surfaced through two *pseudo-columns* — fields that behave like columns in a query but are not part of the table's declared schema: - `_PARTITIONTIME` — a TIMESTAMP, truncated to the partition boundary, always in UTC. - `_PARTITIONDATE` — the DATE form of the same value. Because they are not schema columns, `SELECT *` does not return them. You reference them by name: ```sql SELECT _PARTITIONDATE AS load_day, COUNT(*) FROM ds.raw_logs WHERE _PARTITIONDATE BETWEEN '2026-08-01' AND '2026-08-07' GROUP BY load_day; ``` A predicate on either pseudo-column prunes partitions exactly like a predicate on a real partitioning column, and the same rules apply — constants prune, subquery-derived bounds and disjunctions with non-partition predicates do not. ## They exist only on ingestion-time tables This is the point candidates most often get wrong. A table created with `PARTITION BY DATE(event_ts)` — partitioned on a real column — has **no** `_PARTITIONTIME` pseudo-column. There is no need for one: the partitioning value is a genuine field and you filter it directly. Writing `WHERE _PARTITIONDATE = '2026-08-01'` against such a table is an error, not a silent full scan. ## The UTC and truncation details `_PARTITIONTIME` is truncated to the partition granularity in UTC. For a daily ingestion-time table it is always midnight UTC of the arrival day. Two consequences follow. First, teams operating in other time zones cannot treat the partition boundary as their local business day; a report for "yesterday" in a UTC+9 region spans two partitions. Second, comparing `_PARTITIONTIME` to a local-time literal quietly shifts the window — convert explicitly rather than assuming. ## Special partitions Ingestion-time tables can hold a `__UNPARTITIONED__` partition: rows that have arrived through streaming but are not yet assigned to a dated partition live there temporarily, with a NULL partition value, until the service commits them. A query filtering a specific date will not see them. If a freshness check reports rows missing that you know were streamed, this is the usual explanation, and it resolves on its own. Separately, tables partitioned on a real column place rows whose partitioning column is NULL in a `__NULL__` partition, and integer-range tables place out-of-range values in `__UNPARTITIONED__`. These are addressable partitions in metadata, not error states. ## Writing to a specific partition Ingestion-time tables support partition decorators of the form `table$YYYYMMDD` in load and copy operations, so a job can replace exactly one day: ```bash bq load --replace --source_format=NEWLINE_DELIMITED_JSON \ ds.raw_logs\$20260801 gs://bucket/2026-08-01/*.json ``` That idempotent "reload one day" pattern is the main operational reason people still choose ingestion-time partitioning for landing tables: the pipeline can re-run a day safely without a `DELETE`. ## Arrival time is not event time The semantic caveat is the one that matters in design reviews. `_PARTITIONDATE` answers "when did this row land", not "when did this happen". A mobile client that was offline for three days delivers events whose business date is three days old into today's partition. So: - A report on event date cannot prune tightly on `_PARTITIONDATE` — it must widen the range to cover the expected lateness, then filter the real event column. - Reprocessing a business day means scanning several arrival partitions. The common architecture is to keep the raw landing table ingestion-time partitioned (cheap, idempotent reloads by decorator, no assumptions about payload contents), and have the transformation write a modelled table partitioned on the real event timestamp with clustering on the business keys. Downstream consumers then get partitions that mean what they expect, and the raw table keeps its operational advantages. ## Inspecting partitions `INFORMATION_SCHEMA.PARTITIONS` lists a table's partitions with their identifiers, row counts and last-modified times. Querying it is the cheap way to see whether `__UNPARTITIONED__` or `__NULL__` is accumulating rows, whether a day's load actually landed, and how large the partitions really are — all without scanning the table itself.