PCDE Practice Exam 1 — 20 Questions

Question 1

A company is planning to use Cloud Spanner for a new global application. They estimate a peak write throughput of 10,000 mutations per second. What is the minimum number of processing units (PUs) required, given that each PU supports up to 2000 mutations/second?

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

Accepted Answer

5 processing units. Cloud Spanner's write throughput scales with the number of processing units (PUs), with each PU supporting approximately 2,000 mutations per second. To sustain a peak of 10,000 mutations per second, you divide 10,000 by 2,000, which equals 5 PUs. Therefore, 5 processing units is the minimum required to meet the stated write throughput.

Answer

1 processing unit

Answer

100 processing units

Answer

10 processing units

Question 2

Refer to the exhibit. A BI query is performing slowly. The query plan shows a large shuffle in the aggregate stage. The table is not partitioned or clustered. Which optimization would most directly reduce the shuffle size?

Accepted Answer

Clustering the table on the grouping columns.. Clustering the table on the grouping columns physically co-locates rows with the same group key values within the same storage units (e.g., files or partitions). This allows the query engine to perform partial aggregation locally before the shuffle, dramatically reducing the amount of data that must be moved across the network during the aggregate stage. In systems like BigQuery or Spark SQL, clustering on grouping columns directly minimizes shuffle size by enabling pre-aggregation at the storage layer.

Answer

Converting the query to use a window function.

Answer

Using a materialized view.

Answer

Adding a WHERE clause to filter recent data.

Question 3

An engineer needs to restore a Cloud Spanner database from a backup that was taken 2 hours ago. The backup is stored in Cloud Storage. What steps are required?

Accepted Answer

Use gcloud spanner databases restore --backup= --destination-database=. To restore a Cloud Spanner database from a backup, use the `gcloud spanner databases restore` command and specify the backup ID and destination database. This is the correct method. Import/export uses Cloud Storage for moving data, but restoring a backup is a separate operation and cannot be done with the import command.

Answer

Create a new instance and use gcloud spanner instances restore.

Answer

Use gcloud spanner databases import from the Cloud Storage backup file.

Answer

Restore is not possible; you must use point-in-time recovery.

Question 4

Your team needs to add a new non-nullable column with a default value to a large Cloud Spanner table. The table has thousands of simultaneous writes per second. Which approach minimizes downtime and resource usage?

Accepted Answer

Use ALTER TABLE ADD COLUMN with a non-null default value. Adding a non-nullable column with a default value in Cloud Spanner is a metadata-only operation that does not rewrite existing rows or block reads/writes. This minimizes downtime and resource usage even under thousands of concurrent writes per second, as the default value is applied logically at read time.

Answer

Use ALTER TABLE ADD COLUMN without a default value and then update rows in batches

Answer

Create a new table and use batch operations to copy data

Answer

Drop and recreate the table with the new column

Question 5

A BI team uses BigQuery to report on customer orders. The 'customers' dimension table is updated nightly with Type 2 Slowly Changing Dimensions (SCD). However, some reports show incorrect historical aggregates because the fact table references only the current customer key. Which approach resolves this issue?

Accepted Answer

Store the surrogate customer key from the dimension table in the fact table at transaction time. With Type 2 SCD, each customer row has a unique surrogate key that represents a specific version of the customer's attributes over time. Storing that surrogate key in the fact table at transaction time ensures that historical facts are permanently linked to the correct customer attributes as they existed at the time of the order. This prevents incorrect aggregates when the dimension table is updated, as the fact table will always join to the precise version of the customer record that was active when the transaction occurred.

Answer

Update the fact table nightly to replace old customer keys with the current key

Answer

Denormalize customer attributes into the fact table

Answer

Use the natural customer ID in the fact table and join with the dimension using a BETWEEN condition on effective dates

Question 6

A company wants to implement least-privilege access for service accounts. Which THREE practices should they follow? (Choose 3)

Accepted Answer

