PCDE Mock Exam 74 — 25 Questions

Question 1

An engineer needs to create a cross-region read replica for a Cloud SQL for MySQL instance to improve read scalability and provide disaster recovery. What must be configured on the primary instance to support cross-region replication?

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

Accepted Answer

Enable binary logging on the primary instance.. Cross-region replication for Cloud SQL for MySQL relies on MySQL's native binary log (binlog) based replication. Enabling binary logging on the primary instance records all data changes in binary log files, which the read replica in another region uses as the source for applying changes. Without binary logging enabled, the primary instance cannot provide the change stream necessary for cross-region replication to function.

Answer

Enable automated backups and point-in-time recovery.

Answer

Configure a Cloud VPN between the regions.

Answer

Set the primary instance to use MySQL 8.0.

Question 2

A company is designing a Cloud Spanner database for a global supply chain application. The schema includes a table 'Shipments' with columns: shipment_id (INT64), created_at (TIMESTAMP), origin (STRING), destination (STRING), status (STRING). The application frequently queries for shipments by origin and status. Which three design choices optimize query performance? (Choose THREE.)

Accepted Answer

Use the STORING clause in the index to include frequently accessed columns.. The STORING clause in a Cloud Spanner secondary index allows you to include additional columns (such as 'destination' or 'status') in the index storage, enabling index-only scans that avoid fetching rows from the base table. This reduces read latency and resource consumption for queries that frequently access those columns along with the indexed keys. Option C is correct because interleaving an 'OrderItems' table under 'Shipments' places child rows physically close to parent rows, improving join performance by reducing round trips and allowing efficient row scans. Option D is correct because a secondary index on (origin, status) directly supports the frequent queries filtering on those columns, enabling fast lookups without scanning the entire table. Options A and E are incorrect: Cloud Spanner does not support traditional partitioning by region, and monotonically increasing primary keys cause hotspotting at the leader, reducing write throughput.

Answer

Partition the table by region to limit scans.

Answer

Use a monotonically increasing integer primary key for high write throughput.

Question 3

You are managing a Memorystore for Redis instance that is part of a high-traffic e-commerce application. The instance uses the volatile-lru eviction policy and has persistence disabled. You need to improve data durability without losing the ability to evict keys with TTL. You also want to ensure that the instance can automatically recover from a zonal failure. Which TWO actions should you take? (Choose TWO.)

Accepted Answer

Enable RDB persistence by setting the persistence mode.. (Enable RDB persistence) improves data durability by taking periodic snapshots without interfering with the volatile-lru eviction policy, which only evicts keys with TTL. Option D (Standard Tier with replication) provides automatic failover across zones within a region, ensuring recovery from a zonal failure. Option A only reduces eviction frequency but does not address durability or zonal failover. Option C is incorrect because Memorystore does not support native cross-region replicas. Option E uses manual exports, which do not enable automatic recovery and are not equivalent to persistence.

Answer

Increase the maxmemory setting to reduce eviction frequency.

Answer

Configure a cross-region replica to provide failover in another region.

Answer

Set up a Cloud Scheduler job to export the instance to Cloud Storage every hour.

Question 4

A Cloud Firestore database in Native mode is used for a mobile app. The app queries a collection with a composite filter on two fields. Queries are slow and the app shows an error that an index is required. What should the developer do?

Accepted Answer

Create a composite index in the Firebase Console or gcloud CLI.. Cloud Firestore requires an index to support composite filters (queries filtering on multiple fields). When a query uses a composite filter and no matching index exists, Firestore returns an error indicating an index is required. The correct solution is to create the necessary composite index manually via the Firebase Console or the gcloud CLI, as Firestore does not automatically create composite indexes for queries with equality and range filters on different fields.

Answer

Use a database export and import to regenerate indexes.

Answer

Restructure the data to use a single field for the filter.

Answer

Enable the automatic index creation in the Firestore settings.

Question 5

