Google Professional Data Engineer

Get started today

Ultimate access to all questions.

Quick Answer

Answer-first summary for fast verification

Answer: Create a BigQuery scheduled query to run the SQL transformation with schedule options that repeats every two hours, and enable notification to Pub/Sub topic. Use Pub/Sub and Cloud Functions to send an email after three failed executions.

The most robust and flexible solution involves creating a BigQuery scheduled query that runs the SQL transformation every two hours and enables notifications to a Pub/Sub topic. By using Pub/Sub and Cloud Functions, you can track consecutive failures, implement retry logic, and send an email notification only after three consecutive failures. This approach is superior because: - BigQuery scheduled queries can execute the SQL transformation at the desired frequency. - Pub/Sub captures failure notifications from the scheduled query. - Cloud Functions can monitor for consecutive failures, manage retries, and trigger email notifications after three failures. Other options either use inappropriate operators for aggregate transformations or lack the capability to specifically notify after three consecutive failures.

Author: LeetQuiz Editorial Team

Quick Answer

Answer-first summary for fast verification

Author: LeetQuiz Editorial Team

Comments (1)

Michelle GoogleUserOct 28, 2025 1:53 AM

GPT: Option A works, but it’s the wrong tool and adds avoidable complexity. Retries: BigQuery Scheduled Queries don’t give you first-class, per-task retry control (e.g., “retry up to 3 times immediately on failure”). Airflow’s BigQueryInsertJobOperator does—retries=3—so failures are retried right away within the same run. “Email after three consecutive failures”: With Scheduled Queries you’d have to build and maintain stateful logic in Pub/Sub + Cloud Functions to count consecutive failures and then email. In Airflow you get this behavior natively: the task only “fails” after exhausting its 3 retries, and email_on_failure=True fires then. Appending results: Airflow’s job config can explicitly set WRITE_APPEND on the query job. Scheduled Queries can append too, but you’re already in Airflow if you want robust orchestration and retries—no need to split responsibilities. So A is possible but unnecessarily complex and doesn’t meet the retry requirement as cleanly or explicitly as C.

You need to set up a SQL pipeline that performs an aggregate SQL transformation on a BigQuery table every two hours, appending the result to another existing BigQuery table. The pipeline should retry in case of errors and send an email notification after three consecutive failures. How would you configure this pipeline?

Real Exam

Create a BigQuery scheduled query to run the SQL transformation with schedule options that repeats every two hours, and enable notification to Pub/Sub topic. Use Pub/Sub and Cloud Functions to send an email after three failed executions.

64.7%

Use the BigQueryUpsertTableOperator in Cloud Composer, set the retry parameter to three, and set the email_on_failure parameter to true.

23.5%

Use the BigQueryInsertJobOperator in Cloud Composer, set the retry parameter to three, and set the email_on_failure parameter to true.

11.8%

Create a BigQuery scheduled query to run the SQL transformation with schedule options that repeats every two hours, and enable email notifications.

Powered ByGPT 5.4

Comments (1)

Michelle GoogleUserOct 28, 2025 1:53 AM