Configuring BigQuery SQL Pipeline with Airflow Retries and Notifications

PDE By islever Editorial Last updated

Building the pipelines. Monitoring and troubleshooting processes.

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

The main trap is confusing task-level retries in Airflow with run-level consecutive failures, but selecting the correct operator for SQL execution is the primary discriminator here.

This page explains how to configure a BigQuery SQL pipeline in Cloud Composer using BigQueryInsertJobOperator for aggregation and Airflow's built-in retry and email notification features.

Common mistake: Choosing Option A because Upsert operators are common in data loading, but Upsert is for file loads, not arbitrary SQL transformations. Choosing D is a common mistake due to over-engineering, but it ignores the native orchestration capabilities of Composer.

Expert Analysis

The question asks for a pipeline that runs every two hours, appends results to an existing table, retries on error, and sends an email after three consecutive failures. The key constraint is the notification trigger: it must occur after three consecutive DAG/run failures, not just after task-level retries within a single run.

Option C is incorrect because BigQuery Scheduled Queries do not natively support sending email notifications upon failure; they only support Pub/Sub notifications. Furthermore, scheduled queries do not automatically retry failed executions in the way described (they typically skip or fail without automatic re-attempt logic across runs).

Option D proposes using BigQuery Scheduled Queries with Pub/Sub notifications and Cloud Functions to send emails after three failures. While technically possible, this is overly complex and not the standard 'pipeline' approach for orchestration logic involving retries and conditional notifications. More importantly, the prompt implies a need for a managed pipeline solution where the scheduling and failure tracking are handled centrally.

Options A and B involve Cloud Composer (Airflow). Airflow is the correct tool for orchestrating such workflows. However, we must distinguish between BigQueryUpsertTableOperator and BigQueryInsertJobOperator. The requirement is to "run an aggregate SQL transformation... and append the result." This describes executing a SQL job. BigQueryInsertJobOperator is designed to submit a BigQuery job (SQL query) to execute. BigQueryUpsertTableOperator is used to load data from GCS/CSV into a table with upsert logic, which does not fit the "aggregate SQL transformation" description.

Regarding the failure notification: In Airflow, setting retries=3 means the task will retry up to 3 times if it fails. If all retries fail, the task instance is marked as FAILED. Setting email_on_failure=True on the operator sends an email when the task finally fails (after retries are exhausted). The comments clarify that email_on_failure triggers on the final failure of a task instance. The phrase "three consecutive failures" in the question likely refers to the three retries plus the initial attempt, or it is a slightly ambiguous phrasing for "notify when the job fails after exhausting retries." Given the options, B is the most accurate implementation of the SQL execution and error handling pattern described. Option A uses the wrong operator for SQL transformations.

Therefore, B is the best answer because it correctly selects the operator for running SQL jobs (BigQueryInsertJobOperator) and configures standard Airflow error handling (retries and email_on_failure).

Official Reference

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

Exam Strategy

When asked about running SQL transformations in BigQuery via orchestration, always select BigQueryInsertJobOperator. Reserve BigQueryUpsertTableOperator for loading files (CSV/GCS) with upsert logic. For error handling requirements like 'retry X times' and 'notify on failure', map these directly to Airflow operator parameters retries and email_on_failure rather than building custom Pub/Sub/Cloud Function solutions unless explicitly required.

Frequently Asked Questions

What is the difference between BigQueryInsertJobOperator and BigQueryUpsertTableOperator?

BigQueryInsertJobOperator submits a SQL query job to BigQuery for execution. BigQueryUpsertTableOperator loads data from external sources (like GCS) into a BigQuery table with upsert capabilities. Use InsertJob for SQL transformations.

Does BigQuery Scheduled Query support email notifications on failure?

No, BigQuery Scheduled Queries do not support direct email notifications. They can publish to Pub/Sub, which requires additional infrastructure like Cloud Functions to send emails, making it less efficient than using Composer for this specific requirement.

More PDE FAQ →

Community Votes

D
53%
B
47%

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

Discussion 20 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 20

  1. raaad 👍 8 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. SuperVan 👍 6
    The prompt wants an email notification sent after three failed attempts. Is there any concern that the retry parameter is set to 3, wouldn't this mean that the email is sent after 4 failed attempts (1 original + 3 retries)?
  3. plum21 👍 4 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. MarcoPellegrino 👍 3 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
  5. RenePetersen 👍 3
    Option D mentions nothing about how the job retrying is put in place, so for that reason I don't think this is the correct option.
Show 15 more comments
  1. 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.
  2. gabbferreira 👍 2 Answered D
    It´s D "Notifications on operator failure Set email_on_failure to True to send an email notification when an operator in the DAG fails. To send email notifications from a Cloud Composer environment, you must configure your environment to use SendGrid." Setting email_on_failure = True will send the email after the FIRST failure https://cloud.google.com/composer/docs/composer-2/write-dags#notifications_on_operator_failure
  3. 56d02cd 👍 2 Answered D
    The retries in A and B are only for task failures/retries within the DAG. The question asks for a notification after three consecutive failures of the DAG. IOW ... the retries parameter is correct for handling failures within a single task instance. email_on_failure would typically fire after the final retry of that single task instance fails, not after three consecutive DAG runs fail.
  4. Augustax 👍 2 Answered D
    The retry times in B and clearly mentioned 2 hours in D make me think D is the better option..
  5. gabbferreira 👍 2 Answered D
    chatpgt and gemini said it is D
  6. carbonscave 👍 1 Answered B
    Why B is the right move Native Failure Logic: The strict requirement to only send an email "after three consecutive failures" is a classic workflow orchestration problem. Cloud Composer (managed Apache Airflow) handles this natively. By setting a task's retries parameter to 3 and email_on_failure to True, Airflow will automatically retry the task if it fails. The task only reaches a hard "Failed" state—and triggers the failure email—after it has exhausted all of its retry attempts. The Correct Operator: The BigQueryInsertJobOperator is the modern, standard Airflow operator used to submit any job to BigQuery. You pass it a job configuration payload containing your aggregate SQL query, the destination table, and the WRITE_APPEND disposition to satisfy the requirement to append the results.
  7. lmch 👍 1 Answered B
    This requirement describes a standard orchestration workflow involving a schedule (every two hours), a transformation (SQL aggregation), an append operation, and custom error handling (retries and conditional notifications). Cloud Composer (managed Apache Airflow) is the standard tool for this level of logic.
  8. 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
  9. 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.
  10. 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
  11. 06bbd0e 👍 1 Answered B
    Choose Cloud Composer (Apache Airflow), which handles task state, retries, and failure emails natively using retries and email_on_failure.
  12. 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.
  13. e593506 👍 1 Answered D
    The prompt wants an email notification sent after three failed attempts Option B does not meet that condition
  14. 22c1725 👍 1 Answered B
    There is no retry in (D) https://cloud.google.com/bigquery/docs/scheduling-queries
  15. 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

Bigtable Row Key Design for Time-Series Data

S-grade

Migrating Hadoop Spark Jobs to Dataproc Serverless and Cloud Storage

S-grade

← Back to PDE Study Guide