PDE Maintaining and Automating Data Workloads — Timed 40 Questions

Question 1

A company uses Dataflow streaming pipelines to process real-time events. They notice increasing system lag over time. Which two Cloud Monitoring metrics should be examined to diagnose the cause?

Accepted Answer

Dataflow job/system_lag and Dataflow job/data_freshness. System lag measures the time between event ingestion and processing. Data freshness shows the watermark. Worker CPU indicates compute resource issues.

Answer

Pub/Sub subscription/num_undelivered_messages and Dataflow job/watermark_lag

Answer

Dataproc cluster/yarn_allocated_memory_percentage and Dataflow job/worker_cpu

Answer

BigQuery query/execution_times and Dataflow job/elapsed_time

Question 2

A data team needs to share a BigQuery dataset with another business unit. They want to provide a point-in-time snapshot of the data without incurring additional storage costs for the copy. Which BigQuery feature should they use?

Accepted Answer

BigQuery table clones. Clones use the same underlying storage as the source table; snapshots also share storage but are immutable. Both are cost-effective. For regular updates, clones are more flexible.

Answer

BigQuery table snapshots

Answer

BigQuery authorized views

Answer

BigQuery export to Cloud Storage

Question 3

A company runs a Dataproc cluster for ETL jobs that process data nightly. They want to reduce costs while maintaining performance. Which strategy is MOST effective?

Accepted Answer

Use preemptible VMs for worker nodes only. Using preemptible VMs for worker nodes only is the most effective cost-reduction strategy while maintaining performance because Dataproc workers handle the distributed processing and can tolerate interruptions, whereas the master node must remain stable to manage the cluster. Preemptible VMs cost up to 80% less than standard VMs, and Dataproc is designed to gracefully handle worker preemption by re-running tasks.

Answer

Use committed use discounts for all VMs

Answer

Enable Dataproc auto-scaling

Answer

Use preemptible VMs for all nodes including master

Question 4

A company uses Cloud Composer for pipeline orchestration. They need to define task dependencies where Task B and Task C can run in parallel after Task A, and Task D must run after both B and C complete. How should they define the DAG?

Accepted Answer

A >> [B, C] >> D. The correct DAG definition is A >> [B, C] >> D, which uses Airflow's list syntax to indicate that B and C are downstream of A and run in parallel, and D is downstream of both B and C. This succinctly expresses the required dependencies: A must complete before B and C, and D must wait for both B and C to finish.

Answer

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

Answer

A >> B >> C >> D

Answer

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

Question 5

A data engineer notices that BigQuery queries are slower than expected. They want to identify the most expensive stages in the query execution. Which tool or command should they use?

Accepted Answer

Use bq query --format=prettyjson and look at statistics. Running bq query with --format=prettyjson returns the full job resource JSON, including the statistics field that contains query plan stages, timing, and slot usage. This is the standard way to inspect which stages are most expensive.

Answer

Use bq show to view job statistics

Answer

Use Cloud Monitoring to view query execution graphs

Answer

Use EXPLAIN statement in BigQuery

Question 6

A data engineer needs to migrate a schema from BigQuery where a column is currently REQUIRED and needs to become NULLABLE. Which TWO statements are correct? (Choose 2)

Accepted Answer

Drop the column and add it again as NULLABLE. In BigQuery, relaxing a column from REQUIRED to NULLABLE is not supported as an in-place schema change, so option B is correct: dropping the column and re-adding it as NULLABLE (e.g., via ALTER TABLE DROP COLUMN followed by ALTER TABLE ADD COLUMN ... NULLABLE) produces the desired mode, though existing data in that column is lost unless repopulated. Option D is also correct because creating a new table with the target schema (column defined as NULLABLE) and using a query such as CREATE TABLE ... AS SELECT or INSERT INTO ... SELECT to populate it is the standard, safe migration pattern that preserves data while changing the mode. Option A is wrong because ALTER TABLE RENAME COLUMN only renames a column and does not change its mode, and adding a new column alone does not convert the original REQUIRED column. Option C is wrong because bq update --schema is used for adding columns or relaxing NOT NULL constraints in some contexts but does not reliably change a REQUIRED column to NULLABLE in BigQuery's current schema-update semantics. Option E is wrong because ALTER TABLE ALTER COLUMN SET DATA TYPE changes the data type, not the column mode, and BigQuery does not support changing REQUIRED to NULLABLE through that statement.

