PCDE Mock Exam 81 — 25 Questions

Question 1

Which THREE of the following are best practices for designing BigQuery tables for business intelligence reporting?

Accepted Answer

Partition tables by a date or timestamp column used in WHERE clauses.. Partitioning tables by a date or timestamp column used in WHERE clauses allows BigQuery to prune partitions, scanning only the relevant data instead of the entire table. This reduces query costs and improves performance, making it a best practice for BI reporting where queries often filter by time ranges.

Answer

Store data in many small tables to reduce the amount of data scanned per query.

Answer

Normalize data to reduce data redundancy.

Question 2

A Cloud SQL instance is using InnoDB and has a large buffer pool. The query performance is slower after a failover. What is the most likely cause?

Accepted Answer

Buffer pool warm-up time. After a failover in Cloud SQL, the new primary instance starts with a cold buffer pool. InnoDB relies on the buffer pool to cache data and index pages in memory for fast queries. Since the buffer pool is empty after the failover, queries must read from disk until the cache warms up, causing significantly slower performance.

Answer

Read replica lag

Answer

Binary log not enabled

Answer

Data corruption

Question 3

Refer to the exhibit. What is the most likely performance issue with this schema?

Accepted Answer

Hotspotting on TransactionId due to monotonically increasing values. The schema uses TransactionId as the partition key with monotonically increasing values (e.g., timestamps or auto-incrementing integers). In a distributed database like Cloud Spanner or Bigtable, this causes all writes to land on a single partition, creating a hotspot that throttles throughput and increases latency. The correct answer is C because this hotspotting is the most likely performance issue.

Answer

No performance issue; the schema is optimal

Answer

Hotspotting on UserId due to frequent queries

Answer

Too many secondary indexes causing write amplification

Question 4

A company is migrating a large Oracle database to Cloud Spanner. The schema includes several tables with foreign key relationships. The team wants to minimize query latency for join queries that always involve a parent table and its children. Which THREE schema design strategies should the team consider? (Choose THREE.)

Accepted Answer

Design child table primary keys to start with the parent key (e.g., CustomerId, OrderId). In Cloud Spanner, designing child table primary keys to start with the parent key (e.g., CustomerId, OrderId) enables efficient key-range scans and colocates related rows, reducing cross-node communication. Option B is correct because denormalizing frequently joined lookup tables into the parent table as repeated fields avoids joins entirely, further reducing latency. Option C is correct because parent-child interleaved tables physically store child rows adjacent to their parent row, minimizing splits and cross-node communication for join queries. Options D and E are incorrect: secondary indexes do not provide the same physical colocation benefits, and storing foreign keys as JSON arrays in the parent table would complicate queries and fail to leverage Spanner's distributed architecture.

Answer

Create secondary indexes on foreign key columns

Answer

Store foreign key relationships as JSON arrays in the parent table

Question 5

A company stores user events in BigQuery as nested repeated fields. They want to use Looker to build dashboards on individual events. Which SQL pattern should they use in a derived table to flatten the data?

Accepted Answer

SELECT fields FROM table, UNNEST(events) AS event. UNNEST(events) in BigQuery SQL flattens the nested repeated field 'events' into individual rows, enabling Looker to treat each event as a separate record for dashboarding. This is the standard pattern for denormalizing arrays in BigQuery derived tables, as it converts each array element into its own row while preserving the parent record's fields.

Answer

SELECT fields FROM table WHERE events IS NOT NULL

Answer

SELECT ARRAY_AGG(events) FROM table

Answer

SELECT events.* FROM table

Question 6

A media streaming company uses Cloud Bigtable to store user session data with a single cluster in us-east1. They want to add disaster recovery capability with an RPO of no more than 5 minutes and an RTO of under 10 minutes. Which action should they take?

Accepted Answer

Add a second cluster in a different region with replication, and configure a Cloud DNS health check to redirect traffic. Bigtable replication allows adding a second cluster in a different region with asynchronous replication. The replication lag can be within seconds, typically under 5 minutes. For RTO under 10 minutes, manual failover via the Cloud Console or CLI is sufficient; automatic failover is not built-in but can be achieved with external orchestration.

Answer

Use Cloud SQL cross-region read replicas

Answer

Deploy a new Bigtable instance in another region and set up dataflow pipelines to replicate data

Answer

Create an on-demand backup and restore to another region

Question 7

Which THREE are valid Cloud Deploy deployment strategies? (Choose three.)

Accepted Answer

