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

Question 1

A global e-commerce company uses Cloud Spanner for transactional data. They need to stream real-time order changes to a BigQuery table for analytics. Which approach meets the requirement with minimal latency and operational overhead?

Accepted Answer

Use Spanner change streams to capture changes, publish to Pub/Sub, and use Dataflow to write to BigQuery.. Cloud Spanner change streams capture incremental changes (inserts, updates, deletes) and can be read via Pub/Sub. A Dataflow pipeline can then write these changes to BigQuery in near real-time. This is the recommended pattern for streaming Spanner changes to BigQuery.

Answer

Create a Cloud Function that queries Spanner every minute and writes new records to BigQuery.

Answer

Use Cloud Scheduler to run a daily export of Spanner table to Cloud Storage, then load into BigQuery.

Answer

Configure a Datastream stream from Spanner to BigQuery.

Question 2

You need to migrate an on-premises MySQL 5.7 database (2 TB) to Cloud SQL for MySQL with minimal downtime. Database Migration Service (DMS) is chosen. What is the correct sequence of steps to ensure a successful continuous migration?

Accepted Answer

Set up a connection profile for the source, create a DMS migration job (full dump + CDC), monitor the initial dump, then promote the replica to complete migration.. DMS continuous migration first performs a full dump (initial snapshot), then switches to CDC to replicate ongoing changes. After the initial dump, you validate the replica and promote it to make it the primary. The correct order: create connection profile for source, create migration job with full dump + CDC, monitor initial dump, then promote when ready.

Answer

Use mysqldump to export the database, import to Cloud SQL, then set up DMS for CDC only.

Answer

Create a DMS continuous migration job directly without a connection profile.

Answer

First promote the Cloud SQL replica, then create the connection profile.

Question 3

A company uses Cloud SQL for PostgreSQL as its primary database. They want to query this data from BigQuery for analytics without moving the data. They also need to ensure that BigQuery queries see the most recent data (within seconds of changes). Which approach is most suitable?

Accepted Answer

Set up Datastream to continuously replicate data from Cloud SQL to BigQuery, then query BigQuery tables.. BigQuery federated queries via external tables can directly query Cloud SQL without data movement. However, for near real-time freshness, the best approach is to use Datastream to replicate changes from Cloud SQL to BigQuery in near real-time. Federated queries alone have higher latency (minutes) and are not suitable for sub-second freshness.

Answer

Create a Cloud Function that queries Cloud SQL every 5 seconds and writes results to BigQuery.

Answer

Use BigQuery federated queries to create an external table connected to Cloud SQL.

Answer

Use a scheduled query in BigQuery that exports Cloud SQL data to Cloud Storage and loads into BigQuery.

Question 4

A startup is building an application that requires strong consistency and horizontal scalability across multiple regions. They need a fully managed relational database that supports both SQL queries and ACID transactions. Which database service should they choose?

Accepted Answer

Cloud Spanner. Cloud Spanner is the only fully managed relational database on GCP that offers strong consistency and horizontal scalability across regions. It supports SQL and ACID transactions. Cloud SQL is regionally limited and does not auto-scale horizontally.

Answer

Cloud SQL

Answer

Firestore

Answer

Bigtable

Question 5

You are designing a polyglot persistence architecture for an IoT platform. Device telemetry is stored in Cloud Bigtable for ingestion and low-latency queries. When a device registers a high-priority alert, you need to trigger an email notification. What is the recommended approach to decouple the alert processing from Bigtable?

Accepted Answer

Use the Bigtable change streams feature (currently in preview) to push alerts to Pub/Sub, then a Cloud Function sends the email.. Bigtable does not have built-in change data capture. A common pattern is to write an application that, upon detecting an alert, publishes a message to Pub/Sub. A Cloud Function subscribed to that topic can then send the email. This decouples processing from the database.

Answer

Enable Bigtable replication and set up a trigger in the replica.

Answer

Write a Dataflow pipeline that reads from Bigtable and writes to Cloud Storage.

Answer

Create a scheduled Cloud Function that polls Bigtable every minute for new alerts.

Question 6

A media company uses Cloud SQL for MySQL for its user data and needs to export daily snapshots to BigQuery for reporting. The reporting must reflect data as of midnight UTC. Which approach is most cost-effective and efficient?

Accepted Answer

Export Cloud SQL data to Cloud Storage using a daily Cloud Scheduler job, then load into BigQuery.. Using Cloud SQL export to Cloud Storage (SQL dump) and then loading into BigQuery is simple and cost-effective for daily snapshots. Using Datastream would be overkill and more expensive for a daily batch. Federated queries would incur repeated query costs and may not be as fresh.

Answer

Use Cloud SQL managed backups and read from the backup files directly.

Answer

Use Datastream to continuously replicate data to BigQuery and query BigQuery tables.

Answer

Use BigQuery federated queries to connect directly to Cloud SQL.

Question 7

A data engineer needs to perform complex transformations on streaming data as it moves from Cloud Pub/Sub to Cloud Bigtable. Which service should they use for this purpose?

Accepted Answer

Cloud Dataflow. Dataflow (Apache Beam) is the recommended service for stream processing with complex transformations. It can read from Pub/Sub, apply transformations, and write to Bigtable. Cloud Functions are simpler and not suited for complex ETL.

Answer

