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

Question 1

A company wants to migrate an on-premises Oracle database to Cloud SQL for PostgreSQL with minimal downtime. They plan to use Database Migration Service (DMS) with continuous migration. What must be configured on the source Oracle database to enable change data capture (CDC)?

Accepted Answer

Enable supplemental logging. Database Migration Service for Oracle to PostgreSQL requires supplemental logging at the database level to capture all changes for CDC. Without supplemental logging, DMS cannot replicate ongoing changes.

Answer

Set the database to force logging

Answer

Enable archive log mode

Answer

Configure Oracle GoldenGate

Question 2

A data engineering team needs to replicate data from a PostgreSQL database to BigQuery in near real-time for analytics. Which Google Cloud service is most suitable for this task with minimal setup?

Accepted Answer

Datastream. Datastream is a serverless CDC replication service specifically designed for replicating from sources like PostgreSQL, MySQL, and Oracle to BigQuery, Cloud Storage, or Pub/Sub. It handles schema mapping and initial backfill.

Answer

Cloud SQL for PostgreSQL

Answer

Cloud Data Fusion

Answer

Cloud Composer

Question 3

An e-commerce platform uses Cloud Spanner for order processing and BigQuery for analytics. The team needs to capture all changes in the Spanner 'Orders' table and stream them to Pub/Sub for downstream processing. Which Spanner feature should be used?

Accepted Answer

Configure Spanner change streams. Cloud Spanner change streams capture all data changes in a database or table and can be read via Pub/Sub or Dataflow. This is the native way to stream changes from Spanner.

Answer

Export to GCS using the console

Answer

Use Spanner Interleaved tables

Answer

Create a Cloud Function triggered by Spanner

Question 4

A company uses Cloud SQL for MySQL with automated backups enabled. They want to perform point-in-time recovery (PITR) to recover from an accidental data deletion that occurred 10 minutes ago. What must be configured for PITR to work?

Accepted Answer

Increase the binlog retention period. PITR in Cloud SQL for MySQL requires binary logging (binlog) to be enabled, which is enabled by default when automated backups are on. The binlog retention period determines how far back PITR can go.

Answer

Enable the slow query log

Answer

Enable automated backups only

Answer

Create a cross-region replica

Question 5

A financial services company needs to query data across Cloud SQL (PostgreSQL) and BigQuery without moving data. They want to use federated queries from BigQuery to access Cloud SQL tables. What must be created in BigQuery to enable this?

Accepted Answer

An external table. BigQuery federated queries for Cloud SQL require creating an external table or using the EXTERNAL_QUERY function. An external data source connection is needed, but the question asks specifically what must be created in BigQuery.

Answer

A BigQuery view

Answer

A Cloud SQL instance connection

Answer

A BigQuery dataset

Question 6

An organization has Cloud Bigtable for real-time ad serving and BigQuery for analytics. They want to synchronize data changes from Bigtable to BigQuery every hour with a Dataflow pipeline. However, they notice that some updates are missed. What is the most likely cause?

Accepted Answer

Bigtable does not support change data capture natively. Bigtable does not have built-in change data capture (CDC) like some databases. Dataflow can read Bigtable snapshots but not incremental changes unless using Dataflow's streaming insert, which is not native. The common approach is to use Bigtable replication or export, but missed updates often occur because Bigtable does not log changes. A custom solution using Cloud Bigtable change streams (beta) or using a separate logging table is needed.

Answer

The Dataflow pipeline reads from Bigtable using a snapshot read

Answer

The Bigtable table has no primary key

Answer

The Dataflow pipeline is using the wrong sink

Question 7

A company uses Memorystore for Redis as a cache in front of Cloud Spanner. They want to ensure cache updates happen asynchronously when Spanner data changes. Which pattern is most appropriate?

Accepted Answer

Implement a cache-aside pattern with TTL. The cache-aside pattern involves the application checking the cache first, and if not found, loading from the database and updating the cache. Eventual consistency is acceptable for cache. The saga pattern is for distributed transactions, not caching.

Answer

Implement a saga pattern

Answer

Use a two-phase commit between Spanner and Memorystore

Answer

Use Spanner change streams to invalidate cache entries via Pub/Sub

Question 8

