Question

Your team runs a critical BigQuery scheduled query every morning that populates a reporting table consumed by executive dashboards. Last week the query silently failed for three consecutive days because an upstream table was renamed, and no one noticed until executives complained about stale data. You need a low-maintenance, native solution that alerts the on-call engineer whenever any scheduled query fails, without building custom polling scripts. What should you do?

Accepted Answer

Configure Pub/Sub notifications on the scheduled query's transfer config and create a Cloud Monitoring alerting policy on the log-based metric for BigQuery Data Transfer Service run failures, routing notifications to a Pub/Sub or email channel.

Answer

Write a Cloud Function on a Cloud Scheduler trigger that queries the reporting table's last-modified timestamp every hour and emails the team if it is older than expected.

Answer

Migrate the scheduled query into a Cloud Composer DAG and rely on Airflow's default task retry mechanism to re-run the query until it succeeds.

Answer

Enable BigQuery table expiration on the reporting table so that stale data is automatically deleted, forcing analysts to notice the missing table.