Dataproc

Answer

Cloud Run

Answer

Cloud Functions

Question 8

A company wants to query data residing in Amazon S3 using BigQuery without copying the data. They also need to join this data with other tables in BigQuery. Which BigQuery feature should they use?

Accepted Answer

BigQuery Omni. BigQuery Omni allows querying data across clouds (including AWS S3) using BigQuery SQL. It supports federated queries of data in S3 without movement. BigQuery federated queries (standard) only work with GCP sources.

Answer

BigQuery federated queries (external tables on S3)

Answer

Cloud Storage transfer service to copy S3 data to GCS, then query

Answer

BigQuery Data Transfer Service for S3

Question 9

You are migrating an on-premises Oracle database to Cloud SQL for PostgreSQL. You need to assess the migration feasibility and convert the schema. Which tools should you use?

Accepted Answer

Use Ora2Pg for schema conversion and DMS for data migration.. Ora2Pg is an open-source tool that converts Oracle schema to PostgreSQL. Database Migration Service (DMS) supports migration from Oracle to Cloud SQL for PostgreSQL. For assessment, you can use tools like Oracle Migration Toolkit or Ora2Pg's assessment mode.

Answer

Use mysqldump to export and import to PostgreSQL.

Answer

Use pg_dump to export the Oracle database and import into Cloud SQL.

Answer

Use DMS directly with Oracle source; it automatically converts schema.

Question 10

A corporation needs to back up its Cloud Spanner database for disaster recovery. They require the ability to restore to a specific point in time within the last 7 days. What backup strategy should they implement?

Accepted Answer

Use Spanner's built-in point-in-time recovery (PITR) which retains versions for 7 days.. Cloud Spanner supports full database backups exported to Avro in Cloud Storage. Incremental backups are not supported natively; point-in-time recovery is achieved via versioning (up to 7 days) but backups are full. For granular recovery, you can use the built-in versioning or schedule frequent exports.

Answer

Use Cloud Scheduler to run incremental backups daily.

Answer

Enable cross-region replicas and restore from the replica.

Answer

Export the database to Cloud Storage every hour using Dataflow.

Question 11

You need to perform a one-time historical data migration from an on-premises MySQL database to Cloud SQL for MySQL. The database is 500 GB, and you can afford several hours of downtime. Which migration method is most straightforward?

Accepted Answer

Use mysqldump to create a SQL dump, then import into Cloud SQL.. Using mysqldump to export the database and then importing it via mysql client is the simplest approach for a one-time migration where downtime is acceptable. DMS is more suitable for continuous migrations with minimal downtime.

Answer

Use Database Migration Service with continuous migration.

Answer

Create a Cloud SQL clone of the on-premises database using mysqldump.

Answer

Use Dataflow to read from MySQL and write to Cloud SQL.

Question 12

A company uses Cloud Bigtable for time-series data. They want to enable disaster recovery with the ability to fail over to another region in minutes. What feature should they enable?

Accepted Answer

Enable Bigtable replication across regions.. Bigtable replication allows you to have multiple clusters in different regions, with automatic replication of data. You can fail over by redirecting traffic to the other cluster. This meets the RTO of minutes.

Answer

Export tables to Cloud Storage and import into another Bigtable instance.

Answer

Configure Bigtable backups and restore in another region.

Answer

Use BigQuery federated queries to access Bigtable data from another region.

Question 13

A financial services company is migrating from Oracle to Cloud SQL for PostgreSQL. They need to ensure data consistency during the migration and minimize downtime. Which two actions should they take? (Choose 2)

Accepted Answer

Use Database Migration Service with continuous migration (CDC) to replicate ongoing changes.. DMS continuous migration with CDC ensures minimal downtime by replicating changes. Connection profiles are necessary to configure source and target. Testing with DMS preview jobs is not a standard feature; DMS provides test jobs. Manual schema conversion is not recommended; use Ora2Pg.

Answer

Manually convert the Oracle schema to PostgreSQL using DDL statements.

Answer

Use DMS preview job to test migration before executing the full job.

Answer

Perform a full export using Oracle Data Pump and import directly into Cloud SQL.

Question 14

An e-commerce platform uses Cloud Spanner for transactions and Cloud Bigtable for session state and recommendations. They want to synchronize customer profile changes from Spanner to Bigtable in near real-time. Which three components should they use? (Choose 3)

Accepted Answer

Cloud Spanner change streams. Spanner change streams capture changes, Pub/Sub provides a scalable messaging channel, and Dataflow can transform and write to Bigtable. Cloud Functions could be used but are less suitable for high-throughput. Cloud Scheduler is batch-oriented.

Answer

Cloud Functions

Answer

Cloud Scheduler

Question 15

A company uses Cloud SQL for MySQL and wants to set up cross-region disaster recovery with automated failover. Which two steps are required? (Choose 2)

Accepted Answer

Create a cross-region read replica with failover enabled.. Enabling automated backups and cross-region copies ensures data is available in another region. Configuring a cross-region read replica with failover capability provides a DR instance that can be promoted in a disaster. Point-in-time recovery is for granular restore, not failover.

Answer

Use Database Migration Service to continuously replicate to another region.

Answer

Deploy Cloud SQL in HA configuration within the same region.

Answer

Set up point-in-time recovery (PITR) with 7-day retention.