PCD Manage a Solution that Can Span Multiple Database Systems Practice Test 6 — 15 Questions

Question 1

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 2

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 3

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 4

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 5

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 6

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 7

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.

Question 8

An e-commerce platform uses Cloud SQL MySQL for orders and Bigtable for session history. To provide a unified customer view, they need to join order data with recent session activity. Which approach minimises data movement while achieving low-latency queries?

Accepted Answer

Use Memorystore as a cache to store denormalised customer views, updated from both databases.. Memorystore (Redis) can cache frequently accessed session data from Bigtable and order data from Cloud SQL, enabling low-latency reads without moving data permanently. Dataflow is batch-oriented, Cloud Functions is not for queries, and exporting to BigQuery adds latency.

Answer

Use Dataflow to continuously join the data and write results to BigQuery, then query BigQuery.

Answer

Use Cloud Functions to query both databases and return combined results.

Answer

Export both databases to Cloud Storage and use BigQuery federated queries.

Question 9

A company is migrating from on-premises SQL Server to Cloud SQL SQL Server using Database Migration Service (DMS). During the continuous migration phase, they notice that the replication lag is increasing. What is the most likely cause?

Accepted Answer

Large transactions on the source database are causing CDC to fall behind.. Option C is correct because Database Migration Service (DMS) uses Change Data Capture (CDC) to replicate ongoing changes from the source SQL Server to the target Cloud SQL instance. Large transactions on the source database generate a high volume of log records that the CDC reader must process sequentially. If the transaction volume exceeds the throughput capacity of the DMS replication slot, the lag between the source and target increases, causing the replication lag to grow.

Answer

The DMS migration job is not using a source read replica.

Answer

The target Cloud SQL instance is not using Premium tier.

Answer

The source database has a low transaction log retention period.

Question 10

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 11

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 12

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 13

A company needs to synchronise data from an on-premises Oracle database to BigQuery in near real-time. They also need to perform complex transformations on the data before loading into BigQuery. Which two services should they use together? (Choose two.)

Accepted Answer

Datastream. Datastream is correct because it provides serverless change data capture (CDC) from Oracle databases, enabling near real-time synchronization to BigQuery. Dataflow is correct because it can consume the Datastream output and perform complex transformations (e.g., joins, aggregations, schema mapping) using Apache Beam before writing to BigQuery.

Answer

Cloud Scheduler

Answer

Cloud Functions

Question 14

A company is designing a cross-database solution with eventual consistency. They need to update data in Cloud SQL (orders) and Bigtable (inventory) as part of a single business transaction. Which two patterns can help maintain eventual consistency? (Choose two.)

Accepted Answer

Implement a saga pattern with compensating transactions in case of failure.. Option B is correct because the saga pattern breaks a distributed transaction into a series of local transactions, each with a compensating action that can undo its effects if a subsequent step fails. This pattern is ideal for maintaining eventual consistency across Cloud SQL and Bigtable without requiring a global coordinator or locking resources. Option D is correct because idempotent receivers ensure that duplicate events (e.g., from retries or network failures) do not cause unintended side effects, and retry logic allows the system to recover from transient failures, both of which are essential for achieving eventual consistency in a cross-database solution.

Answer

Use two-phase commit (2PC) across both databases.

Answer

Design for strong consistency by using global transactions.

Answer

Write directly to both databases in a single synchronous call.

Question 15

A company uses Cloud SQL for PostgreSQL and wants to set up a disaster recovery solution with cross-region backup copies. They also need point-in-time recovery (PITR) in the primary region. Which three steps should they take? (Choose three.)

Accepted Answer

Configure the backup retention period to your required PITR window.. Option A is correct because the backup retention period in Cloud SQL directly determines the maximum window for point-in-time recovery (PITR). PITR relies on transaction logs retained alongside automated backups; setting the retention period to cover your required PITR window ensures you can restore to any point within that timeframe.

Answer

Promote a read replica in the secondary region for migration.

Answer

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