CCNA Maintaining and Automating Data Workloads Questions

Dataplex data quality rules include row rules (for null checks) and column rules (for range or value checks). Table rules apply to the entire table; custom SQL can be used but row and column rules are the standard.

A direct dependency edge is the canonical Airflow way to sequence tasks inside one DAG: the scheduler keeps the downstream task in a waiting state until the upstream load succeeds. Sensors and XComs serve other purposes and would add complexity or incorrect semantics here. The dependency edge provides the required completion gating with no polling of BigQuery.

The `bq` command with the `--dry_run` flag allows you to estimate the amount of data a BigQuery query will process before actually executing it. This dry run does not read any data or incur charges; it simply returns the estimated bytes to be processed, which you can use to calculate the cost based on BigQuery's pricing model.

Failed task instances that have exhausted retries stay in a failed state until they are cleared, at which point the scheduler re-queues them. Triggering that clear programmatically, for example from a failure callback that waits for the dependency to recover, restores the pipeline automatically. Backfill settings, retry counts, and timeout tuning only affect future scheduling or in-flight attempts, not instances already marked failed.

Legacy BigQuery streaming inserts draw on a quota pool that is easy to saturate at peak throughput. The Storage Write API is a separate ingestion path with higher and separately metered limits, and its exactly-once mode uses write streams with offsets so retries do not duplicate rows. Switching the sink preserves the output schema while relieving the quota bottleneck.

Cloud DLP (Data Loss Prevention) is the correct choice because it is purpose-built for inspecting, classifying, and de-identifying sensitive data such as PII. It integrates natively with BigQuery via inspection jobs and de-identification templates, allowing you to scan tables for over 150 built-in infoTypes (e.g., email, SSN) and apply transformations like masking, tokenization, or encryption before sharing data with analysts.

Workflows is a serverless, fully managed orchestration service that charges only per step executed, making it ideal for simple linear workflows with basic retries. It natively integrates with Cloud Functions and Cloud Storage via connectors, so the hourly fetch-transform-write pipeline can be defined in YAML/JSON without managing infrastructure. Cloud Scheduler alone can trigger jobs but cannot orchestrate multi-step logic or handle the transform step.

Increasing the number of workers distributes the processing load across more parallel workers, reducing CPU utilization per worker and allowing the pipeline to keep up with the incoming data stream. This directly addresses both high CPU usage and increasing system lag by scaling out horizontally.

Airflow's ExternalTaskSensor exists specifically to express a dependency on a task in another DAG within the same Airflow deployment. It checks the external DAG run for the matching execution date and only allows downstream tasks to proceed after the upstream task succeeds. Time-based waits, external cron triggers, and message-based glue all fail to guarantee that the ingestion actually finished successfully before the load begins.

Transient failures are best handled with operator-level retries, which re-execute the same task after a delay and only surface an alert once the configured attempts are exhausted. Setting retries to 3 with a short retry_delay on the Dataproc task gives the cluster room to recover from resource pressure while keeping the DAG's dependency chain and alerting behavior unchanged.

The DataprocSubmitJobOperator is specifically designed to submit a job (e.g., a Spark job) to an existing Dataproc cluster. In this scenario, you need to run a Spark job on a scheduled basis, and Cloud Composer (Airflow) provides this operator to submit the job to Dataproc. After the Spark job completes, you can chain a BigQuery load operator to trigger the load, matching the requirement exactly.

Cloud Audit Logs capture BigQuery job failures, and log-based alerting policies in Cloud Monitoring can trigger Pub/Sub notifications. A Cloud Function subscribed to the topic can call the ticketing API, creating incidents automatically. This event-driven chain uses managed services, avoids polling, and requires little custom code compared with the other options.

BigQuery reservations allow you to create separate reservations for different workloads (e.g., interactive queries vs. batch loads) and assign them different priority levels. By creating a dedicated reservation for interactive queries with a higher priority, you ensure that interactive queries get preferential access to slots, even when autoscaling slots are consumed by long-running batch jobs. This directly addresses the contention issue without reducing overall capacity.

