Setup a Big Query Alert when no table row update/upload takes place

Question

what's the best way to set up a Big Query alert per Email when no data was uploaded to a Big Query table for longer than one day? What comes to my mind is:

• Stackdriver
• Pub Sub
• Cloud Function

But I am not sure whats the fastest and best way to do so. I tried to set up via Stackdirver -> Create New Alert Ploicy

• Resource type:BigQuery Dataset
• Metric:Uploaded rows
• Condition triggers if -> Any time series violates -> is absent FOR 23 hours

But no Email was send. Although some tables get no data for months...

Answer 1

You should create Audit Log Based Alert. Go to Logging -> Select BigQuery and then filter by inserts and you can create a log based metric and you can setup an alert rule based on that:

To create an alerting policy on a logs-based counter metric, do the following:

1. Go to Logs-based metrics in the Google Cloud Console:
2. Find the metric you want to explore and select Create alert from metric in the metric's More more_vert menu.
3. The Conditions pane opens, with the logs-based metric you chose pre-populated in the Target dialog.
4. In the Configuration dialog, fill in the Threshold value.
5. Click Save, which displays the Create new alerting policy panel with your completed condition.
6. Optional: Fill in the Notifications and Documentation sections.
7. In the Name this policy section, enter a name for the alerting policy.
8. Click Save.

As mentioned on this page: https://cloud.google.com/logging/docs/logs-based-metrics/charts-and-alerts

see this video which explains this in detail with a similar example about how to do this: https://youtu.be/dqoZEfJ7UbM?t=1275