Answer

Use ALTER TABLE RENAME COLUMN and then add new column

Answer

Use bq update --schema to change the mode

Answer

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

Question 7

A company runs BigQuery workloads with varying demand. They want to use flat-rate pricing with baseline slots and the ability to burst during peak times. Which TWO actions should they take? (Choose 2)

Accepted Answer

Use flex slots for short-term bursts. Option D is correct because committed use reservations (a 1- or 3-year commitment) provide the discounted baseline slot capacity that a flat-rate customer needs for steady, predictable demand. Option B is correct because flex slots are a short-term, pay-as-you-go commitment (as little as 60 seconds, sold in 500-slot increments) designed exactly for handling temporary peaks beyond the baseline. Option A is wrong because on-demand pricing is a separate billing model that cannot be mixed with flat-rate reservations for bursting. Option C is wrong because capping slots per query is a workload-governance control, not a mechanism for adding burst capacity. Option E is wrong because autoscaling reservations automatically add and remove slots based on demand, which is a different capability than the short-term flex-slot bursts the scenario asks for.

Answer

Use on-demand pricing for bursting

Answer

Set a maximum number of slots per query

Answer

Create a reservation with baseline + autoscaling slots

Question 8

A company uses Cloud Composer (Airflow) to orchestrate pipelines. They want to implement a pattern where a task polls for a file arrival in Cloud Storage and then triggers subsequent tasks. Which THREE Airflow concepts are essential? (Choose 3)

Accepted Answer

Sensors (e.g., GCSObjectExistenceSensor). A is correct because Sensors are a specialized type of operator designed to wait for a specific condition, such as file arrival in Cloud Storage. The `GCSObjectExistenceSensor` in Cloud Composer (Airflow) polls Google Cloud Storage at a configurable interval until the target file exists, making it the precise tool for this file-arrival polling pattern.

Answer

Operators (e.g., PythonOperator)

Answer

SubDAGs for grouping tasks

Question 9

A company runs a streaming Dataflow pipeline that reads from Pub/Sub, enriches data with a side input from BigQuery, and writes to BigQuery. After updating the pipeline code (adding a new field to the output), the engineer notices that the new pipeline version is not picking up the updated code because the job was started from a template. The engineer wants to update the streaming pipeline without draining it. What should the engineer do?

Accepted Answer

Use the gcloud dataflow jobs update command with the new Flex Template.. The `gcloud dataflow jobs update` command allows you to update a running streaming Dataflow pipeline with a new Flex Template without draining or stopping the job. This command performs an in-place update, preserving the job's state and checkpointing, so the pipeline continues processing with the new code. Since the original job was started from a template, using this command with the new Flex Template ensures the updated code is picked up seamlessly.

Answer

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

Answer

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

Answer

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

Question 10

You are monitoring a streaming Dataflow pipeline that reads from Pub/Sub and writes to BigQuery. In Cloud Monitoring, you notice that the 'system_lag' metric is increasing over time and now exceeds 10 minutes. The 'data_watermark' metric shows a steady lag. What is the most likely cause of the increasing system lag?

Accepted Answer

The Dataflow pipeline is underprovisioned with workers, causing processing backlog.. An underprovisioned pipeline lacks sufficient worker resources to process incoming messages at the rate they arrive. This causes a growing backlog in the pipeline's internal buffers, which directly increases the 'system_lag' metric (the time between data ingestion and processing). The 'data_watermark' lag remaining steady indicates that the pipeline is still making progress on event-time processing, but the overall processing capacity is insufficient to keep up with the input rate.

Answer

BigQuery write throughput is throttling the pipeline.

Answer

The Pub/Sub subscription has too many unacknowledged messages.

Answer

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

Question 11

A company wants to share a large BigQuery dataset with a partner for analysis. The partner needs read-only access to a specific snapshot of the data as of a certain point in time, and the company wants to avoid additional storage costs for the partner. What is the most cost-effective approach?

