PCD Practice Test 5 — 25 Questions

Question 1

A financial services company uses BigQuery for analytics but needs to store transactional data with strong consistency and sub-millisecond latency. They are considering Cloud SQL, Cloud Spanner, and Firestore. Which two services meet all requirements? (Choose two.)

Accepted Answer

Cloud Spanner. Cloud Spanner is correct because it provides strong consistency (external consistency with TrueTime) and sub-millisecond latency for transactional workloads, making it ideal for financial services that require ACID transactions across globally distributed data. It combines the benefits of relational database structure with horizontal scalability, meeting both consistency and latency requirements.

Answer

Cloud SQL

Answer

Bigtable

Answer

Memorystore

Answer

Firestore

Question 2

A data engineer is designing a Bigtable schema for a global ad-tech platform. The workload includes time-series clickstream data with row keys like 'advertiser_id#campaign_id#timestamp'. They are experiencing uneven load distribution. Which TWO row key design changes would best distribute writes across nodes? (Choose two)

Accepted Answer

Swap the order to 'timestamp#advertiser_id#campaign_id'. Option A is correct because placing the timestamp first in the row key ensures that writes are spread across the Bigtable tablet server range. In Bigtable, rows are sorted lexicographically by row key; leading with a monotonically increasing value (like timestamp) causes all new writes to hit a single tablet, creating a hotspot. By swapping to 'timestamp#advertiser_id#campaign_id', writes are still sequential but the timestamp prefix alone does not guarantee perfect distribution—however, combined with salting (Option E), it avoids the original hotspot caused by the advertiser_id prefix.

Answer

Use a single table per advertiser

Answer

Increase the number of Bigtable nodes to 100

Answer

Use a composite key with campaign_id first

Question 3

A mobile application developer needs a serverless NoSQL database that supports offline data synchronization across devices and real-time updates. Which Google Cloud database service should they use?

Accepted Answer

Firestore. Firestore is a serverless, NoSQL document database that provides built-in offline data synchronization across devices and real-time updates via snapshot listeners. It automatically handles conflict resolution and data replication, making it ideal for mobile applications that need to work offline and sync when connectivity is restored.

Answer

Cloud Bigtable

Answer

Cloud SQL

Answer

Cloud Spanner

Question 4

A financial services company is designing a globally distributed ledger system using Cloud Spanner. They need to support high write throughput, avoid hotspots, and ensure fast queries on a customer_id column. The primary key is currently a UUID. Which THREE design changes should they implement? (Choose THREE.)

Accepted Answer

Use a composite primary key with customer_id as the first column. Option A is correct because placing `customer_id` as the first column in a composite primary key enables Spanner to distribute writes across splits based on the leading key column, avoiding hotspots that occur when using a monotonically increasing UUID as the sole primary key. This design also allows fast point-lookups and range scans on `customer_id` without requiring a secondary index, as Spanner can directly locate the row via the primary key.

Answer

Enable point-in-time recovery to reduce storage costs

Answer

Create a secondary index on customer_id

Question 5

A financial services company is implementing a distributed transaction across multiple Cloud Spanner instances in different regions. They require strict ACID compliance. What is the best approach?

Accepted Answer

Use Cloud Spanner with multi-region configuration. Cloud Spanner with a multi-region configuration is the best approach because it provides native support for distributed ACID transactions across regions using synchronous replication and the TrueTime API, ensuring strong consistency, atomicity, and isolation without requiring application-level coordination. This eliminates the complexity and latency of managing distributed transactions manually while meeting strict ACID compliance requirements.

Answer

Use Cloud Pub/Sub to orchestrate eventual consistency

Answer

Use Cloud SQL with cross-region replication and two-phase commit

Answer

Implement a saga pattern with compensating transactions

Question 6

Which TWO Google Cloud services can be used to build a data pipeline that transforms and moves data between different databases? (Select 2)

Accepted Answer

Dataflow. Dataflow is a serverless, fully managed service for executing Apache Beam pipelines, which can read from and write to various databases (e.g., Cloud Spanner, BigQuery, Cloud SQL) while performing transformations in a unified streaming or batch model. It is specifically designed for data movement and transformation between different data sources and sinks, making it a correct choice for building a data pipeline that transforms and moves data between databases.

Answer

Cloud Scheduler

Answer

Memorystore

Answer

Cloud SQL Proxy

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

A company has an application using Cloud SQL MySQL and wants to add a caching layer. They also need the cache to persist data across restarts. Which Memorystore tier should they choose?

Accepted Answer

Memorystore for Redis Standard tier. Memorystore for Redis Standard tier is the correct choice because it provides replication (a replica node) and automatic failover, which enables data persistence across restarts. Unlike the Basic tier, which is a single node with no persistence guarantees, the Standard tier uses append-only file (AOF) persistence to ensure data survives node restarts. This meets the requirement for a caching layer that persists data across restarts.

Answer

Memorystore for Redis Basic tier