Blue/Green. (Blue/Green) is a valid Cloud Deploy deployment strategy because it allows you to run two separate environments (blue and green) and switch traffic between them, enabling instant rollback and zero-downtime deployments. Cloud Deploy natively supports Blue/Green deployments via its Skaffold-based pipeline, where you can define target environments and use load balancer switching to shift traffic. This strategy is particularly useful for critical production services where risk mitigation is paramount.

Answer

Rolling update

Answer

Shadow

Question 8

A financial services company uses Cloud Spanner for transaction processing. They need to run analytical queries that scan large portions of the database without impacting OLTP performance. What schema design technique should they use?

Accepted Answer

Design the primary key so that analytical queries scan a small number of tablets by using interleaved tables.. Interleaved tables in Cloud Spanner physically co-locate parent and child rows on the same tablet (split). This ensures that analytical queries scanning a large portion of the database can be served by a small number of tablets, minimizing cross-tablet reads and reducing contention with OLTP traffic. By designing the primary key to leverage interleaving, you keep analytical scans localized and avoid the performance penalty of scattering reads across many tablets.

Answer

Export data periodically to BigQuery and run queries there.

Answer

Create multiple secondary indexes on frequently scanned columns.

Answer

Use a read replica instance to offload analytical queries.

Question 9

Refer to the exhibit. What is the most effective optimization for this query?

Accepted Answer

Create a composite index on (status, order_date). The query filters on `status` and `order_date`, so a composite index on `(status, order_date)` allows the database to perform an index seek on the equality predicate (`status`) and then a range scan on the ordered column (`order_date`), avoiding a full table scan. This is the most effective optimization because it directly supports the WHERE clause with minimal I/O and no sorting overhead.

Answer

Increase the instance memory to 30 GB

Answer

Remove the WHERE clause and fetch all rows in application

Answer

Partition the orders table by month

Question 10

A team is designing a Cloud SQL for PostgreSQL schema for a multi-tenant SaaS application. They need to isolate tenant data while maintaining query performance and manageability. Which two approaches are appropriate? (Choose two.)

Accepted Answer

Use a single schema with a tenant_id column on every table and row-level security.. Using a single schema with a tenant_id column and row-level security (RLS) in PostgreSQL allows tenant data isolation at the row level while maintaining a single database and schema. RLS policies automatically filter rows based on the current session's tenant context, ensuring performance is optimized through standard indexing on tenant_id and avoiding the overhead of multiple databases or schemas.

Answer

Use separate databases per tenant.

Answer

Use a single table for all tenants with no tenant identifier.

Answer

Use a separate Cloud SQL instance per tenant.

Question 11

An engineer wants to run two Cloud Build steps in parallel to speed up the build. How should they configure the cloudbuild.yaml?

Accepted Answer

Set `waitFor: ['-']` on both steps and ensure they are defined sequentially in the YAML. In Cloud Build, steps defined sequentially in the YAML run in order by default. To run two steps in parallel, you set `waitFor: ['-']` on both steps, which tells Cloud Build not to wait for any previous step. This allows them to start simultaneously. Option D correctly describes this configuration.

Answer

Use two separate cloudbuild.yaml files and run them concurrently

Answer

Set `waitFor: ['previous']` on both steps

Answer

Define steps under a `parallel` key in cloudbuild.yaml

Question 12

Which TWO of the following are valid approaches when troubleshooting a slow BI query in BigQuery that includes a complex JOIN between a large fact table and multiple dimension tables?

Accepted Answer

Ensure the fact table is clustered on the join key. Clustering on the join key in BigQuery physically co-locates rows with the same key value within the same block, reducing the amount of data scanned during the JOIN. This is especially effective for large fact tables, as it minimizes the need to shuffle data across slots, directly improving query performance.

Answer

Split the fact table into multiple smaller tables by region

Answer

Move the data to Cloud SQL for faster joins

Answer

Add indexes on the join columns

Question 13

Refer to the exhibit. Given the table definition and two queries, which statement about query performance is correct?

Accepted Answer

Query 1 will scan less data than Query 2 because it uses both partition pruning and clustering.. Query 1 uses both partition pruning (filtering on the partition key `event_date`) and clustering (filtering on the clustering column `user_id`), allowing it to skip irrelevant partitions and scan only the specific rows within the target partition. Query 2 uses only partition pruning on `event_date` but lacks a clustering filter, so it must scan all rows in the partition. Therefore, Query 1 scans less data than Query 2.

Answer

Query 2 will scan less data than Query 1 because it only needs to read one partition.

Answer

Query 1 will scan the same amount of data as Query 2 because both use partition pruning.

Answer

Both queries will perform a full table scan because the table is partitioned.

