PCDE Hard Questions — 25 Difficult Questions

Question 1

A company stores sensor data in BigQuery. They have a table 'sensor_readings' with columns: sensor_id, reading_time, value. The table is partitioned by reading_time (hourly) and clustered by sensor_id. A BI query aggregates average value per sensor for the last week. The query still scans many bytes. What is the most likely cause?

Accepted Answer

Partition granularity is too fine for the query range. Option D is correct because the query scans a full week of data (168 hourly partitions), and each partition must be read entirely even though only a subset of sensors may be active. Hourly partitioning over a 7-day range means the query engine must scan all 168 partitions, which can result in a large number of bytes being processed. Clustering on sensor_id helps within each partition but does not reduce the number of partitions scanned; the fine granularity of hourly partitioning is the primary cause of excessive bytes scanned.

Answer

The query uses SELECT * instead of specific columns

Answer

Clustering on sensor_id is ineffective

Answer

The table is not using columnar storage

Question 2

Which THREE are valid considerations when designing BigQuery tables for BI reporting?

Accepted Answer

Use partitioning on date columns to reduce query cost. Option C is correct because partitioning BigQuery tables by date columns (e.g., using _PARTITIONTIME or a DATE/TIMESTAMP column) allows the query engine to prune entire partitions during query execution. This significantly reduces the amount of data scanned, directly lowering query costs (since BigQuery charges per byte processed) and improving performance for time-range filters.

Answer

Use nested and repeated fields to avoid JOINs

Answer

Create indexes on frequently queried columns

Question 3

A team is migrating an on-premises PostgreSQL database to Cloud SQL for PostgreSQL. The existing schema uses a large number of foreign key constraints and triggers for data validation. The team wants to minimize migration effort and maintain data integrity. Which schema design approach is most appropriate for Cloud SQL?

Accepted Answer

Keep the existing foreign keys and triggers as-is in Cloud SQL for PostgreSQL. Option A is correct because Cloud SQL for PostgreSQL is fully compatible with the PostgreSQL engine, meaning foreign key constraints and triggers operate identically to on-premises PostgreSQL. This approach minimizes migration effort by preserving the existing schema logic and maintaining referential integrity without requiring application changes or data validation rewrites.

Answer

Migrate to Cloud Spanner and use interleaved tables to simulate foreign keys

Answer

Remove all foreign keys and triggers and implement validation in the application layer

Answer

Convert the schema to use Firestore in Datastore mode with composite indexes

Question 4

A financial services company uses Cloud Spanner for a global transaction processing system. They notice that certain read queries on a table with frequent writes are returning stale data even though they use strong reads. The table has a primary key of (user_id, transaction_id) and a secondary index on (timestamp). What is the most likely cause of the stale reads?

Accepted Answer

The query is using a secondary index that has not yet been updated with the latest write.. Option B is correct because in Cloud Spanner, secondary indexes are implemented as separate tables that are updated asynchronously relative to the base table. When a strong read uses a secondary index, the read may still see a stale version of the index if the write has not yet been fully replicated to the index table. This is a known behavior: strong reads guarantee consistency only when reading from the base table using the primary key, not when using a secondary index.

Answer

The query is using a stale read timestamp.

Answer

The query is reading from a read-only replica.

Answer

Cloud Spanner is using eventual consistency for this query.

Question 5

Your Cloud SQL for MySQL instance is experiencing intermittent performance degradation. You suspect that the issue is due to a sudden spike in connections from a specific application. Which metric and monitoring approach would best help you correlate the connection spike with performance degradation?

Accepted Answer

Monitor 'cloudsql.googleapis.com/database/mysql/threads/threads_connected' and correlate with CPU utilization and query latency.. Option D is correct because the 'threads_connected' metric directly measures the number of active connections to the MySQL instance. Correlating this with CPU utilization and query latency allows you to pinpoint whether a sudden spike in connections is causing resource contention and degraded query performance, which is the exact scenario described.

Answer

