PDE Practice Test 47 — 15 Questions

Question 1

Which Google Cloud service is a fully managed relational database for MySQL, PostgreSQL, and SQL Server, offering automatic replication and backups?

Accepted Answer

Cloud SQL. Cloud SQL is the correct answer because it is Google Cloud's fully managed relational database service that supports MySQL, PostgreSQL, and SQL Server. It provides automatic replication across zones and automated backups, making it the ideal choice for traditional relational database workloads without the need for manual administration.

Answer

Cloud Spanner

Answer

AlloyDB

Answer

Bigtable

Question 2

A data pipeline uses Cloud Data Fusion to perform ETL jobs. The pipeline reads from BigQuery, transforms data using Wrangler, and writes to Cloud Storage. The team notices that the pipeline runs slower than expected. They suspect the Data Fusion instance is under-provisioned. Which action should be taken to improve performance?

Accepted Answer

Change the Data Fusion instance type from Basic to Enterprise. Cloud Data Fusion instance types (Basic, Enterprise, Developer) determine the compute resources available to run pipelines. Upgrading from Basic to Enterprise increases the number of Dataproc worker nodes and resources available to the CDAP runtime, improving pipeline throughput and reducing runtime. This directly addresses the under-provisioned instance suspicion.

Answer

Add more Dataproc Metastore instances

Answer

Enable Data Fusion accelerator for BigQuery

Answer

Rewrite the pipeline using Cloud Dataprep instead

Question 3

A company uses BigQuery for analytics and needs to ensure that certain columns containing PII are encrypted at query time so that only authorized users can decrypt. What should they use?

Accepted Answer

BigQuery AEAD encryption functions. BigQuery AEAD encryption functions allow you to encrypt sensitive columns (e.g., PII) at query time using a user-managed key, so that only authorized users who possess the key can decrypt the data. This is the correct approach because it provides column-level, application-layer encryption that is transparent to the query engine and ensures that unauthorized users see only ciphertext.

Answer

VPC Service Controls

Answer

Customer-managed encryption keys (CMEK)

Answer

Fine-grained IAM roles

Question 4

A data engineer needs to process large CSV files (hundreds of GB) stored in Cloud Storage using Spark on a Dataproc cluster. The job performs a series of transformations and aggregations. Which configuration is most cost-effective and operationally efficient?

Accepted Answer

Use a cluster with a standard master node and 10 preemptible worker nodes (n1-standard-4).. Preemptible workers are significantly cheaper (about 80% discount) and ideal for batch processing of large CSV files where fault tolerance is built into Spark via RDD lineage. Using standard nodes for the master ensures cluster stability, while preemptible workers handle the distributed transformations and aggregations cost-effectively. This configuration balances cost and operational efficiency for ephemeral, fault-tolerant workloads.

Answer

Use a cluster with 10 high-memory (n1-highmem-8) VMs as workers to improve shuffle performance.

Answer

Use a single-node cluster with a high-memory machine type.

Answer

Use a cluster with 10 standard (n1-standard-4) VMs as master and worker nodes, all non-preemptible.

Question 5

A company is designing a Cloud Bigtable row key for a time-series dataset of device readings. They want to avoid hotspotting (uneven load across tablets). Which TWO row key design patterns are effective? (Choose 2)

Accepted Answer

Reverse the timestamp string. Option C is correct because reversing the timestamp string (e.g., turning a lexicographically increasing timestamp into a decreasing one) prevents new writes from always landing on the last tablet, spreading sequential writes across the keyspace. Option D is correct because prepending a hash of the device ID to the timestamp distributes writes across many distinct key prefixes, so consecutive readings from different devices do not concentrate on a single tablet. Options A and B are incorrect because a monotonically increasing counter and a timestamp as the leading key component both produce sequential, ever-increasing keys that funnel all new writes to the last tablet, causing hotspotting. Option E is incorrect because a secondary index on the timestamp column is not a row key design pattern and Bigtable does not support secondary indexes natively; it would not address hotspotting.

Answer

Use a monotonically increasing counter as row key

Answer

Use timestamp directly as the first part of the row key

Answer

Use a secondary index on the timestamp column

Question 6

A data pipeline ingests streaming events into Pub/Sub and needs to join them with a slowly updating reference table (few thousand rows) from a Cloud Storage CSV file. The pipeline runs on Dataflow with Apache Beam. Which approach is most cost-effective and operationally simple?

Accepted Answer

Use a side input that reads the CSV once and broadcasts it to all workers. Option B is correct because a Beam side input reads the small Cloud Storage CSV once and broadcasts the reference table to all workers, letting each streaming element be enriched in memory without per-event external calls, which is both cheap and operationally simple. Since the table has only a few thousand rows, it easily fits in worker memory as a side input. Option A is wrong because issuing a BigQuery query per event is slow, expensive, and adds an external dependency. Option C is wrong because routing events through Cloud SQL and doing SQL JOINs adds infrastructure and cost. Option D is wrong because CoGroupByKey requires reading the CSV into a batch PCollection per window and shuffling both sides, which is more complex and less efficient than a side input for a small, slowly changing table.

