We’ll show you the cheapest & fastest way to find partition existence in BigQuery. Use BigQuery’s INFORMATION_SCHEMA to find the most recent partition ID.
So instead of this:
select max(yourPartitionField) from `yourProject.yourDataset.yourTable`
Do this:
select max(partition_id) from `yourProject.yourDataset.INFORMATION_SCHEMA.PARTITIONS` where table_name = "yourTable"
Need to translate the partition_id back into a date? Do this:
select PARSE_DATE("%Y%m%d", max(partition_id)) from `yourProject.yourDataset.INFORMATION_SCHEMA.PARTITIONS` where table_name = "yourTable"
Depending on your table size, this will save you both a ton of money and time. If your table is tiny, it might be identical-ish since BigQuery is so fastttt. But hope this helps your pipelines! Happy coding everybody! Check out this article for more BigQuery fun!