Big Query table partitions count limit

Question

I am currently working with Big Query and understand that there is a partition limit of up to 4,000 partitions.

Does anyone know if this limit apply to Active Storage Tier only or both Active & Long Term Storage Tier?

Reason for asking because I have a partitioned table, partitioned by hour and have been using it for more than 6 months already but we don't get any error prompting partition limit exceed 4,000 when we insert new data.

I have did a count on the number of partition attached image below:

As we can see the total partitions is 6,401 and we are still able to insert new data.

At the same we also create a new partitioned table and try moving data into this newly created partitioned table but we encountered some error saying we have exceeded the limit of 4,000.

In addition, I also tried to insert data incrementally but I still get error as follow:

Steps to reproduce error:

1. Create a partitioned table (partition by hour)
2. Start moving data by month from another table

My finding:

The mentioned partition limit is only applicable to active storage tier.

Can anyone help to confirm on this?

Answer 1

As I understood the limitation, you can't modify more than 4000 partitions in one job. Your jobs that you describe first are supposedly working because they are modifying only a few partitions. When you try to move more than 4000 partitions in one go, you will hit the limitation as you described.

I noticed I was hitting this limitation on both Active Storage and Long Term Storage. This is a BigQuery-wide limitation.