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

Question 1

A media company stores user profiles in Cloud SQL for MySQL and viewing history in Cloud Bigtable. They need to build a unified user activity report that joins profile attributes with recent viewing events. The report must run daily and return within minutes, and the company wants to minimize data movement and operational overhead. Which two steps should they take? (Choose two.)

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

Accepted Answer

Export Bigtable viewing history to Cloud Storage using a Dataflow template and load it into BigQuery for the join.. Federated queries let BigQuery read Cloud SQL for MySQL without copying profile data, reducing movement and pipeline work. Since Bigtable cannot be queried directly from BigQuery, exporting viewing history to Cloud Storage and loading it into BigQuery is the managed way to make it available for the join. Together these steps meet the daily, minutes-level report with minimal overhead.

Answer

Query Bigtable directly using the BigQuery Bigtable external data source and join with federated Cloud SQL data.

Answer

Create a Cloud SQL read replica and point BigQuery federated queries at the replica to avoid impacting the primary.

Answer

Use Datastream to replicate Bigtable into BigQuery and use federated queries for Cloud SQL.

Question 2

A financial services company runs a global application on Cloud Spanner for transactional data and Cloud Bigtable for time-series market data. They need a unified, low-latency query layer that can join Spanner customer records with Bigtable market data without copying data between systems. They want to minimize data movement and operational overhead. What should they do?

Accepted Answer

Implement an application-level join using the Cloud Spanner client library and the Bigtable client library, fetching related rows by key and joining in memory.. An application-level join is the most practical way to combine Spanner and Bigtable data without copying. The application can use the Spanner client to retrieve the customer record and the Bigtable client to retrieve market data using a row key derived from the customer ID. This keeps data in place, avoids ETL, and provides low latency, though it requires the application to handle the join logic and any consistency requirements.

Answer

Use Cloud Spanner's external table feature to query Bigtable directly from Spanner SQL.

Answer

Use Dataflow to stream both Spanner and Bigtable data into a Cloud SQL for PostgreSQL instance, then join there.

Answer

Use BigQuery federated queries with the Cloud Spanner and Bigtable external data sources to join the data in place.

Question 3

A healthcare company runs Cloud SQL for MySQL for patient records and Cloud Bigtable for device telemetry. They need to build a unified analytics layer in BigQuery that combines both sources, and they want to minimize operational overhead while keeping data fresh. Which two approaches should they use? (Choose two.)

Accepted Answer

Use Datastream to stream changes from Cloud SQL for MySQL into BigQuery. Datastream streams Cloud SQL for MySQL changes into BigQuery with managed change data capture, keeping patient records fresh with minimal operations. The Cloud Bigtable BigQuery connector lets BigQuery query Bigtable telemetry directly, so both sources can be combined in one analytics layer without building custom pipelines or copying data between systems.

Answer

Replicate Cloud Bigtable into Cloud SQL for MySQL and then query it through BigQuery federated queries

Answer

Use Cloud Data Fusion to build a custom pipeline that reads from both sources and writes to BigQuery

Answer

Export Cloud SQL for MySQL to Cloud Storage nightly and load the files into BigQuery

Question 4

A media company stores user profiles in Cloud Firestore and video metadata in Cloud Bigtable. They are designing a service that must span both databases and need to choose integration patterns that minimize duplicate data and operational overhead. (Choose two.)

Accepted Answer

Have the application write to both Firestore and Cloud Bigtable in the same request, using idempotent writes and a reconciliation job for failures.. Two viable patterns are event-driven denormalization through Cloud Functions and application-level dual writes with idempotency and reconciliation. Both avoid copying the full profile dataset and avoid running a separate ETL platform. The event-driven approach keeps writes close to Firestore changes, while dual writes give the application control over exactly which fields go to each store and how failures are repaired.

Answer

Enable Firestore real-time listeners to stream changes directly into Cloud Bigtable without an intermediary.

Answer

Replicate the entire Firestore database into Cloud Bigtable using a nightly batch export.

Answer

Use Cloud Spanner as a two-phase commit coordinator between Firestore and Cloud Bigtable.

