GCP Professional Data Engineer Practice Question

Your team uses Cloud Composer (Airflow 2) to orchestrate a nightly pipeline that stages CSV files from Cloud Storage into BigQuery, transforms them, and then publishes dashboards. Compliance now requires the pipeline to stop and alert operations when more than 0.5 % of rows are missing the mandatory customer_id field or when any transaction_date is set in the future. The team wants to reuse managed services and write as little custom code as possible. Which design meets these requirements?

Replace the load step with a custom Dataflow Flex Template that calculates data-quality metrics and publishes them to Pub/Sub; a Cloud Run service reads the metrics, decides whether to continue, and triggers later DAG tasks through Cloud Tasks.
Add a BigQueryCheckOperator in the DAG immediately after the staging load. The task runs SQL assertions for the null-rate and future-date rules, fails if they are violated, triggers Composer's built-in alerts, and all downstream tasks depend on its success.
Create a Cloud Logging log-based metric that counts BigQuery load-job errors; configure an alerting policy to notify operators and let the remaining DAG tasks run regardless of the metric value.
Define a NOT NULL constraint on customer_id in the target table and schedule a nightly BigQuery job to delete any rows with future transaction_date values after loading completes; continue running transformations even if deletions occur.

Report Issue

Answer Description

Using Cloud Composer's native BigQueryCheckOperator keeps the solution almost entirely declarative and fully managed. After the load step writes the file to a staging table, a BigQueryCheckOperator task can run two SQL assertions:

COUNTIF(customer_id IS NULL) / COUNT(*) < 0.005
COUNTIF(transaction_date > CURRENT_DATE()) = 0 The operator fails automatically when either test returns FALSE, which blocks all downstream tasks because they are set to depend on this check. Composer's built-in alerting (email, Pub/Sub, or PagerDuty) can be enabled on task failure, satisfying the requirement to notify operations. The approach avoids extra infrastructure and custom services.

The log-based alert option does not evaluate business-level rules or stop the DAG; it only reacts to generic load errors. Relying on NOT NULL constraints plus a cleanup query addresses at most one rule and still allows invalid files containing too many nulls to pass. Building a custom Dataflow template and Cloud Run controller adds significant bespoke code and operational overhead compared with using Composer's and BigQuery's native capabilities.

Ask Bash

Bash is our AI bot, trained to help you pass your exam. AI Generated Content may display inaccurate information, always double-check anything important.