PCD Manage a Solution that Can Span Multiple Database Systems — 50 Questions

Question 1

An e-commerce company uses Cloud SQL for MySQL for transactional data and BigQuery for analytics. They need to replicate order data from Cloud SQL to BigQuery in near real-time with minimal latency. Which Google Cloud service should they use?

Accepted Answer

Datastream. Datastream is a serverless CDC replication service that can stream changes from MySQL (including Cloud SQL) to BigQuery, GCS, or Pub/Sub with low latency. Dataflow requires building pipelines, DMS is for database migrations (not ongoing sync), and Pub/Sub alone doesn't load into BigQuery.

Answer

Cloud Dataflow

Answer

Cloud Pub/Sub

Answer

Database Migration Service (DMS)

Question 2

A company is migrating an on-premises Oracle database to Cloud SQL for PostgreSQL. They need to assess the compatibility of the existing schema and identify objects that need conversion. Which tool should they use?

Accepted Answer

Ora2Pg. Ora2Pg is an open-source tool designed to migrate Oracle schemas to PostgreSQL. It assesses compatibility, converts DDL, and reports unsupported features. DMS handles the data migration but doesn't provide schema analysis. Dataflow is for data transformation, not schema assessment.

Answer

gcloud sql import

Answer

Database Migration Service (DMS)

Answer

Cloud Dataflow

Question 3

A developer needs to query data from Cloud Spanner and Bigtable together in a single SQL statement without moving data. Which Google Cloud feature allows this?

Accepted Answer

Federated queries. BigQuery federated queries allow querying external data sources like Cloud Spanner and Bigtable directly from BigQuery using SQL, without importing data.

Answer

Spanner change streams

Answer

BigQuery Omni

Answer

Cloud SQL external tables

Question 4

A financial services company is migrating a critical PostgreSQL database to AlloyDB using Database Migration Service (DMS). They want to minimize downtime by using continuous change data capture (CDC). After the initial full dump, what phase must be completed before promoting the replica to primary?

Accepted Answer

Complete the CDC phase. DMS provides a CDC phase that continuously replicates changes from the source to the destination. Only after the replica catches up and the application can be stopped can you promote the replica. The full dump is the first step, then CDC ensures consistency.

Answer

Perform a final manual sync

Answer

Take a new full dump

Answer

Delete all existing data in AlloyDB

Question 5

A company uses Cloud Spanner for a globally distributed application. They need to capture all row-level changes (inserts, updates, deletes) and publish them to Pub/Sub for downstream processing. Which Spanner feature should they use?

Accepted Answer

Spanner change streams. Spanner change streams allow you to capture data changes and stream them to Pub/Sub topics for further processing. They provide a record of all mutations.

Answer

Spanner commit timestamps

Answer

Spanner mutations in Dataflow

Answer

Spanner interleaved tables

Question 6

A company has an Oracle database and wants to migrate to Cloud SQL for PostgreSQL using Database Migration Service. They set up a connection profile and a continuous migration job. After the initial load, the CDC phase begins. Suddenly, the source database has a schema change (adding a column). What happens?

Accepted Answer

The CDC phase continues to replicate DML but may fail if the column is required. DMS does not automatically replicate DDL changes. The column would be added to the source but not to the destination, potentially causing the CDC to fail due to data type mismatches.

Answer

The migration job automatically skips the new column

Answer

The migration job pauses and asks for manual intervention

Answer

The schema change is automatically replicated to Cloud SQL

Question 7

A startup needs a multi-model database architecture: relational for user profiles, document store for product catalog, and a key-value cache for sessions. Which approach is this called?

Accepted Answer

Polyglot persistence. Polyglot persistence refers to using different database types for different components of an application, each optimized for its use case.

Answer

Multi-cloud database strategy

Answer

Sharding

Answer

Database federation

Question 8

A company uses Datastream to replicate data from an on-premises MySQL database to BigQuery. They notice that the destination BigQuery table has a large number of rows that are duplicates with different timestamps. What is the most likely cause?

Accepted Answer

Datastream is configured in 'at least once' delivery mode. Datastream uses CDC and can deliver duplicate records if the source MySQL binary log is not configured with ROW-based replication and the log position tracking resets due to a restart or network issue. Duplicates can occur if the stream restarts from a checkpoint.

