BigQuery streaming and partitions: when is _PARTITIONTIME really evaluated?

Question

_PARTITIONTIME represents the time (truncated to the day) when a row is inserted into BigQuery.

However, when looking closely at the streaming mechanism (https://cloud.google.com/blog/products/gcp/life-of-a-bigquery-streaming-insert ), we can see 3 different "insertion times" when a row is inserted into BigQuery:

• time when the row is received by a "Streaming ingestion workers"
• time when the row is stored into the "Streaming buffer"
• time when the row is extraction workers stored it into the final (Capacitor) storage.

Does somebody knows which one of those 3 moments correspond to _PARTITIONTIME ?

Answer 1

When the row is still in the streaming buffer, _PARTITIONTIME is null for this row; after the row is extracted, the extraction time is the _PARTITIONTIME for this row. An exception is that when the row is streamed into a partition directly, "table$20180101". In this case the _PARTITIONTIME is always "2018-01-01".