The Dataflow job update mechanism allows you to replace a running pipeline's code with a new version without draining or stopping it, preserving the existing state and minimizing data loss. This mechanism supports exactly-once semantics by ensuring that all in-flight elements are processed exactly once, even after the update, by maintaining the pipeline's checkpoint and watermark state.

BigQuery scheduled queries are the native mechanism for running SQL on a recurring schedule. They accept a schedule, a query, an optional destination table, and configuration for failure notification through Pub/Sub, all without any infrastructure to operate. Composer, Cloud Scheduler with API calls, and Dataflow all can run SQL, but each introduces components to manage, which conflicts with the requirement for the least operational overhead.

Dataflow supports in-place pipeline updates via the update option, which preserves the pipeline's state (including watermarks and deduplication state) and maintains exactly-once semantics. The new transform must be backward compatible with the existing pipeline's state and schema so that the update can be applied without draining.

Flex slots are a BigQuery feature that allows you to purchase slots for a short, fixed duration (e.g., 1 hour, 1 day, 1 week) without a long-term commitment. They are designed exactly for temporary bursts of query activity, such as peak-hour spikes, and can be added to an existing flat-rate reservation. By using flex slots, you temporarily increase the slot capacity of your reservation, reducing queuing, and then the slots automatically expire after the chosen period, avoiding ongoing costs.

Dataplex Data Quality Tasks allow you to define and run data quality rules on BigQuery tables, including column-level checks (e.g., null checks) and row-level checks (e.g., value ranges). These tasks can be scheduled to run periodically, and they generate results that can be monitored. This is the native Dataplex feature for enforcing data quality.

The 'subscription/num_undelivered_messages' metric shows the number of messages not yet acknowledged, and 'subscription/oldest_unacked_message_age' indicates how long messages have been waiting. Both help detect backlog.

Composer surfaces Airflow execution data in two complementary ways: structured log entries in Cloud Logging and Airflow metrics in Cloud Monitoring. A log-based alerting policy catches the explicit DAG run failure event, while a metric-based policy on task duration or retries catches a task that is hanging or looping. Together they cover both the hard failure and the stuck-retrying condition, whereas health, uptime, and audit signals describe infrastructure and control-plane state rather than workload outcomes.

The correct approach is to use a cron expression that runs only Monday through Friday, such as '0 2 * * 1-5', combined with catchup=False to avoid backfilling. This leverages Airflow's native scheduling, requires no changes to task logic, and keeps the DAG simple. Other methods either add external dependencies or still trigger the DAG on weekends.

To achieve exactly-once processing for BigQuery writes in a Dataflow streaming pipeline, use the BigQuery Storage Write API with withMethod(STORAGE_WRITE_API) and configure at least one stream. This method provides transactional writes and deduplication, ensuring each record is written exactly once. Other methods like STREAMING_INSERTS with insertId offer only best-effort deduplication and cannot guarantee exactly-once semantics.

Cloud Composer 2 Dataflow operators can block until the job reaches a terminal state, and the deferrable variant frees the worker while waiting. Setting execution_timeout to a value longer than the expected job duration prevents premature task failure. Schedule interval, Cloud Function wrapping, and retry policies do not make a task wait for the underlying Dataflow job.

Airflow datasets in Cloud Composer create a producer-consumer dependency where the verification DAG's successful completion updates a dataset and the reporting DAG is scheduled on that update. This keeps the dependency inside orchestration, avoids polling, and inherently prevents the reporting DAG from running when verification fails.

Retaining raw messages for years requires a durable, low-cost object store, and Cloud Storage with lifecycle-based class transitions is the standard fit on Google Cloud. By branching the existing Dataflow pipeline to write unmodified messages to a bucket, the company keeps a complete, reprocessable archive without building a separate ingestion path or managing long-lived Pub/Sub backlogs.