Answer

There is a primary key missing on the source table

Answer

BigQuery merge operation is not deduplicating

Answer

MySQL binary log is in STATEMENT format

Question 9

A retail company wants to run analytics queries across their Cloud SQL PostgreSQL database and BigQuery without moving data. They need to join sales data from Cloud SQL with customer demographics in BigQuery. Which approach should they use?

Accepted Answer

Create a BigQuery external table linked to Cloud SQL. BigQuery federated queries can create an external table that references Cloud SQL PostgreSQL. This allows querying Cloud SQL data directly from BigQuery and joining with BigQuery tables.

Answer

Use BigQuery Omni to query Cloud SQL

Answer

Use Datastream to replicate Cloud SQL to BigQuery

Answer

Export Cloud SQL data to GCS and load into BigQuery

Question 10

A company needs to back up their Cloud Bigtable instance for disaster recovery across regions. They want to restore to a different cluster in a different region. What should they use?

Accepted Answer

Use Bigtable managed backups and restore to a different cluster. Bigtable managed backups are cluster-level backups that can be restored to a different cluster, including cross-region, provided the restore is to an instance in the same project.

Answer

Export tables to Avro in GCS and import in another region

Answer

Use gcloud bigtable instances create with replication

Answer

Use Cloud Scheduler to copy tables via Dataflow

Question 11

A company wants to perform cross-cloud analytics, querying data stored in both Google Cloud BigQuery and Amazon S3. Which BigQuery feature enables this?

Accepted Answer

BigQuery Omni. BigQuery Omni allows querying data across clouds (AWS and Azure) using BigQuery, without moving data.

Answer

Cloud Data Fusion

Answer

BigQuery Data Transfer Service

Answer

BigQuery federated queries

Question 12

A company is migrating from MySQL to Cloud SQL MySQL using Database Migration Service with continuous CDC. They want to test the migrated database with production traffic before cutting over, while still keeping the source as primary. What should they do?

Accepted Answer

Create a clone of the Cloud SQL replica for testing. DMS allows you to promote the replica to a standalone instance for testing while the CDC stream continues. However, promoting the replica stops the migration. The recommended approach is to create a clone from the replica for testing, or use a separate test environment.

Answer

Use a separate DMS job for testing

Answer

Point the application directly to the replica

Answer

Promote the replica to a standalone instance for testing

Question 13

A company is building a polyglot persistence architecture. They need to choose the correct Google Cloud databases for the following requirements: (1) ACID transactions for financial orders, (2) high-throughput time-series sensor data, and (3) real-time session cache. Which THREE databases should they choose? (Choose 3)

Accepted Answer

Memorystore. Cloud Spanner provides ACID transactions globally, Bigtable for time-series, and Memorystore for caching. Firestore is document-oriented, not ideal for time-series.

Answer

Cloud SQL

Answer

Firestore

Question 14

A company uses Datastream to replicate changes from an on-premises Oracle database to BigQuery. They need to also send the same change events to a custom application for real-time processing. Which TWO actions should they take? (Choose 2)

Accepted Answer

Configure Datastream to write to a Pub/Sub topic. Datastream can write to Pub/Sub, which then can be consumed by the application. Alternatively, Datastream can write to GCS (Avro/JSON) and then Pub/Sub or Dataflow can be used. The direct approach is to stream to Pub/Sub.

Answer

Use Datastream to write to Cloud Bigtable

Answer

Set up a Cloud Function to read from BigQuery and forward to the application

Answer

Use Dataflow to read from GCS and publish to Pub/Sub

Question 15

A company is migrating a PostgreSQL database to AlloyDB using Database Migration Service. They want to ensure high availability during the migration with minimal risk. Which TWO practices should they follow? (Choose 2)

Accepted Answer

Set up DMS with continuous CDC to keep AlloyDB in sync. DMS supports continuous CDC, so you should enable CDC for minimal downtime. Testing with a clone avoids affecting production. Promoting the replica early would cause downtime.

Answer

Promote the replica immediately after the initial load

Answer

Disable binary logging on the source to improve performance

Answer

Use gcloud sql import command instead of DMS

Question 16

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 17

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 18

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 19

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 20

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