PCDE Practice Exam 59 — 20 Questions

Question 1

You are running a production workload on Cloud Bigtable and notice that read latency has increased. Upon reviewing the monitoring dashboard, you see that CPU utilization is below 50% but the number of active tablets is high. What is the most likely cause of the increased read latency?

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

Accepted Answer

There are too many tablets, causing increased metadata operations and slower reads.. In Cloud Bigtable, each tablet is a contiguous range of rows managed by a tablet server. When the number of active tablets is high, the tablet server must perform more metadata operations (e.g., splitting, merging, and serving multiple tablets) which increases per-request overhead and can degrade read latency. This is true even when CPU utilization is below 50%, because the overhead is not purely CPU-bound but involves increased I/O and coordination.

Answer

Read requests are being throttled due to exceeding IOPS limits.

Answer

A hot node is throttling read requests.

Answer

The cluster is underprovisioned, causing resource contention.

Question 2

A data engineer needs to design a table to store time-series sensor data arriving every second. The data will be queried mainly for the last hour over a specific device. Which table design minimizes query costs?

Accepted Answer

Partition by ingestion_time, cluster by device_id. Minimizes query costs because partitioning by ingestion_time allows the query engine to skip partitions outside the last hour, while clustering by device_id further narrows the scan to only the relevant device's data within those partitions. This combination reduces the amount of data read and the number of files scanned, which is critical for high-frequency time-series data.

Answer

Partition by ingestion_time, cluster by timestamp

Answer

Partition by ingestion_time, no clustering

Answer

No partitioning, cluster by device_id

Question 3

A company wants to create a BI dashboard that shows daily active users. The data is stored in a BigQuery table with columns: user_id, activity_date, and event_type. Which two optimizations would help reduce query costs? (Choose two.)

Accepted Answer

Use a materialized view with COUNT(DISTINCT user_id) grouped by activity_date.. A materialized view precomputes the COUNT(DISTINCT user_id) grouped by activity_date, so queries against it read only the pre-aggregated results rather than scanning the entire base table. This drastically reduces the amount of data processed, lowering query costs in BigQuery's on-demand pricing model where cost is proportional to bytes processed.

Answer

Cluster the table by event_type.

Answer

Use SELECT * and filter in the BI tool.

Answer

Avoid using the LIMIT clause.

Question 4

An organization needs to store backup copies of a Cloud Spanner database for a minimum of 365 days to comply with regulatory requirements. Which backup option should they use?

Accepted Answer

Create a Spanner backup and set the expiration to 365 days. Cloud Spanner backups can be configured with expiration up to 365 days (max). Import/export to Cloud Storage is not a managed backup solution and does not support PITR. Bigtable backups are for Bigtable, not Spanner. Cloud SQL backups are for Cloud SQL.

Answer

Enable point-in-time recovery (PITR) with a 365-day retention period

Answer

Export the database to Cloud Storage using the gcloud command and set a lifecycle policy

Answer

Use Cloud SQL for PostgreSQL with automated backups set to 365 days

Question 5

A company has multiple GCP projects and wants to audit all IAM policy changes. They need a solution that captures who made the change, what was changed, and when. The solution should be cost-effective and require minimal setup. What should they use?

Accepted Answer

Enable Cloud Audit Logs for Admin Activity for all projects.. Cloud Audit Logs for Admin Activity automatically captures all API calls that modify IAM policies, including the identity of the caller, the change made, and the timestamp. This is enabled by default for all GCP projects at no additional cost, making it the most cost-effective and minimal-setup solution for auditing IAM changes.

Answer

Enable Access Transparency logs.

Answer

Use Cloud Asset Inventory to export IAM policies daily.

Answer

Set up Stackdriver (now Cloud Monitoring) alerts on IAM changes.

Question 6

Refer to the exhibit. You are reviewing the following Cloud Spanner DDL statement for a table storing customer orders. What potential performance issue will arise with this schema?

Accepted Answer

