PDE Practice Test 48 — 15 Questions

Question 1

A media company stores 50 TB of video metadata in Cloud Storage buckets in the Standard class. Legal requires that records for a subset of titles be retained for exactly seven years and that they cannot be altered or deleted by any user, including project owners, during that period. The company also wants to minimize storage cost for a second bucket holding derived thumbnails that are accessed only a few times per year but must be retrievable within seconds. Which two configurations should the data engineer implement? (Choose two.)

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create a bucket-level retention policy with a seven-year retention period and lock it.. A locked bucket retention policy delivers the immutability legal demands because it cannot be removed once locked and blocks deletion or overwrite by any principal for the full seven years. For the thumbnails, Nearline matches an access pattern of a few reads per year with second-level retrieval and a lower price than Standard, avoiding the longer minimum-duration and slower-access profile that Coldline or Archive would impose.

Answer

Enable Object Versioning on the metadata bucket to preserve prior versions of each object.

Answer

Set an Object Lifecycle Management rule to move the metadata objects to Archive storage after one year.

Answer

Create the thumbnails bucket in the Coldline storage class and enable Turbo Replication.

Question 2

You are building a Dataflow pipeline that reads from Cloud Storage and writes to BigQuery. The pipeline must handle files that are compressed with gzip and contain JSON data. You need to ensure that the pipeline can process these files efficiently and write to BigQuery with minimal errors. Which approach should you take?

Accepted Answer

Use TextIO to read the gzip files, parse the JSON using a DoFn, and write to BigQuery using BigQueryIO with the STORAGE_WRITE_API method.. TextIO natively supports reading gzip-compressed files, simplifying ingestion. Parsing JSON in a DoFn allows transformation into BigQuery-compatible rows. Using BigQueryIO with the STORAGE_WRITE_API method ensures high-throughput, exactly-once writes, which is critical for efficient and reliable loading. This combination addresses the compressed format and minimizes errors through robust write semantics.

Answer

Use TextIO to read the gzip files, but disable compression detection and manually decompress each file in a DoFn before parsing JSON.

Answer

Use AvroIO to read the gzip files, convert the Avro records to JSON, and write to BigQuery using BigQueryIO with the FILE_LOADS method.

Answer

Use BigQueryIO to read the gzip files directly, parse the JSON, and write to BigQuery using the default write method.

Question 3

Your organization runs a batch Dataflow pipeline that reads from BigQuery, transforms records, and writes Parquet files to Cloud Storage partitioned by event date. The pipeline currently writes all files into a single directory and downstream Hive-style queries scan the entire dataset. You need to restructure the output so that queries scan only the relevant date partitions, while keeping the pipeline idempotent on re-runs. What should you do?

Accepted Answer

Use FileIO.writeDynamic with a destination function that maps each record to a path like gs://bucket/events/event_date=YYYY-MM-DD/, and write to a temporary location before atomically moving files into place.. Hive-style partition pruning depends on directory structure, not file naming. FileIO.writeDynamic with a destination function that emits paths containing event_date=YYYY-MM-DD creates the directories that query engines use to skip irrelevant data. Writing first to a temporary location and then moving completed files into the final partition directory prevents partially written partitions from being visible if the pipeline fails and is re-run, preserving idempotency.

Answer

Use AvroIO with a custom filename policy that includes the event date, and enable the pipeline's --diskSizeGb option to improve write throughput.

Answer

Write all files to a single directory and create a BigQuery external table over the bucket, then rely on BigQuery's automatic partition pruning from the file names.

Answer

Add a GroupByKey transform keyed by event date before writing, then use TextIO to write one file per date into a flat directory with the date embedded in the file name.

Question 4

A data engineer needs to ingest data from a Cloud Storage bucket into BigQuery. The data is in CSV format and is updated daily with new files. The engineer wants to minimize manual intervention and ensure that new files are automatically loaded into BigQuery. Which Google Cloud service should be used to orchestrate this?

Accepted Answer

Cloud Functions. Cloud Functions provides an event-driven, serverless way to respond to new files in Cloud Storage. By configuring a Cloud Function to trigger on object finalize events, the function can execute a BigQuery load job for each new CSV file. This approach requires no polling, minimal code, and automatically scales. Other services like Cloud Scheduler or Cloud Composer could work but involve more overhead and are less direct for this specific requirement.

Answer

Cloud Scheduler

Answer

Cloud Data Fusion

Answer

Cloud Composer

Question 5

A retail analytics team runs a Cloud Composer DAG that starts a Dataproc job, waits for it, and then runs a BigQuery load. The Dataproc job sometimes fails due to a transient YARN resource shortage, and the team wants the DAG to automatically retry the Dataproc submission a few times before alerting. Which Airflow configuration on the Dataproc task best meets this need?

Accepted Answer

Set retries to 3 and retry_delay to 5 minutes on the Dataproc job task.. 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.

Answer

Set the Dataproc operator's cluster_name parameter to a different cluster and rely on Dataproc to resubmit the job.

Answer

Add a downstream EmailOperator that notifies the team on failure and manually reruns the DAG.

