PCDE Practice Exam 55 — 20 Questions

Question 1

A BI developer needs to display sales data in a dashboard that shows sales in local time zones. The source data stores all timestamps in UTC. Which is the best practice for handling time zone conversions?

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

Accepted Answer

Store all timestamps in UTC and convert them to the desired time zone in SQL queries. Storing timestamps in UTC and converting them in SQL queries ensures that the conversion logic is centralized, auditable, and consistent across all BI reports. This approach leverages the database engine's time zone functions (e.g., AT TIME ZONE in SQL Server or CONVERT_TZ in MySQL) to handle daylight saving time transitions accurately, avoiding the pitfalls of application-layer conversions that may be inconsistent or not applied uniformly.

Answer

Store timestamps in UTC and convert to local time in the BI tool's application layer

Answer

Store timestamps as text strings with time zone offset to avoid conversion

Answer

Store both UTC and local time in separate columns

Question 2

An organization wants to implement least-privilege IAM for their DevOps team. They need permissions to manage Compute Engine instances but not to create or delete them. Which IAM role should be assigned?

Accepted Answer

roles/compute.instanceAdmin.v1. The role roles/compute.instanceAdmin.v1 provides permissions to manage Compute Engine instances, including starting, stopping, and modifying them, but explicitly excludes the ability to create or delete instances. This aligns with the least-privilege requirement for the DevOps team, as they need operational control without lifecycle management rights.

Answer

roles/compute.networkAdmin

Answer

roles/compute.admin

Answer

roles/compute.viewer

Question 3

A company is setting up a new Google Cloud organization. The DevOps team wants to enforce that all Compute Engine instances are created only in us-central1 or europe-west1. Which approach should they use?

Accepted Answer

Create an organization policy with constraint `resourceLocations` and set allowed values to `us-central1` and `europe-west1`.. The `resourceLocations` organization policy constraint is specifically designed to restrict the set of Google Cloud locations where resources can be created. By setting allowed values to `us-central1` and `europe-west1`, the DevOps team enforces that Compute Engine instances (and other supported resources) can only be provisioned in those regions. This policy is evaluated at resource creation time and applies across the entire organization, project, or folder hierarchy.

Answer

Set a IAM policy on the organization to allow Compute Engine instances only in those regions.

Answer

Configure a VPC Service Controls perimeter to restrict resource creation to allowed regions.

Answer

Use Cloud Deployment Manager to enforce regions via a template that validates location.

Question 4

A data warehouse in BigQuery stores daily snapshots of customer data. The schema uses a single table with a snapshot_date partition column. Over time, the table has grown to 10 TB and queries often scan entire partitions. Which schema redesign would improve query performance and reduce costs significantly?

Accepted Answer

Use a nested and repeated structure to store all snapshots per customer in a single row.. Storing all snapshots per customer in a nested and repeated structure (e.g., an array of structs) eliminates the need to scan multiple rows for the same customer across different snapshot dates. This reduces the table size by avoiding row duplication, and queries that filter on customer_id can leverage the nested structure to read only the relevant data, significantly cutting both query costs (less data scanned) and improving performance.

Answer

Create separate tables for each snapshot_date.

Answer

Use clustering on customer_id and snapshot_date.

Answer

Use a wildcard table with a _TABLE_SUFFIX filter.

Question 5

A team is migrating an on-premises MySQL database to Cloud SQL. The current schema usesMyISAM tables. What is the recommended approach?

Accepted Answer

Convert MyISAM tables to InnoDB before migration.. Cloud SQL for MySQL does not support MyISAM tables because MyISAM lacks transaction support, row-level locking, and crash recovery, which are essential for a managed database service. Converting MyISAM tables to InnoDB before migration ensures compatibility, data integrity, and performance. The recommended approach is to alter the table engine to InnoDB prior to export or use a migration tool that handles the conversion.

Answer

Keep the schema as is; Cloud SQL supports MyISAM.

Answer

Replicate the on-premises MySQL to Cloud SQL using Database Migration Service.

Answer

Export the database using mysqldump and import directly into Cloud SQL.

