Google Professional Cloud Database Engineer (PCDE) — Questions 1–75

Enabling point-in-time recovery (PITR) in Cloud Spanner allows you to recover data to any point within the retention period (default 7 days), which is essential for granular disaster recovery against logical errors or accidental data changes. This complements backup strategies by providing fine-grained restore capabilities beyond full backups.

Cloud Bigtable does not support in-place conversion of storage type from HDD to SSD. The proper migration path is to create a new cluster with SSD storage within the same instance, enable replication to keep data synchronized, and then delete the old HDD cluster. This approach minimizes downtime and ensures data consistency.

The `secretEnv` field in a Cloud Build step allows you to inject a secret from Secret Manager into the build environment as an environment variable, without the secret value being written to build logs. This ensures the secret is available to the step at runtime but never exposed in log output.

Key Visualiser helps identify hot spots by showing row key distribution. Prefixing the row key with a field that distributes writes (like a hash prefix) is a common mitigation strategy.

First create KMS key, grant access, then create instance with CMEK, verify, test.

The Firestore security rules shown grant read access to all authenticated users without any document-level or collection-level restrictions. The rule `match /databases/{database}/documents { allow read: if request.auth != null; }` applies to all documents in the database, meaning any authenticated user can read every document, including those they should not have access to. This violates the principle of least privilege and can lead to unauthorized data exposure.

The `kustomize build staging/` command processes the Kustomize overlay located in the `staging/` directory, which includes a `kustomization.yaml` file that references the base overlay and applies environment-specific patches, configMapGenerator entries, and namespace settings. This generates the final Kubernetes manifests for the staging environment without applying them to a cluster. The `kustomize` CLI tool is the standard way to render Kustomize overlays, and it is the correct command for generating manifests as part of a CI/CD pipeline.

To correlate logs with traces, you include the trace field (trace ID) and optionally spanId. The severity field is for log level, not correlation.

Action items should be specific, have an owner, and a due date. Assigning blame or vague plans are not appropriate. The items should address systemic issues.

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.

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.

Cloud Spanner supports online schema changes that are non-blocking. Adding a column with a DEFAULT value is an online operation that does not block reads or writes, and does not require copying data. The new column is added with the default value for existing rows at read time, not by backfilling.

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.

Cloud SQL does not allow superuser access; --no-owner and --no-acl prevent errors from missing roles or privileges.

