PCDE Manage a solution that can span multiple database technologies Practice Test 8 — 15 Questions

Question 1

An IoT company ingests device telemetry into Cloud Bigtable and stores reference metadata in Cloud SQL for MySQL. Analysts want to run SQL joins between recent telemetry and the metadata without moving the metadata into Bigtable or the telemetry into Cloud SQL. The team plans to use BigQuery as the query layer. Which two configurations should they implement? (Choose two.)

Choose an answer to begin — your selection is scored in the full session.

Accepted Answer

Create a BigQuery connection to Cloud SQL for MySQL and define a federated query against the metadata tables.. The join is enabled by two configurations: a BigQuery external table over Cloud Bigtable with row-key prefix filtering to keep scans bounded, and a BigQuery connection to Cloud SQL for MySQL so metadata is queried federated rather than copied. Together they let analysts run SQL joins across both databases in place, which is exactly the no-copy requirement.

Answer

Configure Bigtable replication to a second cluster in the same region and query the replica from BigQuery.

Answer

Export Bigtable telemetry to Cloud Storage as Avro and load it into a native BigQuery table before joining.

Answer

Enable Cloud SQL read replicas and point the BigQuery connection at the replica to reduce load on the primary instance.

Question 2

A media company stores user profiles in Firestore in Native mode and time-series playback events in Cloud Bigtable. A new service must read a user profile and then immediately fetch that user's last 24 hours of playback events. The team wants low-latency reads and wants to avoid maintaining a separate cache. Which two actions should they take? (Choose two.)

Accepted Answer

Design Bigtable row keys with a reversed timestamp prefix followed by the user ID so that a user's recent events are contiguous.. Bigtable performance depends on row key design. Prefixing the row key with the user ID and appending the timestamp lets the service seek directly to one user's events and scan only the last 24 hours. Reversing the timestamp prefix also groups recent events together. Either design makes the primary Bigtable store serve the query efficiently, so no separate cache is needed.

Answer

Create a Cloud Memorystore for Redis instance and write every playback event to it with a 24-hour TTL, then read events from Redis.

Answer

Enable Firestore in Datastore mode so that profile reads can use ancestor queries for stronger consistency.

Answer

Use Bigtable column families with garbage collection policies that drop cells older than 24 hours, and query the entire table for the user.

Question 3

A logistics company uses Cloud Spanner for shipment tracking and Cloud Bigtable for IoT sensor readings. They need a weekly report that correlates shipment delays with sensor anomalies. The sensor data is 100 TB per week and is stored in Bigtable with a row key of sensor ID and timestamp. The shipment data is 1 TB in Spanner. The report must run within a few hours and minimize cost. What should they do?

Accepted Answer

Export the week's Bigtable sensor data to Cloud Storage and load it into BigQuery, export the Spanner shipment data to BigQuery, and run the join there.. Exporting the week's Bigtable data to Cloud Storage and loading it into BigQuery, along with the Spanner shipment data, leverages BigQuery's scale for the join. This uses standard export and load features, avoids continuous replication costs, and can complete within the few-hour window. Other options rely on unsupported federation, unsupported Datastream sources, or custom Dataflow pipelines that add overhead.

Answer

Set up a continuous Datastream pipeline from Spanner to BigQuery and a Dataflow pipeline from Bigtable to BigQuery, then run the weekly join.

Answer

Use BigQuery federated queries to read Spanner and a Bigtable external table, then join in BigQuery.

Answer

Use Cloud Dataflow to read both Spanner and Bigtable and write the joined result directly to a report file in Cloud Storage each week.

Question 4

A healthcare company uses Cloud Firestore for patient profiles and Cloud Bigtable for storing medical device telemetry. They need to generate a daily report that joins patient profiles with the previous day's telemetry and stores the result in Cloud Storage for compliance. The report generation must not impact the performance of the operational databases. What should they do?

Accepted Answer

Export Firestore and Bigtable data to Cloud Storage using managed export tools, then use a Cloud Dataproc job to join the exported data and write the report.. Exporting both datasets to Cloud Storage and then processing them with Dataproc is the best way to avoid impacting the operational databases. Bigtable's export tool and Firestore's managed export both produce files in Cloud Storage, which Dataproc can read in parallel. This decouples the analytical workload from the operational systems and is cost-effective for daily batch reports.