Question 6

A Cloud SQL for MySQL instance is running low on disk space. You need to increase the storage without downtime. What is the correct approach?

Accepted Answer

Use the gcloud command to resize the storage; the operation is performed online.. Cloud SQL for MySQL supports online storage resizing using the `gcloud sql instances patch` command or the Google Cloud Console, which allows you to increase the storage capacity without any downtime. The operation is performed while the instance remains available, and the underlying persistent disk is resized live, leveraging Google Cloud's live resize capability for Cloud SQL instances.

Answer

Create a new instance with larger storage and migrate the data using mysqldump.

Answer

Enable automatic storage increase; resize manually is not possible.

Answer

Stop the instance, resize the attached persistent disk, then restart.

Question 7

A startup is migrating from MongoDB to Firestore in Datastore mode. Their existing documents contain nested arrays of sub-objects (e.g., tags, comments). They want to design a schema that scales well and supports efficient queries. What is the recommended approach for handling these nested arrays in Firestore?

Accepted Answer

Flatten the arrays into subcollections under each document.. Firestore in Datastore mode does not support querying or indexing nested array elements efficiently, which can lead to performance issues as the dataset grows. Flattening nested arrays into subcollections allows each sub-object to be a separate document, enabling scalable queries and proper indexing without the limitations of array-based storage.

Answer

Use maps instead of arrays to store the data.

Answer

Store the arrays as stringified JSON in a single field.

Answer

Keep the nested arrays as they are; Firestore supports arrays.

Question 8

A company wants to migrate a 5 TB MySQL database to Cloud Spanner with zero downtime. They need to validate schema and data consistency before switching traffic. Which THREE steps should they include in the migration plan?

Accepted Answer

Set up a Dataflow pipeline to replicate changes from MySQL to Spanner. Zero-downtime migration to Spanner typically involves using Strangler Fig pattern: replicate writes from MySQL to Spanner, validate data, then cutover. Key steps: 1. Export schema and convert to Spanner DDL (using HarbourBridge). 2. Set up live migration using Dataflow for continuous replication. 3. Validate data consistency (e.g., using checksums). Taking an export snapshot is fine but not for zero-downtime; using pgloader is for PostgreSQL; creating a Cloud SQL read replica is not directly relevant.

Answer

Use pgloader to migrate the data

Answer

Create a Cloud SQL read replica for fallback

Question 9

A developer is building an application that uses Firestore (in Datastore mode). The application needs to query data across two properties: 'status' and 'timestamp', with an equality filter on 'status' and a range filter on 'timestamp'. Which three steps are required to support this query efficiently? (Choose THREE.)

Accepted Answer

Ensure that single-field indexes exist for both 'status' and 'timestamp'. Firestore (in Datastore mode) requires single-field indexes to be defined for each property used in a query, even when a composite index is also present. Without a single-field index on 'status' and 'timestamp', the query engine cannot efficiently evaluate the equality and range filters, leading to full table scans or query failures.

Answer

Enable automatic composite index creation in Firestore settings

Answer

Create an index exemption for the 'timestamp' field

Question 10

A company uses Cloud Bigtable for their analytics pipeline. They set up replication with a primary cluster in us-central1 and a secondary in us-west1. They notice that during normal operation, queries always hit the primary cluster even if the secondary is closer. What should they change to route queries to the nearest cluster automatically?

Accepted Answer

Change the app profile routing policy to any-replica. The default routing policy for Bigtable replication is single-cluster (to the primary). To route to the nearest healthy cluster, they need to enable the any-replica routing policy in their Bigtable app profile. read-failover is for DR failover, not for normal operations. Changing the primary cluster does not solve the routing issue. Client-side logic is an option but not a built-in solution.

Answer

Implement client-side logic to choose which cluster to query

Answer

Modify the primary cluster to be in us-west1

Answer

Update the app profile to use read-failover routing

Question 11

An organisation wants to back up their Cloud Spanner database and store the backup in a different region for disaster recovery. They require the backup to be in a format that can be restored into Spanner with minimal effort. Which THREE actions should they take? (Choose three.)

