PCDE Mock Exam 72 — 25 Questions

Question 1

A company runs a retail BI dashboard on BigQuery. The fact_sales table is partitioned by DAY and clustered by product_id. The table is 10 TB. Recently, analysts complain that queries filtering on a specific product_id and a month of data take over 10 minutes. The query uses a subquery to find top products. What should the engineer do?

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

Accepted Answer

Create a materialized view for the subquery.. Creating a materialized view for the subquery that identifies top products pre-computes and stores the results, which are incrementally refreshed by BigQuery. This avoids re-scanning the entire 10 TB fact_sales table each time the query runs, drastically reducing query time for the analysts' frequent filtering on product_id and a month of data.

Answer

Add an ORDER BY product_id to the subquery.

Answer

Change partition type to HOUR.

Answer

Re-cluster the table with product_id as the first clustering column and date as the second.

Question 2

You are designing a BigQuery schema for IoT sensor data. The sensor readings have varying fields depending on the sensor type. You want to minimize storage costs and avoid schema maintenance when new sensor types are added. What is the best schema design?

Accepted Answer

Store the sensor data in a JSON column. Storing sensor data in a JSON column leverages BigQuery's native support for semi-structured data (the `JSON` data type), allowing you to ingest records with varying fields without schema changes. This minimizes storage costs by avoiding the overhead of many NULL columns and eliminates the need for schema maintenance when new sensor types are added, as BigQuery can query JSON fields directly using functions like `JSON_EXTRACT` or dot notation.

Answer

Use a separate table per sensor type

Answer

Use a schema with a STRUCT containing all possible fields as optional

Answer

Use a wide table with many nullable columns

Question 3

You are managing a Spanner instance for a global financial application. The database has a table `transactions` with columns `transaction_id` (INT64), `user_id` (INT64), `amount` (FLOAT64), `timestamp` (TIMESTAMP), and `region` (STRING). The table is interleaved with a parent table `users`. Recently, you observed that point-read queries by `transaction_id` are taking over 100ms on average, whereas they used to take under 10ms. The instance CPU utilization is below 40%, and there are no contention issues. The `transactions` table has a primary key `(user_id, transaction_id)`. Queries filter on `transaction_id` only, without specifying `user_id`. Which optimization should you implement to improve point-read latency?

Accepted Answer

Create a secondary index on `transaction_id` to enable efficient key-based lookups.. Point-read queries by `transaction_id` are slow because the primary key is `(user_id, transaction_id)`, so without `user_id`, Spanner cannot directly locate the split (tablet) and must perform a full table scan or a less efficient lookup. Creating a secondary index on `transaction_id` allows Spanner to use that index for key-based lookups, reducing latency to under 10ms by enabling direct access to the specific split via the index's key.

Answer

Add a secondary index on `user_id` to help narrow down the search.

Answer

Use a Spanner query hint to force a specific index scan.

Answer

Change the primary key to `(transaction_id, user_id)` to enable direct access by transaction_id.

Question 4

A company wants to implement least-privilege IAM for their DevOps team. The team needs to manage Compute Engine instances and Cloud Storage buckets, but not delete resources. Which approach is recommended?

Accepted Answer

Create custom roles with only the necessary permissions, excluding delete.. The requirement is to manage Compute Engine instances and Cloud Storage buckets without the ability to delete resources. Option A is incorrect because the predefined roles `roles/compute.instanceAdmin.v1` and `roles/storage.objectAdmin` include delete permissions (`compute.instances.delete` and `storage.objects.delete`), which violate the least-privilege requirement. Option B is correct because creating custom roles allows you to explicitly grant only the necessary permissions, excluding delete actions, thus satisfying the constraint. Options C and D are incorrect: Cloud KMS is for key management, not IAM roles; primitive roles like `roles/editor` grant broad permissions including delete.

Answer

Grant predefined roles `roles/compute.instanceAdmin.v1` and `roles/storage.objectAdmin`.

Answer

Use Cloud KMS to manage access.

Answer

Grant the primitive roles `roles/editor` and `roles/viewer`.

Question 5

A data engineer notices that a scheduled query exporting BigQuery data to Cloud Storage is failing with a timeout error. The dataset contains 500 million rows. What should they do?

Accepted Answer

Export each partition separately.. Exporting a 500-million-row table as a single operation can exceed BigQuery's 6-hour timeout limit. By exporting each partition separately, you reduce the data volume per export job, allowing each to complete within the timeout window. BigQuery's partitioned tables enable parallel exports, avoiding the fixed timeout limit which cannot be increased.

Answer

Use SELECT * without filters.

Answer

Change the export format from CSV to Avro.

Answer

Increase the query timeout setting.

Question 6

An e-commerce platform uses Cloud SQL for PostgreSQL with max_connections set to 500. They plan to increase the number of application instances requiring connections. The instance has 8 vCPUs and 32 GB RAM. What is the maximum number of connections Cloud SQL can support based on the default formula?

Accepted Answer