The OrderId is likely to be sequentially generated, causing write hotspots. Cloud Spanner uses a distributed architecture that splits data across splits based on the primary key range. If OrderId is sequentially generated (e.g., auto-increment), all new inserts will target the same split, creating a write hotspot that degrades throughput and latency. This is a well-known anti-pattern in Spanner; the recommended approach is to use a UUID or a monotonically increasing key with a hash prefix to distribute writes evenly.

Answer

The primary key includes two columns which reduces insert performance

Answer

The TotalAmount column should be INTEGER for performance

Answer

The table lacks a foreign key constraint

Question 7

A global e-commerce company is designing a Cloud Spanner schema for order processing. They need strong consistency across regions and high write throughput. Orders are identified by a globally unique order ID (UUID). Currently, they use the UUID as the primary key, but they observe write hotspots during peak hours. What primary key design change should they make to distribute writes more evenly?

Accepted Answer

Use a composite primary key starting with a hash of the order ID, followed by the order ID.. Using a composite primary key starting with a hash of the order ID distributes writes evenly across all Cloud Spanner nodes, preventing hotspots. Cloud Spanner uses the first column of the primary key to determine row distribution; a monotonically increasing UUID as the first column causes all new writes to land on the same tablet, creating a hotspot. By hashing the UUID first, writes are spread uniformly across the key space, while the order ID ensures uniqueness.

Answer

Use the timestamp of order creation as the primary key.

Answer

Use a sequential integer primary key with auto-increment.

Answer

Keep UUID as primary key but add a secondary index on a hash of the UUID.

Question 8

You are designing a disaster recovery plan for a Cloud Bigtable instance. The instance has a single cluster in us-east1. You need to ensure that if the cluster becomes unavailable, the database can still serve read and write requests with minimal downtime. Which THREE steps should you take? (Choose three.)

Accepted Answer

Ensure the application can handle eventual consistency between clusters.. Cloud Bigtable uses eventual consistency for replication across clusters. When you add a second cluster and enable replication, data is replicated asynchronously, so writes to one cluster are not immediately visible in the other. The application must be designed to handle eventual consistency to avoid reading stale data after a failover. Option C is correct: adding a second cluster in a different zone (within the same region or a different region) and enabling replication provides redundancy. If the primary cluster fails, the secondary cluster can serve requests. Option D is correct: the application must be configured to route traffic to the secondary cluster in case of primary failure. This can be achieved by using Cloud Bigtable's built-in failover mechanism or by implementing client-side routing logic. Without this routing, traffic would still be directed to the failed primary cluster, causing downtime.

Answer

Enable synchronous replication between clusters.

Answer

Take a full backup of the table to Cloud Storage daily.

Question 9

A company uses Cloud SQL for PostgreSQL for its BI database. Queries involving joins on large tables are slow. Which TWO strategies should they implement to improve join performance? (Choose TWO.)

Accepted Answer

Denormalize tables to reduce the number of joins. Denormalizing tables reduces the number of joins required in queries by combining related data into fewer tables. This directly minimizes the computational overhead of join operations in Cloud SQL for PostgreSQL, which is especially beneficial for large BI datasets where join performance is critical.

Answer

Increase the number of CPU cores on the instance

Answer

Create read replicas for the join queries

Answer

Use connection pooling to reduce connection overhead

Question 10

A company tracks customer demographics that change over time (e.g., address). They need to maintain historical accuracy in BI reports. Which approach correctly implements a Type 2 slowly changing dimension?

Accepted Answer

Add effective start and end date columns for each dimension attribute. Type 2 SCD requires preserving full history by adding effective start and end date columns to the dimension table. This allows BI reports to join on a fact row's transaction timestamp and retrieve the exact dimension attribute values that were current at that point in time, ensuring historical accuracy without data loss.

Answer

Store only the current value and rely on the fact table's timestamp to infer history

Answer

Store only the current value in the dimension table and use an audit log for changes

Answer

