Ingestion-time partitioning in BigQuery

Have you ever used ingestion-time partitioning in BigQuery?

It's a separate type of partitioning that distributes rows into partitions based on the time they land in BQ.

Once such a table is defined, you can query the pseudocolumns PARTITIONDATE and PARTITIONTIME.

As with other partition types, you can set up OPTIONS such as :
- partition_expiration_days = drops a partition after a given period of time
- require_partition_filter = forces a user to use a partition filter when querying

Reminder that if you're ingesting data via a BigQuery job (say using the bq CLI utility), you can also control which partition in this table you want to write to using a decorator e.g. my_table$20240621

No alt text provided for this image

Found it useful? Subscribe to my Analytics newsletter at notjustsql.com.