How to check if a bigquery day partition is empty

Question

To check if a table exists, we can use this query, it's quite cheap:

#legacySql
SELECT last_modified_time FROM {dataset}.__TABLES__ WHERE table_id='{table}'
LIMIT 1

Is there any similar cheap query to check if a partition is empty?

As I understand, I can use a select with _PARTITIONTIME in where clause but it will end up processing all data in the partition (if it exists) regardless of the limit clause. I've to perform check for the existence of many partitions and it has to be in form of a query and not an API request.

I've cronjobs which may run multiple times but I want to write data to the partition only if it's empty.

Answer 1

Below query is as cheap as $0 so you can safely use this approach to identify number of rows in any partition

#standardSQL
SELECT _PARTITIONTIME AS pt, COUNT(1) cnt
FROM `yourproject.yourdataset.yourpartitionedtable`
GROUP BY pt  
HAVING pt = TIMESTAMP('2016-12-11')   

Another option is below - it actually returns 0 if partition is empty and returns count of rows in partition otherwise

#standardSQL
SELECT IFNULL((
    SELECT COUNT(1) FROM `yourproject.yourdataset.yourpartitionedtable`
    GROUP BY _PARTITIONTIME HAVING _PARTITIONTIME = TIMESTAMP('2016-11-11')
  ), 0) cnt