2048. Cloud SQL for PostgreSQL uses a default formula to calculate max_connections: max_connections = min(4 * vCPUs + 100, 2048) for PostgreSQL instances. With 8 vCPUs, the raw calculation gives 132, but the cap of 2048 governs the maximum allowed. Since the question asks for the maximum based on the default formula, the correct answer is the cap value of 2048. Candidates often mistakenly use the raw formula output (132) or confuse with MySQL's cap (4096).

Answer

4096

Answer

1024

Answer

500

Question 7

Which THREE metrics should you set up alerts for to proactively monitor the health of a Cloud SQL for MySQL instance? (Choose 3)

Accepted Answer

Replication lag (for instances with replicas). Replication lag (for instances with replicas) is critical because high lag can cause stale reads and data inconsistency. CPU utilization above 80% indicates resource exhaustion, leading to performance degradation. Disk usage must be monitored to prevent storage full conditions that can cause instance downtime. These three metrics directly reflect instance health, whereas queries per second and network throughput are more related to workload performance, not health.

Answer

Number of queries per second

Answer

Network throughput

Question 8

Your company runs a global e-commerce platform on Cloud Spanner. You need to capture real-time changes to a table (e.g., orders) and stream them to a downstream analytics pipeline in near real-time. Which TWO Google Cloud features or services should you combine to achieve this? (Choose two.)

Accepted Answer

Dataflow streaming pipeline. A Dataflow streaming pipeline can consume Cloud Spanner change streams in near real-time, process the data (e.g., transform, filter, aggregate), and write it to a downstream analytics system. Dataflow provides exactly-once processing semantics and auto-scaling, making it ideal for continuous change data capture (CDC) pipelines.

Answer

Cloud Functions

Answer

Cloud Pub/Sub

Answer

Cloud SQL for PostgreSQL

Question 9

An organization is migrating a 500 GB Oracle database to Cloud SQL for PostgreSQL using DMS with continuous CDC. The migration is in progress, and the team needs to ensure that if the migration fails, they can roll back with minimal data loss. Which two pre-migration steps should they take? (Choose 2)

Accepted Answer

Take a full backup of the source database before starting the migration.. A rollback plan should include keeping the source running (read-only during cutover) and taking a full backup before migration. Exporting from the target is not helpful for rollback. Setting up a replica on-premises is unnecessary.

Answer

Export the Cloud SQL for PostgreSQL schema before migration.

Answer

Configure DMS to allow writes to the source after promotion.

Answer

Set up an Oracle Data Guard replica on-premises.

Question 10

A data warehouse in BigQuery is running slower due to large full-table scans. Which feature can reduce the amount of data processed for common queries?

Accepted Answer

All of the above. Partitioning, clustering, and materialized views all reduce the amount of data scanned in BigQuery. Partitioning limits scans to specific date ranges, clustering sorts data within partitions to skip irrelevant blocks, and materialized views precompute and store query results so subsequent queries read only the pre-aggregated output instead of scanning the base table. Together, these features minimize full-table scans and improve query performance.

Answer

Clustering

Answer

Materialized Views

Answer

Partitioning

Question 11

A company is planning a cutover from an on-premises MySQL database to Cloud SQL after a DMS continuous migration. To ensure minimal downtime and a successful cutover, which TWO actions should be part of the cutover procedure? (Choose TWO.)

Accepted Answer

Stop all writes to the source database.. Before cutover, quiesce writes to source and confirm DMS lag is zero to avoid data loss.

Answer

Increase the source database's CPU.

Answer

Delete the DMS migration job immediately.

Answer

Enable binary logging on Cloud SQL.

Question 12

An organization is migrating a Teradata data warehouse to BigQuery. They need to convert existing Teradata DDL and BTEQ scripts to BigQuery SQL. Which Google Cloud service should they use for schema conversion?

Accepted Answer

Schema Conversion Tool (SCT). Schema Conversion Tool (SCT) (now part of Database Migration Service) converts DDL and scripts from sources like Teradata to BigQuery. BigQuery Data Transfer Service handles data loading, not schema conversion.

Answer

Cloud Data Fusion

Answer

Cloud Composer

Answer

BigQuery Data Transfer Service

Question 13

A company plans to migrate an on-premises PostgreSQL database to Cloud SQL. The database is 2 TB in size and requires minimal downtime. Which migration approach should they use?

Accepted Answer

