You run an aggregate SQL transformation every two hours that appends to a BigQuery table. The pipeline must retry on failures and email after three consecutive failures. How do you configure Cloud Composer?

The BigQueryInsertJobOperator is the correct choice for running SQL transformations in Cloud Composer (Apache Airflow) because it allows you to execute SQL queries, including aggregate statements, and append results to a BigQuery table. Cloud Composer's Airflow operators inherently support retry mechanisms and email notifications directly through parameters like retries and emailonfailure. Setting retries to three ensures the pipeline attempts to rerun on transient failures, and emailonfailure=True triggers an email after the configured number of retries (in this case, three consecutive failures). The BigQueryUpsertTableOperator is designed for upserting data based on a primary key, not for general aggregate SQL transformations that append. BigQuery scheduled queries are a valid way to run SQL but lack the built-in retry logic and direct email-after-N-failures functionality that Composer offers, making the Pub/Sub and Cloud Functions setup overly complex for this requirement.