A company is migrating an on-premises PostgreSQL database to Cloud SQL for PostgreSQL. They need to ensure minimal downtime and maintain ongoing replication until cutover. Which 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 by using PostgreSQL's native logical replication. DMS handles the initial one-time data load and then continuously replicates ongoing changes until you are ready to cut over, meeting the requirement for minimal downtime and ongoing replication.

Answer

Create a replica of the on-premises PostgreSQL and use pg_basebackup to stream to Cloud SQL.

Answer

Export using pg_dump with --no-owner and --no-acl flags, then manually configure logical replication slots.

Answer

Use pg_dump to export the database and import it into Cloud SQL, then set up application-level dual writes.

Question 6

Your organization requires that all database backups be stored in a different region for disaster recovery. You are using Cloud SQL for MySQL. What backup configuration should you use?

Accepted Answer

Enable automated backups and select a different region for the backup location.. Cloud SQL for MySQL allows you to specify a different region for automated backup storage, which satisfies the disaster recovery requirement of storing backups in a separate region. By selecting a different region for the backup location, you ensure that if the primary region fails, the backups remain accessible for recovery. This is the only built-in option that directly meets the cross-region backup requirement without additional manual steps.

Answer

Enable automated backups and select the same region as the instance.

Answer

Use on-demand exports to Cloud Storage in the same region.

Answer

Configure a multi-region Cloud Storage bucket and point automated backups there.

Question 7

A company uses Memorystore for Redis and wants to achieve high availability with automatic failover. They also need to periodically back up the data to Cloud Storage for disaster recovery. Which TWO features should they use? (Choose two.)

Accepted Answer

Standard Tier (replication). Standard Tier (replication) provides a primary-replica architecture with automatic failover, ensuring high availability. The Export functionality allows you to manually or scheduled export Redis data to Cloud Storage, enabling point-in-time recovery for disaster recovery purposes.

Answer

Redis Cluster

Answer

AOF persistence

Answer

Basic Tier

Question 8

An e-commerce application uses Firestore in Native mode. The team needs to run a query that filters on two fields and orders by a third field. What is the correct approach to ensure this query runs efficiently?

Accepted Answer

Create a composite index on the three fields. Firestore requires a composite index for queries that filter on multiple fields and order by a different field, because single-field indexes cannot satisfy the combined filtering and ordering constraints. Creating a composite index on the three fields (the two filter fields and the order field) allows Firestore to efficiently execute the query without scanning all documents.

Answer

Rely on automatic single-field indexes

Answer

Use an index exemption on the fields

Answer

Use Firestore in Datastore mode instead

Question 9

A data engineer is writing a SQL query in BigQuery to calculate the running total of sales per product over time. The table 'sales' has columns product_id, sale_date, and amount. The result must include the cumulative sum ordered by sale_date for each product. Which SQL construct should be used?

Accepted Answer

SUM(amount) OVER (PARTITION BY product_id ORDER BY sale_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW). It uses a window function with a PARTITION BY clause to reset the running total per product and an ORDER BY with a ROWS frame to compute the cumulative sum over time. This is the standard SQL construct in BigQuery for calculating running totals within partitions.

Answer

GROUP BY product_id, sale_date with SUM(amount)

Answer

ROW_NUMBER() OVER (ORDER BY sale_date)

Answer

LAG(amount, 1, 0) OVER (ORDER BY sale_date)

Question 10

A company is using Cloud Run to deploy a service that processes background tasks. The service takes a few seconds to initialize, and users experience high latency on cold starts. How can the company eliminate cold starts for this service?

Accepted Answer

Set the minimum number of instances to a value based on the baseline traffic.. Setting a minimum number of instances ensures that at least that many instances are always running and ready to serve requests, eliminating cold starts.

Answer

Set the maximum number of instances to a higher value.

Answer

Use the gen1 execution environment.

Answer

Set the concurrency to 1.

Question 11