Answer

Create a read replica of Firestore and Bigtable and run the report against the replicas.

Answer

Use a Cloud Dataflow pipeline that reads from Firestore and Bigtable using their respective connectors, performs the join, and writes to Cloud Storage.

Answer

Use BigQuery federated queries to join Firestore and Bigtable directly from BigQuery.

Question 5

A media company runs Cloud SQL for MySQL for its user profiles and Cloud Bigtable for its watch-history events. The database engineer must design a cross-database operational procedure that keeps a user's profile deletion consistent with the removal of that user's Bigtable rows, while minimizing cost and avoiding full-table scans. Which two actions should the engineer take? (Choose two.)

Accepted Answer

Implement a deletion workflow that deletes the Cloud SQL profile in a transaction and enqueues a message with the user ID to a Pub/Sub topic that a worker consumes to perform the Bigtable prefix range delete.. Cross-database consistency is achieved by making the authoritative delete in Cloud SQL transactional and then propagating the user identifier through a durable channel to a worker that performs a targeted Bigtable delete. Designing the Bigtable row key so all of a user's events share a prefix makes that delete a cheap range operation instead of a scan.

Answer

Create a Bigtable column family with a TTL of zero so that rows are removed automatically as soon as the profile is deleted in Cloud SQL.

Answer

Use a Cloud SQL foreign key constraint that references the Bigtable table so that deleting a profile cascades to the watch-history rows.

Answer

Schedule a nightly Cloud Scheduler job that runs a Bigtable query joining the Cloud SQL profile table to the event table and deletes orphaned rows.

Question 6

A logistics company uses Cloud Bigtable for shipment tracking and Cloud Storage for storing scanned delivery receipts. The operations team needs a single dashboard that shows, for a given shipment ID, the latest tracking status from Bigtable and a link to the receipt in Cloud Storage. The team wants to avoid duplicating receipt metadata into Bigtable. What should they do?

Accepted Answer

Create a Bigtable external table in BigQuery that maps the shipment row key, and create a Cloud Storage external table for the receipt objects, then join them on shipment ID.. BigQuery external tables can read both Bigtable and Cloud Storage, so the dashboard can join tracking status with receipt object metadata without copying data. The join happens at query time, and each source remains authoritative. This avoids duplicating receipt metadata into Bigtable and keeps the dashboard simple, which is the correct way to span these two database and storage technologies.

Answer

Configure Cloud Bigtable to store the Cloud Storage object path in a column family, and update it whenever a receipt is uploaded.

Answer

Use Cloud Spanner to store both shipment tracking and receipt metadata, and migrate the Bigtable data into Spanner.

Answer

Use Cloud Dataflow to stream Bigtable rows and Cloud Storage object metadata into Cloud SQL, then build the dashboard on Cloud SQL.

Question 7

A financial analytics firm runs a mission-critical trading dashboard on Cloud SQL for PostgreSQL in us-central1. The application also uses Cloud Bigtable for real-time market data ingestion. The firm needs a single, unified view of all database metrics and logs for compliance reporting, and wants to minimize operational overhead. What should they do?

Accepted Answer

Enable Cloud SQL Insights and Bigtable's built-in monitoring, then use Cloud Monitoring dashboards to aggregate metrics from both services.. Cloud SQL Insights and Bigtable both integrate with Cloud Monitoring, which allows aggregation of metrics and logs across services. Using Cloud Monitoring dashboards provides a unified view, supports compliance reporting, and minimizes operational overhead by leveraging native Google Cloud tooling. The other options require custom development or third-party tools, which are less efficient and more error-prone.

Answer

Export logs and metrics from both services to a self-managed Elasticsearch cluster, then build custom dashboards.

Answer

Deploy a third-party APM agent on all application servers and configure it to scrape metrics from both Cloud SQL and Bigtable.

Answer

Use Cloud SQL's built-in logging and Bigtable's HBase API to write a custom script that polls metrics and stores them in a Cloud Storage bucket.

Question 8

A global retail company uses Cloud Spanner for its order management system and Cloud Firestore for user session data. They need to implement a consistent backup and disaster recovery strategy across both databases. The RPO is 15 minutes and RTO is 1 hour. They want to minimize data loss and ensure recoverability. What should they do?