Question 14

A company plans to migrate an on-premises MySQL database to Cloud SQL. Which THREE steps should they include in their migration plan?

Accepted Answer

Test application compatibility with Cloud SQL.. Testing application compatibility with Cloud SQL ensures that any MySQL-specific features, configurations, or behaviors used by the application are supported in the Cloud SQL environment. This step is critical to identify potential issues early, such as unsupported storage engines, character set differences, or version-specific SQL syntax, before committing to the full migration.

Answer

Convert all stored procedures to PostgreSQL dialect.

Answer

Enable point-in-time recovery before migration.

Question 15

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?

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 16

A company is migrating a large Oracle database to Cloud Spanner. The source database uses sequences for primary key generation. The database engineer needs to design the Cloud Spanner schema to avoid hotspotting. What primary key design should they recommend?

Accepted Answer

Use a hash of the original key as a prefix to the primary key.. Using a hash of the original key as a prefix to the primary key distributes writes evenly across Cloud Spanner's splits, preventing hotspotting. Cloud Spanner uses a distributed, append-only storage model where sequential keys (like from Oracle sequences) cause all new writes to land on the same split, creating a hotspot. A hash prefix ensures that related rows are still co-located for efficient queries while spreading write load across multiple nodes.

Answer

Keep the same sequence-based integer keys.

Answer

Use a composite primary key with a timestamp prefix.

Answer

Use UUIDs as the primary key without modification.

Question 17

A team is migrating an on-premises Oracle database to Cloud SQL for PostgreSQL using DMS. They have completed the schema conversion using Ora2Pg and are now setting up continuous migration. The source database is behind a firewall. Which connectivity method should they use for the source connection profile if they cannot use public IP?

Accepted Answer

Configure a VPN or Dedicated Interconnect with VPC. Since the source Oracle database is behind a firewall and cannot use a public IP, a VPN or Dedicated Interconnect with VPC provides a private, encrypted connection between the on-premises network and Google Cloud. This allows Database Migration Service (DMS) to reach the source database securely without exposing it to the public internet. DMS supports connectivity via these private network paths when public IP is not an option.

Answer

VPC peering

Answer

Cloud SQL Auth Proxy

Answer

IP allowlisting

Question 18

An e-commerce company uses BigQuery for BI. They have a large orders table with columns: order_id, customer_id, order_date, amount, status. Queries frequently aggregate total amount by customer and month. The current table is not partitioned. Users complain about high costs. The table is 2 TB and grows by 50 GB daily. Which action reduces query costs most?

Accepted Answer

Create a materialized view that aggregates by customer and month.. A materialized view pre-aggregates the total amount by customer and month, eliminating the need to scan the full 2 TB table for every query. This drastically reduces the bytes processed per query, directly lowering BigQuery costs. Since the table grows by 50 GB daily, the materialized view incrementally updates, ensuring fresh results without reprocessing historical data.

Answer

Partition the table by order_date and cluster by customer_id.

Answer

Use a wildcard table with daily shards.

Answer

Set a maximum bytes billed limit on the project.

Question 19

A company is migrating an on-premises PostgreSQL database to Cloud SQL. They need to minimize downtime during migration. Which TWO steps should they take?

Accepted Answer

Set up a read replica for switchover. (Set up a read replica for switchover) minimizes downtime by allowing a near-instantaneous cutover: you can promote the read replica to primary with minimal interruption. Option E (Use Database Migration Service with continuous replication) synchronizes changes in real time, enabling a controlled switchover with very low downtime. Option B (pg_dump and restore) involves exporting and importing data, which requires taking the source database offline or operating in read-only mode, causing significant downtime. Option C (Enable automatic backups) is a backup strategy that does not directly address migration downtime. Option D (Configure a high-availability instance) is post-migration configuration for resilience, not a step that reduces downtime during the migration.

Answer

Use pg_dump and restore

Answer

Enable automatic backups

Answer

Configure a high-availability instance

Question 20

Refer to the exhibit. The team notices high write latency on the Events table. They are inserting 1,000 events per second. The EventId is generated by a sequence. What is the most likely issue?

Accepted Answer

The sequential primary key creates a hotspot on a single split.. The sequential primary key (EventId generated by a sequence) causes all new writes to be directed to the last tablet or split in the table, creating a hotspot. In Cloud Spanner, this leads to contention on a single split, increasing write latency despite adequate overall throughput capacity.

Answer

The allow_commit_timestamp option on CreatedAt column adds overhead.

Answer

The BYTES(MAX) data type causes excessive writing.

Answer

The node count is insufficient for the write throughput.