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

Question 1

A company runs an e-commerce application with a relational database (PostgreSQL) for orders and inventory. They need to store clickstream data for real-time personalization, with a flexible schema and sub-second read latency. Which database best suits the clickstream workload?

Accepted Answer

Firestore

Answer

BigQuery

Answer

Cloud Spanner

Answer

Cloud Bigtable

Question 2

A financial services company uses Cloud SQL for MySQL for transactional data. They need to analyze this data in BigQuery without moving data or impacting transactional performance. Which approach should they use?

Accepted Answer

Create a BigQuery federated query to Cloud SQL MySQL using an external table

Answer

Set up Cloud SQL replication to a read replica and connect BigQuery to it

Answer

Export Cloud SQL data to GCS as CSV and load into BigQuery daily

Answer

Use Datastream to replicate Cloud SQL data to BigQuery in real-time

Question 3

A company needs to migrate a self-managed PostgreSQL database (version 12) to Cloud SQL for PostgreSQL with minimal downtime. They plan to use Database Migration Service (DMS). What is the correct set of steps?

Accepted Answer

Create a DMS connection profile for the source; create a migration job with 'Full dump + CDC'; configure source for logical replication; promote destination when ready.

Answer

Create a DMS connection profile for the source; create a migration job with 'Full dump' only; promote the destination after dump completes.

Answer

Create a DMS connection profile for the source; set up Cloud SQL for PostgreSQL; use pg_dump and pg_restore manually.

Answer

Create a DMS connection profile for the source; create a migration job with 'CDC' only; no initial dump is needed.

Question 4

A company uses Cloud Spanner for a globally distributed application. They need to capture all data changes from a critical Spanner table and stream them to Pub/Sub in real-time for downstream processing. Which approach meets this requirement?

Accepted Answer

Use Spanner change streams with a Dataflow pipeline that reads the change stream and publishes each change to a Pub/Sub topic.

Answer

Enable Spanner change streams on the table, then create a Pub/Sub subscription to consume changes directly.

Answer

Configure Spanner to export change logs to GCS and set up a Cloud Function to publish them to Pub/Sub.

Answer

Use Spanner commit timestamps and Cloud Scheduler to periodically query recent changes and publish to Pub/Sub.

Question 5

A company needs to perform complex transformations and join data from Cloud SQL, Cloud Storage, and Pub/Sub to load into BigQuery. Which Google Cloud service is designed for this ETL/ELT workload?

Accepted Answer

Dataflow

Answer

Cloud Functions

Answer

Cloud Run

Answer

Dataproc

Question 6

An organization plans to adopt polyglot persistence: Cloud SQL for transactions, Cloud Bigtable for time-series, and BigQuery for analytics. They need to ensure data consistency when updating both Cloud SQL and Bigtable in a single business transaction. What approach should they use?

Accepted Answer

Implement a saga pattern where each database operation is a local transaction, with compensating actions for failures.

Answer

Use Cloud Spanner for all data to avoid cross-database consistency issues.

Answer

Use a two-phase commit protocol across Cloud SQL and Bigtable.

Answer

Write to Cloud SQL first, then use Cloud Functions to asynchronously write to Bigtable with retries.

Question 7

A company uses Cloud Bigtable for real-time user sessions. They need to back up Bigtable data to another region for disaster recovery, with the ability to restore to a specific point in time within the last week. Which backup strategy should they choose?

Answer

Enable Bigtable managed backups with a retention policy of 7 days and a restore to another cluster.

Answer

Use Bigtable managed backups with a 7-day retention, and restore to a cluster in the same region; then replicate to the DR region.

Answer

Use Bigtable replication across two instances in different regions, with a scheduled script to take snapshots.

Answer

Export Bigtable data to GCS using a Dataflow template daily, and store backups with object lifecycle management.

Question 8

A team needs to replicate changes from an on-premises Oracle database to BigQuery in near real-time. They want to use Google's serverless CDC solution. Which service should they use?

Accepted Answer

Datastream

Answer

Dataflow with JDBC connector

Answer

Database Migration Service

Answer

Cloud Functions with Oracle trigger

Question 9

A company wants to run analytical queries across data stored in Cloud SQL (MySQL), Cloud Storage (Parquet files), and Bigtable without moving any data. Which BigQuery feature enables this?

Accepted Answer

Federated queries using external tables

Answer

BigQuery Omni

Answer

Cloud Data Fusion

Answer

BigQuery BI Engine

Question 10

An organization is migrating a MySQL database to Cloud SQL for MySQL using Database Migration Service. The migration job is in the 'Full dump + CDC' phase. After a successful full dump, the CDC phase is replicating changes. To complete the migration with minimal downtime, what should the engineer do?

Accepted Answer

Promote the Cloud SQL instance using the DMS console or API, which triggers a short cutover window.

Answer

Wait for the CDC phase to automatically complete and promote.

Answer

Stop the application, then promote the Cloud SQL instance to make it the primary.

Answer

Delete the source database and then promote the Cloud SQL instance.

Question 11

A company uses Cloud Spanner for global transaction processing. They need to export a full database backup to GCS in a portable format for long-term archival. Which method should they use?

Accepted Answer

Use Cloud Spanner backups (built-in) which export to GCS in Avro format.

Answer

Use gcloud beta spanner databases export command to export to GCS in CSV format.

Answer

Use Dataflow to read the Spanner database and write to GCS in JSON format.

Answer

Use Cloud SQL export to GCS.

Question 12

A developer needs to implement caching for a Cloud SQL database to reduce read latency for frequently accessed data. Which Google Cloud service should they use?

Accepted Answer

Memorystore for Redis

Answer

Cloud Spanner

Answer

Cloud CDN

Answer

Cloud Bigtable

Question 13

A company is designing a polyglot persistence architecture. They need to use Cloud SQL for transactions, Bigtable for real-time analytics, and BigQuery for reporting. They also need to keep data across these systems eventually consistent. Which TWO approaches should they use?

Accepted Answer

Implement a saga pattern with compensating transactions for each write.

Answer

Use two-phase commit across all databases.

Answer

Use Cloud Spanner as a single database to avoid polyglot complexity.

Answer

Write to all three databases in a single transaction using distributed transactions.

Question 14

A company is migrating a PostgreSQL database to AlloyDB using Database Migration Service. They need to ensure the migration has minimal downtime and can be tested before cutover. Which THREE steps should they include in their migration plan?

Accepted Answer

Create a migration job with 'Full dump + CDC' to enable continuous replication.

Answer

Test the migration by promoting a replica in a non-production environment before final cutover.

Answer

Use pg_dump to take a manual backup and restore it to AlloyDB before setting up DMS.

Question 15

A company needs to query data across Cloud SQL (MySQL) and Bigtable using BigQuery without moving the data. Which TWO configurations are required?

Accepted Answer

Create an external table in BigQuery that references the Bigtable table using a Bigtable URI.

Answer

Enable BigQuery Omni for cross-cloud querying.

Answer

Use Dataflow to replicate Cloud SQL and Bigtable data to BigQuery.

Answer

Create a BigQuery dataset in the same region as Cloud SQL and Bigtable.