Answer

Memorystore for Memcached

Answer

Cloud Bigtable

Question 9

A company is using Datastream to replicate data from an on-premises PostgreSQL database to BigQuery. They encounter high latency in replication. Which THREE steps should they take to improve performance? (Select 3)

Accepted Answer

Enable parallel processing in Datastream source configuration. Option B is correct because enabling parallel processing in the Datastream source configuration allows multiple threads to read from the PostgreSQL database simultaneously, reducing the time to capture and stream changes. This directly addresses high latency by increasing throughput from the source, leveraging PostgreSQL's logical replication slots more efficiently.

Answer

Switch to a larger Datastream machine type

Answer

Reduce the retention period of the PostgreSQL WAL logs

Question 10

A company is migrating an on-premises Oracle database to AlloyDB for PostgreSQL. They need to minimize downtime and ensure data consistency. They plan to use Database Migration Service (DMS). Which migration job type and configuration should they use?

Accepted Answer

Use a continuous migration job that performs an initial full dump followed by CDC replication.. Option D is correct because Database Migration Service (DMS) continuous migration jobs perform an initial full dump to establish a baseline, then use Change Data Capture (CDC) to replicate ongoing changes from the source Oracle database to AlloyDB for PostgreSQL. This approach minimizes downtime by allowing the target to stay synchronized with the source until a cutover, ensuring data consistency without requiring a separate manual import step.

Answer

Use a continuous migration job with CDC only, skipping the initial dump.

Answer

Use a batch migration job that exports Oracle data to CSV files and loads them into AlloyDB.

Answer

Use a one-time full dump migration job and then manually import the dump into AlloyDB.

Question 11

A financial services company needs to maintain a backup of their Bigtable instance in a different geographic region. They want to restore the backup quickly in case of a regional failure. What should they do?

Accepted Answer

Create a Bigtable backup and restore it to a cluster in another region.. Bigtable backups are designed for exactly this use case: they are full, consistent copies of the table schema and data that can be restored to a different cluster in another region. This approach provides a fast, managed restore without the overhead of maintaining a hot standby or the complexity of export/import pipelines. Option C directly uses the Bigtable backup and restore feature, which is the recommended method for cross-region disaster recovery.

Answer

Set up a hot standby Bigtable cluster in another region using replication.

Answer

Use Cloud SQL cross-region backup copy feature.

Answer

Export the Bigtable data to Cloud Storage and import it into a new cluster.

Question 12

An application uses Cloud SQL MySQL for OLTP and wants to run analytics queries without impacting performance. Which TWO approaches can achieve this? (Select 2)

Accepted Answer

Use BigQuery federated queries to query Cloud SQL directly. Option A is correct because BigQuery federated queries allow you to query Cloud SQL MySQL in real time using BigQuery's SQL engine, without moving data. This enables analytics on live OLTP data while offloading the query processing to BigQuery, thus avoiding performance impact on the Cloud SQL instance. Option C is correct because a Cloud SQL read replica is a separate read-only copy of the primary instance; running analytics queries on the replica prevents resource contention with OLTP write operations.

Answer

Use Cloud Spanner for OLTP instead

Answer

Use BigQuery Omni

Answer

Enable Cloud SQL automatic storage increase

Question 13

An organisation needs to run SQL queries across data stored in Cloud SQL MySQL and Cloud Storage (CSV files) without moving the data. They want to use BigQuery as the query engine. Which feature should they use?

Accepted Answer

BigQuery federated queries using external tables. BigQuery federated queries using external tables allow you to query data stored in Cloud SQL and Cloud Storage (CSV files) directly from BigQuery without moving the data. This is achieved by creating an external table definition in BigQuery that references the source, enabling SQL queries across both sources in a single query using BigQuery's query engine.

Answer

Cloud SQL cross-database query

Answer

BigQuery Omni

Answer

BigQuery Data Transfer Service

Question 14

A company wants to implement polyglot persistence: an RDBMS for transactions, a NoSQL database for session storage, and a data warehouse for analytics. Which combination of Google Cloud databases best suits this architecture?

Accepted Answer

Cloud Spanner (transactions), Firestore (session storage), BigQuery (analytics). Cloud Spanner provides ACID transactions with global consistency and horizontal scalability, making it ideal for transactional workloads. Firestore is a NoSQL document database optimized for real-time updates and high-read/write throughput, perfect for session storage. BigQuery is a serverless data warehouse designed for analytical queries on large datasets, fitting the analytics requirement.

Answer

Cloud SQL (transactions), Firestore (session storage), Bigtable (analytics)

Answer

Firestore (transactions), Memorystore (session storage), BigQuery (analytics)

Answer

Cloud SQL (transactions), Bigtable (session storage), BigQuery (analytics)

Question 15

A company uses Cloud SQL MySQL for transactional workloads, BigQuery for analytics, and wants to stream real-time changes from Cloud SQL to BigQuery with minimal latency and no custom code. Which approach is most appropriate?

