Question

Your team runs a nightly transformation in BigQuery that consists of a single SQL statement writing to a reporting table. It has no upstream or downstream dependencies on other systems, requires no conditional branching, and only needs to run once per day at 02:00. Currently it is a manual query someone triggers each morning. Operations wants the lowest-maintenance way to automate this while keeping failure notifications visible. Which approach best fits these requirements?

Accepted Answer

Create a BigQuery scheduled query with a daily schedule at 02:00 and configure a Pub/Sub notification (or email) on run completion/failure.

Answer

Deploy a Cloud Composer environment and build a DAG with a single BigQueryInsertJobOperator scheduled daily.

Answer

Create a Cloud Function triggered by Cloud Scheduler that submits the query through the BigQuery client library.

Answer

Write a Dataflow batch pipeline that executes the SQL and schedule it with a Flex Template trigger.