Monitor 'cloudsql.googleapis.com/network/received_bytes_count' and compare with connection count.

Answer

Monitor 'cloudsql.googleapis.com/database/mysql/replication/seconds_behind_master' and compare with query latency.

Answer

Monitor 'cloudsql.googleapis.com/instance/uptime' and check for instance restarts during degradation.

Question 6

A company uses Cloud Spanner. The backup service account 'sa-backup' needs to create and manage backups of the 'orders' database. However, backup creation fails with a permission error. What is the most likely cause?

Accepted Answer

The backup role must be granted at the instance level, not on the database.. Option C is correct because Cloud Spanner backup permissions must be granted at the instance level, not on the database itself. The service account 'sa-backup' needs the `spanner.backups.create` permission on the instance resource to create backups, and assigning a role like `roles/spanner.databaseBackupAdmin` at the database level does not propagate the necessary instance-level permissions, causing the backup creation to fail with a permission error.

Answer

The service account lacks the spanner.databases.read permission.

Answer

The service account is assigned the role roles/spanner.databaseBackupAdmin, which is a custom role that does not include the spanner.backups.create permission.

Answer

The instance 'orders-db' is in a regional configuration, which does not support backups.

Question 7

A financial services company uses Cloud Spanner for transaction processing. They need to ensure zero downtime during a schema change that adds a new column with a default value to a large table. Which approach should the Database Engineer take?

Accepted Answer

Use ALTER TABLE to add the column; Spanner handles schema changes online.. Option D is correct because Cloud Spanner supports online schema changes, including adding columns with default values, without requiring a maintenance window or causing downtime. Spanner applies schema updates asynchronously across all nodes while the database remains fully available for reads and writes, making it the ideal approach for zero-downtime requirements.

Answer

Create a new table with the new column, then use a fan-out pattern to write to both tables until the old table is deprecated.

Answer

Use an ALTER TABLE statement during a maintenance window.

Answer

Drop the table and recreate it with the new schema.

Question 8

Your company runs a global application on Cloud Spanner. You notice that recent schema changes have caused a significant increase in latency for cross-node transactions. The previous schema used interleaved tables for parent-child relationships, but the new schema uses separate tables with foreign keys. What is the most likely cause of the increased latency?

Accepted Answer

The new schema uses foreign keys that require cross-node transactions.. The new schema uses separate tables with foreign keys instead of interleaved tables. In Cloud Spanner, interleaved tables guarantee that parent and child rows are co-located on the same split, allowing local joins without cross-node communication. Foreign keys between non-interleaved tables can reference rows stored on different splits, forcing distributed transactions that require two-phase commit across nodes, which significantly increases latency.

Answer

The new schema does not use commit timestamps for versioning.

Answer

The new schema lacks secondary indexes on foreign key columns.

Answer

The Spanner instance was not resized after the schema change.

Question 9

A financial services company uses BigQuery to run complex analytical queries on trading data. They notice that a particular query joining a large fact table (10 TB) with a small dimension table (100 MB) is slow. The fact table is partitioned by date and clustered by symbol. The dimension table is not partitioned. The query filters on a specific date range and a few symbols. Which optimization is MOST likely to improve query performance?

Accepted Answer

Enable automatic query rewriting to use clustering keys for pruning on the dimension table join.. Option B is correct because BigQuery's automatic query rewriting can leverage clustering keys from the fact table to prune the join, even though the dimension table is not clustered. When the query filters on a specific date range and symbols, BigQuery can use the fact table's clustering on symbol to skip irrelevant blocks during the join, reducing data scanned and improving performance. This optimization is automatic and does not require manual denormalization or repartitioning.

Answer

Denormalize the dimension table into the fact table.

Answer

Partition the dimension table by its primary key.

Answer

Cluster the dimension table on its primary key.

Question 10

A company is migrating a legacy on-premises MySQL database to Cloud SQL for PostgreSQL. The database uses composite primary keys on multiple tables and heavily relies on cross-table joins with foreign keys. The team wants to minimize application code changes during migration. Which schema design strategy should the Cloud Database Engineer recommend to ensure compatibility and performance?