Accepted Answer

Enable Cloud Spanner backups with a 15-minute schedule and use Firestore's managed export to a multi-region Cloud Storage bucket, then automate restoration with Cloud Scheduler and Cloud Functions.. Cloud Spanner scheduled backups and Firestore managed exports are native, automated features that can meet tight RPO/RTO when combined with orchestration via Cloud Scheduler and Cloud Functions. This strategy ensures consistent backups across both databases and enables rapid restoration. The other options rely on replication or manual processes, which do not protect against data corruption or meet the recovery objectives.

Answer

Set up Cloud Spanner replication to a different region and use Firestore's real-time listeners to replicate data to a secondary database, then fail over manually.

Answer

Export Cloud Spanner data to Cloud Storage using Dataflow, and export Firestore data using the managed export feature, then restore manually when needed.

Answer

Use Cloud Spanner's built-in replication and Firestore's multi-region replication, and rely on their automatic failover capabilities for disaster recovery.

Question 9

A healthcare company uses Cloud SQL for MySQL to store patient records and Cloud Bigtable to store time-series vitals data. They need to implement fine-grained access control such that only authorized applications can read patient records from Cloud SQL and only specific analytics jobs can read vitals from Bigtable. They also need to audit all access. What should they do?

Accepted Answer

Use Cloud IAM with predefined roles for both Cloud SQL and Bigtable, and enable Cloud Audit Logs for data access.. Cloud IAM predefined roles for Cloud SQL and Bigtable provide fine-grained access control, and Cloud Audit Logs capture data access events for compliance. This native integration ensures only authorized applications and analytics jobs can access the respective data, and all access is auditable. The other options either rely on unsupported features, add unnecessary complexity, or do not provide centralized auditing.

Answer

Create database users in Cloud SQL with specific privileges, and use Bigtable's HBase ACLs to restrict access, then enable audit logging at the application level.

Answer

Use Cloud SQL Auth proxy for Cloud SQL and Bigtable's built-in IAM conditions for Bigtable, and export logs to BigQuery for auditing.

Answer

Deploy a service mesh to intercept all database traffic and enforce access policies, and use Stackdriver logs for auditing.

Question 10

A media streaming company uses Cloud Firestore for user profiles and Cloud Bigtable for viewing history. They need to run a complex query that joins user profiles with viewing history to generate personalized recommendations. The query must be executed on-demand and return results within seconds. What should they do?

Accepted Answer

Use BigQuery to query Firestore and Bigtable data by creating external tables, then run a join query in BigQuery.. BigQuery external tables for Firestore and Bigtable enable querying data directly from these services without moving it. A join query in BigQuery can execute quickly and on-demand, meeting the seconds-level requirement. This approach leverages BigQuery's serverless architecture and SQL support, making it ideal for ad-hoc analytical queries across multiple database technologies.

Answer

Use Cloud Spanner to store both user profiles and viewing history, then run a join query directly in Spanner.

Answer

Use Cloud Functions to fetch data from Firestore and Bigtable via their APIs, perform the join in memory, and return the results.

Answer

Use Cloud Data Fusion to create a pipeline that joins Firestore and Bigtable data, then export the results to a Cloud SQL database for querying.

Question 11

A company runs a hybrid environment with an on-premises PostgreSQL database and Cloud SQL for PostgreSQL. They need to migrate the on-premises database to Cloud SQL with minimal downtime and ensure data consistency. They also want to validate the migration before cutover. (Choose two.)

Accepted Answer

Use Datastream to replicate changes from the on-premises PostgreSQL to Cloud SQL, and use Dataflow to validate data.. Database Migration Service (DMS) provides continuous migration and built-in validation, making it ideal for minimal downtime. Datastream with Dataflow also enables continuous replication and validation. Both options leverage native Google Cloud services for a reliable migration. The other options either require manual effort, cause downtime, or are not supported for on-premises sources.

Answer

Use Cloud SQL's built-in replication to create a read replica from the on-premises database, then promote it.

Answer

Use Database Migration Service (DMS) to perform a one-time migration, then manually verify data consistency with checksums.

Answer

Use pg_dump and pg_restore to take a full backup and restore it to Cloud SQL, then set up logical replication manually.

Question 12