Airflow pools provide a shared concurrency limit that spans DAGs. Sizing a pool to keep total in-flight API calls under the provider's rate limit prevents 429 responses. Increasing workers, limiting per-DAG active runs, or relying on retries does not coordinate request volume across the many DAGs that share the external API.

ExternalTaskSensor is the purpose-built mechanism for waiting on a task in another DAG. Configuring it with the correct execution date alignment makes the report DAG block until the load task succeeds, which is exactly the cross-DAG dependency described. Time-based and intra-DAG mechanisms cannot observe another DAG's success state.

Dataflow Flex Templates allow packaging a pipeline as a Docker image with a metadata file, enabling reuse with different parameters at runtime without code changes. They support dynamic parameters and are the recommended approach for reusable pipelines.

Dataflow supports replacing a running streaming job with an updated one using the same pipeline name via the 'Replace job' (or update) mechanism, which performs an in-place upgrade that preserves the pipeline's state and does not require draining. This is the supported path for updating streaming pipelines without stopping data ingestion. Draining, cancelling, or stopping the job all interrupt processing and are not the recommended no-drain update strategy.

Replacing deprecated operators with a PythonOperator that calls the service's current client library removes reliance on unsupported code and keeps the DAG functional on Composer 2. Adding tests ensures the replacement behaves correctly and catches regressions during future upgrades, which is the durable, maintainable outcome the scenario asks for.

The --dry_run flag in BigQuery allows you to validate a query and estimate the number of bytes it will process without actually running it, thus providing a cost estimate. This is a standard feature in the bq command-line tool and the BigQuery API. It returns the total bytes processed, which can be used to calculate the cost based on current pricing.

BigQuery scheduled queries are the native, serverless way to run SQL on a recurring schedule and write output to a destination table, with automatic retry of failed runs. Because the schedule, SQL, and destination all live inside BigQuery, no additional infrastructure or orchestration tooling is needed, matching the low-overhead requirement precisely.

Airflow uses the bitshift operator (>>) to define task dependencies in a linear sequence. The DAG must ensure that the extract task completes before the transform task starts, and the transform task completes before the load task starts. This is achieved by chaining the tasks in order: extract >> transform >> load, which enforces the required sequential execution.

The bq query --dry_run flag validates a query and returns the amount of data it would process without actually executing it, which is the standard way to estimate BigQuery on-demand query cost. Since BigQuery on-demand pricing is based on bytes scanned (currently $6.25 per TiB), the dry-run's reported bytes give a direct cost estimate. It also validates syntax and permissions without incurring charges.

BigQuery scheduled queries are a built-in, serverless feature that lets you schedule SQL queries to run at specified intervals. They require no infrastructure management and can write results to a destination table. For a simple daily query, this is the most straightforward and low-maintenance solution, avoiding the overhead of Cloud Composer, Cloud Functions, or Dataflow.

The Cloud Monitoring metric pubsub.googleapis.com/subscription/oldest_unacked_message_age reports the age (in seconds) of the oldest unacknowledged message in a subscription, which is exactly the latency indicator requested. It is a subscription-level metric that directly reflects how long a message has been waiting without acknowledgment, making it the correct choice for monitoring message age.

Cloud Composer is the most appropriate service because it is a fully managed Apache Airflow service that provides workflow orchestration with DAGs, dependency management, retries, scheduling, and monitoring. It natively integrates with Dataproc and BigQuery operators, allowing you to define the entire pipeline as code. This matches the requirement for orchestrating a multi-step daily batch workflow.

Dataflow's update capability is the supported way to change a running streaming pipeline without stopping it. When the new graph keeps transform names consistent and the changes are compatible, the service swaps the graph while preserving streaming state and maintaining the sink's exactly-once guarantees. Stopping and restarting, running duplicate pipelines, or redeploying templates all either create a processing gap, risk data loss, or cause duplicate output.