Accepted Answer

Specify a different location for the backup at creation time for cross-region storage. Cloud Spanner allows you to specify a different location for the backup at creation time, enabling cross-region storage for disaster recovery without additional export or import steps. Option D is correct because restoring a backup to a new database using `gcloud spanner databases restore` directly utilizes the backup format, requiring minimal effort. Option E is correct because creating a database-level backup using `gcloud spanner databases backup` is the native method to back up Spanner databases, and such backups can be stored in different regions for geographic redundancy.

Answer

Export the database using gcloud spanner databases export to CSV format

Answer

Use Dataflow to import the backup into a new instance

Question 12

A company runs a financial analytics platform on BigQuery. They need to reduce query costs for frequent, predictable queries. Which three strategies can help? (Choose THREE.)

Accepted Answer

Use BI Engine to cache results of frequent queries.. BigQuery BI Engine provides an in-memory analysis service that caches results of frequent and predictable queries, reducing the need to scan data in BigQuery storage and thereby lowering query costs. By serving cached results directly from memory, BI Engine avoids repeated data processing and slot consumption for recurring queries.

Option B is correct because materialized views allow you to pre-compute and store the results of common aggregations. When you query a materialized view, BigQuery uses the pre-computed results instead of scanning the base tables, which reduces the amount of data processed and thus lowers query costs, especially for frequent, predictable aggregations.

Option D is correct because clustering tables on frequently filtered columns can significantly reduce the amount of data scanned by queries that filter on those columns. By organizing data based on the clustering columns, BigQuery can efficiently prune partitions and only scan relevant blocks. This reduces the bytes billed for each query, leading to cost savings for frequent, predictable queries with filtering predicates.

Option C is incorrect because partitioning by ingestion time is primarily used for managing data lifecycle and improving query performance on time-based ranges, but it does not directly address cost reduction for frequent, predictable queries. While it can reduce scanned data for time-range queries, it is not as targeted as the other strategies for predictable, repeated access patterns.

Option E is incorrect because using DML statements to pre-aggregate data would require additional processing and storage costs for the aggregated tables, and the queries against those tables would still incur costs. This approach does not inherently reduce query costs compared to using materialized views or BI Engine, and it adds complexity and maintenance overhead.

Answer

Partition tables by ingestion time.

Answer

Use DML statements to pre-aggregate data.

Question 13

A company wants to migrate from a self-managed MySQL database to a fully managed GCP service. They need high availability with automatic failover, automated backups, and read replicas for scaling read traffic. Which two Google Cloud services meet these requirements? (Choose TWO.)

Accepted Answer

Cloud Spanner. Cloud SQL for MySQL is correct because it is a fully managed MySQL-compatible service that offers high availability with automatic failover, automated backups, and support for read replicas to scale read traffic. Cloud Spanner is also correct because it provides a fully managed, horizontally scalable relational database with automatic synchronous replication for high availability, automatic failover, automated backups, and read replica support, making it suitable for migrating from MySQL. Both services meet the requirements for a fully managed, highly available database with read scaling.

Answer

Firestore

Answer

Cloud Bigtable

Answer

BigQuery

Question 14

A Cloud Bigtable instance is experiencing high read latency. The team suspects hot spotting on a single node. Which tool should they use to identify the hotspot?

Accepted Answer

Key Visualizer. Key Visualizer is the correct tool because it is specifically designed to analyze access patterns in Cloud Bigtable and identify hot spotting—where a small range of row keys receives a disproportionate amount of traffic. It visualizes read and write heatmaps across row key space and time, allowing teams to pinpoint the exact keys causing the hotspot. Unlike general-purpose monitoring tools, Key Visualizer provides row-key-level granularity essential for diagnosing hot spotting.

Answer

Cloud Logging to review request logs

Answer

Bigtable Admin API to list tables

Answer

Cloud Monitoring (Stackdriver) to check CPU utilization

Question 15

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 16