Accepted Answer

Use the same composite primary keys and foreign key constraints in Cloud SQL for PostgreSQL, leveraging its full support for these features.. Option B is correct because Cloud SQL for PostgreSQL fully supports composite primary keys and foreign key constraints, which are standard SQL features. By maintaining the same schema, the team minimizes application code changes while preserving referential integrity and join performance, as PostgreSQL's query planner handles these constructs efficiently.

Answer

Maintain the same schema and rewrite joins as materialized views in PostgreSQL to optimize queries.

Answer

Migrate to Cloud Spanner instead, using interleaved tables to replace join-heavy operations.

Answer

Remove composite primary keys and replace them with surrogate keys; use look-up tables for foreign key relationships.

Question 11

A retail company uses Cloud Spanner to handle global transaction processing. The database has a single regional instance in us-central1. The company expects a 10x increase in write traffic from a new mobile app. The database engineer needs to design for low latency writes globally and high availability. What should the Database Engineer do?

Accepted Answer

Change the instance configuration to a multi-region configuration like nam3 (us-central1, us-east1, us-west1) and configure a dedicated write region.. Option C is correct because a multi-region configuration like nam3 (us-central1, us-east1, us-west1) with a dedicated write region provides low-latency writes globally by using Google's managed replication and automatic failover. This design ensures high availability and meets the 10x write traffic increase without sacrificing write performance, as writes are processed in the designated write region and asynchronously replicated to other regions.

Answer

Shard the database across multiple regional instances based on user geography.

Answer

Create read replicas in other regions to offload read traffic and keep writes in the primary region.

Answer

Increase the number of nodes in the existing regional instance to handle the increased write capacity.

Question 12

A Database Engineer is designing a tiered storage strategy for a large BigQuery dataset. The dataset contains data that is accessed frequently for the first 30 days, moderately for the next 6 months, and rarely after that. The engineer wants to minimize overall storage cost while maintaining fast query performance on recent data. Which approach should the engineer take?

Accepted Answer

Use physical storage billing, partition the table by ingestion date, cluster on frequently used columns, and set an expiration on partitions older than 6 months to move them to long-term storage.. Option D is correct because physical storage billing in BigQuery charges separately for active and long-term storage, and setting a partition expiration on partitions older than 6 months automatically moves that data to long-term storage at a lower cost. Partitioning by ingestion date and clustering on frequently used columns ensures fast query performance on recent data while minimizing overall storage costs.

Answer

Use logical storage billing and partition the table by date; older data automatically moves to long-term storage after 90 days.

Answer

Use Bigtable for recent data and BigQuery for historical data, with a scheduled transfer between them.

Answer

Export data older than 30 days to Cloud Storage as Avro files and delete them from BigQuery; query them externally when needed.

Question 13

A financial services company uses BigQuery for risk analysis. They have a table `market_data` with columns `symbol`, `date`, `price`, and `volume`. The query pattern involves window functions over the last 30 days for many symbols. The table is partitioned by date and clustered by symbol. However, analysts report that queries are slow and expensive. What is the most likely cause?

Accepted Answer

Clustering on symbol may cause many blocks to be scanned because symbols are not sorted. Option B is correct because clustering in BigQuery does not physically sort data within partitions; it only co-locates rows with similar cluster column values. When a query uses window functions over a rolling 30-day window for many symbols, BigQuery must scan all blocks that contain any of those symbols, even if only a subset of rows is needed. Since symbols are not strictly sorted, many blocks contain multiple symbols, leading to excessive block scans and high query costs.

Answer

Clustering does not create indexes on symbol

Answer

Partitioning causes data skew across partitions

Answer

Partitioning by date is not granular enough

Question 14

A multinational corporation uses BigQuery to combine sales data from multiple regions. Each region stores data in separate tables with identical schemas. The BI team needs to create a unified view for a dashboard that queries data by region and product. Which TWO strategies should the data engineer implement to optimize query performance and reduce costs?