Accepted Answer

Create a BigQuery table snapshot at the desired point in time and share it.. A BigQuery table snapshot captures the table's data at a specific point in time and is stored as a lightweight, read-only reference that shares the base table's storage — you only pay for storage that diverges from the base table. Sharing the snapshot grants the partner read-only access to that frozen view of the data without duplicating storage costs, which is exactly the requirement.

Answer

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

Answer

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

Answer

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

Question 12

Your organization has a BigQuery flat-rate reservation with 2000 slots. During peak hours, query performance degrades because concurrent queries exceed the available slots. You want to handle these bursts without changing the base reservation. What should you do?

Accepted Answer

Purchase flex slots during peak hours to add capacity temporarily.. Flex slots allow you to temporarily add capacity to a BigQuery flat-rate reservation without committing to a permanent increase. This handles burst workloads during peak hours by adding slots on demand, and you only pay for the time they are used. The base reservation of 2000 slots remains unchanged, meeting the requirement.

Answer

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

Answer

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

Answer

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

Question 13

A data engineer is designing a Dataflow pipeline that reads from a Kafka topic (using Pub/Sub for Kafka) and writes to BigQuery. The data schema may change over time, with new fields appearing. The engineer wants to handle schema drift automatically without failing the pipeline. Which approach should the engineer use?

Accepted Answer

Store the entire record as a single JSON string column in BigQuery and parse it later.. Storing the entire record as a single JSON string column in BigQuery allows the pipeline to accept any schema changes without requiring schema modifications at write time. This approach decouples the ingestion from schema evolution, enabling the data to be parsed later using BigQuery's JSON functions (e.g., JSON_EXTRACT) or by loading into a separate schema-on-read layer. It avoids pipeline failures caused by mismatched fields or type changes.

Answer

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

Answer

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

Answer

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

Question 14

You are troubleshooting a Dataproc cluster that runs nightly Spark jobs. The jobs are failing with out-of-memory errors. You want to reduce costs while fixing the issue. Which combination of actions should you take? (Select the BEST answer.)

Accepted Answer

Increase the number of preemptible worker nodes and use standard machine types for the master.. Out-of-memory errors in Spark jobs typically stem from insufficient executor memory or too few workers to distribute the load. Increasing preemptible worker nodes adds cheap compute capacity to spread the workload, while keeping the master on a standard machine type ensures cluster stability (preemptible masters are not supported and would destabilize the cluster). This combination reduces cost while addressing the OOM root cause.

Answer

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

Answer

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

Answer

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

Question 15

A data engineer needs to set up a Dataplex data quality scan to run weekly on a BigQuery table. The scan should check that: (1) the 'email' column is not null, (2) the 'age' column is between 0 and 120, and (3) the 'country_code' column matches a list of valid ISO codes. Which TWO Dataplex features should the engineer use?

Accepted Answer

Schedule the data quality scan using Dataplex scheduling options. Option A is correct because Dataplex data quality scans support built-in scheduling (for example, a weekly cron-style schedule) so the scan runs automatically on the BigQuery table without external orchestration. Option C is correct because Dataplex data quality rules include column-level rules, and a SQL condition such as 'age BETWEEN 0 AND 120' is exactly the supported way to express a range check on the 'age' column. Option B is not appropriate because Cloud DLP is for discovering and classifying sensitive data (PII), not for enforcing not-null, range, or ISO-code validity rules. Option D is not appropriate because Dataplex tags and tag templates are metadata/classification constructs for governance, not constraint enforcement for data quality checks. Option E is not appropriate because 'email IS NOT NULL' is a column-level (not row-level) rule, and Dataplex expresses such checks as column rules rather than row rules.

Answer

Use Cloud DLP to inspect the table for data quality issues

Answer

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

Answer

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

Question 16

A data engineer is building a Cloud Workflows workflow that orchestrates multiple Cloud Functions and API calls. The workflow should handle transient failures with retries and send a notification to a Pub/Sub topic if the workflow ultimately fails. Which THREE steps should the engineer include in the workflow definition?

