How to Configure BigQuery SQL Pipeline Retries and Notifications?

PDE By islever Editorial Last updated

Short answer

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

Question

You need to create a SQL pipeline. The pipeline runs an aggregate SQL transformation on a BigQuery table every two hours and appends the result to another existing BigQuery table. You need to configure the pipeline to retry if errors occur. You want the pipeline to send an email notification after three consecutive failures. What should you do?
  1. Use the BigQueryUpsertTableOperator in Cloud Composer, set the retry parameter to three, and set the email_on_failure parameter to true.
  2. Use the BigQueryInsertJobOperator in Cloud Composer, set the retry parameter to three, and set the email_on_failure parameter to true. Correct Answer
  3. Create a BigQuery scheduled query to run the SQL transformation with schedule options that repeats every two hours, and enable email notifications.
  4. 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.

Key Takeaways

Tests the distinction between native service features and managed orchestration workflows, with the common trap being over-engineering a custom Pub/Sub/Cloud Functions solution when Composer natively handles scheduling, retries, and alerts.

This question evaluates orchestrating scheduled BigQuery SQL transformations with automatic retries and failure alerts using Cloud Composer. Community consensus confirms that leveraging Apache Airflow operators is the most reliable and exam-approved approach compared to relying solely on native BigQuery scheduled queries.

Common mistake: Option D is frequently selected because it explicitly mentions the two-hour schedule and three-failure threshold, but it incorrectly assumes BigQuery scheduled queries can natively track consecutive failures across runs without extensive custom logic.

Expert Analysis

Why the Answer Is Correct

Cloud Composer (managed Apache Airflow) natively supports DAG scheduling, task retries, and failure notifications. The BigQueryInsertJobOperator efficiently executes arbitrary SQL, including INSERT...SELECT statements for appending aggregated results. Configuring retries=3 ensures automatic restarts on transient errors, while email_on_failure=True triggers an alert only after all retry attempts are exhausted, perfectly matching the requirement. The two-hour execution interval is standardly configured at the DAG level, which is assumed in professional certification contexts.

Why the Other Options Are Wrong

Option A utilizes BigQueryUpsertTableOperator, which is designed for MERGE or UPSERT operations rather than simple data appends. Option C relies on BigQuery Scheduled Queries, which lack built-in retry mechanisms and cannot inherently track consecutive failures across independent execution cycles. Option D introduces unnecessary architectural complexity by routing alerts through Pub/Sub and Cloud Functions; Google exams consistently prefer fully managed orchestration tools like Composer for workflow management, reliability, and native alerting.

Community Comment Notes

Candidates frequently debate between B and D due to the explicit phrasing in the question, but experienced engineers emphasize that Composer inherently manages both the schedule and failure tracking through DAG configuration. Comment [10] correctly points out that BigQuery scheduled queries do not natively retry failed executions, making Airflow the necessary choice for robust error handling. Comment [9] provides direct documentation links confirming the operator parameters and Composer notification settings, validating the technical accuracy of the recommended approach.

Official Reference

These links open the official vendor documentation in a new tab.

Exam Strategy

When a scenario emphasizes recurring schedules, automatic retries, and cross-run failure tracking, prioritize managed orchestration platforms like Cloud Composer over native service features. Certification exams routinely expect you to assume standard configuration practices, such as defining DAG schedules, rather than demanding they be explicitly repeated in the correct option.

Community Votes

B
56%
D
44%

56% of anonymous learners picked answer B. Votes are pick records left by other test-takers — they are not the verified answer.

Discussion 12 contributions

Compiled from public community threads and reader submissions, lightly edited for clarity. Handles and vote tallies are shown as recorded, and comments reflect the views of individual contributors.

From public community threads 12

  1. raaad 👍 6 Answered B
    - It provides a direct and controlled way to manage the SQL pipeline using Cloud Composer (Apache Airflow). - The BigQueryInsertJobOperator is well-suited for running SQL jobs in BigQuery, including aggregate transformations and handling of results. - The retry and email_on_failure parameters align with the requirements for error handling and notifications. - Cloud Composer requires more setup than using BigQuery's scheduled queries directly, but it offers robust workflow management, retry logic, and notification capabilities, making it suitable for more complex and controlled data pipeline requirements.
  2. datapassionate 👍 2 Answered D
    D. 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 This method utilizes BigQuery's native scheduling capabilities for running the SQL job and leverages Pub/Sub and Cloud Functions for customized notification handling, including the specific requirement of sending an email after three consecutive failures.
  3. MarcoPellegrino 👍 2 Answered D
    A) Wrong, Upsert is not for appending B) Wrong, doesn't mention the 2 hours scheduling C) Wrong, doesn't mention the emailing D) Correct
  4. Pime13 👍 1 Answered B
    B. Use the BigQueryInsertJobOperator in Cloud Composer, set the retry parameter to three, and set the email_on_failure parameter to true. This approach leverages Cloud Composer (Apache Airflow) to manage the SQL pipeline, providing robust workflow management, retry logic, and notification capabilities. By setting the retry parameter to three and enabling email notifications, you ensure that the pipeline will retry on errors and notify you after three consecutive failures.
  5. josech 👍 1 Answered B
    https://airflow.apache.org/docs/apache-airflow-providers-google/stable/_api/airflow/providers/google/cloud/operators/bigquery/index.html#airflow.providers.google.cloud.operators.bigquery.BigQueryInsertJobOperator https://cloud.google.com/composer/docs/composer-2/write-dags#notifications_on_operator_failure
Show 7 more comments
  1. b3e59c2 👍 1 Answered D
    Terrible options as usual. Whilst B is the most elegant, it doesn't explicitly address the 2 hour scheduling (you can schedule within Composer, but the answer doesn't mention it). If we take these answers on the surface level, D is the only option that actually achieves our goal.
  2. joao_01 👍 1
    It s B (however for me its a incomplete answers cause it does not address the schedule of every 2 hours). Its not C or D because BigQuery scheduled queries by default does not retries the queries when error occurs. Link: https://cloud.google.com/bigquery/docs/scheduling-queries
  3. plum21 👍 1 Answered D
    "You want the pipeline to send an email notification after three consecutive failures" - it is not about retries which are configurable via Composer operator - it is about 3 consecutive executions which could be for different hours.
  4. scaenruy 👍 1 Answered B
    B. Use the BigQueryInsertJobOperator in Cloud Composer, set the retry parameter to three, and set the email_on_failure parameter to true.
  5. e593506 👍 1 Answered D
    The prompt wants an email notification sent after three failed attempts Option B does not meet that condition
  6. Augustax 👍 1 Answered D
    The retry times in B and clearly mentioned 2 hours in D make me think D is the better option..
  7. JyoGCP 👍 1 Answered B
    Option B

Reader comments & corrections

No reader corrections yet — spotted an error or have a note? Share it below.

Log in to comment, report an error, or add a note about this question.

How to establish consistent BigQuery spend?

S-grade

Triggering Airflow DAGs Reactively in Private Cloud Composer

S-grade

How to resolve Cloud Composer 2 worker pod evictions and task failures?

S-grade

Which Google Cloud database for on-prem PostgreSQL with analytics?

S-grade

← Back to PDE Study Guide