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

Question 1

A company is migrating an on-premises PostgreSQL database to Cloud SQL for PostgreSQL. They need to minimize downtime and ensure continuous replication during the cutover. Which Google Cloud service should they use to achieve continuous change data capture (CDC)?

Accepted Answer

Database Migration Service (DMS). Database Migration Service (DMS) supports continuous change data capture (CDC) for PostgreSQL migrations to Cloud SQL by using PostgreSQL's native logical replication. This allows DMS to replicate ongoing changes from the source to the target with minimal downtime, enabling a near-zero-downtime cutover by simply promoting the Cloud SQL instance as the new primary.

Answer

Cloud Data Fusion

Answer

Dataproc

Answer

Cloud Memorystore

Question 2

An e-commerce application uses Cloud SQL for transactional data and Cloud Bigtable for user session logs. A new requirement demands real-time analytics that joins order data with session behavior. The team wants to avoid moving data into a separate data warehouse. Which approach should they take?

Accepted Answer

Use BigQuery federated queries with external tables for both Cloud SQL and Bigtable. BigQuery federated queries allow you to query data in Cloud SQL and Cloud Bigtable directly using external tables, without moving the data. This approach meets the real-time analytics requirement by joining order data (Cloud SQL) with session logs (Cloud Bigtable) in a single SQL query, avoiding the need for a separate data warehouse or data movement.

Answer

Use Dataflow to continuously stream both Cloud SQL and Bigtable data into a single BigQuery dataset

Answer

Use Cloud Spanner as a unified database for both transactional and session data

Answer

Use Datastream to replicate Cloud SQL data to BigQuery, then join with Bigtable data using BigQuery's Bigtable external table

Question 3

A financial services company is architecting a multi-database solution. They need strong consistency across Cloud Spanner and Cloud SQL for a critical transaction flow. However, two-phase commit (2PC) across heterogeneous databases is not supported. Which pattern should they adopt to maintain data integrity?

Accepted Answer

Saga pattern with orchestration and compensating transactions. The Saga pattern with orchestration and compensating transactions is correct because it maintains data integrity across heterogeneous databases like Cloud Spanner and Cloud SQL without relying on distributed transactions (2PC). In this pattern, each local transaction commits independently, and if a subsequent transaction fails, a compensating transaction is executed to undo the previous committed changes, ensuring eventual consistency and business-level rollback.

Answer

Use Cloud Spanner change streams to sync data to Cloud SQL

Answer

Eventual consistency with conflict resolution

Answer

Migrate all data to Cloud Spanner to avoid cross-database transactions

Question 4

A company wants to replicate data from an on-premises Oracle database to BigQuery for analytics. They need serverless, near-real-time CDC with minimal operational overhead. Which service should they use?

Accepted Answer

Datastream. Datastream is a serverless, fully managed change data capture (CDC) and replication service that streams data from Oracle (and other sources) into BigQuery with near-real-time latency. It uses log-based CDC (Oracle Redo Logs) to capture changes without impacting source performance, and it handles schema mapping and data transformation automatically, minimizing operational overhead.

Answer

Cloud Data Fusion with Oracle plugin

Answer

Cloud Composer with scheduled jobs

Answer

Dataflow with a Java transformation

Question 5

A team is using Cloud SQL for MySQL and wants to recover the database to a specific point in time within the last 7 days. They have automated backups enabled. Which feature must be configured to enable point-in-time recovery (PITR)?

Accepted Answer

Enable binary logging and set a suitable binlog retention period. PITR for Cloud SQL MySQL requires binary log (binlog) retention to be set appropriately.

Answer

Use Database Migration Service for continuous replication

Answer

Configure cross-region replication

Answer

Enable automated backups only

Question 6

An organization is using Cloud Spanner for a global application. They need to capture all data changes (inserts, updates, deletes) in a Spanner table and stream them to Pub/Sub for downstream processing. Which feature should they use?

Accepted Answer

Cloud Spanner change streams. Cloud Spanner change streams can capture row-level changes and publish them to Pub/Sub via Dataflow or direct integration.

Answer

Datastream for Spanner

Answer

Cloud Bigtable change data capture

Answer

Cloud SQL publication with pglogical

Question 7

A company is migrating a 5 TB Oracle database to AlloyDB for PostgreSQL. They need to validate the migration with a test replica without impacting the source. After testing, they plan to promote the replica to become the primary. Which migration strategy should they use?

Accepted Answer

Use Database Migration Service to create a continuous migration job with a full dump and CDC, then test the replica and promote it. Database Migration Service (DMS) with a continuous migration job using a full dump and Change Data Capture (CDC) is the correct choice because it allows you to create a fully synchronized test replica of the 5 TB Oracle database in AlloyDB for PostgreSQL without impacting the source. After validating the replica, you can promote it to become the primary with minimal downtime, as CDC ensures near-real-time consistency.

Answer

Use Ora2Pg to migrate the schema, then perform a manual bulk data export/import

Answer

Use Datastream to replicate to AlloyDB, then promote the target

Answer

Use Cloud Data Fusion to create an ETL pipeline from Oracle to AlloyDB

Question 8

A company uses Cloud Bigtable for time-series data and wants backups that can be restored in a different region for disaster recovery. Which backup method meets this requirement?

Accepted Answer

Use Bigtable managed backups, which are stored in the same region and can be restored to any cluster. Bigtable managed backups are cluster-level and support cross-cluster restore, including to a different region.