Accepted Answer

Use the 'googleapis.pubsub.v1.projects.topics.publish' connector to send a failure notification.. Option B is correct because Cloud Workflows provides built-in connectors such as 'googleapis.pubsub.v1.projects.topics.publish' that let the workflow publish a message to a Pub/Sub topic when the workflow ultimately fails, satisfying the notification requirement. Option C is correct because a 'try'/'catch' block is the Workflows construct for catching exceptions raised by steps and routing execution to a failure-handling step, which is exactly how the workflow detects ultimate failure before notifying Pub/Sub. Option E is correct because the 'retry' block (with max_retries and backoff parameters such as initial_delay and multiplier) is the native Workflows mechanism for handling transient failures on API call steps. Option A is not correct because a 'for' loop is a general iteration construct, not the retry mechanism, and manually looping does not provide the built-in backoff and retry semantics of the 'retry' block. Option D is not correct because a 'switch' step only performs conditional branching on expressions; it does not itself detect or handle exceptions, so it is not the mechanism for routing on failure.

Answer

Use a 'for' loop to iterate over retries.

Answer

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

Question 17

A company uses BigQuery flat-rate pricing with 500 slots purchased as a committed use discount. During peak hours, they need additional capacity but do not want to buy more committed slots. They have a secondary project used for ad-hoc queries by analysts. How can they provide burst capacity to the primary project during peak times without increasing committed spend?

Accepted Answer

Create flex slots in the secondary project, create a reservation in the secondary project, and assign the reservation to the primary project.. BigQuery flat-rate pricing allows purchasing committed slots. To provide burst capacity without increasing committed spend, you can use flex slots, which are a flexible, short-term commitment. You create flex slots in a secondary project, create a reservation there, and then assign that reservation to the primary project. This allows the primary project to use the additional slots during peak times.

Answer

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

Answer

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

Answer

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

Question 18

Your team uses Cloud Composer to run Apache Airflow DAGs. One DAG uses a BigQueryInsertJobOperator to run a query and then uses BigQueryCheckOperator to verify the results. The DAG is failing intermittently because the query result is not ready when the check operator runs. How should you modify the DAG to ensure the check operator runs only after the query completes successfully?

Accepted Answer

Add a BigQueryTableExistenceSensor before the BigQueryCheckOperator to wait for the table to be created.. The BigQueryInsertJobOperator is synchronous by default, so the DAG should already wait for completion. However, if the check operator fails due to data not being written, adding a sensor operator (e.g., BigQueryTableExistenceSensor) between the two tasks ensures the table exists before checking.

Answer

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

Answer

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

Answer

Increase the timeout of the BigQueryCheckOperator.

Question 19

A data engineer needs to share a large BigQuery table with a different team, but wants to minimize storage costs. The table is 1 TB in size and is updated daily. The other team only needs read access to the data as of a specific point in time (e.g., end of each day). Which BigQuery feature should be used to provide a read-only copy without duplicating the entire table?

Accepted Answer

Table snapshot. BigQuery table clones provide a lightweight, writable copy of a table that initially shares storage with the base table and only incurs costs for changes made to the clone. Snapshots are read-only and also share storage but require the base table to be preserved until snapshot expires. For read-only point-in-time access, a snapshot is more appropriate because it is immutable and cost-effective.

Answer

Table clone

Answer

Authorized views

Answer

Time travel using FOR SYSTEM_TIME AS OF

Question 20

You are designing a Dataflow pipeline that reads from Pub/Sub, performs transformations, and writes to BigQuery. The pipeline must handle schema changes in the incoming data (e.g., new fields appearing). The BigQuery schema should evolve automatically to accept new fields without failing. Which approach should you use?

Accepted Answer

Set the table schema to allow unknown fields and use BigQuery's schema auto-update feature in the pipeline.. Using BigQuery's schema auto-detection combined with specifying the write disposition as WRITE_APPEND and allowing unknown fields can handle schema drift. However, the most robust approach is to use a flexible schema in the pipeline and set BigQuery's schema update options to allow automatic addition of nullable fields.

Answer

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

Answer

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

Answer

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