Question 5

A fintech firm keeps transactional ledgers in Cloud Spanner and analytical copies in Cloud Bigtable. A compliance audit requires that a queryable, point-in-time snapshot of the Spanner ledger be retained for seven years at the lowest storage cost, and that the snapshot be readable by BigQuery without restoring into a live Spanner instance. Which combination of steps should the team take?

Accepted Answer

Run a Dataflow job that reads Spanner and writes Parquet files to a Cloud Storage bucket, then define a BigQuery external table over that bucket and set a bucket retention lock.. Exporting a point-in-time Spanner dataset to Parquet in Cloud Storage via Dataflow, then querying it with a BigQuery external table, produces a queryable snapshot without restoring into Spanner. A bucket retention lock enforces the seven-year compliance period, and archival storage classes keep the cost low. Spanner backups cannot be exported as Avro or queried directly, and change streams capture mutations rather than a snapshot.

Answer

Use Spanner change streams to publish ledger changes to Pub/Sub, then sink them into Cloud Bigtable and query Bigtable with the BigQuery Bigtable connector.

Answer

Create a Spanner backup, keep it for seven years, and query the backup directly from BigQuery using a BigQuery connection to Spanner.

Answer

Create a Spanner backup, then export the backup to a Cloud Storage bucket in Avro format and query the exported files with a BigQuery external table.

Question 6

A media company uses Cloud Bigtable for time-series sensor data and Cloud Firestore for user profiles. They want a single application layer that can query both databases without duplicating data. They need to minimize latency for point reads and support SQL-like joins across the two datasets. Which two Google Cloud services should they use? (Choose two.)

Accepted Answer

BigQuery BI Engine to accelerate joins between Bigtable and Firestore external tables. BigQuery federated queries and BI Engine together allow SQL joins across Cloud Bigtable and Firestore without duplicating data, and BI Engine accelerates those queries in memory. The other options either cannot federate to those services, are batch-oriented, or do not provide a unified low-latency SQL layer.

Answer

Cloud Data Fusion with Bigtable and Firestore plugins for a unified SQL layer

Answer

Dataproc with HBase and Firestore connectors for cross-database joins

Answer

Cloud Spanner as a federated query engine over Bigtable and Firestore

Question 7

A financial analytics team runs an operational PostgreSQL database on Cloud SQL and a petabyte-scale analytics dataset in Cloud Bigtable. They need a daily job that reads the previous day's Cloud SQL transactions and writes them into Cloud Bigtable with row keys optimized for time-range scans. The job must run with minimal operational overhead and must not expose Cloud SQL credentials to the analytics code. Which approach should the database engineer implement?

Accepted Answer

Export Cloud SQL to CSV in Cloud Storage, then load the files into Bigtable using the Cloud Bigtable HBase client from a Dataflow pipeline.. The requirement is a scheduled, low-overhead pipeline that moves Cloud SQL data into Cloud Bigtable with time-optimized row keys while keeping database credentials out of the analytics code. Exporting to Cloud Storage and using a Dataflow pipeline with the Bigtable connector satisfies all constraints: managed orchestration, service-account-based authentication, and programmatic control over row key design.

Answer

Configure a Cloud SQL replica, then run a cron job on a Compute Engine VM that uses the psql client and the Bigtable cbt CLI to copy rows.

Answer

Use Datastream to replicate the Cloud SQL PostgreSQL changes into Cloud Bigtable, then query Bigtable directly for analytics.

Answer

Create a federated query in BigQuery that reads Cloud SQL and Cloud Bigtable, then schedule it to write results back into Bigtable using the BigQuery Storage Write API.

Question 8

A financial services firm uses Cloud Spanner for its ledger and Cloud Bigtable for market data time series. Analysts need to run ad-hoc SQL joins between ledger entries and market data for a specific trading day. The market data for that day is about 2 TB, and the ledger snapshot is 50 GB. They want to avoid moving data permanently and need results within an hour. What should they do?

Accepted Answer