A DevOps engineer wants to manage Google Cloud resources as code using a declarative language. Which tool is the current industry standard and recommended by Google?

Accepted Answer

Terraform. Terraform is the current industry standard for Infrastructure as Code (IaC) and is recommended by Google for managing Google Cloud resources declaratively. It uses the HashiCorp Configuration Language (HCL) to define cloud resources, supports state management for tracking resource changes, and provides a consistent workflow across multiple cloud providers. Google’s own documentation and professional certification materials explicitly endorse Terraform as the primary IaC tool for GCP.

Answer

Cloud Deployment Manager

Answer

Pulumi

Answer

Ansible

Question 12

An engineer is performing a manual migration from PostgreSQL to Cloud SQL. They run pg_dump and want to import the dump into Cloud SQL. Which pg_dump flags are necessary to avoid errors related to ownership and ACLs?

Accepted Answer

--no-owner and --no-acl. Cloud SQL does not allow setting ownership or ACLs; using --no-owner and --no-acl prevents errors during restore.

Answer

--format=custom and --compress=9

Answer

--schema-only and --data-only

Answer

--create and --clean

Question 13

You are monitoring a Cloud Spanner instance and see that the average commit latency is high. The application performs many single-row inserts. Which metric would you check first to understand the root cause?

Accepted Answer

Lock conflicts (e.g., Spanner/API/Lock_wait).. High commit latency for single-row inserts in Cloud Spanner is often caused by lock conflicts, as concurrent transactions may contend for the same row or index. The metric Spanner/API/Lock_wait directly measures time spent waiting for locks, making it the first metric to check. High commit latency without high lock wait suggests other issues, but lock conflicts are the most common root cause for write-heavy single-row workloads.

Answer

Read latency.

Answer

Storage utilization.

Answer

CPU utilization per node.

Question 14

A team executed the above DDL to create interleaved tables in Cloud Spanner. They need to query all orders for a specific customer. Which query will be most efficient?

Accepted Answer

SELECT * FROM Orders WHERE CustomerId = 1234;. In Cloud Spanner, interleaved tables store child rows physically adjacent to their parent row within the same split. Querying Orders directly on the interleaved key (CustomerId) allows Spanner to perform a local index scan within the parent row's split, avoiding a distributed cross-table join. This leverages the interleaved table's physical clustering for the most efficient retrieval.

Answer

SELECT * FROM Orders WHERE CustomerId = 1234 AND OrderDate = '2023-01-01';

Answer

SELECT * FROM Customers JOIN Orders ON Customers.CustomerId = Orders.CustomerId WHERE Customers.CustomerId = 1234;

Answer

SELECT * FROM Orders WHERE OrderId = 5678;

Question 15

A company uses Cloud Spanner and needs to implement change data capture (CDC) to stream changes to a downstream analytics pipeline. Which two features can they use? (Choose TWO.)

Accepted Answer

Pub/Sub integration to consume change stream data. Spanner change streams capture row-level changes in a Cloud Spanner database. These changes can be consumed via Pub/Sub integration, allowing downstream analytics pipelines to process the data. Therefore, Option E (Spanner change streams) is correct for capturing the changes, and Option A (Pub/Sub integration) is correct for streaming them. Option B (Cloud SQL for PostgreSQL logical replication) is not applicable as it is for Cloud SQL, not Spanner. Option C (Datastream) is used for database migrations and replication, not for CDC from Spanner. Option D (Bigtable replication) is for Bigtable, not Spanner.

Answer

Cloud SQL for PostgreSQL logical replication

Answer

Datastream

Answer

Bigtable replication

Question 16

A company uses Cloud SQL for PostgreSQL to store transactional data and BigQuery for analytics. They need to sync a subset of tables from Cloud SQL to BigQuery daily for BI reporting. The tables are updated incrementally (INSERT, UPDATE, DELETE). Which approach is MOST reliable and cost-effective?

Accepted Answer

