PCDE Practice Exam 62 — 20 Questions

Question 1

A company is designing a schema for time-series sensor data in Cloud Spanner. They need to efficiently query the latest reading for each sensor. Which schema design is most appropriate?

Accepted Answer

Create a Sensors table and an interleaved Readings table with primary key (SensorId, Timestamp DESC). The most appropriate because it uses an interleaved Readings table under the Sensors table with the primary key (SensorId, Timestamp DESC). This allows efficient retrieval of the latest reading for each sensor by taking the first row per SensorId using the descending timestamp order. Interleaving ensures that rows for the same sensor are stored together, minimizing cross-node reads. Option A (single table with wide rows) leads to large rows and poor scalability. Option B (Cloud SQL) is not designed for high-throughput time-series data at scale. Option D (Bigtable) is a good choice for time-series but the question specifically requires using Cloud Spanner.

Answer

Use a single table with columns for each sensor and wide rows

Answer

Use Cloud SQL with a normalized schema

Answer

Use Cloud Bigtable with row keys (SensorId#Timestamp)

Question 2

A team uses GitOps with Config Sync to manage multiple GKE clusters. They want to ensure that if a cluster's state drifts from the desired state in the Git repository, it is automatically corrected. Config Sync is installed with the default settings. What else must be configured?

Accepted Answer

No additional configuration is required; drift correction is automatic. Config Sync, by default, operates in a continuous reconciliation loop. It periodically compares the live state of the cluster against the desired state defined in the Git repository. If any drift is detected, Config Sync automatically applies the necessary changes to revert the cluster back to the desired state. Therefore, no additional configuration is needed for automatic drift correction.

Answer

Enable the 'drift correction' option in Config Sync

Answer

Set up a Cloud Scheduler to run 'kubectl apply' periodically

Answer

Install Anthos Config Management with the 'policy controller' enabled

Question 3

A Firestore database is used for a social app. A collection of posts has indexes on fields `author` and `timestamp`. The query `where author == 'user1' order by timestamp desc limit 10` is performing a large number of document reads. What is the likely cause?

Accepted Answer

Missing composite index on (author, timestamp).. The query filters on `author` and orders by `timestamp`, which requires a composite index on `(author, timestamp)` to avoid a full scan. Without this composite index, Firestore must scan all documents matching `author == 'user1'` (or all posts if no single-field index on `author` is used) and then sort them in memory, leading to excessive document reads. The existing single-field indexes on `author` and `timestamp` are insufficient for this combined filter and sort operation.

Answer

The limit is too high.

Answer

The query is scanning all posts.

Answer

Index on timestamp is not descending.

Question 4

A company uses BigQuery to generate daily sales reports. The query aggregates sales by product category and region. The table 'sales_raw' is 500 GB and is updated every hour with new transactions. The report runs slowly. What is the most cost-effective method to improve query performance without changing the existing table schema?

Accepted Answer

Create a materialized view that aggregates sales by product category and region. A materialized view in BigQuery pre-computes and stores the aggregated results of the query, allowing subsequent queries to read the pre-aggregated data instead of scanning the entire 500 GB 'sales_raw' table. This reduces both the data scanned and the query execution time, and it is automatically refreshed when the base table is updated (every hour), making it cost-effective as you only pay for the bytes used by the materialized view and the incremental refreshes, not for full table scans.

Answer

Partition the table by product category

Answer

Create a separate summary table using scheduled queries

Answer

Cluster the table by region

Question 5

A team is designing a Cloud Spanner schema for an e-commerce platform. They have 'Customer' and 'Order' tables and want to ensure that queries for all orders of a specific customer are efficient. Which schema design approach should they use?

Accepted Answer

Interleave the 'Order' table under the 'Customer' table with CustomerID as the first part of Orders' primary key. Spanner supports interleaving tables, where child rows are stored physically near the parent row. Interleaving Orders under Customers using the CustomerID as the first part of the primary key in Orders makes queries for all orders of a customer efficient by colocating related data.

Answer

Use a single table with denormalized customer and order data

Answer

Use a secondary index on CustomerID in the Order table

Answer

Create a separate 'Order' table with a foreign key to Customer

Question 6

A financial services company uses Cloud Spanner for transaction processing. They need to capture real-time changes from the database to stream to a downstream analytics system. Which THREE services or features can achieve this?

Accepted Answer

Cloud Pub/Sub. Spanner change streams are correct because they capture row-level mutations in real time directly from Cloud Spanner. Cloud Pub/Sub is correct as it provides a fully managed messaging service that ingests and delivers change events from Spanner change streams to downstream systems with at-least-once delivery and low latency. Cloud Dataflow is correct because it can process and transform the change stream events in real time, enabling streaming analytics or integration with other services. Together, these three services form a complete pipeline: Spanner change streams capture changes, Pub/Sub transports them, and Dataflow processes them for the analytics system.

Answer

Cloud SQL for PostgreSQL

Answer

Bigtable replication

Question 7

A company uses Cloud Bigtable for time-series data from IoT devices. Each device sends a reading every second. The row key is device_id#timestamp (reverse timestamp). The team reports that queries for a specific device's data over the last hour are fast, but queries for all devices' data over the last minute are very slow. What is the most likely cause?

Accepted Answer

The row key design does not allow efficient scanning for all devices because device_id is the prefix.. The row key design uses device_id as the prefix, which means all data for a given device is co-located in contiguous rows, making per-device scans efficient. However, a query for all devices over the last minute requires scanning every row in the table because the timestamp suffix is reversed and not a prefix; Bigtable cannot perform a range scan across all devices for a recent time window without a full table scan, which is extremely slow.

Answer

The Bigtable cluster does not have enough nodes to handle the scan.

Answer

The query is scanning multiple column families.

Answer

The table has too many tablets, causing high overhead.

Question 8

Which SQL function in BigQuery is best for replacing NULL values in a numeric column with a default value?

Accepted Answer

COALESCE. COALESCE, is correct because it returns the first non-NULL value from a list of expressions, making it ideal for replacing NULLs in a numeric column with a default value. In BigQuery, COALESCE is the standard, flexible function that can handle multiple arguments, unlike IFNULL which only accepts two. This aligns with SQL ANSI standards and is the recommended approach for NULL handling in numeric columns.

Answer

NULLIF

Answer

NVL

Answer

IFNULL

Question 9

You are designing a database architecture for a global e-commerce application. The application requires low-latency reads in multiple regions and must handle up to 100,000 writes per second globally. Which Google Cloud database solution should you use?

Accepted Answer

Cloud Spanner with multi-region configuration.. Cloud Spanner with a multi-region configuration is the correct choice because it provides globally distributed, strongly consistent relational database service with horizontal scalability, supporting over 100,000 writes per second across multiple regions while maintaining ACID transactions and low-latency reads via regional replicas. This meets the requirements of a global e-commerce application needing high write throughput and low read latency in multiple regions.

Answer

Firestore in multi-region mode.

Answer

Bigtable with multiple clusters.

Answer

Cloud SQL with cross-region replication.

Question 10

A bigquery job is running slower than expected. Checking the job information, you see that the slot usage is at 100% for the entire duration of the query. You are using on-demand pricing. What is the most effective way to improve query performance?

Accepted Answer

Purchase a slot reservation and assign the project to it.. With on-demand pricing, your query is limited to the default per-project slot capacity (typically 2,000 slots in BigQuery). If slot usage is at 100% for the entire duration, the query is resource-constrained and cannot be sped up without additional slots. Purchasing a slot reservation and assigning the project to it provides dedicated slots, eliminating the contention and allowing the query to run faster.

Answer

Create materialized views for common aggregations.

Answer

Cluster the tables on frequently filtered columns.

Answer

Partition the tables by date.

Question 11

A company uses Cloud Build to build a multi-module Maven project. They want to run unit tests for module A and integration tests for module B in parallel. In cloudbuild.yaml, how should they configure the steps to run in parallel?

Accepted Answer

steps:
- id: 'unit'
  waitFor: ['-']
  ...
- id: 'integration'
  waitFor: ['-']
  .... In Cloud Build, setting `waitFor: ['-']` on a step makes it start immediately without waiting for any other step, effectively allowing both the 'unit' and 'integration' steps to run in parallel. This configuration meets the requirement to run unit tests for module A and integration tests for module B concurrently.

Answer

steps:
- id: 'unit'
  ...
- id: 'integration'
  waitFor: ['-']
  ...

Answer

steps:
- id: 'unit'
  waitFor: ['previous']
  ...
- id: 'integration'
  waitFor: ['unit']
  ...

Answer

Steps run sequentially by default; parallelism is not supported.

Question 12

A gaming company uses Cloud Spanner to store player profiles and game state. They need to run a one-time analytic query on historical data that would take minutes and cannot impact production performance. What is the best approach?

Accepted Answer

Export the required data to BigQuery using Dataflow and run the query there. Exporting the data to BigQuery via Dataflow isolates the analytic workload from Cloud Spanner, ensuring zero impact on production performance. Cloud Spanner is designed for transactional workloads, not heavy analytic queries that take minutes, and BigQuery is purpose-built for such analytics. This approach also avoids consuming Spanner's CPU or memory resources, which could degrade real-time game state operations.

Answer

Create a read-only replica and query it

Answer

Use the gcloud spanner databases execute-sql command with --query-mode=read-only

Answer

Run the query using a read-only transaction with strong reads

Question 13

A company wants to run complex analytical queries on terabytes of sales data with sub-second query response times for dashboards. Data is updated frequently in near real-time. Which combination of services is most appropriate?

Accepted Answer

AlloyDB with columnar engine. AlloyDB with its columnar engine supports both high-speed transactions and fast analytical queries on the same data, fulfilling near-real-time analytics requirements.

Answer

Cloud Spanner with interleaved tables

Answer

Cloud SQL for MySQL with read replicas

Answer

Bigtable with aggregation queries

Question 14

A DevOps engineer is setting up Docker credential helper for Artifact Registry on a Cloud Build worker. They want the build steps to authenticate to Artifact Registry without storing service account keys. What is the recommended approach?

Accepted Answer

Run gcloud auth configure-docker as a build step; Cloud Build's service account will be used automatically.. `gcloud auth configure-docker` configures Docker to use `gcloud` as a credential helper, which automatically uses the Cloud Build worker's attached service account to obtain short-lived access tokens for Artifact Registry. This avoids storing any long-lived service account keys, aligning with Google Cloud's security best practices for CI/CD pipelines.

Answer

Use a custom builder that includes Docker with Artifact Registry credentials.

Answer

Store a service account key in Cloud Secret Manager and load it in a build step.

Answer

Set environment variable ARTIFACT_REGISTRY_KEY in cloudbuild.yaml.

Question 15

Your company is deploying a new application on Google Cloud and needs to choose a database solution. The application requires strong transactional consistency, complex SQL queries, and the ability to scale horizontally for read-heavy workloads. Which database service should you recommend?

Accepted Answer

Cloud Spanner. Cloud Spanner is the correct choice because it provides strong transactional consistency (ACID) across globally distributed nodes, supports complex SQL queries with standard SQL syntax, and offers horizontal scaling for read-heavy workloads through automatic sharding and read replicas. Unlike other options, Spanner uniquely combines these three requirements—strong consistency, SQL, and horizontal scaling—in a single managed service.

Answer

BigQuery

Answer

Cloud SQL

Answer

Cloud Firestore

Question 16

A data analytics company uses Bigtable for high-throughput writes. They notice that some rows are receiving a disproportionate number of reads and writes, causing performance degradation. Which tool should they use to identify and resolve this hot spotting issue?

Accepted Answer

Key Visualiser. Key Visualizer is the correct tool because it is specifically designed to analyze Bigtable access patterns and visualize hot spotting—where a small number of rows or row ranges receive a disproportionate share of reads and writes. It provides heatmaps and time-series graphs that pinpoint the exact keys causing performance degradation, enabling you to redesign your row keys or implement salting to distribute load evenly.

Answer

cbt tool

Answer

Cloud Logging

Answer

Cloud Monitoring

Question 17

A company uses Cloud Bigtable with replication across two regions. They want to ensure that read requests are automatically routed to the nearest available cluster with the lowest latency. Which routing policy should be configured?

Accepted Answer

Multi-cluster routing. Bigtable replication supports two routing policies: single-cluster routing and multi-cluster routing. Multi-cluster routing allows the Bigtable client to route requests to the nearest cluster based on latency, and it automatically fails over to another cluster if the first is unavailable. Any-replica routing is not a valid Bigtable policy. Read-failover routing is a Cloud DNS concept, not Bigtable. Single-cluster routing forces all requests to a specific cluster.

Answer

Single-cluster routing

Answer

Any-replica routing

Answer

Read-failover routing policy

Question 18

An engineer is building a CI/CD pipeline for a microservice that uses feature flags via LaunchDarkly. They want to automatically enable a new feature flag in a staging environment after deployment, but not in production. How should they integrate this into Cloud Deploy?

Accepted Answer

Configure a postDeploy hook in Cloud Deploy that invokes a Cloud Run Job to enable the flag. Cloud Deploy's postDeploy hooks allow you to run a Cloud Run Job after a rollout completes, which can call the LaunchDarkly REST API to enable the flag specifically for the staging target. This integrates flag management directly into the pipeline without manual steps or exposing production flags.

Answer

Use a Cloud Build step after deployment to call LaunchDarkly API

Answer

Enable the flag manually in LaunchDarkly after deployment

Answer

Use a Kubernetes ConfigMap to hold flag state, and have the app read it

Question 19

Which TWO database services are fully managed and support global distribution of data for low-latency reads and writes?

Accepted Answer

Cloud Spanner. Cloud Spanner is a fully managed, globally distributed relational database service that provides strong consistency and horizontal scaling across regions. It supports global distribution of data for low-latency reads and writes by using synchronous replication and atomic clocks for TrueTime, enabling ACID transactions at global scale.

Answer

Memorystore

Answer

Bigtable

Answer

Cloud SQL

Question 20

A company is using Cloud Bigtable and wants to set up monitoring and alerting for replication lag between clusters. Which TWO metrics should they use? (Choose 2)

Accepted Answer

cloudbigtable.googleapis.com/cluster/replication_lag. `replication_lag` directly measures the time difference between the primary cluster and a replica cluster in Cloud Bigtable, which is the key metric for monitoring replication delay. Option D is also correct because `replication_delay` is another metric that tracks the same concept, often reported in seconds, and is used to alert when replicas fall behind. Both metrics are essential for ensuring data consistency and timely failover in multi-cluster Bigtable deployments.

Answer

cloudbigtable.googleapis.com/cluster/disk_usage

Answer

cloudbigtable.googleapis.com/cluster/cpu_load

Answer

cloudbigtable.googleapis.com/cluster/operations_count