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

CREATE OR REPLACE TABLE learning.weather_measurements (location_id INT64,
                                                       location_name STRING,
                                                       temperature NUMERIC,
                                                       humidity INT64 )
PARTITION BY DATE(_PARTITIONTIME);



INSERT INTO learning.weather_measurements(location_id, location_name, temperature, humidity)

SELECT 1, 'New York', NUMERIC '25.4', 65;

SELECT 2, 'Rome', NUMERIC '34.2', 27;

SELECT 3, 'Madrid', NUMERIC '23.1', 41;

SELECT *, _PARTITIONDATE, _PARTITIONTIME FROM `learning.weather_measurements`