Dataflow Flex Templates allow you to containerize a pipeline and provide runtime parameters, enabling reusability across different environments or jobs.

Cloud Data Loss Prevention (DLP) is Google Cloud's service for discovering, classifying, and de-identifying sensitive data such as credit card numbers, SSNs, and emails. It provides infoType detectors (including CREDIT_CARD_NUMBER) and de-identification transforms like masking, tokenization, and format-preserving encryption. It integrates directly with BigQuery for scanning tables and applying masking at query or storage time.

Airflow tasks in Cloud Composer support retries and retry_delay parameters, which allow automatic retries of failed tasks. This is the correct way to handle transient failures. Other options either do not address task-level retries or could cause data duplication.

The Storage Write API lowers ingestion cost and supports exactly-once semantics, addressing both cost and reliability. Enabling withAutoSharding lets the write step adapt shard count to actual throughput, preventing shard bottlenecks and avoiding over-provisioning. Together these changes reduce per-row cost and improve the robustness of the BigQuery write step without altering the pipeline's business logic.

The failure mode is a task that reports success while producing an empty result set, so the safeguard must inspect the data itself and raise a task-level failure. A BigQueryCheckOperator performs exactly that assertion and integrates with Airflow's dependency graph, which prevents downstream tasks from consuming invalid output and triggers configured alerting.

The metric subscription/oldest_unacked_message_age directly reports the age of the oldest unacknowledged message in a subscription, which is exactly what's needed to alert when messages are older than 1 hour. The filter subscription_id scopes the metric to the specific subscription. This metric is designed for monitoring message backlog age and is the correct choice for this requirement.

Preemptible (spot) workers in Dataproc can be reclaimed by Compute Engine at any time, and when they are preempted mid-task, the task must be retried on remaining workers. Frequent preemption causes repeated task retries, stragglers, and overall job slowdown even though the cluster appears highly utilized. This is the classic symptom of relying heavily on preemptible workers.

The requirement is to begin processing only when a specific object appears, which is precisely what a Cloud Storage sensor provides. Sensors defer downstream tasks until their condition is met, so pairing a GCSObjectExistenceSensor with the load task converts the DAG from a blind schedule into a data-arrival trigger while keeping the existing load logic intact.

Cloud Scheduler plus Cloud Functions delivers a fully managed, serverless combination for a recurring ETL task. Cloud Scheduler handles the cron cadence and can target a function over HTTP or Pub/Sub, while Cloud Functions executes the Python code with automatic retries, integrated logging, and the ability to publish notifications. No cluster or VM is required, satisfying the operational constraints.

The Dataflow update feature is designed to replace a running pipeline with a new version while preserving state, enabling seamless updates with minimal downtime and no data loss. Stopping and restarting, creating new subscriptions, or using external triggers do not maintain pipeline state and risk data duplication or loss.

Cloud Composer (Apache Airflow) natively supports task-level retries via the 'retries' parameter in default_args. When a DataflowPythonOperator fails due to a transient error, Airflow automatically re-executes the task up to the specified number of retries, without requiring custom sensors or external triggers. This is the simplest and most reliable mechanism for handling transient failures in a DAG-driven pipeline.

The bq query --dry_run flag validates a query and returns the estimated bytes that would be processed without actually executing it or incurring charges. This is the standard method to estimate on-demand BigQuery costs before running a query, since on-demand pricing is based on bytes processed.

A BigQuery table snapshot creates a lightweight, read-only copy of a table at a specific point in time without duplicating the underlying data, and it is the correct feature for providing a consistent point-in-time view cheaply. Snapshots preserve the table state at creation and can be queried or restored, making them ideal for this requirement.

Using the task's execution_timeout parameter allows Airflow to enforce a time limit on the task. If the task exceeds this limit, it fails and can be automatically retried based on the retries and retry_delay settings. This requires only a small change to the DAG's task definition and leverages native Airflow features, effectively addressing both detection of delays and automatic retries.