Use Workload Identity Federation instead of service account keys.. Option A is correct because Workload Identity Federation lets external workloads (e.g., on AWS, Azure, or GitHub) impersonate a service account via short-lived OIDC tokens, eliminating long-lived JSON key files that are the primary credential-leak risk. Option D is correct because when keys must be used, regularly rotating them limits the window of exposure if a key is compromised; rotation should be automated and old keys disabled/deleted promptly. Option E is correct because custom IAM roles let you define a permission set containing only the specific permissions the service account needs, which is the essence of least privilege when predefined roles are too broad. Option B is wrong because primitive roles (Owner, Editor, Viewer) are coarse-grained and grant far more access than needed, directly violating least privilege. Option C is wrong because predefined roles are not inherently least-privilege; they often bundle extra permissions, so choosing them merely for compatibility does not minimize access.

Answer

Grant primitive roles for simplicity.

Answer

Use predefined roles to ensure compatibility.

Question 7

A retail company is deploying a new Cloud SQL for MySQL instance for an order management system. The database must survive a zonal failure with minimal downtime and no data loss for committed transactions. They want the simplest configuration that meets this requirement. What should they do?

Accepted Answer

Create the instance with high availability enabled, which provisions a standby in a different zone with synchronous replication and automatic failover.. Enabling high availability on a Cloud SQL for MySQL instance provisions a standby in a different zone with synchronous replication and automatic failover. This provides minimal downtime and no committed data loss during a zonal failure, which is the simplest configuration that meets both requirements for the order management workload.

Answer

Create the instance with a single zone and configure a maintenance window during off-peak hours to reduce the impact of zonal failures.

Answer

Create the instance with a single zone and add a cross-region read replica, then promote the replica if the primary zone fails.

Answer

Create the instance with a single zone and enable automated backups with binary logging for point-in-time recovery.

Question 8

A company is setting up access control for a BigQuery dataset using the above IAM policy. An analyst who is a member of the group 'analysts@example.com' also has the user account 'analyst@example.com'. They need to create new tables in the dataset. What will be the outcome?

Accepted Answer

The analyst can create tables because they have dataOwner role on their user account.. IAM policies grant permissions based on the union of all roles assigned to the user, regardless of whether they come from group membership or direct user assignment. The analyst has the `dataOwner` role directly on their user account, which includes the `bigquery.tables.create` permission required to create new tables. Group membership with a lower-privilege role (e.g., `dataViewer`) does not override or conflict with the higher-privilege role on the user account.

Answer

The analyst will get an error because of conflicting roles.

Answer

The analyst cannot create tables because the group only has dataViewer.

Answer

The analyst can create tables if they also have jobUser role.

Question 9

A company wants to reduce BigQuery query costs for their BI workloads. Which THREE actions effectively lower the amount of data processed per query? (Choose THREE.)

Accepted Answer

Use partitioned tables on date column. Partitioned tables in BigQuery allow queries to use the WHERE clause to filter on the partition column (e.g., a date column), so BigQuery can prune entire partitions from the scan. This directly reduces the amount of data read and billed, lowering query costs. Option A is correct because it is a primary cost-control mechanism in BigQuery.

Answer

Use LIMIT in subqueries to reduce output

Answer

Use SELECT * to avoid missing columns

Question 10

A company runs a microservices application on GKE. They want to automatically adjust both the number of pods (for varying load) and the resource limits of individual pods (to avoid resource waste). Which two Kubernetes resources should they configure together? (Choose two.)

Accepted Answer

Vertical Pod Autoscaler (VPA). The Horizontal Pod Autoscaler (HPA) (C) is correct because it automatically scales the number of pod replicas in a Deployment, ReplicaSet, or StatefulSet based on observed metrics such as CPU utilization, memory, or custom metrics, directly addressing the requirement to adjust pod count for varying load. The Vertical Pod Autoscaler (VPA) (B) is correct because it automatically adjusts the CPU and memory requests/limits of individual pods (typically by evicting and recreating them with updated resource values), which addresses the requirement to tune resource limits and avoid resource waste. Used together, HPA handles horizontal scaling while VPA handles vertical right-sizing. PodDisruptionBudget (A) is not correct because it only controls voluntary disruptions to maintain availability during operations like node drains, not autoscaling. Cluster Autoscaler (D) is not correct because it scales the number of nodes in the cluster, not pods or pod resource limits. Node Auto-Provisioning (E) is not correct because it dynamically creates node pools with appropriate machine types, again operating at the node level rather than adjusting pod counts or pod resource limits.