Accepted Answer

Use Datastream to capture CDC events from Cloud SQL MySQL and replicate them directly to BigQuery.. Datastream is purpose-built for minimal-latency, serverless change data capture (CDC) from sources like Cloud SQL MySQL to BigQuery. It uses log-based replication (reading the MySQL binary log) to stream row-level changes directly into BigQuery without requiring custom code or intermediate processing. This approach meets the requirements of real-time streaming with no custom code and minimal latency.

Answer

Set up a Cloud Function that queries Cloud SQL periodically and loads results into BigQuery via the streaming API.

Answer

Configure Cloud Scheduler to run an export of the Cloud SQL database every minute and load into BigQuery.

Answer

Create a Dataflow pipeline with a Pub/Sub topic and a change data capture connector for Cloud SQL.

Question 16

A company uses Cloud Spanner for its global financial application. They want to capture all changes to a Spanner table and stream them to Pub/Sub for downstream processing by a Dataflow pipeline. What is the recommended approach?

Accepted Answer

Create a Spanner change stream and use Dataflow to read from the change stream and publish to Pub/Sub.. Option C is correct because Spanner change streams are the native, built-in mechanism for capturing row-level changes in Cloud Spanner. Dataflow can directly read from a Spanner change stream using the Spanner change streams connector, and then publish the changes to Pub/Sub for downstream processing. This approach is fully managed, scalable, and avoids the latency and consistency issues of polling or external CDC tools.

Answer

Configure Pub/Sub Lite to directly connect to Spanner change streams.

Answer

Use a Cloud Function to query the Spanner table every minute and publish changes to Pub/Sub.

Answer

Enable Cloud SQL change data capture on the Spanner table and push changes to Pub/Sub.

Question 17

A data engineer needs to run a complex transformation pipeline between Cloud SQL and BigQuery, including joining data from both sources. The pipeline should run on a schedule and handle large volumes efficiently. Which service should they use?

Accepted Answer

Dataflow. Dataflow is the correct choice because it is a fully managed, serverless service for executing Apache Beam pipelines, which can handle complex transformations (e.g., joining data from Cloud SQL and BigQuery) at scale. It supports scheduled execution via Cloud Scheduler or built-in triggers, and its auto-scaling and exactly-once processing semantics ensure efficient handling of large volumes without manual infrastructure management.

Answer

Cloud Functions

Answer

Cloud Run

Answer

Datastream

Question 18

A company uses Cloud SQL for PostgreSQL and wants to set up point-in-time recovery (PITR) with a recovery window of 7 days. They have automated backups enabled. What additional configuration is required to achieve PITR?

Accepted Answer

Configure the backup retention period to 7 days and ensure automatic backup is enabled.. Point-in-time recovery (PITR) in Cloud SQL for PostgreSQL relies on write-ahead log (WAL) archiving, which is automatically enabled when automated backups are turned on. To retain the WAL logs needed to replay transactions to any point within the recovery window, you must set the backup retention period to at least 7 days. Option C correctly combines a 7-day backup retention with automated backups, which is the only additional configuration required beyond the default automated backup setting.

Answer

Create an on-demand backup every hour for the last 7 days.

Answer

Set up a cross-region backup copy with a retention of 7 days.

Answer

Enable binlog replication on the instance.

Question 19

A company is migrating an Oracle database to Cloud SQL for PostgreSQL using Ora2Pg. They need to convert stored procedures and functions. After conversion, they find that some PL/SQL code is not working. What is the most likely reason?

Accepted Answer

Ora2Pg does not convert Oracle-specific PL/SQL packages like DBMS_*.. Ora2Pg is an open-source tool that automates migration from Oracle to PostgreSQL, but it does not convert Oracle-specific PL/SQL packages such as DBMS_*, UTL_*, or other built-in Oracle libraries. These packages have no direct equivalent in PostgreSQL, so any stored procedures or functions that rely on them will fail after conversion. Manual rewriting or using PostgreSQL-compatible extensions (e.g., pg_dbms_* community modules) is required.

Answer

pgTap is needed to validate the converted code.

Answer

The Database Migration Service is required to convert stored procedures.

Answer

The PostgreSQL target instance is running an incompatible version.

Question 20

A company has a globally distributed application using Cloud Spanner. They need to backup the database for compliance purposes and restore it in a different region if needed. Which backup method should they use?

Accepted Answer

Export the Spanner database to Cloud Storage in Avro format using the console or gcloud.. Cloud Spanner does not support native managed backups or incremental backups. The correct method to create a backup for compliance and cross-region restore is to export the database to Cloud Storage in Avro format using the console or gcloud. This export can then be imported into a new Spanner instance in a different region, meeting the disaster recovery requirement.

Answer

Use Bigtable managed backups for Spanner.

Answer

Use Cloud SQL automated backups and cross-region copy.

Answer

Use Spanner incremental backups to save only changes since the last full backup.