PCDE Practice Exam 59 — 20 Questions

Question 1

Your team is designing a schema for Cloud SQL (MySQL) for a content management system. You need to implement full-text search on article content. Which TWO schema design choices are appropriate? (Choose two.)

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

Accepted Answer

Use Cloud SQL's built-in full-text search feature.. Cloud SQL for MySQL provides built-in full-text search capabilities that allow efficient searching of text data. Adding a FULLTEXT index on the content column enables the use of MATCH...AGAINST queries, which are optimized for natural language search and are far more performant than LIKE operations. This is the correct approach because it leverages the database engine's native indexing and search algorithms.

Answer

Use the LIKE operator with wildcards for pattern matching.

Answer

Store article content in a Cloud Storage bucket and query metadata.

Answer

Normalize content into a separate table and use joins.

Question 2

You want to use OpenTelemetry Collector to send traces from an on-premises application to Cloud Trace. Which exporter should you configure in the Collector pipeline?

Accepted Answer

googlecloud. The OpenTelemetry Collector has an exporter 'googlecloud' (or 'googlecloudtrace') that sends traces to Cloud Trace. The 'logging' exporter writes to stdout, 'otlp' sends to an OTLP receiver, and 'prometheus' exports metrics. For Cloud Trace, the googlecloud exporter is correct.

Answer

logging

Answer

prometheus

Answer

otlp

Question 3

You are using Cloud Memorystore for Redis as a caching layer. You notice that cache hit ratio is below 50%. What is the best action to improve it?

Accepted Answer

Increase the TTL (time-to-live) for cached data.. A low cache hit ratio indicates that a large proportion of requests are not finding their data in the cache, forcing the application to fetch from the primary database. Increasing the TTL (time-to-live) for cached data keeps valid entries in Redis longer, reducing the frequency of evictions and cache misses. This directly improves the hit ratio by ensuring that more requests can be served from the cache before the data expires.

Answer

Flush the cache periodically to remove stale data.

Answer

Enable persistence to avoid data loss.

Answer

Increase the instance memory size.

Question 4

A global e-commerce platform uses Cloud Spanner multi-region configuration. The compliance team mandates that all write transactions must be committed in a specific geographic region to comply with data sovereignty laws. Which Spanner feature should be used to enforce this requirement?

Accepted Answer

Set the default leader region to the required region. Spanner's leader region configuration allows you to designate a specific region where all write transactions are coordinated. This ensures that the commit timestamp and transaction processing occur within that region, helping meet data sovereignty requirements.

Answer

Enable customer-managed encryption keys (CMEK)

Answer

Use read-only replicas in the required region

Answer

Deploy a regional instance in the required region

Question 5

You are running a Cloud Spanner instance and notice that a secondary index is causing performance issues for write operations. The index includes all columns of the table. Which Spanner feature can reduce the storage and write overhead of the index?

Accepted Answer

Use the STORING clause to include only the necessary columns. The STORING clause in Spanner allows you to include additional columns in the index without storing them in the index, reducing write overhead. This is used in 'covering indexes' but the STORING clause specifically stores the column in the index? Actually, STORING stores the column in the index so that queries don't need to read the base table. However, writing to the table requires updating the index, and if the index includes all columns, it's essentially a copy. To reduce overhead, you can use the STORING clause to only store necessary columns. The question asks to 'reduce the storage and write overhead' — using STORING with only needed columns reduces the index size, thus reducing write overhead. Alternatively, you could use a filtered index (partial index) but Spanner does not support filtered indexes. The correct answer is to use the STORING clause with only the columns needed.

Answer

Use a hash index instead of a secondary index

Answer

Drop the secondary index and rely on the primary key

Answer

Create a covering index without STORING

Question 6

A company uses Memorystore for Redis as a cache. They need to survive a zone failure. Which configuration should they choose?

Accepted Answer

Standard tier with replication. Standard tier with replication (Option D) provides a primary-replica pair in the same region but across two zones, ensuring automatic failover if one zone fails. This meets the requirement to survive a zone failure while maintaining low-latency access within a single region.

Answer

Cluster tier with multiple shards

Answer

Basic tier with a single node

Answer

Standard tier cross-region

Question 7

An engineer is planning to migrate a PostgreSQL database to AlloyDB using Database Migration Service (DMS). The source database is version 13 and the target is AlloyDB. The migration must replicate ongoing changes with minimal downtime. Which source configuration is required to enable continuous change data capture (CDC) with DMS for PostgreSQL?

Accepted Answer

Set wal_level=logical and install the pglogical extension.. DMS for PostgreSQL continuous migration uses logical replication. The source must have the `pglogical` extension installed and configured, with the `wal_level` set to `logical`.

Answer

Configure streaming replication with a replication slot.

Answer

Enable binary logging and set binlog_format=ROW.

Answer

Set max_replication_slots=0 to disable replication conflicts.

Question 8

A company uses Cloud Spanner with a multi-region configuration (nam3) for a global application. They notice that write latency has increased significantly during peak hours. After investigation, they find that the number of splits has increased from 10 to 50, and the CPU utilization on most nodes is below 10%. However, writes are being throttled due to excessive hot spots on a few nodes. What should they do?