Overwrite the old value with the new value

Question 11

You are building a global application that requires strong consistency across regions. The application needs to support SQL queries and horizontal scaling. Which database service should you choose?

Accepted Answer

Cloud Spanner

Answer

Cloud SQL with cross-region replicas

Answer

Cloud Bigtable

Answer

Firestore

Question 12

A DevOps engineer wants to build a CI/CD pipeline that automatically builds and tests code every time a developer pushes a new branch to a Git repository. Which Cloud Build trigger type should they use?

Accepted Answer

Push to any branch trigger. A 'Push to any branch' trigger in Cloud Build automatically initiates a build whenever a developer pushes code to any branch in the repository. This matches the requirement to build and test code on every new branch push, without manual intervention or branch-specific filters.

Answer

Scheduled trigger

Answer

Manual trigger

Answer

Pull request trigger

Question 13

A company wants to run complex analytical queries on terabytes of data with sub-second response times. The data is structured and stored in Cloud Storage as Parquet files. They need a serverless solution that can query the data directly without loading it into a database. Which service should they use?

Accepted Answer

BigQuery. BigQuery is the correct choice because it is a serverless, fully managed data warehouse that supports querying structured data directly from Cloud Storage using external tables, without requiring data loading. It can handle terabytes of data with sub-second response times via its columnar storage, automatic scaling, and BI Engine for acceleration.

Answer

Cloud Dataproc

Answer

Cloud Bigtable

Answer

Cloud SQL

Question 14

An organization is migrating a large on-premises MySQL database to Cloud SQL using DMS. They want to minimize downtime. Which TWO configurations should they implement? (Choose 2)

Accepted Answer

Enable continuous migration with CDC.. Option A is correct because Database Migration Service (DMS) continuous migration uses change data capture (CDC) to replicate ongoing changes from the source MySQL instance to Cloud SQL after the initial full dump, keeping the target in sync and allowing a short cutover window that minimizes downtime. Option B is correct because enabling parallel dump in the migration job splits the initial data load into multiple concurrent threads, which significantly speeds up the bulk load phase of a large database and shortens the overall migration window. Option C is wrong because binary logging must remain enabled on the source; DMS relies on the binary log to read changes for CDC replication. Option D is wrong because a one-time migration job performs only a single full dump with no ongoing replication, so any writes after the dump are lost and downtime at cutover is much longer. Option E is wrong because manual mysqldump and import is a slow, error-prone process with no CDC support, which does not meet the minimal-downtime requirement.

Answer

Disable binary logging on the source.

Answer

Use a one-time migration job.

Answer

Use mysqldump and import manually.

Question 15

You are running a Cloud Bigtable instance for time-series data ingestion. Write throughput has dropped significantly, and you see an increase in 'resource exhausted' errors. The table has one column family and one rowkey format: `#orgId#deviceId#timestamp`. After analyzing cluster metrics, you see that one node is handling most of the traffic. What is the most likely cause?

Accepted Answer

The rowkey design is causing hotspotting on one tablet node due to a prefix (orgId) being written heavily.. The rowkey design `#orgId#deviceId#timestamp` causes hotspotting because all writes for a given `orgId` are directed to a single tablet node. Cloud Bigtable partitions data by rowkey range, and sequential or heavily skewed prefixes (like `orgId`) concentrate write traffic on one node, leading to 'resource exhausted' errors and throughput degradation.

Answer

The column family design is creating too many columns.

Answer

The cluster does not have enough nodes.

Answer

The timestamp is too granular, causing many rows with the same timestamp.

Question 16

You are tuning a Cloud SQL for PostgreSQL instance that runs reporting queries. The slowest query performs a full table scan on a 100 GB table. Which action is most likely to improve performance?

Accepted Answer

Create an index on the columns used in the WHERE clause. A full table scan on a 100 GB table indicates that PostgreSQL has no efficient access path to retrieve the required rows. Creating an index on the columns used in the WHERE clause allows the query planner to use an index scan instead of a sequential scan, drastically reducing the number of disk pages read and improving query performance. This is the most direct and effective optimization for a query that filters rows without an index.