Export the Bigtable market data to Cloud Storage and load it into BigQuery, export the Spanner ledger snapshot to BigQuery, and run the join there.. BigQuery is the right tool for joining large datasets ad-hoc. Exporting Bigtable to Cloud Storage and loading into BigQuery, plus exporting the Spanner ledger snapshot, enables the join without permanent data movement. This approach uses standard export and load mechanisms and can meet the one-hour window for 2 TB. Federated queries do not cover Bigtable, and building a Dataflow pipeline is overkill for a one-time analysis.

Answer

Use BigQuery federated queries to read Spanner directly and Bigtable via the BigQuery Bigtable connector, then join in BigQuery.

Answer

Create a Cloud Dataflow pipeline that reads both Spanner and Bigtable and writes the joined result to BigQuery on a schedule.

Answer

Use Cloud Spanner external tables to query Bigtable data directly from Spanner and perform the join in Spanner.

Question 9

A financial analytics platform ingests market data into Cloud Bigtable and also maintains a normalized reference dataset in Cloud SQL for PostgreSQL. Analysts run a daily batch job that must join Bigtable rows with the PostgreSQL reference table. The team wants to minimize data movement and operational overhead while keeping both databases as the systems of record. What should they do?

Accepted Answer

Use BigQuery federated queries with the Cloud SQL connection to join Bigtable external tables and Cloud SQL tables in a single SQL statement.. BigQuery federated queries let a single SQL statement combine Bigtable external tables with Cloud SQL tables through the BigQuery Connection API, so the join happens without copying or transforming data. Both databases stay authoritative, and the batch job becomes a scheduled query rather than a custom pipeline. This is the lowest-overhead way to span the two database technologies while preserving their roles.

Answer

Create a Cloud Spanner instance, migrate both datasets into it with the Data Migration Service, and run the join there.

Answer

Export the Cloud SQL reference table to CSV in Cloud Storage, then import it into a new Bigtable column family and perform the join with a Cloud Dataflow pipeline.

Answer

Configure Cloud SQL for PostgreSQL as a replica of Bigtable using Datastream, then run the join inside PostgreSQL.

Question 10

A retail company uses Cloud SQL for MySQL for its transactional workload and Cloud Firestore for user session data. They need to generate daily reports that combine sales data from Cloud SQL with user session data from Firestore. The reports are run once per day and can tolerate some latency. Which solution should they use to minimize operational overhead and cost?

Accepted Answer

Use BigQuery federated queries to query Cloud SQL and Firestore directly, then join in BigQuery. BigQuery federated queries can directly access both Cloud SQL and Firestore, enabling a join without data movement. This reduces operational overhead and cost because no ETL pipeline or replication is needed. The other options require custom pipelines, manual exports, or CDC, all of which add complexity and maintenance. For daily reports with tolerable latency, federated queries are the most efficient choice.

Answer

Use Cloud Dataflow to read from both Cloud SQL and Firestore, perform the join, and write the results to BigQuery

Answer

Export Cloud SQL data to Cloud Storage and Firestore data to BigQuery, then join in BigQuery

Answer

Replicate Firestore data into Cloud SQL using a change-data-capture tool, then query Cloud SQL directly

Question 11

A retail company uses Cloud SQL for MySQL for its online transaction processing and Cloud Bigtable for real-time inventory lookups. They need to ensure that when a customer places an order, the inventory count in Bigtable is decremented and the order is recorded in Cloud SQL. They want to minimize latency and avoid double-decrementing inventory if the order process is retried. What should they do?

Accepted Answer

Use Bigtable's CheckAndMutateRow to atomically check the current inventory and decrement it, using the order ID as part of the row key to ensure idempotency.. Bigtable's CheckAndMutateRow operation provides atomic read-modify-write on a single row, which is perfect for inventory decrement. By designing the row key to include the order ID or by using a separate qualifier to track processed orders, you can make the operation idempotent and prevent double-decrementing on retries. This keeps latency low because it avoids cross-database coordination.

Answer

Use a Cloud SQL transaction to insert the order, then use a database trigger to call a Cloud Function that updates Bigtable.

Answer

Use a Cloud SQL transaction to decrement inventory in a mirrored table, then asynchronously replicate to Bigtable.