Accepted Answer

Redesign the primary key to avoid monotonic increases. Hot spots in Spanner are often caused by monotonically increasing primary keys, which concentrate writes on a few splits. Redesigning the key to distribute writes (e.g., using a hash prefix) spreads the load evenly. Option C (increase nodes) does not fix the hot spot; the bottleneck is lock contention on specific splits. Option B (interleaved tables) helps with child table joins but not with primary key distribution. Option D (read replicas) is for read scaling, not write throughput.

Answer

Enable interleaved tables

Answer

Increase the number of nodes

Answer

Use a read replica

Question 9

A company is designing a global application using Cloud Spanner. They need to ensure low latency reads and writes across three continents. Which TWO configurations should they consider?

Accepted Answer

Use a multi-region configuration with leader regions in each continent.. Cloud Spanner multi-region configurations allow you to place leader regions in multiple continents, which enables low-latency strongly consistent reads and writes by directing traffic to the nearest leader. This is achieved through Spanner's TrueTime and Paxos-based replication, ensuring global consistency without sacrificing performance. Option D is also correct because read replicas in each continent can serve stale reads (read-only queries that tolerate slightly outdated data) with low latency, which is acceptable for many use cases like dashboards or reporting. Options B and C are incorrect: a single-region instance would cause high latency for users far from that region, and application caching does not guarantee consistency. Option E is incorrect: interleaved tables optimize query performance within a single table hierarchy but do not address global latency.

Answer

Use a single-region instance and rely on application caching.

Answer

Use strongly consistent reads from a single region.

Answer

Use interleaved tables to optimize query performance.

Question 10

A company is designing a landing zone in Google Cloud. They need to set up a shared VPC for multiple projects. Which TWO steps should they take? (Choose two.)

Accepted Answer

Attach service projects to the host project using the Shared VPC admin.. Attaching service projects to a host project is the fundamental step in setting up a Shared VPC. This allows the service projects to consume resources (like VMs and GKE clusters) from the shared VPC network in the host project, enabling centralized network management and isolation.

Answer

Create a VPC network in each service project and peer them with the host project.

Answer

Configure VPC peering between the host project and each service project.

Answer

Grant the compute.networkUser role on the host project to users who need to create resources in the shared VPC.

Question 11

Refer to the exhibit. What is the likely cause of this error?

Accepted Answer

The query does not include WHERE clause with partition column. The error occurs because the query attempts to access a partitioned table without specifying the partition column in the WHERE clause. In BigQuery (the platform relevant to the Google Professional Cloud Data Engineer exam), querying a large partitioned table without a partition filter forces a full scan of all partitions, which can exceed resource limits or time out. The correct approach is to include the partition column in the WHERE clause to enable partition pruning and avoid such errors.

Answer

The table is a view

Answer

The table is not partitioned

Answer

The user does not have permission to query the table

Question 12

A data engineering team ingests JSON logs into BigQuery using a streaming pipeline. Queries need to extract specific fields from nested arrays. Which SQL construct should be used to efficiently transform the nested data into a flat table for BI?

Accepted Answer

UNNEST with CROSS JOIN. `UNNEST` with `CROSS JOIN` is the standard SQL construct in BigQuery to flatten nested arrays (repeated fields) into a flat table. When JSON logs contain arrays of structs, `CROSS JOIN UNNEST(array_column)` expands each array element into its own row, allowing BI tools to access individual fields directly. This is the most efficient and idiomatic way to transform nested data into a relational format for querying.

Answer

ARRAY_AGG with STRUCT

Answer

STRUCT with nested field access

Answer

SELECT * EXCEPT with UNNEST

Question 13

A company uses Cloud SQL for MySQL with a failover replica. The primary instance is in us-central1 and the replica is in us-east1. During a regional outage in us-central1, the database engineer executes an emergency failover to the replica. After the failover, applications experience high latency when writing to the new primary. What is the most likely cause?

Accepted Answer

The new primary is in a different region, causing higher network round-trip times for applications that are still in us-central1.. After a cross-region failover, the new primary resides in us-east1 while the applications remain in us-central1. This geographic distance increases network round-trip time (RTT) for write operations, as each write must traverse the WAN between regions. Cloud SQL for MySQL does not automatically relocate compute resources, so latency-sensitive applications will experience higher write latency until they are migrated or configured to connect to the new region.

Answer

Cross-region replication triggers a mandatory 1-hour delay before writes are allowed.

Answer

The failover did not complete successfully; the replica is still in read-only mode.

Answer

The replica had a replication lag of 5 minutes, causing data inconsistency.

Question 14

A team is migrating an on-premises PostgreSQL database to Cloud SQL for PostgreSQL. The existing schema uses a large number of foreign key constraints and triggers for data validation. The team wants to minimize migration effort and maintain data integrity. Which schema design approach is most appropriate for Cloud SQL?

Accepted Answer

