Basic checks on BigQuery tables

[covaikumar99]

I have a set of CRITICAL tables in BigQuery that are getting loaded hourly by DAGS. 

I have been tasked to develop a standalone solution to check the following:

1) Are the tables present ?[ There are chances that the tables may get deleted by operations team]

2) if The table is present, is the table getting loaded on time ?

3) If the table is getting loaded, is there a difference in the size during consecutive runs[ The table is expected to increase in size]

If any of the above checks fails, the operations team has to be notified as soon as possible.

Can someone suggest a solution( probably a service or list of services) for the above requirement ?

[singhgyan]

You can build your solutions using below steps. 

1. Use INFORMATION_SCHEMA.TABLES to check for existence of a table. 
2. You need to define what "table getting loaded on time means". If you have a load timestamp for last updated timestamp in table, use that to find last loaded job or if you are maintaining a audit table which tracks job, use that.
3. You will have to use a audit table to store table and size at set frequency and then during every new run, compare it with previous run.  You can use INFORMATION_SCHEMA.TABLE_STORAGE to get row count of table or size of a table at a given point of time.

[caryna]

Hi @covaikumar99,

Welcome to Google Cloud Community!

In addition to @singhgyan suggestion, you may consider checking the BigQuery monitoring documentation which offers a high-level overview of monitoring data in BigQuery using Cloud Monitoring, Cloud Logging and INFORMATION_SCHEMA. 

You can also check this Medium article Email Alerts for GCP Events which provides a detailed guide on configuring email alerts in BigQuery using Cloud Logging, Pub/Sub, Cloud Function that could be particularly helpful for you. 

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.