Answer

Export tables to Avro files in GCS, then import in the new region

Answer

Enable replication between Bigtable clusters

Answer

Use BigQuery federated queries to export data to GCS

Question 9

A data engineer needs to run complex transformations on streaming data from Pub/Sub and then write the results to both BigQuery and Cloud Bigtable. Which Google Cloud service is best suited for this task?

Accepted Answer

Dataflow. Dataflow is the correct choice because it is a fully managed, unified stream and batch processing service built on Apache Beam. It can directly read from Pub/Sub, apply complex transformations (e.g., windowing, aggregations, joins), and write the results to both BigQuery and Cloud Bigtable using native Beam I/O connectors, all within a single pipeline.

Answer

Cloud Data Fusion

Answer

Datastream

Answer

Cloud Functions

Question 10

An organization has data in Amazon S3 that they want to query from BigQuery without moving it. Which BigQuery feature should they use?

Accepted Answer

BigQuery Omni. BigQuery Omni allows querying data across clouds, including Amazon S3, without moving the data.

Answer

Cloud Storage transfer service to copy data to GCS first

Answer

BigQuery federated queries with AWS Glue

Answer

Bigtable federation

Question 11

A team is using Datastream to replicate from MySQL to BigQuery. They notice that the BigQuery table is missing some updates that occurred in the source. Which is the most likely cause?

Accepted Answer

The source MySQL binary log retention period is too short, causing log truncation before Datastream reads the changes. Datastream uses transaction logs and may skip events if the source binary logs are not retained long enough, or if there is a schema mismatch.

Answer

BigQuery does not support streaming inserts from Datastream

Answer

The Datastream connection profile uses read replicas that are lagging

Answer

Cloud SQL does not support MySQL

Question 12

A company wants to run analytics queries on Cloud SQL data without impacting the transactional workload. They need to query the Cloud SQL database directly from BigQuery. Which BigQuery feature should they use?

Accepted Answer

BigQuery federated queries using Cloud SQL external table. BigQuery federated queries using Cloud SQL external tables allow you to query Cloud SQL databases directly from BigQuery without moving or replicating the data. This feature uses a federated query engine that pushes down SQL operations to Cloud SQL, minimizing the impact on the transactional workload by only reading the required data on demand.

Answer

Cloud SQL read replicas

Answer

Datastream to replicate Cloud SQL to BigQuery

Answer

BigQuery Omni

Question 13

A company is designing a polyglot persistence architecture. They need to store (1) customer profiles with complex relationships and ACID transactions, (2) high-volume time-series IoT sensor data, and (3) interactive analytics dashboards. Which TWO Google Cloud databases should they choose for these workloads? (Choose 2 options.)

Accepted Answer

Cloud SQL. Cloud SQL (or Spanner) for relational ACID, Bigtable for time-series, BigQuery for analytics. The question asks for two, but three are mentioned; the best pair for the first two requirements is Cloud SQL and Bigtable. However, since it asks for TWO, we select Cloud SQL for transactions and Bigtable for time-series.

Answer

BigQuery

Answer

Firestore

Answer

Cloud Memorystore

Question 14

A company is migrating a SQL Server database to Cloud SQL for SQL Server using Database Migration Service (DMS). They have created a continuous migration job with CDC. They need to test the migrated data before promotion without affecting the source. Which TWO actions should they take? (Choose 2 options.)

Accepted Answer

Use a separate DMS job to a different Cloud SQL instance for testing. To test without impacting source, they can create a clone of the Cloud SQL instance (or use a different instance) and promote the clone, but DMS does not support cloning directly. The recommended approach is to set up a separate DMS job to a different target instance for testing, or use the existing job and promote a test instance after taking a snapshot. However, the most practical options are: (1) Create a clone of the Cloud SQL instance from the DMS target, and (2) Use a separate DMS job to a test target. Among the options, 'Create a clone of the Cloud SQL instance from the DMS job' and 'Promote the DMS job to a test instance' are valid. But given DMS limitations, the typical answer is to use a separate DMS job or clone. I'll pick the two that align with GCP documentation: Create a clone of the Cloud SQL instance (from the target) and promote the DMS job to a separate instance (though DMS doesn't support multiple targets). Let's refine: 'Create a clone of the Cloud SQL instance and run a DMS job to that clone' is not an option. The best answers are: 'Use a separate DMS job to a different Cloud SQL instance for testing' and 'Promote the DMS job to a test instance, then re-create the job for production' — but promoting is one-time. I'll choose: B and D.

Answer

Stop the DMS job, test on the target, then resume

Answer

Test directly on the DMS target instance without promotion; changes won't affect source

Answer

Promote the DMS job to make the Cloud SQL instance the primary, then re-create the job for production

Question 15

A company uses Cloud Spanner for a global database and needs to export a full backup to GCS for long-term archival and compliance. Which THREE statements are true about Cloud Spanner backups? (Choose 3 options.)

Accepted Answer

Backups can be created on demand or via a scheduled policy. Option A is correct because Cloud Spanner supports both on-demand backups (created manually via the console, gcloud CLI, or API) and scheduled backup policies that automate backup creation at defined intervals. This flexibility allows administrators to meet compliance requirements without manual intervention.

Answer

Incremental backups are supported to reduce storage costs

Answer

Backups are automatically replicated across regions