Cloud DLP (Data Loss Prevention) is the correct service because it is specifically designed to inspect, classify, and de-identify sensitive data such as credit card numbers and email addresses. It provides built-in infoType detectors for over 150 types of sensitive data and supports masking, tokenization, and other de-identification techniques. The engineer can use Cloud DLP to scan BigQuery tables and then apply transformations to mask the sensitive columns before sharing.

The backlog indicates that subscribers cannot keep up with the message flow. Increasing the number of subscribers or scaling their throughput capacity directly addresses the processing bottleneck, allowing messages to be pulled and acknowledged faster. Cloud Pub/Sub scales horizontally, so adding more pull subscribers or increasing the resources of existing ones (e.g., more worker threads, higher CPU/memory) reduces the backlog.

BigQuery partitioned tables allow partition pruning, where the query engine skips partitions that do not match the filter condition. Adding a WHERE clause on the partitioning column (date) restricts the scan to only the last 7 days' partitions, drastically reducing bytes scanned. This is the most direct and effective optimization for the described scenario.

System lag in Dataflow measures the age of the oldest unprocessed message, and a steadily increasing value indicates the pipeline cannot keep up with input. The most common cause is CPU-bound workers, so scaling out workers or tuning autoscaling (e.g., max workers, worker type) restores throughput. This directly addresses the bottleneck rather than a downstream symptom.

DataprocSubmitJobOperator returns a job reference that Airflow pushes to XCom, which makes the job ID available for downstream tasks and visible in the Airflow UI. Configuring retries specifically on the dbt task ensures that a transient failure there triggers only that task to rerun, leaving the already-successful Dataproc submission intact. This combination precisely meets both the observability and granular retry goals.

Preemptible instances are short-lived, lower-cost VMs that Cloud Dataproc can use for worker nodes. Because the training jobs are non-critical and fault-tolerant (e.g., they can handle node failures via Spark's built-in resilience), preemptible instances significantly reduce costs while still completing the workload. This directly addresses the requirement to reduce costs for fault-tolerant jobs.

Cloud Composer is a fully managed workflow orchestration service built on Apache Airflow. It is designed to orchestrate complex data pipelines with dependencies, scheduling, monitoring, and retries, making it the correct choice for orchestrating extraction, transformation, and loading tasks across Google Cloud services.

execution_timeout is the task-level setting that bounds wall-clock runtime and triggers a failure, while retries and retry_delay make the scheduler automatically re-queue the task. Together they produce fast failure, automatic retry, and a recorded state that alerting can observe, which is precisely what the scenario requires for a slow external API.

Cloud DLP can inspect BigQuery tables and de-identify using transforms like masking. Scheduling can be done via Cloud Scheduler.

Worker saturation caused by long-blocking sensors and job-waiting tasks is best relieved by changing how those tasks consume resources rather than by adding concurrency limits or rescheduling. Deferrable sensors release slots during waits, and decoupling long Dataflow job monitoring from the worker keeps the pool available for other tasks, which lowers both stalls and cost.

Decomposing a monolithic task into discrete operators is the standard Airflow pattern for granular retries and clearer observability. Each task maintains its own state, so a failure in the load step can be retried without rerunning ingestion or transformation. Purpose-built operators emit structured logs and status, and task-level retry configuration gives precise control over failure handling.

XComs (cross-communications) are Airflow's built-in mechanism for passing small pieces of data between tasks. A task can push a value via xcom_push() or by returning it, and downstream tasks pull it via xcom_pull(), enabling dynamic decisions such as choosing a branch based on a prior task's output. This is exactly what conditional branching in a Composer DAG requires.

Dataflow update replaces a running streaming pipeline in place while preserving state, provided the transform changes are compatible with the update compatibility rules. This avoids downtime and keeps windowed state intact. Draining, snapshot-based restart, and cancel-redeploy all interrupt processing or discard state, making them unsuitable for stateful, zero-downtime updates.