Answer

Use a distributed transaction with XA between Cloud SQL and Bigtable.

Question 12

A logistics company uses Cloud SQL for PostgreSQL for shipment records and Cloud Bigtable for vehicle telemetry. They need a nightly batch process that joins the two datasets to produce delivery performance reports, and they want to avoid impacting the production Cloud SQL instance. Which approach should they use?

Accepted Answer

Export Cloud SQL data to Cloud Storage, load it into BigQuery, and use BigQuery external tables to read Cloud Bigtable for the join.. The cleanest way to avoid production impact is to move the analytical workload off Cloud SQL. Exporting shipment records to Cloud Storage and loading them into BigQuery, then joining with Cloud Bigtable through an external table, uses BigQuery's scale for the join. This keeps the transactional instance free for shipment processing while still producing nightly reports.

Answer

Run the join directly on the production Cloud SQL instance using a foreign data wrapper to Cloud Bigtable.

Answer

Use Dataproc with the HBase client to read Cloud Bigtable and JDBC to read Cloud SQL, then write results to Cloud Storage.

Answer

Create a read replica of Cloud SQL for PostgreSQL and perform the join on the replica using a Bigtable connector.

Question 13

A healthcare company operates a Cloud SQL for MySQL primary instance and a Cloud SQL for PostgreSQL primary instance in the same project. The security team requires that all database connections use IAM-based authentication and that no database passwords be stored in application configuration. The applications run on Compute Engine VMs in a shared VPC. Which two actions should the team take? (Choose two.)

Accepted Answer

Enable the Cloud SQL IAM database authentication flag on both instances and create IAM database users for the applications.. IAM database authentication on Cloud SQL replaces database passwords with short-lived tokens tied to IAM principals. Enabling the flag and creating IAM database users is the core configuration, and the Cloud SQL Auth Proxy is the client that supplies those tokens automatically. Together they remove passwords from application configuration for both MySQL and PostgreSQL instances, meeting the security policy.

Answer

Enable the cloudsql.iam.login permission on the service accounts and grant them the Cloud SQL Client role.

Answer

Create a Cloud VPN tunnel between the VMs and the Cloud SQL instances to encrypt all database traffic.

Answer

Store the database passwords in Secret Manager and have the applications retrieve them at startup.

Question 14

A startup runs a Cloud SQL for MySQL instance for its primary application and wants to add a read-heavy analytics dashboard. The dashboard queries would compete with transactional workloads, so the team wants to offload reads to a separate database that supports SQL and can scale horizontally. They want minimal changes to the application and no data duplication. What should they use?

Accepted Answer

Cloud SQL read replicas. Cloud SQL read replicas replicate the primary instance and serve read-only queries, allowing analytics to run without impacting transactional performance. They require minimal application changes and no separate data duplication. The other options introduce incompatible interfaces, additional latency, or data copying that the scenario rules out.

Answer

Cloud Spanner with a MySQL-compatible interface

Answer

Cloud Bigtable with an HBase client

Answer

BigQuery with federated queries to Cloud SQL

Question 15

A media company uses Cloud Bigtable for storing user interaction events and Cloud Spanner for user profile data. They need to implement a real-time recommendation engine that reads from both databases with low latency. The solution must ensure that reads are consistent and do not impact the write performance of either database. Which two approaches should they use? (Choose two.)

Accepted Answer

Use Cloud Spanner's stale reads to reduce latency by reading slightly older data. Cloud Spanner read-only transactions and stale reads both provide consistent, low-latency reads without locking or impacting write performance. Read-only transactions are lock-free and can be served from any replica; stale reads allow reading from local replicas with bounded staleness. Together, they enable the recommendation engine to query Spanner efficiently. For Bigtable, reads can be distributed across clusters, but there is no separate read replica; however, the question requires two approaches, and the Spanner options are correct.

Answer

Use Cloud Bigtable's single-row transactions to ensure atomicity when reading events

Answer

Use Cloud Bigtable's change streams to capture writes and feed them into a separate read-optimized store

Answer

Use Cloud Bigtable's read replicas to offload read traffic from the primary cluster