Maintaining and Automating Data Workloads practice questions

Pub/Sub subscription/num_undelivered_messages and Dataflow job/watermark_lag

Why it fails: num_undelivered_messages is a Pub/Sub metric; watermark_lag is similar to system lag but not standard. Better metrics exist.

Dataproc cluster/yarn_allocated_memory_percentage and Dataflow job/worker_cpu

Why it fails: Dataproc metrics are irrelevant for Dataflow.

BigQuery query/execution_times and Dataflow job/elapsed_time

Why it fails: BigQuery metrics irrelevant. elapsed_time is total job duration, not lag.

BigQuery table snapshots

Why it fails: Snapshots are immutable; if the source changes, the snapshot remains static. For ongoing sharing, clones are better.

BigQuery authorized views

Why it fails: Authorized views allow querying the latest data but do not provide a point-in-time snapshot.

BigQuery export to Cloud Storage

Why it fails: Exporting incurs costs and creates a separate copy, not a zero-copy clone.

Use committed use discounts for all VMs

Why it fails: Committed use discounts require 1-year commitment; preemptible VMs are cheaper without commitment.

Enable Dataproc auto-scaling

Why it fails: Auto-scaling helps but does not reduce cost as much as using preemptible VMs.

Use preemptible VMs for all nodes including master

Why it fails: Master node should not be preemptible to avoid cluster instability.

A >> B; B >> D; A >> C; C >> D

Why it fails: This is equivalent but less idiomatic; still works but not the best practice.

A >> B >> C >> D

Why it fails: This runs sequentially: A then B then C then D.

A.set_downstream(B); B.set_upstream(C); C.set_downstream(D)

Why it fails: B.set_upstream(C) makes C run before B, which is incorrect.

Use bq show to view job statistics

Why it fails: bq show returns dataset, table or job metadata such as configuration and status, not the per-stage timing breakdown needed to find the costliest stages. It is tempting because it does list job details, but the execution timeline with slot consumption per stage comes from the query plan in the job's information schema or the query execution graph.

Use Cloud Monitoring to view query execution graphs

Why it fails: Cloud Monitoring surfaces infrastructure and slot metrics, not per-stage query execution breakdowns, so it cannot pinpoint the costliest stages. It is tempting because it visualises performance dashboards, but it is the right tool for resource utilisation alerting rather than inspecting a single query's execution plan.

Use EXPLAIN statement in BigQuery

Why it fails: BigQuery does not support the EXPLAIN statement. Query plan information is obtained through job statistics.

Use ALTER TABLE RENAME COLUMN and then add new column

Why it fails: Renaming does not change mode.

Use bq update --schema to change the mode

Why it fails: bq update does not support changing column mode directly.

Use ALTER TABLE ALTER COLUMN SET DATA TYPE to change to NULLABLE

Why it fails: ALTER COLUMN only allows relaxing a column from REQUIRED to NULLABLE if using DDL with SET NOT NULL? Actually BigQuery only allows relaxing REQUIRED to NULLABLE via SQL using a query or manually recreating the table.

Use on-demand pricing for bursting

Why it fails: On-demand pricing is not part of flat-rate; flex slots are for bursting.

Set a maximum number of slots per query

Why it fails: This limits query resources but does not provide bursting capability.

Create a reservation with baseline + autoscaling slots

Why it fails: Autoscaling in reservations is for on-demand, not for flat-rate with committed use.

Operators (e.g., PythonOperator)

Why it fails: Operators perform actions; sensors are a type of operator but specifically for waiting.

SubDAGs for grouping tasks

Why it fails: SubDAGs are optional and not essential for this pattern.

Stop the pipeline, update the template, and restart with the same job name.

Why it fails: Stopping causes data loss unless using drain; restarting loses checkpointed state. The requirement is to update without draining.

Modify the original template and redeploy it as a new job with the same pipeline name.

Why it fails: Redeploying as a new job creates a separate pipeline; the original continues running. You cannot update a running job by launching a new one.

Use the gcloud dataflow jobs drain command, then restart with the new template.

Why it fails: Draining stops the pipeline gracefully but violates the requirement of updating without draining.

BigQuery write throughput is throttling the pipeline.

Why it fails: BigQuery write throttling would stall the sink and back up the pipeline, but system lag measures unprocessed input in Pub/Sub, which points to insufficient worker throughput or a bottleneck upstream of the write. Throttling is tempting because slow sinks do cause lag, yet the steady watermark indicates processing, not writing, is the constraint.

The Pub/Sub subscription has too many unacknowledged messages.

Why it fails: Unacknowledged messages reflect subscriber backlog, not pipeline throughput; Dataflow autoscaling and the Pub/Sub Lite/Streaming Engine absorb this, so system_lag would not climb steadily. This metric matters when diagnosing subscription-level delivery problems, such as a stuck subscriber or expired ack deadline, rather than a pipeline that cannot keep pace with input.

The pipeline is using a global window with late data handling.

Why it fails: Global windows with late-data handling affect watermark advancement and trigger firings, not the system_lag metric, which measures the gap between the pipeline's processing time and the input's event time. Late-data handling is the right design when correctness under out-of-order events matters more than latency, but it does not explain steadily rising lag.