The `push` trigger with a branch filter for `main` ensures builds are triggered on pushes to the main branch.

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`.

AlloyDB is a fully managed PostgreSQL-compatible database designed for hybrid transactional and analytical processing (HTAP). It includes a built-in columnar engine that accelerates analytical queries on transactional data without separate ETL or duplication.

Increasing the number of vCPUs on the primary instance (Option C) can reduce read latency by providing more CPU resources to process queries, especially if the current bottleneck is CPU-bound operations like query parsing, sorting, or aggregation. In Cloud SQL for PostgreSQL, scaling vCPUs also increases available memory and I/O bandwidth proportionally, which can alleviate contention and improve throughput for read-heavy workloads.

Blameless postmortems focus on identifying contributing factors and systemic issues, not individual blame. The 5 Whys technique helps uncover root causes.

A composite index on chat room ID and timestamp is essential for efficiently querying messages in order within a specific chat room. Firestore requires composite indexes for queries that combine equality filters on one field (chat room ID) with an order on another (timestamp). Option E is correct because storing messages as documents in a subcollection under each chat room document is the recommended pattern for Firestore, as it allows scalable, independent message collections per room without hitting the 1 MiB document size limit.

Config Sync is the correct choice because it is the native GitOps agent within Google Cloud's Anthos platform, designed specifically to continuously reconcile the state of a GKE cluster with Kubernetes manifests stored in a Git repository. It automatically detects changes in the configured source of truth and applies them to the cluster, ensuring drift is corrected without manual intervention.

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.

The proportion of requests that complete within a defined threshold (e.g., 200ms) directly measures user-perceived performance. Other options are less representative.

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.

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.

Database Migration Service (DMS) with continuous replication is the best approach because it supports minimal-downtime migrations from on-premises PostgreSQL to Cloud SQL. DMS uses change data capture (CDC) to replicate ongoing writes while the initial data load completes, then performs a cutover with only seconds of downtime. This handles the 2 TB size and high write workload without requiring manual export/import or schema changes.

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.

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.

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.

Schema Conversion Tool (SCTS) is correct because it automates the conversion of Teradata-specific DDL, BTEQ scripts, and stored procedures into BigQuery-compatible SQL. This is critical for migrating 20 TB of data and complex BTEQ scripts without manual rewriting, reducing errors and accelerating the migration timeline.

Valid approaches: (1) Use the OpenTelemetry .NET SDK to instrument the application and export metrics to Cloud Monitoring via the Google Cloud exporter. (2) Use the Google.Cloud.Monitoring.V3 client library to write custom metrics directly via the Monitoring API. The other options are invalid: Stackdriver client is deprecated, StatsD is not directly supported by Cloud Monitoring, and gcloud commands are for administration, not application-level metric writing.

The volatile-lru policy evicts keys with an expire set (TTL) using the LRU algorithm. For a session store, sessions typically have a TTL, so volatile-lru is appropriate. allkeys-lru would evict any key regardless of TTL, which might remove important keys. noeviction would cause write errors when memory is full. volatile-ttl evicts based on TTL, not LRU.

Cloud Bigtable is a fully managed, scalable NoSQL database designed for large analytical and operational workloads, handling millions of writes per second with consistent low-latency reads. It uses a distributed, replicated SSTable storage engine and is optimized for time-series data, making it ideal for high-throughput sensor ingestion.

Hot spotting in Cloud Bigtable is typically caused by a suboptimal row key design that concentrates read/write traffic on a small number of tablets. Redesigning the row key scheme to distribute the workload—for example, by adding a hash prefix or using a more granular key—spreads requests across multiple tablets, alleviating the hotspot. Key Visualizer explicitly identifies the skewed row keys, guiding the redesign effort.

Subcollections in Cloud Firestore allow you to organize related data hierarchically within a document, enabling efficient queries and scalability without hitting the 1 MiB document size limit. This structure is ideal for data like user posts or product reviews, where each subcollection can grow independently.

Shuffling occurs when data must be redistributed across nodes during joins, often because the join keys are not co-located. Clustering the fact table on the dimension join keys physically co-locates rows with the same join key values, minimizing data movement during the join. This is a direct optimization for shuffle-heavy workloads in distributed SQL engines like Spark SQL or Hive.

Key Visualiser is a Cloud Bigtable tool that visualises read/write traffic across the row key space, helping identify hot spots (rows with disproportionate traffic). Cloud Monitoring provides overall metrics but not row-level detail. Cloud Logging logs queries but not aggregated heat maps. cbt is the command-line tool for administrative tasks but does not provide visualisation.

Connection pooling reduces the overhead of establishing new database connections, which can consume significant CPU and memory resources on the primary instance. By reusing existing connections, the primary instance can handle more read requests without needing additional read replicas to offload the connection management load. This directly reduces the number of replicas required for a read-heavy workload.

An incident command system (ICS) establishes a clear hierarchy with an incident commander who coordinates teams, assigns roles (e.g., operations lead, communications lead), and ensures focused response.

A CUMULATIVE metric measures a value that increases over time, such as a count of requests. It is appropriate when you want to know the total count up to a point in time, which can be queried for any time range (e.g., last 24 hours) by subtracting the start value from the end value. GAUGE represents a snapshot value that can go up and down, DELTA represents a rate per time interval but does not accumulate.

SCTS can convert Teradata DDL (e.g., CREATE TABLE) to BigQuery SQL, and also helps convert BTEQ scripts (Teradata scripting language) to BigQuery SQL. However, it does not handle data migration or performance tuning.

Commit latency is a critical metric for Cloud Spanner because it directly measures the time taken to commit a transaction, which reflects the database's ability to handle write operations efficiently. High commit latency can indicate contention, node overload, or suboptimal schema design, making it essential for performance evaluation.

Cloud Build's default service account is automatically granted the Artifact Registry Reader role (roles/artifactregistry.reader) when the Cloud Build API is enabled in a project. This means any build step that references an image from Artifact Registry can pull it without explicit authentication, as Cloud Build uses its own service account credentials to authenticate the pull request to the registry.

Sustained CPU utilization above 80% indicates the instance is compute-bound, which can lead to queuing and increased latency for write operations. In Cloud SQL for MySQL, high CPU usage often means the instance lacks sufficient vCPUs to handle the write throughput, necessitating a larger tier with more CPU cores.

Creating a separate reservation for the dashboard queries with a baseline of 500 slots ensures that the critical BI dashboard always has guaranteed compute capacity, preventing starvation by ad-hoc queries. Using a low-priority job queue for ad-hoc queries allows them to use any remaining idle slots without interfering with the dashboard's reserved slots. This directly addresses the 100% slot utilization and timeout issue without requiring query rewrites or schedule changes.

Key Visualizer is the correct tool because it is specifically designed to visualize access patterns in Cloud Bigtable and identify hot spots—regions of a table that receive disproportionate read/write traffic. It generates heatmaps of row key access, allowing operators to pinpoint uneven load distribution that causes high latency.

These are key concepts for understanding Spanner's architecture and consistency.

Cloud Deploy supports approval gates: you can define a manual approval step in the delivery pipeline that must be approved before proceeding to the next target.

Conftest uses Rego policies. To enforce consistently, the policies should be version-controlled in a separate repository and the CI/CD pipeline should fetch the latest policy bundle before running tests. Using a centralized policy repo ensures all workspaces are checked against the same rules.

Error Reporting automatically groups errors, shows trends, and integrates with Cloud Logging and Cloud Trace for full context.

Continuous (CDC) migration jobs replicate ongoing changes after the initial dump, allowing minimal downtime during cutover. One-time jobs only perform a full dump and import without ongoing replication.

Cloud Spanner distributes rows across splits based on the primary key prefix. By making `account_id` the first part of the transactions table primary key, all transactions for a given account are co-located, enabling efficient range scans and point lookups without cross-node shuffling.

Cross-region replicas are created using the gcloud command with the --region flag and the --master-instance-name pointing to the primary. After creation, replication must be verified. If needed, the replica can be promoted using gcloud sql instances promote-replica.

Increasing the number of nodes in the Bigtable cluster (option B) directly improves read throughput by distributing the read load across more tablet servers. High read latency during peak hours typically indicates that the existing nodes are saturated, and adding nodes reduces per-node load, lowering overall latency. This is the most direct and effective configuration change for addressing read latency in a single-cluster setup.

High replication lag in Cloud SQL is most commonly caused by a heavy write workload on the primary instance. When the primary processes a large volume of write operations (e.g., INSERT, UPDATE, DELETE), the replica must replay those changes from the binary log, and if the rate of writes exceeds the replica's ability to apply them, lag accumulates. This is a fundamental behavior of MySQL asynchronous replication, where the replica is always slightly behind the primary under load.

Terraform remote state data sources allow one configuration to read outputs from another configuration's state file stored in a backend like GCS.

The resourceLocations constraint is used to restrict where resources can be created. It can be applied at any level of the resource hierarchy.

Cloud Deploy's canary deployment strategy allows you to progressively shift traffic to a new version, which aligns with the requirement for a gradual rollout. Option C is correct because SLO-based verification uses latency metrics (e.g., from Cloud Monitoring) to automatically determine whether the canary should progress, meeting the need for automated progression based on latency. Option E is correct because a manual approval gate can be inserted before the final full rollout, satisfying the requirement for a manual approval step.

Using a reverse timestamp allows the most recent data to be at the beginning of the row key, making scans for the latest reading efficient. Field promotion ensures that device_id comes first in the row key, enabling efficient prefix scans for all readings of a device. Salting is not needed because device_id already distributes writes if there are many devices.

Key Visualizer is the correct tool because it is specifically designed to visualize access patterns in Cloud Spanner and identify hot spots—keys or ranges that receive a disproportionate share of reads or writes. Unlike generic monitoring tools, Key Visualizer provides a heatmap of key-space activity, enabling you to pinpoint and mitigate performance bottlenecks caused by uneven distribution of workload across splits.

To promote a read replica to a standalone instance, you must stop replication on the replica and then promote it. The promotion process converts the replica into an independent primary instance.

The OTel Collector pipeline consists of receivers (data ingestion), processors (optional), and exporters (data output). For this scenario, they need a receiver to accept data and exporters to send to Google Cloud.