PCD Practice Test 31 — 15 Questions

Question 1

A company runs a global e-commerce site on Cloud Spanner. They notice that some queries on the Orders table by customer_id are slow because customer_id is not part of the primary key (order_id). They want to avoid full table scans. Which TWO actions will improve query performance? (Choose two)

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

Accepted Answer

Create a secondary index on customer_id. Option A is correct because creating a secondary index on customer_id lets Cloud Spanner satisfy the query with an index scan instead of a full table scan, since the index is keyed on customer_id and stores the base table's primary key for lookups. Option E is correct because adding a STORING clause to that secondary index includes frequently accessed columns directly in the index, so the query can be answered entirely from the index without extra base-table lookups, reducing latency. Option B is not appropriate because adding nodes increases overall throughput and capacity but does not change the query plan, so a full table scan remains a full table scan. Option C is not appropriate because changing the primary key to customer_id first would optimize lookups by customer_id but would break the existing order_id-based access pattern and requires recreating the table, which is not the intended targeted fix. Option D is not appropriate because interleaving is a parent-child storage locality mechanism for related tables, not a way to avoid scanning Orders by a non-key column.

Answer

Increase the number of nodes to improve scan speed

Answer

Re-create the table with customer_id as the first part of the primary key

Answer

Use interleaved tables to store customer data with orders

Question 2

Which Google Cloud database service should be used to implement a low-latency session store for a web application that requires pub/sub capabilities and rate limiting?

Accepted Answer

Memorystore for Redis. Memorystore for Redis is the correct choice because it provides sub-millisecond latency, native pub/sub messaging, and atomic operations suitable for rate limiting (e.g., INCR with expiry, Lua scripts). These features map directly to the session store, pub/sub, and rate-limiting requirements.

Answer

Firestore

Answer

Cloud Spanner

Answer

Memorystore for Memcached

Question 3

An engineer is monitoring Cloud Bigtable performance and notices high read latency. They suspect a hot spot issue. Which tool should they use to identify the hot spot?

Accepted Answer

Key Visualiser. The Key Visualiser is the Bigtable tool specifically designed to analyze row-key distribution and detect hot spots. It renders a heatmap of key ranges versus traffic, making it immediately obvious which row-key ranges are receiving disproportionate load. This is the canonical tool for diagnosing hot-spotting caused by poorly designed row keys.

Answer

The cbt command-line tool

Answer

Bigtable Studio

Answer

Cloud Monitoring (formerly Stackdriver)

Question 4

Which TWO of the following are valid ways to export Cloud Logging logs to BigQuery?

Accepted Answer

Use a Dataflow pipeline to stream logs from Pub/Sub to BigQuery. Option C is correct because Cloud Logging supports log sinks whose destination can be a BigQuery dataset; when you create a sink with a BigQuery destination, Logging routes matching log entries into BigQuery tables (with the log name and date determining the table). Option B is correct because a common and supported pattern is to create a log sink to Pub/Sub and then run a Dataflow streaming pipeline that reads from that Pub/Sub topic and writes to BigQuery, which is useful for transformations or custom routing. Option A is incorrect because the Logging API is used to write and read log entries (entries.write/entries.list), not to export logs directly into BigQuery. Option D is incorrect because BigQuery Data Transfer Service supports scheduled transfers from sources like Google Ads, Amazon S3, and Campaign Manager, not Cloud Logging logs. Option E is incorrect because Cloud Monitoring is for metrics, dashboards, and alerting; it does not provide a mechanism to export Cloud Logging logs to BigQuery.

Answer

Use the Logging API to write logs directly to BigQuery

Answer

Use the BigQuery Data Transfer Service for Cloud Logging

Answer

Use Cloud Monitoring to send logs to BigQuery

Question 5

To monitor Cloud SQL query performance, which metric should an engineer examine to see the rate of queries being executed?

Accepted Answer