Answer

PodDisruptionBudget

Answer

Cluster Autoscaler

Answer

Node Auto-Provisioning

Question 11

A team is adopting GitOps for infrastructure. They want to ensure that all Terraform configuration changes are automatically applied after merging to the main branch. Which CI/CD approach best supports this?

Accepted Answer

Use a CI/CD pipeline that runs `terraform apply` after a merge to the main branch.. GitOps requires that the desired state in the main branch be the single source of truth, and that merges to main automatically trigger reconciliation. A CI/CD pipeline that runs `terraform apply` after a merge to main implements this declarative, automated flow. This is the canonical GitOps pattern for Terraform.

Answer

Run `terraform plan` on every commit and require manual approval before apply.

Answer

Have developers apply changes locally using `terraform apply`.

Answer

Use Cloud Deployment Manager with a trigger on Cloud Source Repository commits.

Question 12

A team uses Helm charts to deploy applications to GKE. They need to manage environment-specific configurations (e.g., dev, staging, prod) using a single chart. Which tool should they use?

Accepted Answer

Helm with values files. Helm with values files is the standard way to manage environment-specific configurations with a single chart. The chart contains templated Kubernetes manifests, and separate values files (values-dev.yaml, values-staging.yaml, values-prod.yaml) override defaults per environment, allowing one chart to deploy consistently across dev, staging, and prod.

Answer

Kustomize

Answer

Skaffold

Answer

Config Connector

Question 13

Which THREE components are required to compute a 7-day moving average of daily sales using a window function? (Choose three.)

Accepted Answer

AVG() function. The AVG() function is the aggregate function that computes the arithmetic mean of the sales values over the specified window frame. In a moving average calculation, AVG() is applied to the rows defined by the window frame to produce the average for each row.

Answer

PARTITION BY product

Answer

WINDOW clause

Question 14

Your company runs a critical application on Google Kubernetes Engine (GKE) with a StatefulSet using persistent volumes backed by Compute Engine persistent disks. The application performs frequent small random writes to a MySQL database stored on the persistent disks. You notice that the disk write latency has increased significantly, and the application's throughput has dropped. Monitoring shows that the disk queue depth is consistently high. The current disk type is pd-standard. What is the most cost-effective way to reduce write latency and improve throughput?

Accepted Answer

Change the persistent disk type from pd-standard to pd-ssd.. The application is experiencing high write latency due to insufficient IOPS from pd-standard disks, which are HDD-based and optimized for sequential reads, not small random writes. Changing to pd-ssd (SSD-based) provides significantly higher IOPS and lower latency for random write workloads, directly addressing the high queue depth and throughput drop. This is the most cost-effective solution because pd-ssd offers the necessary performance improvement without requiring architectural changes or over-provisioning capacity.

Answer

Use a regional persistent disk for higher availability and performance.

Answer

Add more replicas of the StatefulSet to distribute writes across multiple disks.

Answer

Increase the size of the persistent disks to improve IOPS limits.

Question 15

An engineer is migrating a workload from a relational database to Bigtable. The current schema has a Customers table (1M rows) and an Orders table (100M rows) with a foreign key. Queries often fetch all orders for a customer. What is the best row key design for the Bigtable orders table?

Accepted Answer

