PCDE Mock Exam 73 — 25 Questions

Question 1

A company uses Terraform to manage infrastructure. They have a monolithic Terraform configuration that manages all projects in a single state file. As the organization grows, the configuration becomes slow and error-prone. The team wants to adopt a modular approach with separate state files for each project while reusing common modules. Which strategy should they follow?

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

Accepted Answer

Split the monolithic configuration into separate root modules per project, store state in GCS buckets with prefix per project, and use terraform_remote_state data sources to share outputs between modules.. Terraform workspaces are used to manage multiple state files within a single root module but are not designed for separate project state files. Remote state data sources allow reading outputs from other state files, enabling modular architecture. Using separate root modules for each project with remote state dependencies is the recommended approach for large environments.

Answer

Create a single Terraform module for all resources and call it with different variables for each project, using the same state file.

Answer

Keep the monolithic configuration but use Terraform workspaces to create separate state files for each project.

Answer

Use Cloud Deployment Manager with separate YAML templates for each project and a central state stored in Cloud Storage.

Question 2

A company is evaluating disaster recovery options for their production Bigtable instance. They need asynchronous replication with manual failover and the ability to route reads to the secondary cluster only when the primary is unhealthy. Which TWO settings should they configure? (Choose 2 correct answers.)

Accepted Answer

Use Cloud DNS health checks to update DNS records to point to the secondary cluster. Bigtable replication supports asynchronous replication with app profiles. To achieve manual failover with read routing to the secondary cluster only when the primary is unhealthy, two configurations are needed: (1) A read-failover routing policy on an app profile directs reads to the secondary during a failover event, and (2) Cloud DNS health checks update DNS records to point to the secondary cluster, enabling manual failover control. Option B is incorrect because synchronous replication would require automatic failover, not manual. Option C is incorrect because multi-cluster routing with single-cluster fallback does not meet the primary-unhealthy-only requirement. Option E is incorrect because any-replica routing would send reads to the secondary during normal operation, not just when primary is unhealthy.

Answer

Enable automatic failover by setting the replication to synchronous mode

Answer

Configure a multi-cluster routing policy with a single-cluster fallback

Answer

Create an app profile with any-replica routing policy

Question 3

A company runs a Memorystore for Redis cluster with standard tier (for replication). They need to ensure high durability of session data and be able to recover from a complete zone failure. Which three actions should they take? (Choose THREE.)

Accepted Answer

Schedule regular exports of the instance data to Cloud Storage using gcloud. To ensure high durability and recovery from a complete zone failure for a Memorystore for Redis cluster with standard tier (replication enabled): Option A: Regular exports to Cloud Storage provide point-in-time backups that can be restored in a different zone if needed. Option B: Cross-region replication creates a replica in another region, allowing failover if the primary zone fails. Option E: Setting up a second instance in a different zone with client-side replication allows the application to switch to the replica if the primary zone fails. Option C is incorrect because Redis Cluster with multiple shards is for horizontal scaling and does not inherently provide zone failure recovery or durability beyond what replication provides. Option D is incorrect because RDB persistence, while supported in Memorystore for Redis, only provides local recovery and does not protect against a complete zone failure; you need cross-zone or cross-region replication for that.

Answer

Use a Redis Cluster with multiple shards for horizontal scaling

Answer

Enable RDB persistence on the primary instance

Question 4

Which TWO best practices should be followed when modeling data for a Looker BI dashboard to optimize query performance?

Accepted Answer

Use persistent derived tables (PDTs) to materialize intermediate results. Persistent Derived Tables (PDTs) materialize intermediate query results into physical tables in the underlying database (e.g., BigQuery). This avoids re-executing complex logic on every user interaction, drastically reducing query latency and cost. PDTs are a core Looker optimization for repeated, heavy transformations.

Answer

Use derived tables for all complex logic

Answer

Use native derived tables to leverage BigQuery's UDFs

Answer

Use materialized views in the underlying database