database/queries. The metric database/queries in Cloud Monitoring (for Cloud SQL) reports the rate of queries executed against the instance, which is exactly what the engineer needs to monitor query throughput. It is a cumulative counter that can be viewed as a rate (queries per second) and is available for MySQL, PostgreSQL, and SQL Server Cloud SQL instances. This directly answers the requirement to see the rate of queries being executed.

Answer

database/disk/bytes_used

Answer

database/memory/utilization

Answer

database/cpu/utilization

Question 6

Drag and drop the steps to set up a Cloud SQL instance with a private IP in the correct order.

Accepted Answer

Create VPC network, Allocate IP range for private services access, Create private services access connection, Create Cloud SQL instance with private IP. Setting up private IP Cloud SQL requires a VPC, private services access, and creating the instance with private IP.

Answer

Create VPC network, Create private services access connection, Allocate IP range for private services access, Create Cloud SQL instance with private IP

Answer

Allocate IP range for private services access, Create VPC network, Create private services access connection, Create Cloud SQL instance with private IP

Answer

Create Cloud SQL instance with private IP, Create VPC network, Allocate IP range for private services access, Create private services access connection

Question 7

You need to continuously replicate change data from an on-premises Oracle database to BigQuery for real-time analytics. Which Google Cloud service is designed specifically for this purpose?

Accepted Answer

Datastream. Datastream is a Google Cloud serverless change data capture (CDC) and replication service specifically designed to continuously replicate data from sources like Oracle databases to BigQuery. It handles schema drift, supports log-based CDC, and provides low-latency streaming, making it the correct choice for real-time analytics.

Answer

Database Migration Service (DMS)

Answer

Cloud Dataproc

Answer

Dataflow

Question 8

An organization is migrating a MySQL database to Cloud SQL for MySQL. The source database uses the 'utf8' character set. To support emoji characters, what should they do before migration?

Accepted Answer

Change the character set to 'utf8mb4' on the source database before migration.. MySQL's 'utf8' character set does not support 4-byte Unicode characters like emojis. Cloud SQL for MySQL supports 'utf8mb4', which is the correct charset for full Unicode support.

Answer

No change needed; 'utf8' supports emoji in Cloud SQL.

Answer

Use 'latin1' character set instead for better performance.

Answer

Enable the 'character_set_emoji' flag in Cloud SQL.

Question 9

A company is migrating a PostgreSQL application to Cloud SQL. They require automatic failover in under 60 seconds if the primary instance fails, and they want to minimize write latency. Which configuration should they choose?

Accepted Answer

Create a Cloud SQL for PostgreSQL instance with high availability (HA) configuration.. Cloud SQL for PostgreSQL high availability (HA) configuration uses a regional instance with a standby replica in a different zone, and failover is automatic — typically completing in under 60 seconds — with the same connection endpoint preserved via a static IP. Because the standby is in the same region, write latency to the primary remains low, satisfying both the failover and latency requirements.

Answer

Create a zonal Cloud SQL instance and set up a read replica in a different zone.

Answer

Create a Cloud SQL for PostgreSQL instance with cross-region replication.

Answer

Configure a Cloud Spanner regional instance with multi-zone nodes.

Question 10

A developer wants to automatically capture CPU and memory profiles from a production application running on Compute Engine to identify performance bottlenecks. Which Google Cloud tool should they use?

Accepted Answer

Cloud Profiler. Cloud Profiler is the correct tool because it continuously gathers CPU and memory usage data from production applications with minimal overhead, using statistical sampling to identify which functions or methods consume the most resources. This allows developers to pinpoint performance bottlenecks without adding significant latency or requiring code changes.

Answer

Cloud Logging

Answer

Cloud Monitoring

Answer

Cloud Trace

Question 11

A corporation needs to back up its Cloud Spanner database for disaster recovery. They require the ability to restore to a specific point in time within the last 7 days. What backup strategy should they implement?