Use customer ID + order ID as the row key (e.g., cust123#ord456).. Using customer ID + order ID as the row key ensures that all orders for a single customer are stored in contiguous rows, enabling efficient range scans. Bigtable orders rows lexicographically by row key, so a prefix scan on the customer ID retrieves all related orders in a single read operation, avoiding expensive joins or scatter-gather patterns.

Answer

Use the order ID as the row key and store customer ID as a column.

Answer

Use a hash of the customer ID as the row key.

Answer

Use a random UUID as the row key.

Question 16

An organization is migrating an Oracle database to Cloud SQL for PostgreSQL. They have numerous stored procedures written in PL/SQL. Which tool should they use to automatically convert these procedures to PL/pgSQL?

Accepted Answer

Ora2Pg. Ora2Pg is a schema conversion tool that translates Oracle PL/SQL code to PostgreSQL PL/pgSQL, handling data type mappings and object conversions.

Answer

BigQuery Data Transfer Service

Answer

Database Migration Service (DMS)

Answer

gcloud sql import

Question 17

A financial application requires zero data loss (RPO=0) and automatic failover within 60 seconds in the event of a zone failure. The database must support ACID transactions. Which Google Cloud service meets these requirements?

Accepted Answer

Cloud SQL with HA configuration. Cloud SQL with HA configuration uses synchronous replication to a standby instance in a different zone. If the primary zone fails, Cloud SQL automatically fails over to the standby. Because replication is synchronous, RPO is effectively zero. Automatic failover typically completes within 60 seconds.

Answer

Cloud SQL cross-region read replica

Answer

Cloud SQL single-zone instance with automatic backups

Answer

Cloud Bigtable multi-cluster replication

Question 18

A financial services company uses Cloud Spanner for a global ledger application. The 'Transactions' table has a primary key of 'TransactionId' (a UUID string) and includes columns 'AccountId', 'Amount', and 'Timestamp'. The application frequently runs queries that retrieve all transactions for a specific account within a date range, sorted by timestamp. These queries are currently slow because they require a full table scan. The team wants to optimize the schema for these queries without changing the application's write pattern, which inserts transactions with random UUIDs. What should they do?

Accepted Answer

Create a secondary index on (AccountId, Timestamp) and include the 'Amount' column as a stored column.. Creating a secondary index on (AccountId, Timestamp) allows Cloud Spanner to quickly find all transactions for a given account within a time range, and the index is sorted by timestamp, satisfying the sort order. Storing 'Amount' in the index makes it covering, so the query can be answered entirely from the index without fetching the base row. This approach optimizes read performance while leaving the primary key and write pattern unchanged.

Answer

Change the primary key to (AccountId, Timestamp, TransactionId) to cluster transactions by account and time.

Answer

Create a secondary index on (Timestamp, AccountId) and rely on the query optimizer to use it for account-specific queries.

Answer

Add a foreign key from 'Transactions' to an 'Accounts' table and interleave 'Transactions' in 'Accounts'.

Question 19

A company is designing a database schema for a global e-commerce platform. Orders are created with high frequency, and order status updates occur frequently. The team needs to choose a primary key strategy for the orders table in Spanner. Which approach minimizes hot-spotting?

Accepted Answer

Use a universally unique identifier (UUID) as the primary key. In Spanner, monotonically increasing or time-ordered primary keys cause hot-spotting because all new writes are directed to the same tablet server, creating a single point of contention. UUIDs are randomly distributed, ensuring writes are spread evenly across the entire key space, which minimizes hot-spotting and maximizes write throughput.

Answer

Use a monotonically increasing integer (e.g., auto-increment)

Answer

Use a timestamp as the primary key

Answer

Use a composite key with user_id and order_date

Question 20

A BI dashboard query is slow and high cost. The query does multiple joins on large tables and uses window functions. The data engineer suggests using materialized views. However, the query uses non-deterministic functions. What is the limitation?

Accepted Answer

Materialized views cannot include non-deterministic functions. Materialized views store the result set of a query physically, like a table. If the query includes non-deterministic functions (e.g., NOW(), RAND(), CURRENT_TIMESTAMP), the stored result would become stale immediately because the function's output changes each time it is evaluated. Most SQL databases (e.g., PostgreSQL, Oracle, Snowflake) explicitly forbid non-deterministic functions in materialized view definitions to prevent this logical inconsistency.

Answer

Materialized views cannot be updated automatically

Answer

Materialized views cannot be created with joins

Answer

Materialized views only support simple aggregation