Use Database Migration Service with a continuous migration job.. Database Migration Service (DMS) with a continuous migration job is the correct approach because it supports minimal-downtime migrations from on-premises PostgreSQL to Cloud SQL. DMS uses logical replication (via PostgreSQL's pgoutput plugin) to continuously sync changes from the source to the target, allowing a short cutover window. For a 2 TB database, this avoids the lengthy downtime required by a full dump-and-load method.

Answer

Export the database using pg_dump and import into Cloud SQL using psql.

Answer

Use Datastream to stream data into Cloud SQL.

Answer

Copy the data files to Cloud Storage and use gcloud to load into Bigtable.

Question 14

An engineer is designing a Bigtable schema for a weather data application. The data is written by thousands of sensors, each generating a reading every minute. Queries typically retrieve all readings for a sensor in a time range. The row key should be designed to avoid hotspots and support these queries. Which two row key components are recommended? (Choose two.)

Accepted Answer

Reversed timestamp. Using a reversed timestamp (e.g., Long.MAX_VALUE - timestamp) as part of the row key spreads writes across Bigtable tablets, avoiding hotspots that occur when sensors write sequentially in natural time order. This design also supports efficient range scans for a sensor's data over a time range when combined with a sensor ID prefix.

Answer

Sensor ID (raw) as the only key

Answer

Sensor location as a column

Answer

Timestamp in natural order

Question 15

A Memorystore for Redis instance is experiencing out-of-memory errors, causing keys to be evicted. The application relies on all keys being present. The current maxmemory-policy is allkeys-lru. What should the engineer do to prevent evictions?

Accepted Answer

Change the eviction policy to noeviction.. Changing the eviction policy to noeviction causes Redis to return errors instead of evicting keys when memory is full. This ensures that all existing keys remain present, which is critical for the application. While scaling up (option D) could increase memory capacity, it does not guarantee that evictions will be prevented if memory usage grows to exceed the new limit. The noeviction policy directly prevents evictions, at the cost of write failures when memory is exhausted.

Answer

Change the eviction policy to volatile-lru.

Answer

Enable persistence to Cloud Storage to offload memory.

Answer

Scale up the instance to a higher tier with more memory.

Question 16

You are managing a Cloud SQL for PostgreSQL instance that is experiencing high CPU usage and slow query performance. You notice that the database has a high number of idle-in-transaction connections. Which immediate action should you take to reduce CPU load without disrupting active transactions?

Accepted Answer

Set the cloudsql.enable_idle_in_transaction_session_timeout flag to true and configure idle_in_transaction_session_timeout.. Setting the `cloudsql.enable_idle_in_transaction_session_timeout` flag to true and configuring `idle_in_transaction_session_timeout` allows Cloud SQL to automatically terminate idle-in-transaction connections after a specified timeout, reducing CPU load without manually killing connections or disrupting active transactions. This is a built-in, non-disruptive mechanism that targets only connections that are holding resources while idle, freeing up CPU and memory for active queries.

Answer

Use VPC firewall rules to block new connections until the issue resolves.

Answer

Kill all idle-in-transaction connections using pg_terminate_backend.

Answer

Set a statement_timeout at the session level for new connections.

Question 17

A team is migrating a MySQL OLTP database to Cloud Spanner. The existing schema uses auto-increment primary keys. They plan to convert them to STRING columns with UUIDs. However, the application also relies on ORDER BY on the original integer key. How should they preserve ordering while avoiding hotspots in Spanner?

Accepted Answer

Use the UUID as the primary key and create a secondary index on the original integer. Using UUID as the primary key avoids hotspotting because UUIDs are random and distribute writes evenly across splits. Creating a secondary index on the original integer key allows efficient ORDER BY queries on that column. While the secondary index may experience some hotspotting due to monotonically increasing values, Spanner's automatic split management can mitigate this, and the approach meets both requirements: hotspot avoidance on the primary key and ordering capability on the original integer.

Answer

Keep the auto-increment key but use bit-reversal

Answer

Use a composite primary key with a hash prefix followed by the UUID

Answer

Use a monotonically increasing custom ID and rely on Spanner's split management

Question 18

A startup is building a ride-sharing application that requires globally distributed, strongly consistent transactions for ride matching and payments. The database must scale horizontally and provide low-latency reads and writes. Which Google Cloud database should they use?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only Google Cloud database that provides global strong consistency, horizontal scaling, and supports ACID transactions across regions.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Firestore

Question 19

A company uses Cloud Spanner and needs to capture real-time changes from a table for downstream processing. They want to avoid writing custom application code to track changes. Which THREE components should they use? (Choose three.)

Accepted Answer

Pub/Sub. Cloud Spanner Change Streams (option C) capture real-time changes (inserts, updates, deletes) from a table. These changes are automatically published to a Pub/Sub topic (option B). A subscriber can then use Dataflow (option E) to process the change stream messages for downstream transformations or loading. Cloud Bigtable (option A) is a different database service not involved in this scenario. The Cloud Spanner API (option D) is not needed because Change Streams already integrate with Pub/Sub.

Answer

Cloud Bigtable

Answer

Cloud Spanner API

Question 20

A company wants to use Cloud SQL for MySQL to serve a read-heavy application. They need to ensure high availability and offload read traffic. Which TWO steps should they take? (Choose two.)

Accepted Answer

Create a same-region read replica. To ensure high availability, create a Cloud SQL HA configuration (regional instance with standby). To offload read traffic, create read replicas. Cross-region replicas are for disaster recovery but add latency. On-demand backups are for data protection, not availability. Auto-storage increase is for storage management.

Answer

Schedule on-demand backups daily

Answer

Create a cross-region read replica

Answer

Enable auto-storage increase