In BigQuery integer range partitioning, is it possible to create non-uniform partition intervals? The use case that I have is that the date column is an integer (20201231 for example). All current queries target this date format and cannot be changed so the partition has to be on this field. Integer partitioning for this works, but only for the yearly case, eg:

PARTITION BY RANGE_BUCKET(INTEGER_DATE, GENERATE_ARRAY(20150000, 20250000, 10000))

for partitioning from 2015 to 2024. However, for other cases there are problems. Daily partitioning creates too many partitions for which there are no corresponding actual dates and easily crosses BQ’s limit of 4000 partitions while not being able to span even 1 year’s worth of data. For other cases (weekly, monthly etc.), the intervals are not aligned with the yearly calendar so some weeks and months will span two partitions. I tried the below (specifying a hardcoded array instead of GENERATE_ARRAY in the PARTITION BY clause) but BQ gives an error:

PARTITION BY RANGE_BUCKET(INTEGER_DATE, [20150000, 2016000, 2017000])

So, directly specifying the partition array does not work.

Does anyone know any alternatives, work arounds to this?