Accepted Answer

Use Spanner's built-in point-in-time recovery (PITR) which retains versions for 7 days.. Cloud Spanner's built-in point-in-time recovery (PITR) retains versioned data for a configurable window of up to 7 days, allowing restoration to any timestamp within that window. This is the native, lowest-overhead mechanism for the stated requirement. No external tooling or export pipeline is needed.

Answer

Use Cloud Scheduler to run incremental backups daily.

Answer

Enable cross-region replicas and restore from the replica.

Answer

Export the database to Cloud Storage every hour using Dataflow.

Question 12

A data engineering team wants to implement version-controlled schema migrations for their PostgreSQL database on Cloud SQL. They need to integrate with their CI/CD pipeline. Which TWO tools should they consider? (Choose 2 options.)

Accepted Answer

Liquibase. Liquibase (B) is correct because it is a dedicated database schema migration tool that tracks changesets in a changelog table and supports PostgreSQL on Cloud SQL, making it suitable for version-controlled migrations triggered from a CI/CD pipeline. Flyway (D) is also correct because it is a schema migration tool that applies versioned SQL migrations in order and records them in a schema history table, and it integrates easily with CI/CD systems for PostgreSQL. Cloud Build (A) is a CI/CD build service, not a schema migration tool, so it orchestrates pipelines rather than managing migrations itself. Dataform (C) focuses on SQL-based data transformation and orchestration in BigQuery, not PostgreSQL schema migration. Terraform (E) is infrastructure as code for provisioning resources, not for version-controlled database schema migrations.

Answer

Cloud Build

Answer

Dataform

Answer

Terraform

Question 13

A team is designing a Cloud Spanner schema for a hierarchical product catalog with categories and products. They want to ensure that products are stored in the same split as their parent category for low-latency queries. Which feature should they use?

Accepted Answer

Use interleaved tables with the products table interleaved in the categories table. Interleaved tables in Cloud Spanner physically co-locate child rows with their parent row in the same split, which ensures that a parent and its children are stored together and can be retrieved with low latency using a single lookup. Interleaving the products table in the categories table achieves this for the hierarchical product catalog.

Answer

Use a composite primary key with category ID as the first part, but no interleaving

Answer

Use Cloud Spanner's commit timestamp feature

Answer

Define a global secondary index on the category ID

Question 14

A company is planning to migrate from Snowflake to BigQuery. They have existing complex SQL transformations using Snowflake-specific functions like LISTAGG and PIVOT. What is the best approach to handle these differences?

Accepted Answer

Rewrite the SQL queries to use BigQuery equivalent functions and syntax.. Snowflake and BigQuery have different SQL dialects. The best approach is to use a migration assessment tool to identify incompatible syntax, rewrite queries to BigQuery-compatible syntax (e.g., use STRING_AGG instead of LISTAGG), and test thoroughly. BigQuery does not support PIVOT natively; use conditional aggregation.

Answer

Use BigQuery Data Transfer Service to automatically convert Snowflake functions.

Answer

Keep Snowflake for complex queries and use BigQuery only for simple analytics.

Answer

Use Cloud Spanner as an intermediary to translate queries.

Question 15

A company is migrating from MySQL 5.7 to Cloud SQL for MySQL 8.0. After migration, a legacy application that uses the 'GROUP BY' clause with implicit sorting stops working correctly. What is the most likely cause?

Accepted Answer

MySQL 8.0 removed the implicit sorting of GROUP BY results.. MySQL 8.0 removed the implicit sorting of GROUP BY that was present in MySQL 5.7. Applications relying on this behavior must explicitly use ORDER BY to guarantee sort order.

Answer

MySQL 8.0 changed the default collation, affecting string comparison.

Answer

MySQL 8.0 no longer supports the 'GROUP BY' clause.

Answer

The 'ONLY_FULL_GROUP_BY' SQL mode is enabled by default.