Keep the existing foreign keys and triggers as-is in Cloud SQL for PostgreSQL. Cloud SQL for PostgreSQL is fully compatible with the PostgreSQL engine, meaning foreign key constraints and triggers operate identically to on-premises PostgreSQL. This approach minimizes migration effort by preserving the existing schema logic and maintaining referential integrity without requiring application changes or data validation rewrites.

Answer

Migrate to Cloud Spanner and use interleaved tables to simulate foreign keys

Answer

Remove all foreign keys and triggers and implement validation in the application layer

Answer

Convert the schema to use Firestore in Datastore mode with composite indexes

Question 15

A production Cloud SQL for PostgreSQL instance needs to handle increased read traffic and provide automatic failover in case of a zone outage. Which architecture satisfies both requirements?

Accepted Answer

Enable high availability (regional) and create a read replica in the same region.. Enabling high availability (regional) for Cloud SQL for PostgreSQL creates a primary and standby instance in different zones within the same region, providing automatic failover during a zone outage. Adding a read replica in the same region offloads read traffic from the primary instance, satisfying the increased read traffic requirement while the HA configuration ensures high availability.

Answer

Enable high availability only.

Answer

Use a CMEK key and enable binary logging.

Answer

Create multiple read replicas without HA.

Question 16

A data team uses BigQuery for ad-hoc BI queries. They have a table with 100 columns. Analysts often select many columns. The table is partitioned by event_date. Queries are slow and expensive. What two-step optimization should they implement? (Note: This is a single correct answer among four options that combine two steps.)

Accepted Answer

Cluster the table by commonly used columns and limit the selected columns in queries.. Clustering by commonly used columns organizes data within partitions so that queries scanning only those columns read fewer blocks, reducing bytes processed. Limiting selected columns in queries further reduces the data scanned by avoiding unnecessary column reads. Together, these two steps directly address the high cost and slow performance caused by scanning many columns across a large partitioned table.

Answer

Convert the table to an Avro format and use partitioned tables.

Answer

Partition by event_date and use column-level security.

Answer

Cluster the table by event_date and use SELECT *.

Question 17

Which TWO are best practices for designing a Cloud Spanner schema?

Accepted Answer

Use commit timestamp columns to track row versions. Cloud Spanner's commit timestamp columns allow you to automatically track the time of the last write to a row, which is essential for implementing optimistic concurrency control, ordering versions, and building change data capture pipelines. This feature leverages the TrueTime API to provide globally consistent timestamps, making it a best practice for versioning and auditing.

Answer

Avoid secondary indexes to keep writes faster

Answer

Use monotonically increasing primary keys

Answer

Store all related data in a single row to avoid joins

Question 18

A company uses Bigtable for time-series analytics and needs to query the most recent data points first. The row key currently consists of a user ID followed by a timestamp (e.g., user123#2024-01-15T10:30:00). However, frequent queries filter by time range across all users. Which row key design change would optimize query performance for this access pattern?

Accepted Answer

Reverse the timestamp and place it at the beginning of the row key (e.g., 2024-01-15T10:30:00_rev#user123).. Reversing the timestamp and placing it at the beginning of the row key ensures that the most recent data points are stored first in lexicographic order. Bigtable stores rows sorted by row key, so queries filtering by a time range across all users can now scan a contiguous range of rows without needing to skip over user ID prefixes. This design avoids the hotspotting and inefficient scans that occur when the timestamp is not the leading part of the key for time-range queries.

Answer

Use the user ID as the only row key and store timestamps as column qualifiers.

Answer

Use a monotonically increasing integer as the row key.

Answer

Use a hash of the user ID as a prefix (salting) to distribute writes evenly.

Question 19

A company uses Cloud SQL for PostgreSQL. They need to monitor the replication lag on a read replica. Which metric should they use in Cloud Monitoring?

Accepted Answer

cloudsql.googleapis.com/database/postgresql/replication/replica_lag. For PostgreSQL replicas in Cloud SQL, the metric 'cloudsql.googleapis.com/database/postgresql/replication/replica_lag' measures lag in bytes (seconds can be derived). The metric 'replication_lag' is available for MySQL. For PostgreSQL, the specific metric is 'replica_lag'.

Answer

cloudsql.googleapis.com/database/replication/replica_lag

Answer

cloudsql.googleapis.com/database/replication/lag_seconds

Answer

cloudsql.googleapis.com/database/postgresql/replication/lag

Question 20

A company has a Cloud SQL for MySQL instance with automated backups enabled. They want to ensure they can recover to any point within the last 7 days with minimum storage cost. What should they do?

Accepted Answer

Keep the default automated backup configuration with 7-day retention and enable point-in-time recovery.. Cloud SQL for MySQL with automated backups enabled by default retains backups for 7 days. Enabling point-in-time recovery (PITR) uses the existing binary logs to allow recovery to any point within that retention period, without additional storage cost for the logs beyond the backup storage. This meets the requirement of 7-day recoverability with minimum storage cost.

Answer

Enable binary logging manually and store transaction logs in Cloud Storage.

Answer

Increase automated backup retention to 30 days and disable point-in-time recovery.

Answer

Disable automated backups and create manual backups daily.