Use Datastream to stream changes from Cloud SQL to BigQuery in near real-time.. Datastream is purpose-built for exactly this use case: it captures CDC (Change Data Capture) events from Cloud SQL for PostgreSQL (using the PostgreSQL logical replication slot and the pgoutput plugin) and streams them directly into BigQuery via a streaming ingestion pipeline. This approach handles INSERT, UPDATE, and DELETE operations reliably without custom code, and it is cost-effective because it avoids full table exports and leverages BigQuery's streaming buffer for near-real-time updates.

Answer

Write a custom cron job on App Engine to extract changes and load them into BigQuery.

Answer

Create BigQuery federated queries that directly read from Cloud SQL.

Answer

Export the Cloud SQL tables to Cloud Storage as CSV files daily, then load them into BigQuery.

Question 17

A company is migrating an on-premises PostgreSQL database to Cloud SQL for PostgreSQL. The database uses several custom PL/pgSQL functions that perform complex calculations. The migration must minimize application changes and support high availability. Which strategy should the database engineer use for the schema migration?

Accepted Answer

Use pg_dump to export the schema including functions and restore directly to Cloud SQL.. Pg_dump can export the entire PostgreSQL schema, including custom PL/pgSQL functions, in a format that Cloud SQL for PostgreSQL natively understands. Restoring directly with pg_restore or psql preserves the functions without requiring syntax conversion, minimizing application changes. Cloud SQL for PostgreSQL supports high availability through regional persistent disks and automatic failover replicas, meeting the HA requirement without altering the schema.

Answer

Convert the functions to stored procedures in Cloud Spanner and migrate data separately.

Answer

Export the functions as SQL scripts and convert them to pgSQL syntax for Cloud SQL.

Answer

Export the functions as SQL scripts and rewrite them in JavaScript using Cloud Functions.

Question 18

A developer reports that an application cannot connect to a Cloud SQL SQL Server instance. The error log shows the message in the exhibit. The instance exists and the user credentials are correct. What is the most likely cause?

Accepted Answer

The database name specified in the connection string is incorrect.. The error message in the exhibit indicates that the login failed for the user, which is a common symptom when the database name in the connection string does not match an existing database on the Cloud SQL SQL Server instance. Even though the user credentials are correct, SQL Server requires a valid database context to establish the connection; an incorrect database name causes the server to reject the login attempt. This is a configuration issue, not an authentication or network problem.

Answer

The Cloud SQL instance has reached its maximum number of connections.

Answer

The Cloud SQL proxy is not running.

Answer

The Cloud SQL instance is not in the same VPC network as the application.

Question 19

The user runs a BigQuery query on a non-partitioned table and receives the error shown. Which optimization should be applied first to resolve the issue?

Accepted Answer

Partition the table by the event_date column. The error indicates that the query is scanning too much data, likely exceeding the free tier or slot quota. Partitioning the non-partitioned table by `event_date` allows BigQuery to perform partition pruning, scanning only the relevant date range instead of the entire table. This directly reduces the data processed, which is the most effective first optimization for cost and performance.

Answer

Increase the BigQuery reservation slot count

Answer

Create a materialized view that pre-aggregates the data

Answer

Cluster the table by event_date

Question 20

A company uses Cloud Spanner with a multi-region configuration (nam7) to support a global user base. They notice increased read latency for users in Europe, while write latency is acceptable. The database engineer observes that most queries are single-row reads using the primary key. What is the best approach to reduce read latency for European users?

Accepted Answer

Use read-only transactions with stale reads (timestamp bound) to read from local replicas.. Cloud Spanner's multi-region configuration (nam7) includes regional replicas in Europe. By using stale reads with a timestamp bound, read-only transactions can be served from a local replica without incurring cross-region latency, which directly reduces read latency for European users while maintaining acceptable consistency.

Answer

Create a secondary index on the primary key column.

Answer

Increase the number of Spanner nodes to distribute the load.

Answer

Partition the database by region and use directed reads.