PCDE Practice Exam 57 — 20 Questions

Question 1

A team is using Cloud Spanner and wants to reduce latency for queries that filter on a column that is not part of the primary key. Which feature should they use?

Accepted Answer

Secondary index. Secondary indexes allow efficient queries on non-primary-key columns.

Answer

Storing index

Answer

Interleaved table

Answer

Query explain plan

Question 2

A company is migrating an on-premises PostgreSQL database to Cloud SQL. The database is 2 TB and must have minimal downtime during migration. Which migration strategy should the Database Engineer recommend?

Accepted Answer

Use Database Migration Service to set up continuous replication from the on-premises database to Cloud SQL, then perform a minimal-downtime cutover.. The Database Migration Service (DMS) supports continuous replication from an on-premises PostgreSQL database to Cloud SQL using logical replication (pglogical or native PostgreSQL logical replication). This allows the source database to remain operational during the migration, and the cutover to Cloud SQL can be performed with minimal downtime, typically seconds to minutes, meeting the 2 TB size and low-downtime requirements.

Answer

Export the database using pg_dump and import into Cloud SQL using pg_restore during a maintenance window.

Answer

Set up PostgreSQL on Compute Engine and replicate data, then switch over to Cloud SQL.

Answer

Use Cloud SQL's built-in replication feature to connect directly to the on-premises database.

Question 3

A team is running a critical application on Cloud SQL (PostgreSQL) that serves both OLTP transactions and read-heavy reporting. The reporting queries are causing performance degradation for the OLTP traffic. The team needs to optimize performance with minimal application changes. Which THREE actions should they take? (Choose THREE)

Accepted Answer

Enable slow query logging and use EXPLAIN ANALYZE to optimize queries. Read replicas offload reporting queries from the primary instance. Connection pooling with PgBouncer reduces connection overhead and improves throughput. Slow query logging and EXPLAIN ANALYZE help identify and tune problematic queries, which is essential for performance. VCPUs and memory should be right-sized based on workload, not arbitrarily increased. Cloud SQL Auth Proxy is for secure connections, not performance.

Answer

Route all traffic through the Cloud SQL Auth Proxy for better performance

Answer

Increase the number of vCPUs and memory on the primary instance

Question 4

A company uses Cloud Spanner for a global application. They need to capture real-time changes to certain tables for downstream processing. Which two services or features can be used together to achieve this? (Choose TWO.)

Accepted Answer

Spanner change streams. Spanner change streams capture transactional changes in near real-time. Dataflow can read from change streams and stream the changes to downstream systems like Pub/Sub, BigQuery, or Cloud Storage.

Answer

Cloud Pub/Sub Lite

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Functions

Question 5

A data engineer runs a BigQuery query that joins a large fact table with a small lookup table. The query processes 1 TB of data and takes 30 seconds. The engineer wants to reduce the amount of data processed. Which optimization technique is MOST effective?

Accepted Answer

Use a WITH clause to pre-filter the fact table before joining.. Pre-filtering the fact table with a WITH clause (CTE) reduces the amount of data scanned and processed before the join occurs. Since the fact table is large (1 TB), applying filters early minimizes the data shuffled and joined, directly reducing the bytes billed in BigQuery. This is a form of predicate pushdown that leverages BigQuery's columnar storage and dynamic query optimization.

Answer

Increase the number of slots available for the query.

Answer

Cluster the lookup table on the join key.

Answer

Materialize the lookup table as a separate table with the same data.

Question 6

A developer is setting up a Memorystore for Redis instance and needs to restrict access to only a specific Compute Engine VM in the same VPC network. Which configuration should they use?

Accepted Answer

Configure the Memorystore instance with a firewall rule that allows only the VM's internal IP. Memorystore for Redis uses Private Service Access and requires authorizing a VPC network to access the instance. After authorizing the VPC network, you can restrict access to a specific Compute Engine VM by applying a firewall rule in that VPC that allows traffic from only that VM's internal IP to the Memorystore instance. This is the most granular control available without using additional authentication like AUTH.

Answer

Use Cloud Armor to whitelist the VM's external IP

Answer

Use the AUTH password and share it only with the VM

Answer

Place the Redis instance in a separate VPC and peer only with the VM's VPC

Question 7

A company has a Cloud Bigtable cluster using HDD storage. They are migrating to a workload that requires lower latency, so they need to switch to SSD storage. How should they accomplish this?

Accepted Answer

Create a new Bigtable cluster with SSD storage in the same instance, replicate data, then delete the old cluster.. 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.

Answer

Add more nodes to the existing cluster to improve latency.

Answer

Export data to Avro files, create a new SSD cluster, and import the data.

Answer

Modify the existing cluster's storage type to SSD using the Cloud Console.

Question 8

