Your table exceeded quota for Number of partition modifications in BigQuery

When loading data into partitioned tables or while doing any DML statements (INSERT / UPDATE / MERGE), the data in the individual partitions are updated. There is a quota on how many partitions can be modified by a job per day. If you exceed that quota you will get the below error message

{Location: "partition_modifications_per_column_partitioned_table.long"; 
Message: "Quota exceeded: Your table exceeded quota for Number of partition 
modifications to a column partitioned table. For more information, see 
https://cloud.google.com/bigquery/troubleshooting-errors"; 
Reason: "quotaExceeded"}

Solution:

Redesign your loading process to be within the daily quotas

• Check the quotas and limits on partitioned tables. 
• At the time of writing this article, the quotas are:  Maximum number of partitions modified by a single job — 4,000, 30,000 partition modifications for a column partitioned table per day in total
• Instead of doing hundreds of times on small files, you can consider doing a batch load
• Or, Consider changing your partition keys, such that your daily operations remain under the stipulated quotas
• Or, Consider converting the job to a streaming insert instead

victor

posted on 01 Oct 20