Answer

Read the CSV in a DoFn and perform a BigQuery query each time an event is processed

Answer

Implement a custom sink that writes events to Cloud SQL and performs a SQL JOIN there

Answer

Use CoGroupByKey to join the stream and batch PCollections by a common key after reading the CSV into a batch PCollection each window

Question 7

A media company runs a batch data pipeline on Cloud Dataflow that ingests log files from Cloud Storage, transforms them, and writes results to BigQuery for analytics. The pipeline runs daily and has been stable for months. Recently, the source log format changed: a new optional field was added to some records. The pipeline started failing with ParseErrors for rows that contain the new field. The error logs show that the Dataflow job uses a hardcoded JSON schema that does not include the new field. The Dataflow pipeline logs are written to Stackdriver Logging, but no alerts are configured. The team wants to ensure that future schema changes do not break the pipeline and that failures are detected promptly. The team has limited experience with streaming and wants to keep the batch approach. Which course of action should the team take to improve solution quality?

Accepted Answer

Implement schema validation and evolution using a schema registry (e.g., AVRO) in the Dataflow pipeline, and configure Stackdriver alerts on pipeline failure or error logs.. It directly addresses both requirements: schema validation and evolution via a schema registry (e.g., AVRO) allows the Dataflow pipeline to handle added optional fields without hardcoded schema ParseErrors, and configuring Stackdriver (Cloud Monitoring) alerts on pipeline failure or error logs ensures prompt detection of future failures. A schema registry supports backward-compatible schema evolution, which is the standard way to prevent new optional fields from breaking batch ingestion. Option A only adds alerting and a manual runbook, so it detects failures but does not prevent schema changes from breaking the pipeline. Option B's hourly Cloud Function header check is a custom, brittle workaround that does not integrate with Dataflow's parsing or guarantee schema compatibility. Option C's BigQuery dry runs validate BigQuery load compatibility, not the Dataflow pipeline's JSON parsing schema, so it would not prevent the ParseErrors.

Answer

Create a Cloud Monitoring alert on any PipelineError log entries from the Dataflow job, and set up a runbook to manually fix schema mismatches within one hour.

Answer

Schedule a Cloud Function to run every hour that checks the latest log file headers and compares them to the pipeline schema, sending an alert if differences are found.

Answer

Use BigQuery dry run queries to validate the schema before loading data, and if a mismatch is detected, block the pipeline run and notify the team via email.

Question 8

A startup wants to build a data lake on Google Cloud to store raw JSON, CSV, and Parquet files from various sources. They need a storage solution that is highly durable, globally accessible, and integrates natively with BigQuery and Dataproc. They want to minimize management overhead. Which Google Cloud service should they use?

Accepted Answer

Cloud Storage. Cloud Storage is the foundational object store for data lakes on Google Cloud. It offers high durability, global accessibility, and native integration with analytics services like BigQuery and Dataproc. It requires no capacity planning or server management, aligning with the goal of minimal overhead. Relational, NoSQL, and NFS services are not suited for raw file storage at scale.

Answer

Filestore

Answer

Bigtable

Answer

Cloud SQL

Question 9

You are designing a BigQuery table to store clickstream events. Queries will frequently filter by a user_id column and a TIMESTAMP column named event_time, and the table will grow to several petabytes. You want to minimize bytes scanned and cost for these queries. Which TWO actions should you take? (Choose two.)

Accepted Answer

Partition the table by the DATE of event_time using time-unit partitioning, and require queries to include a filter on event_time.. For a petabyte-scale clickstream table filtered by event_time and user_id, partitioning on event_time enables partition pruning, and clustering on user_id enables block-level pruning within partitions. Together they minimize bytes scanned. Materialized views, table expiration, and BI Engine do not provide the same structural cost reduction for arbitrary filters.

Answer

Create a materialized view that pre-aggregates events by user_id and event_time, and query the view instead of the base table.

Answer

Set the table's expiration time to 30 days so that older partitions are automatically deleted, reducing the data volume.

Answer

Enable the BigQuery BI Engine reservation and route all queries through it to cache results in memory.

Question 10

Your team runs a Cloud Composer (Airflow) environment that executes a nightly BigQuery ELT DAG. A downstream task must only run after an upstream task that loads a partitioned table completes, and you want the downstream task to wait for the load's completion signal without polling BigQuery repeatedly. Which Airflow mechanism should you implement to coordinate these tasks within the DAG?

Accepted Answer

Define a direct downstream dependency (loader >> downstream) so Airflow's scheduler marks the downstream task runnable only after the loader task succeeds.. 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.

Answer

Use a task dependency with an ExternalTaskSensor pointed at the loader task's DAG and task ID, configured with the correct execution_delta.

Answer

Set the loader task's trigger_rule to all_done and add a dependency edge so the downstream task runs immediately after the loader reaches any terminal state.

Answer