A company is migrating an Oracle database to AlloyDB for PostgreSQL using Database Migration Service. They have completed a full dump and are now in the CDC phase. They want to test the target database with minimal risk. What should they do?

Accepted Answer

Promote the continuous migration replica to a standalone instance. Database Migration Service allows creating a connection profile and a migration job. During the CDC phase, you can promote the replica to make the target database writable for testing. After testing, you can resume migration by re-creating the job.

Answer

Stop the migration job and promote the replica

Answer

Create a snapshot of the target instance

Answer

Use Cloud SQL Auth Proxy to connect

Question 9

A company wants to use BigQuery Omni to query data stored in Amazon S3. They have set up a BigQuery Omni connection. Which querying method should they use?

Accepted Answer

Create an external table referencing the S3 data. BigQuery Omni uses external tables stored in S3 (or Azure Blob). You create an external table that points to the S3 location, then query it with standard SQL.

Answer

Use Cloud Storage Transfer Service

Answer

Use the BigQuery Data Transfer Service to copy data

Answer

Use the bq load command to load data into BigQuery

Question 10

A company uses Cloud SQL for PostgreSQL with a cross-region replica for disaster recovery. They need to perform a regional failover with minimal data loss. What configuration is required?

Accepted Answer

Promote the cross-region replica to a primary instance. Cloud SQL cross-region replicas use asynchronous replication, so some data loss is possible. To minimize loss, ensure that the replication lag is low and that automated backups are taken frequently. However, the key is to promote the replica to a standalone instance, which will stop replication and make it writable.

Answer

Use Database Migration Service instead

Answer

Take a manual backup of the primary and restore to the replica

Answer

Enable synchronous replication

Question 11

A company wants to back up their Cloud Spanner database for disaster recovery. They need a backup that can be restored in a different region. Which backup method should they use?

Accepted Answer

Use Spanner export to GCS. Cloud Spanner supports full database exports to GCS in Avro format. These exports can be restored to any Spanner instance, including in a different region. There are no incremental backups; only full exports.

Answer

Use Bigtable managed backups

Answer

Use Cloud SQL export

Answer

Use gcloud spanner operations backup

Question 12

A company uses a polyglot persistence architecture: Cloud SQL for transactions, Bigtable for real-time analytics, and BigQuery for reporting. They need to synchronize data from Cloud SQL to Bigtable in near real-time. Which combination of services should they use?

Accepted Answer

Datastream + Dataflow. Datastream can capture CDC from Cloud SQL (MySQL/PostgreSQL) and publish to Pub/Sub. Dataflow can then read from Pub/Sub and write to Bigtable. This provides near real-time sync.

Answer

Dataflow + Cloud SQL

Answer

Cloud Pub/Sub + Cloud Functions

Answer

Cloud Functions + BigQuery

Question 13

A company needs to migrate an on-premises PostgreSQL database to Cloud SQL for PostgreSQL with near-zero downtime. Which TWO features of Database Migration Service (DMS) should they use?

Accepted Answer

Connection profiles. Continuous migration with CDC allows near-zero downtime by capturing ongoing changes after the initial dump. Connection profiles define source and target endpoints. A one-time dump would cause downtime. Continuous migration includes CDC.

Answer

Dataflow templates

Answer

Full dump only

Answer

One-time migration

Question 14

A company uses Cloud Spanner for a globally distributed application. They need to capture all changes from a Spanner table and send them to a BigQuery dataset for real-time analytics. Which THREE Google Cloud services or features should they use?

Accepted Answer

Dataflow. Spanner change streams capture row-level changes. Dataflow can read from change streams and write to BigQuery. Pub/Sub can be an intermediary but is not required; Dataflow can directly write to BigQuery. However, the typical architecture uses Pub/Sub for decoupling. The question expects the three core components: change streams, Dataflow, and BigQuery.

Answer

Datastream

Answer

Cloud Functions

Question 15

A company wants to query data across Cloud SQL (MySQL) and BigQuery using federated queries. They want to avoid data movement. Which TWO components must be set up?

Accepted Answer

External table in BigQuery. A BigQuery connection (external data source) is required to link to Cloud SQL. An external table in BigQuery references the Cloud SQL table for querying. A Cloud SQL instance is the source, but the connection and external table are the components set up in BigQuery.

Answer

Cloud SQL instance

Answer

Cloud Storage bucket

Answer

Dataflow pipeline