Question 5

A company wants to migrate a self-managed PostgreSQL database to AlloyDB with minimal downtime. They plan to use Database Migration Service with continuous CDC. The source database is in a different region. Which THREE steps should they include in their cutover plan? (Choose 3 correct answers.)

Accepted Answer

Quiesce writes to the source database.. Cutover plan: quiesce writes to source, confirm replication lag is 0, promote the destination, update connection strings, and keep source running read-only for rollback. Testing before cutover is important but not part of the cutover plan step. DMS can work across regions but may have increased lag. Deleting the source immediately is risky.

Answer

Run a full performance test on AlloyDB before cutover.

Answer

Delete the source database immediately after promotion.

Question 6

A company uses Bigtable for time-series data with a row key format: 'deviceID#timestamp'. They notice write hotspotting on a few devices that generate high volumes of data. How should they redesign the row key to distribute writes evenly?

Accepted Answer

Add a random salt prefix to the row key: 'random_number#deviceID#timestamp'. Hotspotting occurs because rows for a single high-volume device all share the same deviceID prefix, causing writes to be directed to a single tablet. To distribute writes evenly, the row key must include a non-deterministic or varying component. Option A adds a random salt per write, which scatters the keys for the same device across many tablets, effectively distributing the load. Option B still uses deviceID as the first part, so it doesn't spread writes. Option C creates monotonically increasing keys, leading to sequential writes and hotspotting. Option D does not change the row key, so it fails to address the prefix issue.

Answer

Reverse the timestamp: 'deviceID#reverse_timestamp'

Answer

Use a monotonically increasing integer for the row key.

Answer

Store all data in a single column family and use column qualifiers for timestamps.

Question 7

A company needs to store and analyze semi-structured JSON logs from multiple microservices. The data is write-heavy with bursts of 100,000 writes/sec, and queries filter by service name and timestamp range. They require low operational overhead and the ability to query with SQL. Which Google Cloud database should they choose?

Accepted Answer

BigQuery. BigQuery supports SQL queries on semi-structured JSON data and can handle high write rates via streaming inserts (up to 100,000 rows per second per project). Cloud Bigtable does not support SQL natively, Cloud SQL cannot handle the write throughput, and Firestore is limited in write capacity for this volume.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Firestore

Question 8

A CI/CD pipeline uses Cloud Build to build a Docker image. The Dockerfile copies dependencies from a private repository in the same VPC. The build takes a long time due to repeated downloads. How can the engineer optimize the build?

Accepted Answer

Enable Kaniko layer caching and push cache to Artifact Registry. Kaniko layer caching stores intermediate Docker image layers in Artifact Registry, allowing subsequent builds to reuse cached layers instead of re-downloading dependencies from the private repository. This directly addresses the repeated downloads causing long build times, as Kaniko checks the cache before executing each RUN command in the Dockerfile.

Answer

Use a larger machine type

Answer

Increase the build timeout

Answer

Use Cloud Build's local SSD for temporary storage

Question 9

Your Memorystore for Redis instance is used as a session store for a web application. You need to ensure that session data is not lost during a node failure. What should you do?

Accepted Answer

Enable AOF persistence in the Memorystore instance.. Memorystore for Redis supports persistence via RDB snapshotting or AOF (Append-Only File). For minimal data loss during a node failure, AOF persistence is recommended because it logs every write operation, allowing recovery with only a few seconds of data loss (depending on fsync settings). Standard Tier with replication provides high availability but does not persist data to disk, so a full zone failure can cause data loss. Periodic exports to Cloud Storage have gaps between exports, leading to data loss of up to the export interval. Therefore, enabling AOF persistence is the best approach to prevent data loss during a node failure.

Answer

Use a Basic Tier instance with a large maxmemory setting.

Answer

Configure the instance as Standard Tier (with replication) and schedule periodic exports to Cloud Storage.

Answer