Accepted Answer

Partition the table by date and cluster by region and product. Option A is correct because partitioning the table by date and clustering by region and product allows BigQuery to use partition pruning and clustering block elimination to scan only the relevant data for queries filtered by region and product. This directly reduces the amount of data read, lowering query costs and improving performance. Clustering also sorts data within partitions, enabling efficient filtering without full scans.

Answer

Create a view with UNION ALL of all region tables

Answer

Create materialized views for each region

Answer

Store all data in a single table with region as a column

Question 15

A company is migrating a large Oracle Data Warehouse to BigQuery. The source schema includes many partitioned tables and materialized views. Which THREE considerations are important when designing the BigQuery schema?

Accepted Answer

Clustering can be used to improve query performance on frequently filtered columns.. Option A is correct because BigQuery clustering organizes data based on the values of specified columns, which improves query performance by reducing the amount of data scanned when filtering on those columns. This is particularly useful for large data warehouses migrating from Oracle, as it mimics the performance benefits of indexes without the overhead of explicit index management.

Answer

BigQuery requires explicit indexes on columns used in WHERE clauses.

Answer

BigQuery supports unique constraints and foreign keys for data integrity.

Question 16

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). Option A is correct because in Cloud Spanner, designing child table primary keys to start with the parent key (e.g., CustomerId, OrderId) enables efficient key-range scans and reduces the number of splits needed for join queries. This pattern leverages Spanner's distributed architecture to colocate related rows, minimizing cross-node communication and query latency.

Answer

Create secondary indexes on foreign key columns

Answer

Store foreign key relationships as JSON arrays in the parent table

Question 17

You are managing a Cloud SQL for MySQL instance that is experiencing high latency and connection timeouts during peak hours. The current configuration uses 4 vCPUs, 15 GB memory, and 100 GB SSD storage. The database workload is a mix of transactional queries and batch inserts. Which TWO actions would most effectively reduce latency and improve performance?

Accepted Answer

Increase the instance to 8 vCPUs and 30 GB memory.. Option C is correct because increasing vCPUs and memory directly addresses the resource bottleneck causing high latency and connection timeouts during peak hours. Cloud SQL for MySQL performance is heavily dependent on CPU for query processing and memory for buffer pool caching; doubling these resources reduces query execution time and improves concurrency handling.

Answer

Disable binary logging to reduce write I/O.

Answer

Increase the storage size to 200 GB to improve IOPS.

Answer

Decrease the max_connections parameter to reduce overhead.

Question 18

Which THREE factors should you consider when configuring Cloud SQL for MySQL query caching to optimize performance?

Accepted Answer

High write workloads can lead to frequent cache invalidation, reducing its effectiveness.. Option A is correct because in Cloud SQL for MySQL, high write workloads cause frequent updates to tables, which invalidates the query cache entries for those tables. This means the cache must be repopulated often, reducing the hit rate and potentially adding overhead from cache maintenance, making it less effective for performance optimization.

Answer

Query cache uses disk storage for cached results.

Answer

Prepared statements always bypass the query cache.

Question 19

Your Spanner instance is running a workload with high read throughput. You notice that read latency has increased significantly. Upon investigating, you find that the instance is experiencing high CPU utilization on the Spanner nodes. The workload consists of many small point lookups (reads by primary key). Which action is most likely to reduce read latency?

Accepted Answer

Use stale reads with a timestamp bound to allow reads from replicas.. B is correct because stale reads allow Cloud Spanner to serve read requests from read-only replicas, which offloads CPU-intensive processing from the leading replica nodes. By using a timestamp bound (e.g., exact_staleness or max_staleness), the workload can tolerate slightly outdated data, reducing the load on the nodes and lowering read latency for point lookups.

Answer

Add secondary indexes on the primary key columns.

Answer

Redesign the schema to use interleaved tables.

Answer

Reduce the number of Spanner nodes to lower CPU overhead.

Question 20

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.. Option C is correct because 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.