Create a BigQuery table clone at the desired point in time and share it with the partner.

Why it fails: Incorrect. Table clones are writable and intended for development/testing, not for read-only sharing. They also do not provide a stable point-in-time snapshot if the clone is modified later.

Export the table to Cloud Storage as Avro and share a signed URL.

Why it fails: Incorrect. Exporting to Avro in Cloud Storage incurs export costs and storage costs. Signed URLs have expiration and security considerations, and the partner would still need to load the data, incurring additional costs.

Grant the partner access to the original table with an authorized view.

Why it fails: Incorrect. Granting access via an authorized view allows querying the original table but does not create a point-in-time snapshot. The partner's queries would incur costs on the original table, and the data could change over time.

Enable autoscaling on the reservation to automatically add slots up to a maximum.

Why it fails: Autoscaling is a feature for on-demand pricing, not flat-rate. Flat-rate reservations have a fixed number of slots.

Purchase committed use discounts to increase the base reservation to 3000 slots.

Why it fails: Committed use discounts require a 1- or 3-year commitment, which is unnecessary for handling occasional bursts.

Change the pricing model to on-demand to allow unlimited slots.

Why it fails: Switching to on-demand changes the cost model and may lead to unpredictable costs. It also does not use the existing flat-rate reservation.

Use a Dataflow side input that reads the latest schema from a file and updates the BigQuery schema accordingly.

Why it fails: This approach can work but is complex and requires custom logic to update BigQuery schema. It is not a built-in feature and adds latency.

Define a UDF in Dataflow that dynamically adjusts the output schema.

Why it fails: Dataflow does not support dynamic schema changes per element; the output schema is fixed at pipeline construction time.

Configure the Dataflow pipeline to use BigQuery's schema autodetect option for each insert.

Why it fails: BigQuery streaming inserts do not support schema autodetect; the schema must be predefined. Autodetect is for batch load jobs.

Use SSD persistent disks for all nodes to improve I/O performance.

Why it fails: SSDs improve I/O but do not increase memory. OOM is a memory issue, not I/O.

Decrease the number of worker nodes and increase the size of each worker.

Why it fails: This may increase cost if using larger machines, and decreasing parallelism might slow down jobs. It does not guarantee OOM resolution.

Switch to a high-memory machine type for the master node only.

Why it fails: OOM errors occur on workers, not the master. Increasing master memory does not resolve worker memory issues.

Use Cloud DLP to inspect the table for data quality issues

Why it fails: Wrong: Cloud DLP is for sensitive data inspection, not data quality scans.

Create a Dataplex asset and add a tag template to enforce constraints

Why it fails: Wrong: Tag templates are for metadata, not data quality enforcement.

Create a row rule for the 'email' column using SQL condition 'email IS NOT NULL'

Why it fails: Wrong: While a row rule could check for non-null, the same check can be done with a column rule, so this is an extra feature not required.

Use a 'for' loop to iterate over retries.

Why it fails: Workflows does not have a 'for' loop; retries are handled via a declarative 'retry' block, not iterative loops.

Use a 'try' / 'catch' block to handle exceptions and route to a failure step.

Why it fails: Workflows does not have try/catch; instead, you use 'on_error' or 'retry' within steps.

Enable autoscaling slot management in the primary project's reservation, allowing slots to scale up based on demand.

Why it fails: Incorrect. Autoscaling can be used with committed use reservations to scale slots up to a maximum, but it still requires base committed slots. It does not provide purely temporary capacity without commitment, unlike flex slots.

Upgrade the primary project's edition to Enterprise Plus to allow bursting.

Why it fails: Incorrect. Upgrading to Enterprise Plus changes pricing and features but does not directly provide burst capacity without additional spend.

Purchase additional committed use slots in the primary project and apply them to the reservation.

Why it fails: Incorrect. Purchasing additional committed use slots increases committed spend, which is what the company wants to avoid.

Change the BigQueryInsertJobOperator to use deferrable mode to make it async.

Why it fails: Deferrable mode is not needed; the operator is already synchronous.

Use a PythonOperator to call the BigQuery API directly and wait for the job to finish.

Why it fails: Not necessary; the existing operator is synchronous. The issue is about data readiness.

Increase the timeout of the BigQueryCheckOperator.

Why it fails: Timeout does not address the root cause; check might still run before data is ready.

Table clone

Why it fails: Clones are writable; if the team only needs read access, a snapshot is better.

Authorized views

Why it fails: Views do not create copies; they query the live table, so the other team's queries would scan the full table.

Time travel using FOR SYSTEM_TIME AS OF

Why it fails: Time travel is limited to 7 days and requires the base table; it's not a persistent copy.

Use Dataprep to clean and standardise the data before loading into BigQuery.

Why it fails: Dataprep is for data preparation, not for handling schema evolution in a streaming pipeline.

Predefine the BigQuery table schema with all possible fields and use UPDATE to add missing fields.

Why it fails: Not feasible for unknown fields; update would require altering schema.

Use a JavaScript UDF to parse incoming data and map to a fixed schema, ignoring new fields.

Why it fails: Ignoring new fields may cause data loss; requirement is to accept new fields.