Answer

Set max_active_runs to 3 on the DAG so multiple DAG runs can absorb the failure.

Question 6

A data engineering team needs to run SQL analytics on a large BigQuery dataset from a Looker Studio dashboard. The dashboard must return results quickly, and the underlying data changes only once per day through a batch load. The team wants to minimize query cost and latency for repeated dashboard queries. What should they do?

Accepted Answer

Create a materialized view that aggregates the dataset and configure it to refresh on a daily schedule.. Materialized views precompute and store query results, and with a daily refresh schedule they match the batch update cadence. Repeated dashboard queries hit the stored results, cutting bytes scanned and latency. This directly targets the cost and performance goals, unlike caching layers or partitioning that do not precompute aggregations.

Answer

Enable the BigQuery BI Engine reservation and rely on it to cache all dashboard queries automatically.

Answer

Export the dataset to Cloud Storage as CSV and have Looker Studio query the files directly.

Answer

Partition the table by ingestion date and require all dashboard queries to filter on that column.

Question 7

A startup runs an operational analytics dashboard on a Cloud SQL for MySQL instance. The dashboard issues many short read queries that are slowing down the write-heavy transactional workload on the primary instance. The team wants to offload reads without changing application write logic and without re-architecting to a different database engine. What should the data engineer configure?

Accepted Answer

Create one or more read replicas of the Cloud SQL instance and direct the dashboard's read queries to the replica connection.. Read replicas are the intended Cloud SQL feature for scaling read traffic: the dashboard connects to a replica endpoint, the transactional application keeps writing to the primary, and no write-path code changes are required. A high-availability standby is passive and cannot serve reads, vertical scaling keeps both workloads on the same instance, and federated BigQuery queries still hit the source database. The replica approach isolates the dashboard's short read queries from the write-heavy workload.

Answer

Enable high availability on the primary instance so a standby instance can absorb the dashboard's read queries.

Answer

Migrate the dashboard to query a BigQuery federated external table that reads directly from the Cloud SQL instance.

Answer

Increase the number of vCPUs and memory on the primary instance to handle the combined read and write load.

Question 8

A data engineering team manages BigQuery datasets across multiple projects. They want to automatically detect and respond when a scheduled query fails, and they want the response to create an incident in their existing ticketing system. The team prefers minimal custom infrastructure and wants to use native Google Cloud tooling. Which approach should they use?

Accepted Answer

Enable BigQuery audit logs, create a log-based alerting policy in Cloud Monitoring, and route the alert to a Pub/Sub topic that triggers a Cloud Function to open the ticket.. 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.

Answer

Schedule a Cloud Scheduler job that polls the BigQuery INFORMATION_SCHEMA.JOBS view and emails the team when errors are found.

Answer

Configure BigQuery to send failure notifications to a Cloud Storage bucket and use a Dataproc job to parse them.

Answer

Use the BigQuery REST API from a long-running Compute Engine VM to poll for failed jobs and call the ticketing API.

Question 9

A media company streams playback telemetry through Pub/Sub into a Dataflow pipeline that writes to BigQuery. During prime-time peaks, the pipeline's BigQuery write step shows growing latency and the job repeatedly reports that it is backing off on insert retries. The team wants to reduce write pressure without changing the downstream table schema or losing exactly-once semantics. What should they do?

Accepted Answer

Switch the sink to the BigQuery Storage Write API with the exactly-once stream type and group records into batches before writing.. Backoff on insert retries signals that the write path is saturating BigQuery's streaming ingestion limits. The Storage Write API's exactly-once stream type preserves exactly-once semantics through offset-based deduplication, and batching rows into larger appends cuts request volume and quota pressure. This keeps the existing table schema and the streaming model intact while removing the write bottleneck that was throttling the pipeline during peak load.

Answer

Enable Dataflow Shuffle and set the pipeline's disk size larger so the write stage can buffer more rows before retrying.

Answer

Change the pipeline to use a BigQuery load job triggered every 15 minutes by writing micro-batches to Cloud Storage first.

Answer

Increase the number of BigQuery Streaming Inserts API calls by adding more Dataflow workers so each record is inserted individually.

Question 10

A retail analytics team stores transaction data in a BigQuery table partitioned by day on a DATE column named transaction_date. Analysts repeatedly run queries that filter on transaction_date and also on a high-cardinality STRING column named store_id. These queries scan far more data than expected, and slot usage is high. The team wants to reduce bytes processed without changing query results. What should they do?

Accepted Answer

Create a clustered table on store_id, so BigQuery can prune blocks within each transaction_date partition.. Clustering a partitioned table on the frequently filtered high-cardinality column lets BigQuery prune storage blocks within each partition. Partition pruning on transaction_date still happens, and cluster pruning further limits scanned data. This directly reduces bytes processed and slot consumption for the described queries without altering results, making it the appropriate storage optimization.

Answer

Create a materialized view that precomputes all transactions grouped by store_id and transaction_date.

Answer

Export the table to Cloud Storage as Avro and query it with an external table that applies store_id predicates.