A company uses Cloud Deploy to deploy to multiple GKE clusters (dev, staging, prod). They want to implement a canary deployment strategy for the prod target. The canary should deploy 10% of pods initially, then after 30 minutes and a manual approval, promote to 100%. Which configurations are necessary? (Choose THREE).

Accepted Answer

Set the canary percentage to 10% and the promotion interval to 30m.. Setting the canary percentage to 10% and the promotion interval to 30m directly implements the required behavior: initially 10% of pods receive traffic, and after 30 minutes the deployment automatically attempts promotion. This matches the canary deployment strategy in Cloud Deploy where the `canaryDeployment` configuration specifies `percentages` and `verify` intervals.

Answer

Set the strategy to 'blue/green' with a 10% initial pool.

Answer

Create a separate delivery pipeline for canary deployments.

Question 17

A company wants to migrate their on-premises PostgreSQL database to Cloud SQL. The database currently runs mixed workloads: OLTP with heavy writes and occasional complex analytical queries. They want to avoid performance impact on the transactional workload. Which approach should they take?

Accepted Answer

Create a read replica of the Cloud SQL instance and run analytical queries against the replica.. To avoid performance impact on transactional workloads, a read replica of the Cloud SQL instance should be created and analytical queries directed to it. Read replicas in Cloud SQL are separate instances that replicate data from the primary asynchronously, providing isolation so that heavy analytical queries don't compete for resources with OLTP operations. This is a recommended best practice for workload separation in Cloud SQL for PostgreSQL.

Answer

Migrate to Cloud Spanner to handle both workloads using its analytics interface.

Answer

Use Cloud SQL with the 'analytics' tier enabled.

Answer

Use the same Cloud SQL instance but schedule analytical queries during off-peak hours.

Question 18

A team is migrating a relational database to Bigtable. The existing schema uses foreign keys to join orders, customers, and products. Which data model approach is most suitable for Bigtable?

Accepted Answer

Denormalize orders, customers, and products into a single table with a composite row key.. Bigtable is a wide-column NoSQL database optimized for high-throughput, low-latency access, and it does not support SQL-style joins or secondary indexes in the traditional relational sense. Denormalizing orders, customers, and products into a single table with a composite row key (e.g., customer_id#order_id#product_id) allows all related data to be co-located and retrieved with a single row scan, eliminating the need for joins and aligning with Bigtable's key-value access pattern.

Answer

Store each entity in a separate table and use secondary indexes.

Answer

Use Cloud SQL as a lookup table for joins.

Answer

Keep the normalized structure and use MapReduce to perform joins.

Question 19

A company is using Cloud SQL for MySQL for its OLTP workload. They want to run complex analytical queries on the same data without impacting transactional performance. The analytical queries involve large scans and joins. What is the recommended approach?

Accepted Answer

Export the data to BigQuery periodically and run analytical queries there. BigQuery is a serverless, highly scalable data warehouse designed for analytical queries on large datasets. By exporting data from Cloud SQL (OLTP) to BigQuery, you isolate the analytical workload from the transactional database, preventing resource contention and performance degradation on the primary instance. This separation of concerns is the recommended pattern for running complex scans and joins without impacting OLTP performance.

Answer

Enable MySQL Query Cache to speed up analytical queries on the primary instance

Answer

Use Cloud Spanner to handle both OLTP and analytics with interleaved tables

Answer

Create a Cloud SQL read replica and route analytical queries to it

Question 20

The query above fails with 'Resources exceeded: UDF out of memory' on a large table. What is the best way to fix this?

Accepted Answer

Rewrite the function as a SQL UDF to avoid JavaScript overhead. JavaScript UDFs in BigQuery run in a sandbox with limited memory (typically 6 MB per UDF instance). When processing a large table, the UDF may exceed this memory due to per-row overhead or large intermediate results. Rewriting the function as a SQL UDF eliminates JavaScript overhead and runs natively within BigQuery's distributed execution engine, which can handle larger datasets without memory constraints.

Answer

Add a GROUP BY clause to reduce the number of rows processed

Answer

Convert the temporary UDF to a persistent UDF

Answer

Increase the memory allocation for JavaScript UDFs