Enable persistence by setting the 'persistence' parameter to 'rdb' in the instance configuration.

Question 10

A team uses Cloud Monitoring to track availability SLI as good-request-count / valid-request-count. They want to create a window-based SLO. Which metric filter should they use for the numerator?

Accepted Answer

count of minutes where availability >= 99.9%.. Window-based SLOs use 'good minutes' where the availability is above a threshold (e.g., 99.9% of requests succeeded). The numerator is the count of minutes where the SLI was good.

Answer

total number of minutes in a month.

Answer

count of requests with status 200.

Answer

count of errors.

Question 11

Match each BigQuery DDL statement to its function.

Accepted Answer

CREATE TABLE: Creates a new table in a dataset. BigQuery DDL statements manage database objects. CREATE TABLE and CREATE VIEW define new objects, while DROP TABLE removes them. Common confusions arise from swapping CREATE and DROP or using ALTER for creation.

Answer

CREATE TABLE: Deletes a table and its data

Answer

ALTER TABLE: Creates a new table in a dataset

Question 12

Match each Cloud SQL high-availability feature to its description.

Accepted Answer

Automatic failover: Automatically switches to a standby instance in a different zone if the primary instance fails, ensuring minimal downtime.. Automatic failover switches to a standby zone on failure, read replicas provide read-only copies for scaling, and point-in-time recovery allows restoring to any time within retention. Common confusions mix automatic failover with read replicas.

Answer

Automatic failover: Provides read-only copies of the primary instance to distribute read load.

Answer

Read replicas: Automatically switches to a standby instance in a different zone if the primary fails.

Question 13

A Cloud SQL for MySQL database has frequent table locks causing contention and slow queries. Which diagnostic approach helps identify the blocking queries?

Accepted Answer

Use INFORMATION_SCHEMA.INNODB_TRX. `INFORMATION_SCHEMA.INNODB_TRX` provides real-time data on all currently executing InnoDB transactions, including transaction IDs, state, and the waiting flag. By joining this with `INNODB_LOCK_WAITS` and `INNODB_LOCKS`, you can pinpoint which transaction is blocking others, directly addressing table lock contention in Cloud SQL for MySQL.

Answer

Set up a Cloud Monitoring alert on CPU

Answer

Use Query Insights

Answer

Enable slow query log

Question 14

Match each BigQuery feature to its purpose.

Accepted Answer

Partitioning: Divides table into segments based on a column (e.g., date) to reduce query cost and improve performance.. These features optimize cost, performance, and management in BigQuery. Partitioning reduces scan costs, Clustering improves query performance, Materialized Views accelerate complex queries, and Slots provide resource control.

Answer

Partitioning: Sorts data based on one or more columns to improve query performance.

Answer

Clustering: Divides table into segments based on a column to reduce cost.

Question 15

A retail company uses Cloud Spanner to handle global transaction processing. The database has a single regional instance in us-central1. The company expects a 10x increase in write traffic from a new mobile app. The database engineer needs to design for low latency writes globally and high availability. What should the Database Engineer do?

Accepted Answer

Change the instance configuration to a multi-region configuration like nam3 (us-central1, us-east1, us-west1) and configure a dedicated write region.. A multi-region configuration like nam3 (us-central1, us-east1, us-west1) with a dedicated write region provides low-latency writes globally by using Google's managed replication and automatic failover. This design ensures high availability and meets the 10x write traffic increase without sacrificing write performance, as writes are processed in the designated write region and asynchronously replicated to other regions.

Answer

Shard the database across multiple regional instances based on user geography.

Answer

Create read replicas in other regions to offload read traffic and keep writes in the primary region.

Answer

Increase the number of nodes in the existing regional instance to handle the increased write capacity.

Question 16