Use an XCom to push a completion flag from the loader task and have the downstream task pull it via xcom_pull, combined with a trigger rule of all_success on the dependency edge.

Question 11

Your team runs a Cloud Composer 2 environment in project `analytics-prod`. A nightly DAG loads Cloud Storage files into BigQuery, but a recent Cloud Storage outage caused several tasks to fail after exhausting their retries. You want failed task instances to automatically re-run without manual intervention once the upstream dependency recovers. What should you do?

Accepted Answer

Define a task-level `on_failure_callback` that calls the Airflow REST API to clear the failed task instances after a delay.. 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.

Answer

Set the Airflow configuration option `[core] default_task_retries` to a higher value in the environment configuration and restart the schedulers.

Answer

Increase the DAG's `retry_delay` and `execution_timeout` so tasks wait longer for Cloud Storage to recover before failing.

Answer

Configure the DAG with `catchup=True` and set `max_active_runs=1` so missed schedule intervals are backfilled.

Question 12

Your analytics team queries a large BigQuery table `events` that is partitioned by `event_date` and clustered by `user_id`. A new dashboard runs a query filtering on `user_id = 'abc123'` but does not include a filter on `event_date`. You want to minimize the bytes scanned by this query. What should you do?

Accepted Answer

Add a filter on `event_date` to the query, such as `event_date >= '2024-01-01'`.. The table is partitioned by `event_date`; without a filter on that column, BigQuery cannot prune partitions and scans the entire table. Adding a date filter enables partition pruning, reducing bytes scanned. Clustering on `user_id` helps only within the scanned partitions, so it does not replace the need for a partition filter.

Answer

Use `SELECT *` instead of selecting specific columns.

Answer

Add a `LIMIT` clause to the query, such as `LIMIT 1000`.

Answer

Create a clustered table on `user_id` instead of `event_date`.

Question 13

A financial services firm stores trade documents in a Cloud Storage bucket. Auditors require that each object be retained for exactly seven years and that no user, including project owners, be able to delete or overwrite the objects during that period. The firm also needs to prove compliance to auditors. Which combination of controls should the data engineer implement?

Accepted Answer

Create a bucket lock on a retention policy of seven years and grant auditors the Storage Object Viewer role.. A locked bucket retention policy enforces object immutability for the specified duration and cannot be removed or shortened, satisfying the requirement that even project owners cannot delete data. Because the lock is a permanent bucket property, auditors can verify it directly. Lifecycle rules, versioning, and IAM conditions are all modifiable controls and therefore cannot guarantee the required immutability.

Answer

Apply IAM conditions that deny storage.objects.delete unless the request comes from an auditor's service account.

Answer

Set an Object Lifecycle Management rule to delete objects after 2555 days and enable uniform bucket-level access.

Answer

Enable Cloud Storage versioning and set a lifecycle rule to keep noncurrent versions for seven years.

Question 14

A healthcare company stores patient documents in a Cloud Storage bucket with a retention policy. Auditors require that once an object is written, it cannot be overwritten or deleted by any user, including project owners, for 7 years. The company also wants to minimize storage cost after the first year while preserving immutability. What should the data engineer configure?

Accepted Answer

Apply a bucket lock on a retention policy with a 7-year retention period, then add a lifecycle rule to transition objects to Archive after 365 days.. A locked retention policy is the only Cloud Storage mechanism that enforces immutability against all users, including project owners, for the specified duration. Combining it with a lifecycle rule that transitions objects to Archive after one year satisfies the retention requirement while lowering storage cost for the remaining six years.

Answer

Enable uniform bucket-level access and create a retention policy without locking it, then transition objects to Coldline after 365 days.

Answer

Set an IAM deny policy on storage.objects.delete for all principals and use a lifecycle rule to move objects to Nearline after 365 days.

Answer

Enable Object Versioning and set a lifecycle rule to move objects to Coldline after 365 days.

Question 15

A logistics company runs Cloud SQL for MySQL for its order management system. The database is 2 TB and growing 100 GB per month. They need to run analytical queries without impacting transactional performance, and they want to minimize operational overhead. They also require the analytics data to be no more than 15 minutes stale. Which storage approach should they use?

Accepted Answer

Configure a Datastream stream from Cloud SQL to BigQuery with a change data capture policy and query the replicated tables in BigQuery.. Datastream provides serverless change data capture from Cloud SQL for MySQL into BigQuery, keeping the analytics copy within minutes of the source. BigQuery isolates analytical scans from the transactional instance, so order management performance is unaffected. The managed service minimizes operational overhead compared to maintaining replicas or custom export jobs, and it satisfies the 15-minute freshness requirement through continuous replication.

Answer

Export the Cloud SQL database to a Cloud Storage bucket nightly with mysqldump, then load the files into BigQuery each morning.

Answer

Enable the Cloud SQL federated query feature in BigQuery and query the Cloud SQL instance directly from BigQuery.

Answer

Create a read replica of the Cloud SQL instance and run analytical queries directly against the replica.