A team is migrating an Oracle database to Cloud Spanner. They have a large table with an auto-increment primary key. Which key design strategy should they use to avoid hot spots?

Accepted Answer

Use a composite primary key with a hash prefix of the auto-increment value. Using a composite primary key with a hash prefix of the auto-increment value distributes writes across multiple splits in Cloud Spanner. Cloud Spanner uses range-based sharding; a monotonically increasing primary key (like an auto-increment value) would cause all new writes to land on the same split, creating a hot spot. By hashing the auto-increment value and prepending it to the primary key, you randomize the key distribution, ensuring writes are spread evenly across the table's splits.

Answer

Use the auto-increment value as the primary key

Answer

Use interleaved tables to colocate related data

Answer

Use UUID as the primary key

Question 9

A company is migrating a large Oracle Data Warehouse to BigQuery. The source schema includes many partitioned tables and materialized views. Which THREE considerations are important when designing the BigQuery schema?

Accepted Answer

Clustering can be used to improve query performance on frequently filtered columns.. Options A, B, and D are correct. Clustering (A) improves query performance on frequently filtered columns by colocating data, similar to Oracle's partitioning but without manual index management. Partitioning in BigQuery (B) supports DATE, TIMESTAMP, or INTEGER columns, which is crucial for migrating Oracle's partitioned tables. Materialized views in BigQuery (D) are automatically refreshed when base tables change, aligning with Oracle's materialized view refresh mechanisms but with less administrative overhead. Options C and E are incorrect because BigQuery does not require explicit indexes and does not enforce unique or foreign key constraints, reflecting its schema-on-read approach.

Answer

BigQuery requires explicit indexes on columns used in WHERE clauses.

Answer

BigQuery supports unique constraints and foreign keys for data integrity.

Question 10

Your team uses Cloud Monitoring to alert on high CPU usage for Compute Engine instances. You want to be notified via email and Slack. You have created a notification channel for email. What must you do to also notify Slack?

Accepted Answer

Create a Cloud Pub/Sub notification channel and set up a subscription to post messages to Slack via webhook.. While Cloud Monitoring supports a direct Slack notification channel via webhook, it is also possible to use Cloud Pub/Sub to send notifications to Slack. This involves creating a Pub/Sub notification channel and configuring a subscription to post messages to Slack via webhook. Option B correctly describes this alternative method.

Answer

Create a second email notification channel with the Slack email integration address.

Answer

Use Cloud Logging to forward logs to Slack directly.

Answer

Install the Stackdriver Slack app and configure it to receive alerts via Cloud Monitoring API.

Question 11

An organization uses Artifact Registry to store Docker images. They want to enforce that only images that have passed vulnerability scanning and are signed can be deployed to GKE. Which two services should they use together?

Accepted Answer

Binary Authorization and Container Scanning API. Binary Authorization enforces that only signed and verified container images can be deployed to GKE, while the Container Scanning API (now part of Artifact Analysis) performs vulnerability scanning on images stored in Artifact Registry. Together, they ensure that only images that have passed vulnerability scanning and are cryptographically signed can be deployed, meeting the organization's requirements.

Answer

Cloud Deploy and Cloud Run

Answer

Cloud Build and Artifact Registry

Answer

Security Command Center and Cloud Asset Inventory

Question 12

An organization is deploying a containerized application to Cloud Run. They want to gradually roll out a new revision to 10% of traffic, monitor for errors, and then fully promote if stable. Which Cloud Run feature should they use?

Accepted Answer

Use the --to-revisions flag with gcloud run deploy to assign traffic percentages.. The `--to-revisions` flag with `gcloud run deploy` allows you to specify traffic percentages for revisions in a single Cloud Run service. This enables a gradual rollout by sending 10% of traffic to the new revision, monitoring for errors, and then promoting it to 100% without deploying a separate service or using external load balancers.

Answer

Deploy two separate Cloud Run services and use a global load balancer.

Answer

Use Cloud Deploy with a canary strategy.

Answer

Use Cloud Load Balancing with a backend bucket to split traffic.

Question 13

An organization uses GitOps with Config Sync to manage multiple GKE clusters. They want to ensure that any change to the Git repository is automatically applied to the clusters, and that no manual kubectl apply commands are used. Which TWO resources should they configure?

Accepted Answer

RepoSync resource pointing to the Git repository. A RepoSync resource is used in Config Sync to synchronize a non-root namespace-level repository with the cluster, ensuring that any changes pushed to the Git repository are automatically applied without manual kubectl commands. Option D is correct because a RootSync resource is used to synchronize the root-level configuration (e.g., cluster-scoped resources) from a Git repository, providing the same automated GitOps workflow. Together, they cover both namespace and cluster-scoped resources, fulfilling the requirement of no manual kubectl apply.

Answer

A Cloud Build trigger that runs kubectl apply on push

