Question

A data engineering team runs a nightly transformation that reads from three staging tables and writes into a reporting table used by BI dashboards each morning. They currently trigger the SQL manually. They want a fully managed, low-maintenance way to run this single BigQuery SQL statement on a recurring schedule with built-in run history, retries, and no external infrastructure to manage. Which approach best meets these requirements with the least operational overhead?

Accepted Answer

Configure a BigQuery scheduled query that runs the transformation SQL on a nightly cron schedule.

Answer

Deploy a Cloud Composer environment and author an Airflow DAG with a BigQueryInsertJobOperator to run the SQL nightly.

Answer

Create a Cloud Scheduler job that publishes to Pub/Sub, triggering a Cloud Function that calls the BigQuery API.

Answer

Write a Python script on a Compute Engine VM invoked by a system cron entry to execute the query each night.