Your team manages a Cloud SQL for MySQL instance used by a critical application. You need to ensure the instance is recoverable to any point within the last 4 days, with a Recovery Point Objective (RPO) of under 5 minutes. What configuration steps are required?

Accepted Answer

Enable automated backups and binary logging. Set the transaction log retention period to 4 days.. Point-in-time recovery (PITR) in Cloud SQL for MySQL requires both automated backups and binary logging to be enabled. The transaction log retention period (set via transactionLogRetentionDays) determines how far back you can perform PITR, with a maximum of 7 days. Enabling automated backups alone (option A) does not provide PITR because binary logging is needed. Option B is incorrect because automated backups are required for PITR, and binary logging only without backups does not meet the recovery window. Option C with on-demand daily backups cannot provide an RPO under 5 minutes because binary logging captures ongoing changes. Therefore, option D is correct: enable automated backups, binary logging, and set the transaction log retention to 4 days to meet the 4-day recovery window and RPO under 5 minutes.

Answer

Enable automated backups and set the backup retention to 4 days. Binary logging is not required because automated backups already capture all changes.

Answer

Enable binary logging and set the binary log retention to 4 days. Automated backups are optional and not needed for PITR.

Answer

Create an on-demand backup daily and set binary log retention to 4 days. This provides the same RPO as automated backups with binary logging.

Question 13

You are responsible for a Cloud Spanner instance that serves a global user base. A new feature requires adding an index on a column of an existing table that contains millions of rows. The table is actively used by production traffic. What is the recommended approach to add the index with minimal impact?

Accepted Answer

Use the gcloud command 'gcloud spanner databases ddl update' with a CREATE INDEX statement. The operation will be non-blocking and will not impact production traffic.. Cloud Spanner supports online schema changes, including CREATE INDEX, which run asynchronously in the background without locking the table or blocking production traffic. The recommended approach is to issue the DDL via 'gcloud spanner databases ddl update' with a CREATE INDEX statement; Spanner backfills the index while serving reads and writes normally. This is the standard, low-impact method for adding indexes to large, actively used tables.

Answer

Create a second table with the index already defined, then copy the data using batch writes and switch traffic. This avoids any impact to the original table.

Answer

Take a full database backup to Cloud Storage, restore it to a new instance, create the index on the restored instance, and then fail over to the new instance.

Answer

Use the gcloud command 'gcloud spanner databases ddl update' with an ALTER TABLE statement to add the index. This will lock the table briefly but is acceptable.

Question 14

Your Firestore database in Native mode is used by a mobile app. You need to query a collection where documents are filtered by two fields: 'status' (string) and 'createdAt' (timestamp). The query is not performing as expected. What action is required?

Accepted Answer

Create a composite index on the 'status' and 'createdAt' fields.. Firestore in Native mode requires a composite index to efficiently query documents filtered by multiple fields, such as 'status' and 'createdAt'. Without this index, the query may fail or perform poorly, as Firestore cannot combine separate single-field indexes for equality and range filters. Option C is correct because creating a composite index on both fields enables the query to run as expected.

Answer

Create an index exemption for the collection to allow multi-field queries.

Answer

Add a third field to the query to make it more specific.

Answer

Ensure that single-field indexes exist for both 'status' and 'createdAt'.

Question 15

You need to set up disaster recovery for a Cloud SQL for PostgreSQL instance. The primary instance is in us-central1, and you want a standby in us-west1 that can be promoted to a standalone instance during a regional outage. The solution must minimize data loss and recovery time. Which approach should you take?

Accepted Answer

Create a cross-region read replica in us-west1. During a disaster, promote the replica to a standalone instance.. Creating a cross-region read replica in us-west1 and promoting it during a disaster is the correct approach for Cloud SQL for PostgreSQL. This provides a live standby with asynchronous replication, minimizing data loss (low RPO) and recovery time (low RTO) because promotion is quick and the replica is continuously updated.

Answer

Configure cross-region automated backups with a retention of 7 days. In the event of a disaster, restore the latest backup to a new instance in us-west1.

Answer

Set up an on-premise PostgreSQL instance and configure streaming replication from Cloud SQL to the on-premise instance.

Answer

Use gcloud sql instances create with a backup configuration pointing to us-west1, but this does not create a live standby.