Answer

Increase shared_buffers to 50% of instance memory

Answer

Set statement_timeout to 30 seconds

Answer

Move the query to a read replica

Question 17

A manufacturing company uses Cloud SQL for PostgreSQL for its inventory system. The database has grown and now experiences high read latency. The team wants to improve read performance without changing application code. Which THREE actions should they consider? (Choose 3)

Accepted Answer

Add read replicas. Option A (Add read replicas) is correct because Cloud SQL for PostgreSQL read replicas offload read-only traffic from the primary instance, reducing contention and latency for read-heavy inventory queries without requiring application code changes beyond pointing reads at the replica endpoint. Option B (Create appropriate indexes based on query patterns) is correct because missing or poorly designed indexes force sequential scans over large tables, and adding targeted B-tree indexes on frequently filtered or joined columns directly reduces read latency at the query level. Option E (Increase the instance's memory allocation) is correct because a larger Cloud SQL tier increases the shared_buffers and OS page cache, allowing more of the working set to be served from memory instead of disk, which lowers read latency. Option C (Migrate to Cloud Spanner) is not appropriate here because it is a major re-architecture requiring schema and application changes, contradicting the 'without changing application code' constraint. Option D (Implement database sharding) is also not suitable because sharding requires application-level routing logic and schema changes, adding significant complexity rather than a no-code performance improvement.

Answer

Migrate to Cloud Spanner

Answer

Implement database sharding

Question 18

A company wants to ensure that all new projects created in their Google Cloud organization automatically inherit a set of baseline IAM roles for the security team. Which approach should they use?

Accepted Answer

Grant the roles at the folder or organization level.. IAM policies granted at the folder or organization level are inherited by all child resources, including any new projects created beneath them. This means the security team's baseline roles are automatically applied to every new project without any manual or scripted intervention. This is the recommended Google Cloud approach for enforcing consistent access control across an organization.

Answer

Use Cloud Audit Logs to detect new projects and trigger a Cloud Function to add roles.

Answer

Grant the roles at the project level after each project is created.

Answer

Use a custom script that runs periodically to add roles.

Question 19

A company is using Memorystore for Redis as a session store. They need to scale horizontally to handle increasing traffic and ensure high availability with automatic failover. Which TWO actions should they take?

Accepted Answer

Enable Redis Cluster mode (sharding). Option C is correct because enabling Redis Cluster mode (sharding) in Memorystore for Redis distributes data across multiple shards, allowing the instance to scale horizontally to handle increasing traffic beyond the limits of a single node. Option D is correct because the Standard tier provides a primary and replica configuration with automatic failover, which is exactly what is needed to ensure high availability for the session store. Option A is incorrect because manually creating multiple standalone instances and routing traffic yourself does not provide automatic failover or a managed scaling solution. Option B is incorrect because Persistent Disk is not a Memorystore for Redis feature; Memorystore is an in-memory service and persistence is not configured via Persistent Disk. Option E is incorrect because the Basic tier runs on a single node with no replication or automatic failover, so it cannot meet the high availability requirement.

Answer

Create multiple standalone instances and route traffic manually

Answer

Use Persistent Disk for data persistence

Answer

Use the Basic tier

Question 20

An engineer is configuring Cloud Build to use a substitution variable for the Docker image tag. They want the tag to be the short commit SHA. Which built-in substitution should they use?

Accepted Answer

$SHORT_SHA. $SHORT_SHA is a built-in Cloud Build substitution that resolves to the first seven characters of the commit SHA for the triggering build. It is specifically designed for tagging Docker images with a concise, human-readable commit identifier, avoiding the full 40-character SHA. This makes it the correct choice when a short commit SHA is required for the image tag.

Answer

$TAG_NAME

Answer

$COMMIT_SHA

Answer

$REVISION_ID