Answer

A Kustomize overlay for each cluster

Answer

An Argo CD Application resource

Question 14

A Cloud Database Engineer is designing a schema for an e-commerce application on Cloud Spanner. The application requires high read throughput for product queries by category and price range, and must support global scale with strong consistency. The team is considering primary key design and interleaved tables. Which TWO design considerations should the engineer apply? (Choose TWO.)

Accepted Answer

Use a primary key that starts with the category column to colocate product data for efficient queries by category.. Colocating product data by category in the primary key enables efficient range scans on category and price, as Cloud Spanner stores rows in sorted order by primary key. This design minimizes cross-node fan-out for queries filtering by category, directly supporting high read throughput at global scale with strong consistency.

Answer

Define secondary indexes on price and category columns to support range queries without considering the primary key design.

Answer

Use a timestamp as the first part of the primary key to enable time-based partitioning and efficient range scans.

Answer

Define interleaved tables for all related entities, even if they are not always accessed together, to reduce joins.

Question 15

A company uses BigQuery for BI dashboards. Users report that queries on the sales table take longer than expected. The table contains daily transaction data and is not partitioned. Which action will most improve query performance while minimizing cost?

Accepted Answer

Partition the table by the transaction date column. Partitioning the table by the transaction date column allows BigQuery to perform partition pruning, scanning only the relevant date ranges instead of the entire table. This directly reduces the amount of data read, improving query performance and lowering costs since BigQuery charges based on the data scanned.

Answer

Increase the BigQuery reservation slot count

Answer

Cluster the table by the transaction date column

Answer

Denormalize the table by including dimension attributes

Question 16

A company stores log files in Cloud Storage buckets. The logs are accessed frequently for the first 30 days, then rarely for the next 6 months, after which they must be archived for 7 years. They want to minimize storage costs. Which two actions should they take? (Choose two.)

Accepted Answer

Set a lifecycle rule to change storage class to Nearline after 30 days.. Nearline storage is optimized for data accessed less than once a month, making it cost-effective for logs that are rarely accessed after 30 days. Option E is correct because Archive storage is the lowest-cost option for data that must be retained for 7 years with infrequent access, meeting the archival requirement while minimizing costs.

Answer

Keep objects in Standard storage class for the entire retention period.

Answer

Set a lifecycle rule to delete the objects after 30 days.

Answer

Enable Autoclass to automatically transition objects to colder storage classes.

Question 17

You manage a Cloud SQL for PostgreSQL instance that is experiencing high read latency. You have already tuned the buffer cache and queries. Which THREE actions can further reduce read latency? (Choose three.)

Accepted Answer

Increase the number of vCPUs on the primary instance.. 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.

Answer

Enable the PostgreSQL slow query log and analyze it.

Answer

Use connection pooling to reduce the number of open connections.

Question 18

A company is designing a Cloud Bigtable schema for time-series data. The data is written by millions of devices every second. The query patterns are: (1) retrieve the most recent reading for a specific device, (2) retrieve all readings for a device in a time range. Which TWO row key design techniques should the team use to optimize for these patterns? (Choose two.)

Accepted Answer

Use field promotion: put device_id as the first component of the row key. 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.

Answer

Use salting (hash prefix) to distribute writes

Answer

Use a monotonically increasing timestamp as the first part of the row key

Answer

Store the entire row as a single column family to reduce overhead

Question 19

Your organization uses Cloud SQL for MySQL as the backend for a content management system. The Operations team reports that the database performance degrades every weekday morning at 9 AM, coinciding with a batch job that updates thousands of rows. You need to minimize the impact on end users. What is the best approach?

Accepted Answer

Break the batch job into smaller transactions with a delay between batches.. Breaking the batch job into smaller transactions with a delay between batches reduces lock contention and transaction log pressure on the primary Cloud SQL instance. This prevents a single large transaction from blocking concurrent user queries, thereby minimizing performance degradation for end users during the batch window.

Answer

Increase the Cloud SQL instance memory and CPU before the job starts.

Answer

Disable binary logging during the batch window.

Answer

Move batch reads to a read replica.

Question 20

A company uses Firestore to power a live sports score app. Scores are updated frequently, and many clients listen to real-time updates on specific games. Which two design decisions will minimize the number of reads and reduce costs? (Choose two.)

Accepted Answer

Store an aggregate score summary document per game and listen to it. Storing an aggregate score summary document per game and listening to it reduces reads by consolidating frequently updated fields into a single document, minimizing the number of document reads per update. Option C is correct because using a separate document per game with listeners filtered by game ID ensures each client only listens to the specific game they care about, avoiding unnecessary reads from irrelevant documents.

Answer

Use a collection group query to listen to all games at once

Answer

Use a single document for all games with nested fields

Answer

Use a subcollection of periods (quarters) to spread writes