A retail company uses Cloud SQL for PostgreSQL for inventory management. The schema has a table 'inventory' with columns: product_id, warehouse_id, quantity, last_updated. The table contains over 100 million rows. The application frequently runs aggregate queries to compute total quantity of a product across all warehouses (e.g., SELECT SUM(quantity) FROM inventory WHERE product_id = ?). These queries are slow, taking tens of seconds. The team tries a covering index on (product_id, quantity) but sees little improvement because they still need to scan many rows. They need to redesign the schema to improve aggregation performance. What is the best approach?

Accepted Answer

Create a summary table 'product_totals' with columns product_id and total_quantity, and use triggers to keep it updated on INSERT/UPDATE/DELETE in inventory.. Creating a summary table 'product_totals' that pre-aggregates total quantity per product, updated via triggers on INSERT, UPDATE, DELETE in the inventory table, dramatically speeds up aggregate queries by avoiding full scans of the large table. Option A (covering index on product_id, quantity) was tried and still requires scanning many rows to sum quantities, so it does not solve the performance issue. Option B (migrating to Cloud Spanner) is an unnecessary and costly migration. Option C (using BigQuery as a read replica) adds latency and complexity without being a schema redesign. Thus, D is the best approach.

Answer

Add a covering index on (product_id, quantity).

Answer

Migrate the inventory table to Cloud Spanner and use interleaved indexes.

Answer

Use BigQuery as a read replica and query there.

Question 17

A team has a service with an SLO of 99.5% uptime over 30 days. They track error budget and want to alert when the error budget is almost exhausted. What is their total error budget in minutes per month?

Accepted Answer

216 minutes. Error budget = 100% - SLO = 0.5%. Over 30 days (720 hours), 0.5% of 720 hours = 3.6 hours = 216 minutes.

Answer

360 minutes

Answer

43.2 minutes

Answer

72 minutes

Question 18

A company runs a critical application on Cloud SQL for PostgreSQL. The database engineer needs to ensure that if the primary instance fails, a standby instance in a different region can take over with minimal data loss. Which configuration should the Database Engineer implement?

Accepted Answer

Create a cross-region replica with asynchronous replication and manually promote it during a disaster.. Cloud SQL for PostgreSQL supports cross-region replicas with asynchronous replication, which allows a standby instance in a different region to be promoted manually during a disaster. This minimizes data loss by replicating changes asynchronously, though some transactions may be lost if the primary fails before replication completes. Automatic failover is not supported for cross-region replicas in Cloud SQL, so manual promotion is required.

Answer

Set up a second Cloud SQL instance and configure application-level dual-writes to both instances.

Answer

Configure high availability (HA) within the same region using a regional persistent disk.

Answer

Create a cross-region replica with synchronous replication and enable automatic failover.

Question 19

A company is migrating a monolithic application to Google Cloud and needs to modernize the database layer. The application has both OLTP (high-volume transactions) and OLAP (complex reporting) workloads. The team wants to use a single database to simplify operations but with high performance for both. Which TWO Google Cloud database services support hybrid transactional/analytical processing (HTAP)? (Choose two.)

Accepted Answer

AlloyDB. AlloyDB with columnar engine and Spanner with analytics interface support HTAP. Cloud SQL and Bigtable do not natively support HTAP. BigQuery is purely analytical.

Answer

Cloud Bigtable

Answer

BigQuery

Answer

Cloud SQL

Question 20

An engineer is using Database Migration Service to migrate a MySQL database to Cloud SQL. They need to set up a source connection profile. Which TWO methods can the engineer use to allow DMS to connect to the source database? (Choose 2 correct answers.)

Accepted Answer

VPC peering for private IP. VPC peering allows DMS to connect to a source database using private IP addresses without traversing the public internet, which is a secure and recommended method for connectivity within Google Cloud. This method establishes a direct, low-latency connection between the DMS service's VPC and the source database's VPC, provided the IP ranges do not overlap. It is particularly suitable when the source database is hosted in a different VPC or on-premises via a VPN-connected VPC.

Answer

No public endpoint

Answer

Cloud SQL Auth Proxy

Answer

Cloud VPN