Exam Professional Data Engineer topic 1 question 270 discussion

- 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.

There is no retry in (D) https://cloud.google.com/bigquery/docs/scheduling-queries

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

chatpgt and gemini said it is 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

The retry times in B and clearly mentioned 2 hours in D make me think D is the better option..

"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.

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.

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.

The prompt wants an email notification sent after three failed attempts Option B does not meet that condition

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

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

Option B

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.

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