Answer

Change the table to require a partition filter, forcing analysts to always specify transaction_date.

Question 11

You need to create a Dataflow pipeline that reads from Pub/Sub and writes to BigQuery. The pipeline must handle malformed messages by writing them to a dead-letter table in BigQuery. Which two Apache Beam transforms or patterns should you use to achieve this? (Choose two.)

Accepted Answer

Use a ParDo transform with a side output for failed messages.. To handle malformed messages, you need to detect them and route them separately. A ParDo transform with a side output allows you to process each message and emit malformed ones to a secondary output. Then, you can write that side output to a BigQuery dead-letter table. The combination of a ParDo with side output and a separate BigQueryIO.Write for that output achieves the required dead-letter pattern.

Answer

Write all messages to a BigQuery table and use a view to filter out malformed records.

Answer

Use a ParDo transform that throws an exception for malformed messages, and configure a dead-letter queue in Dataflow.

Answer

Use a Filter transform to separate valid and invalid messages.

Question 12

A retail company uploads daily sales CSV files to a Cloud Storage bucket. The analytics team wants BigQuery to automatically detect the schema and make new files queryable without running load jobs. The files are added with date-based prefixes, and the team wants to minimize operational overhead. Which BigQuery feature should they use?

Accepted Answer

A BigQuery external table with schema autodetect over the Cloud Storage prefix.. An external table over the Cloud Storage prefix with schema autodetect lets BigQuery read the CSV files directly. New files that match the prefix appear in query results without any load jobs, which precisely fits the requirement to minimize operational overhead while keeping daily files queryable.

Answer

A Cloud Dataflow streaming pipeline that writes rows into BigQuery.

Answer

A BigQuery native table loaded with a recurring scheduled query.

Answer

A BigQuery table partitioned by ingestion time with a load job per file.

Question 13

A financial services firm is designing a Dataflow pipeline that reads from Pub/Sub and writes enriched transactions to BigQuery. The pipeline must guarantee exactly-once processing semantics for the BigQuery sink, even during pipeline updates and worker restarts. The team plans to use the Apache Beam Java SDK with the BigQueryIO connector. Which combination of configurations should they use?

Accepted Answer

Use BigQueryIO.write() with withMethod(STREAMING_INSERTS) and provide a deterministic insertId derived from the transaction ID. Set withFailedInsertRetryPolicy to RETRY_ALWAYS.. Exactly-once semantics for BigQuery writes in Dataflow require deduplication on the BigQuery side. With STREAMING_INSERTS, BigQuery can use the insertId to discard duplicate rows within a short time window. A deterministic insertId derived from the transaction ID ensures that retries after worker restarts or pipeline updates do not create duplicates. RETRY_ALWAYS preserves data by retrying transient errors, while deduplication prevents duplicates.

Answer

Use BigQueryIO.write() with withMethod(STREAMING_INSERTS) and set withFailedInsertRetryPolicy to RETRY_NEVER. This ensures that failed inserts are not retried, preventing duplicates.

Answer

Use BigQueryIO.write() with withMethod(FILE_LOADS), withTriggeringFrequency set to a fixed interval, and enable withAutoSharding. Rely on the default insertion semantics.

Answer

Use BigQueryIO.write() with withMethod(FILE_LOADS) and set withWriteDisposition(WRITE_TRUNCATE) for each window. This ensures that each window's data replaces the previous contents of the table.

Question 14

A data engineer needs to run a recurring SQL transformation in BigQuery every night at 02:00 and, if it fails, retry automatically and send a notification. The team wants the least operational overhead and no external orchestrator. What should they use?

Accepted Answer

A BigQuery scheduled query configured with a schedule, a destination table, and notification settings.. 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.

Answer

A Cloud Composer DAG that runs a BigQueryInsertJobOperator on a cron schedule.

Answer

A Cloud Scheduler job that calls the BigQuery jobs.insert API with a service account.

Answer

A Dataflow batch pipeline that reads the source table and writes the transformed result.

Question 15

A healthcare analytics team needs to run a series of SQL transformations on data stored in BigQuery. The transformations must run on a schedule, and the team wants to minimize operational overhead by using a fully managed service that integrates with BigQuery and Cloud Logging. They also need to parameterize the SQL queries with runtime values such as the current date. Which Google Cloud service should they use?

Accepted Answer

BigQuery scheduled queries, using the @run_date parameter for runtime values.. BigQuery scheduled queries are a native, fully managed feature that executes SQL on a defined schedule. They support parameterization with system variables like @run_date, which allows dynamic date-based filtering. Integration with Cloud Logging provides visibility. This eliminates the need to manage infrastructure or write code, perfectly matching the requirement for a low-overhead, scheduled SQL transformation solution.

Answer

Cloud Composer with a DAG that uses BigQueryInsertJobOperator to run the SQL queries.

Answer

Dataflow with a pipeline that reads from BigQuery, applies SQL transformations using Beam SQL, and writes back to BigQuery.

Answer

Cloud Scheduler triggering a Cloud Function that calls the BigQuery API to execute the SQL.