To determine whether a table is empty, the query SELECT 1 FROM <table_name> LIMIT 1; can be efficient and potentially cost-free, as BigQuery often optimizes it to perform a metadata read (avoiding a full data scan). The INFORMATION_SCHEMA.TABLES approach provides an even more explicit method for checking the existence of a table.
To determine whether a specific partition exists and is likely not empty, the query SELECT 1 FROM <table_name> WHERE DT = ‘<partition_date>’ LIMIT 1; can be used. However, this query incurs a cost because it necessitates reading data from the partition. A potentially more efficient approach is to query INFORMATION_SCHEMA.PARTITIONS to check for the existence of the partition. This minimizes data scanned and can keep your BigQuery bills down.
The SELECT 1 FROM <table_name> LIMIT 1; query is preferable when determining whether the entire table is empty, especially if Bytes Processed is zero. However, if the objective is to verify the presence of data within a specific partition, checking INFORMATION_SCHEMA.PARTITIONS is likely more efficient than querying the partition directly (unless you need absolute certainty of non-emptiness and are willing to pay the cost).
Was this helpful? If so, please accept this answer as “Solution”. If you need additional assistance, reply here within 2 business days and I’ll be happy to help.