I had a pretty big table with ~7 years of data with a daily partitioning (appr. 2000-2500 partitions). Not far ago I've started to receive an error Quota exceeded: Your table exceeded quota for Number of partition modifications to a column partitioned table. As far as I know there was a limit "5000 partitions updated per table per day", but looks like it was increased x6 not long ago (at least it is stated as 30000 in the docs). However the error was appearing with the same frequency.
After that I decided to change granularity from day partitions to months. As always, I made a copy with CREATE TABLE ... AS ... and replaced my old table with a modified one. So, instead of 2000-2500 partitions I have less than 100 partitions now. But I still keep receiving this error!
I checked number of partitions with INFORMATIONS_SCHEMA.PARTITIONS and everything is correct. I tried to count total_modified_partitions from INFORMATIONS_SCHEMA.JOBS and I get a number much less than the limit.
Any solution to fix it? Can I keep table with the same old name, but get rid of this error?
Well, I've found a reason and it's not related to BigQuery. I use DBT for data transformations and actually error was appearing as a result of so called "incremental" model. And the error's text pointed to the final table, while actual issue was in the temp table created by DBT to perform "incremental" update. And DBT didn't change granularity automatically for this table and it was the root cause.